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

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 DB1

go



use DB1

go



create function dbo.MyTrim

(@name as varchar(100))

returns varchar (100)

as

begin

return (RTRIM(ltrim(@name)))

end

go



create table dbo.tab_prod

(c1 int, c2 varchar(10))

go





declare @a int

set @a = 1

while @a <= 100000

begin

insert into tab_prod

values (@a , ' test ')

set @a = @a + 1

end



begin tran

select *, 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.
Propriedades

ID do Artigo: 3144525 - Última Revisão: 3 de mar de 2016 - Revisão: 1

Comentários