Measure TSQL Statement Performance

Now another method the measure elapsed time is to use a SET STATISTICS command to display the elapsed time for a query.  Below is a screen shot that shows the elapsed time statistics in the query results pane for each statement executed in my query window:

Here I executed the “SET STATISTICS TIME ON” statement prior to running my simple SELECT statement.  Note how the “elapsed time” statistics are displayed in the result pane, along with the “CPU time”, and output from my PRINT statement.   The “SET STASTISTICS TIME ON” statement displays the amount of time it takes to parse, compile and execute each statement.  This statement is useful if you are only executing a few statements.  If you are executing a large number of statements like my first looping example then the number of lines of output would be very large making it hard to read. 

Now sometimes you need to measure how long it takes to execute different chunks of code in a batch, not just a specific TSQL statement.  This would be useful if you had a large script or stored procedure that takes a long time, and you want to figure out which part takes the longest.  To create elapsed time for sections of code we can use the following method:

Here you can see that I ran two different simple loops.  For each loop I calculated the time it takes to process each loop.   I do this by capturing the begin time of each chunk of code into a variable named @START.  Then when the loop finishes I print out the time difference between the @START DATETIME variable and the current value returned from the GETDATE() function.  In my case it took 16 ms to process a through my first loop 10,000 times, where as it took 110 second to process through the second loop 90,000 times.