Script to Capture Performance Stats When Testing SQL Server T-SQL Statements

By:   |   Comments (2)   |   Related: > TSQL


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)

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

  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

    --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 transaction

  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

declare @overallEndTime datetime = getdate()

@overallStartTime as OverallStartTime, 
@overallEndTime as OverallEndTime, 
datediff(ms,@overallStartTime,@overallEndTime) as OverallElapsedTime,
@totalElapsedTime/cast(@numberOfTests as float) as AverageElapsedTime

The output of the script is shown below.

Output from the command
Next Steps

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Dallas Snider Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, April 6, 2017 - 12:38:38 PM - jeff_yao Back To Top (54362)

Strictly speaking, this is not an ideally designed testing framework with not-so-good example. A few things need to considered, such as execution plan cached? any I/O, CPU acitvities stability during each batch? Any 

The point is for valid and convincing test result, this is not a good QA test and is difficult to draw conclusions based on such "duration time" result. 


Thursday, April 6, 2017 - 3:51:56 AM - Ruslan K. Back To Top (54335)

There is no point to use datetime instead of datetime2, you just lose accuracy.

get free sql tips
agree to terms