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

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

Problem

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?

Solution

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.

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 MSSQLTips.com:

About the author

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

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