Performance decreases when you run a SELECT ..INTO query after you upgrade to SQL Server 2012 and later
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.
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.
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.
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:
|Version||Execution Time (CPU time)|
|SQL Server 2008 R2||719 ms|
|SQL Server 2014||1360 ms|
The following table compares transaction log usage for a select-into operation in SQL Server 2008 R2 with that of SQL Server 2014:
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.
|Version||Database name||Log size (MB)||Log space used (%)||Status|
|SQL Server 2008 R2||DB1||0.7421875||51.57895||0|
|SQL Server 2014||DB1||32.17969||38.4438||0|
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