Script to Capture Performance Stats When Testing SQL Server T-SQL Statements
I need to test a SQL Server T-SQL statement and collect information on its elapsed time. How can I perform a thorough, repeatable test of my statement?
Whether you are a newbie to T-SQL or an experienced professional, you should always test your statements not only for accuracy, but also for performance. Executing a command multiple times and collecting the average elapsed time is a quick way to ensure that your code is on the right path to acceptable performance. Adjusting the statement or a database object such as an index, and then repeating the command to compare how the adjustments affect the performance is also desirable.
In this tip, we will look at a T-SQL script that contains a nested loop. The inner loop executes a command a preset number of times and the outer loop will repeat the command or set of commands another preset number of times. There are numerous comments in the script below that will explain what is happening.
For this test, I am creating a table dbo.testPeformance and I will do 100,000 inserts into the table and do this test 5 times. Then we will see how long this took for each of these 5 tests.
Setup the Test
The first thing I need to do for my test is create this table where I will test the inserts.
--Create a table for testing create table dbo.testPerformance ( pk int identity(1,1) primary key, testDate date, testInteger int, testVarchar varchar(10) ) go
The command that I want to test is the following and is in the larger script below.
insert into dbo.testPerformance values (getdate(), round(rand()*1000,0), 'testString')
Also, I want to truncate the dbo.testPerformance table before my next interation starts. I will use this command:
truncate table dbo.testPerformance
Code to Run the Test
Here is the code that will loop through the test.
--The outer loop - the number of times to repeat the test declare @numberOfTests integer = 5 --The inner loop - the number of times to repeat the code declare @numberOfIterations integer = 100000 --The following statement will display a message to the user select 'Starting performance test' as MessageToUser --Declare the variables declare @overallStartTime datetime = getdate() declare @testStartTime datetime = getdate() declare @testEndTime datetime = getdate() declare @testElapsedTime int = 0 declare @totalElapsedTime bigint = 0 declare @averageElapsedTime float = 0 declare @testRun integer = 1 declare @iteration integer = 1 begin transaction --start the outer loop while @testRun <= @numberOfTests begin set @iteration=1 set @testStartTime = getdate() ----------------------------------------- --the command below is part of my test truncate table dbo.testPerformance --end of code ----------------------------------------- --start the inner loop while @iteration <= @numberOfIterations begin ----------------------------------------- --the command below is part of my test insert into dbo.testPerformance values (getdate(), round(rand()*1000,0), 'testString') --end of code ----------------------------------------- --advance the inner loop counter set @iteration = @iteration + 1 --commit after 1000 inserts, this can be adjusted to see how the performance is affected if (@iteration%1000=0) begin commit begin transaction end end set @testEndTime = getdate() --get the elapsed time in milliseconds for the test set @testElapsedTime = datediff(ms,@testStartTime,@testEndTime) --accumulate the total elapsed time set @totalElapsedTime = @totalElapsedTime+@testElapsedTime --display the elapsed time for the test select 'testRun '+cast(@testRun as varchar(10)) + ', elapsed time = ' +cast(@testElapsedTime as varchar(10)) + 'ms' as MessageToUser --advance the outer loop counter set @testRun=@testRun+1 end commit declare @overallEndTime datetime = getdate() select @overallStartTime as OverallStartTime, @overallEndTime as OverallEndTime, datediff(ms,@overallStartTime,@overallEndTime) as OverallElapsedTime, @totalElapsedTime/cast(@numberOfTests as float) as AverageElapsedTime go
The output of the script is shown below.
Change the T-SQL command to experiment with different situations. You can also adjust the script as needed to display statistics that are required.
Also, please check out other T-SQL tips on MSSQLTips.com:
- SQL Server T-SQL Code to Calculate a Moving Average
- Avoiding erroneous results when using T-SQL Trigonometric Functions in SQL Server 2012
- Transforming Cartesian Coordinates to Spherical Coordinates in SQL Server with T-SQL
- Using T-SQL to Perform Z-Score Column Normalization in SQL Server
About the author
View all my tips
Article Last Updated: 2017-04-06