SQL Server and UDF’s Performance Issues

Recently this issue happened once we upgraded a database from SQL Server 2005 to SQL Server 2014 to meet the compliance. Once the upgrading process is done, users tried to do the User Acceptance Test (UAT) to make sure every single functionality in the application and also the database performance still remain intact. A part of the application is doing some BULK INSERT operation to move certain data rows from a table to historical table (I know it is not the best practice to do), this operation took less than a minute to be completed in SQL Server 2005 but it takes few minutes to be completed in SQL Server 2014, Client expected to see some performance gains instead of losing it. After my investigations I found that there is a User Defined Function in Select…Into statement. The UDF is doing some calculation for every single row once the row is moved to the archived/history table.
Based on the findings I tried to simulate the environment in our own server to compare the Transaction activities in the BULK Operation with UDF and without UDF to observe how SQL Server behaves toward this BULK Operation. The following statements are for regenerate the performance issue.

[codesyntax lang=”tsql”]

Create Database Test;
go
Use Test;
go
Create Table Active (ID Bigint Identity(1,1), Padding Binary(10) Default 0XFFF, LastUpdate datetime Default SysDateTime());
Go
Insert Into Active Default Values
Go 3000
go
Create Function GetRecordAge(@LastUpdate as Datetime)
Returns  bigint
as begin
return (Select DateDiff(MINUTE,@LastUpdate,Sysdatetime()));
end
go
Select ID, Padding,LastUpdate from Active;
–Total Records: 3000
go
select * from fn_dblog(null,null);
–Current LSN: 0000002b:00000037:0002;
go
Select ID,Padding,dbo.GetRecordAge(LastUpdate) As [MinutesOld] Into History From Active;

[/codesyntax]

As you see on the above statements, there is a UDF that does simple calculation and there are one table called active and also the Select…Into statements that does the data movement operation.

At the next step, I took a deep look into the Transaction Log file by using the following statements:-

[codesyntax lang=”tsql”]

select * from fn_dblog(null,null);

[/codesyntax]

 

 

The fn_dblog() function reads the entire transaction log and dump all the information, as you see at below , it shows that SQL Server does fully logged the entire transactions of the Select…Into with the UDF.

[codesyntax lang=”tsql”]

select Count(*),Max([Current LSN]) from fn_dblog(null,null) where [Current LSN]>'0000002b:00000037:0002'
--Transactions Log Record Counts: 3200
--Current LSN: 0000002c:0000010f:0121

[/codesyntax]

 

And below shows that SQL Server does minimally logged the entire transactions of the Select…Into without UDF.

[codesyntax lang=”tsql”]

Select ID,Padding, DateDiff(MINUTE,LastUpdate,Sysdatetime()) As [MinutesOld] Into History2 From Active;

select Count(*),Max([Current LSN]) from fn_dblog(null,null) where [Current LSN]>'0000002c:0000010f:0121'
--Transactions Log Record Counts:142
--Current LSN: 0000002c:0000015b:0071

[/codesyntax]

 

Then what is the solution to rectify this performance issue? I thought of the following configurations before finding the real solutions:-

  1. Enable the Instant File Initialization (But it does not work for Transaction Log files)
  2. Enable MAXDOP (It did not work as well)
  3. Disable Non-Clustered Indexes on the Archive/History table (It did not improve that much)
  4. Move the Archive/History table into a filegroup with multiple files. (It did not improve that much)

After reading the entire MSDN regarding User Defined Functions and their behavior and the changes has been done in SQL Server 2014, I found that in SQL Server 2012 and later the Select…Into operation will be fully logged due to data integrity issue as the UDF might read/write data in the same object that cause data corruption, which was not enabled on the earlier versions. To rectify this issue we need to make sure that UserDataAccess option in the UDF is set to 0 by enabling Schemabinding option on the UDF.

[info]If the Select…Into statement still references at least one user-defined function that has UserDataAccess property set to 1, the operation is fully logged.[/info]

As you see in the below statements, after I have enabled the Schemabinding option on the scalar function, the number of transaction log records reduced significantly.

[codesyntax lang=”tsql”]

Alter Function GetRecordAge(@LastUpdate as Datetime)
Returns  bigint With Schemabinding
as begin
	 return (Select DateDiff(MINUTE,@LastUpdate,Sysdatetime()));
end

go

Select ID,Padding, dbo.GetRecordAge(LastUpdate) As [MinutesOld] Into History3 From Active;

select Count(*),Max([Current LSN]) from fn_dblog(null,null) where [Current LSN]>'0000002c:0000015b:0071'
--Transaction Log Record Counts: 194

[/codesyntax]

 

 

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

About The Author

Search Articles

Categories

Follow Fard Solutions

Share Your Comments

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788

info@fard-solutions.com

Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan