Getting accurate execution time in SQL Server SSMS

If you are using SQL Server Management Studio (SSMS) to analyze the SQL statements, checking the time taken to execute an sql statement is one among the important analyses. There are several ways to find the execution time of a statement in SQL Server. The easiest way is to execute the query and see the time displayed at the status bar of the SSMS. In it, you can see the time taken for execution to a minimum of seconds. The statement’s execution time in milliseconds is rounded to the nearest second and displayed in the status bar which is not an accurate execution time.

Default execution time display in SSMS

So, what to do if you want to get the accurate execution time up to its milliseconds? Here, I have discussed two simple methods in SSMS to get the accurate execution time in milliseconds.

Using SET STATISTICS TIME

In this method, you have to set the statistics T-SQL statement STATISTICS TIME to ON before executing the statement. Here is an example.

USE WideWorldImporters;  
GO

/* Switch on statistics time */
SET STATISTICS TIME ON; 

/* Your SQL Statement */
SELECT * FROM sales.Invoices;

/* Switch off statistics time */
SET STATISTICS TIME OFF; 
GO

/* RESULT */
(70510 rows affected)

 SQL Server Execution Times:
   CPU time = 1297 ms,  elapsed time = 2133 ms.

Completion time: 2021-10-08T10:16:50.0039661+05:30
Getting Accurate Execution Time using STATISTICS TIME

In this method, you can get the total elapsed time and the CPU time as well.

Using Client Statistics

Another method is to use the Client Statistics option for the Query Window in SSMS. In order to enable and use the Client Statistics option:

  1. Go to Menu >> Query >> Select Include client Statistics. Enabling Client Statistics Tims
  2. Execute your query.
  3. In the results panel, you can see a new tab Client Statistics.
  4. Go to the Client Statistics tab to see the execution time. Getting Accurate Execution Time using Client Statistics option

NOTE: The advantage of using client statistics is, you can execute your statement multiple times to get the average execution time, as seen in the image above. This will help you to perform multiple trials and get the average execution time.

Reference


Related Articles & Tools

Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.