Performance decreases when you run a SELECT ..INTO query after you upgrade to SQL Server 2012 and later

Extended support for SQL Server 2005 ended on April 12, 2016

If you are still running SQL Server 2005, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

Symptoms
After you upgrade from Microsoft SQL Server 2008 R2 or an earlier version to SQL Server 2012 or a later version, you may notice that SELECT... INTO queries that contain user-defined functions take a longer time to complete than in the earlier versions.
Cause
This issue occurs because that SELECT..INTO statements that contain user-defined functions (UDFs) are fully logged operations and take additional time to complete in SQL Server 2012 and later versions.

Note Any SELECT..INTO statements that contain user-defined functions are minimally logged operations in earlier versions (SQL Server 2008 R2 and earlier). This change was introduced to make sure of data integrity, as the user-defined function may perform read/write operations on the same object and can cause data corruption if minimal logging is enabled.
Workaround
If the user-defined functions that are used in the SELECT...INTO statement don't perform any data access operations, you can specify the SCHEMABINDING clause for the user-defined functions, which will set the derived UserDataAccess property for those user-defined functions to 0. After this change, SELECT...INTO statements will be minimally logged. For more information, see the sample in the blog for an example on using SCHEMABINDING.

Note If the statement still references at least one user-defined function that has this property set to 1, the operation is fully logged.
More information
The following sample code demonstrates the difference in behavior between SQL Server 2008 R2 and SQL Server 2012 or 2014:

create database DB1gouse DB1gocreate function dbo.MyTrim (@name as varchar(100))returns varchar (100)asbeginreturn (RTRIM(ltrim(@name)))endgocreate table dbo.tab_prod (c1 int, c2 varchar(10))godeclare @a int set @a = 1while @a <= 100000begin insert into tab_prodvalues (@a , '  test ')set @a = @a + 1endbegin transelect  *,  dbo.mytrim(c2) as trimc2 into tab_test from tab_prod

The following table compares the CPU time for a select-into operation in SQL Server 2008 R2 with that of SQL Server 2014:

VersionExecution Time (CPU time)
SQL Server 2008 R2719 ms
SQL Server 20141360 ms

The following table compares transaction log usage for a select-into operation in SQL Server 2008 R2 with that of SQL Server 2014:

VersionDatabase nameLog size (MB)Log space used (%)Status
SQL Server 2008 R2DB10.742187551.578950
SQL Server 2014DB132.1796938.44380
Important note The results in these tables are just an example of the change in behavior between SQL Server 2008 R2 and SQL Server 2014 and are very specific to the lab environment that was used for this test. The actual performance difference in your environment will depend on the hardware that your SQL instance is running on.
Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.
Properties

Article ID: 3144525 - Last Review: 03/03/2016 22:28:00 - Revision: 1.0

Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Express Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Workgroup Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2008 Developer, Microsoft SQL Server 2008 Enterprise, Microsoft SQL Server 2008 Express, Microsoft SQL Server 2008 Standard, Microsoft SQL Server 2008 Web, Microsoft SQL Server 2008 Workgroup, Microsoft SQL Server 2008 R2 Datacenter, Microsoft SQL Server 2008 R2 Developer, Microsoft SQL Server 2008 R2 Enterprise, Microsoft SQL Server 2008 R2 Express, Microsoft SQL Server 2008 R2 Standard, Microsoft SQL Server 2008 R2 Web, Microsoft SQL Server 2008 R2 Workgroup

  • kbsurveynew kbtshoot kbexpertiseadvanced KB3144525
Feedback