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


By:   |   Updated: 2017-04-06   |   Comments (2)   |   Related: More > T-SQL


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 = @[email protected]
  
  --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 @[email protected]+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.

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



Last Updated: 2017-04-06


get scripts

next tip button



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.

View all my tips





Comments For This Article




Thursday, April 06, 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 06, 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.



download





Recommended Reading

Cursor in SQL Server

Using MERGE in SQL Server to insert, update and delete at the same time

Rolling up multiple rows into a single row and column for SQL Server data

How to use @@ROWCOUNT in SQL Server

SQL Server Loop through Table Rows without Cursor








get free sql tips
agree to terms


Learn more about SQL Server tools