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.

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:

Go to Menu >> Query >> Select Include client Statistics.
Execute your query.
In the results panel, you can see a new tab Client Statistics.
Go to the Client Statistics tab to see the execution time.

Advantages of Client Statistics

The main 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.

Other Methods

Apart from these two methods, I have discussed about another method to find the execution time up to its milliseconds In my article How To Get Execution Time In Milliseconds In SSMS?

Reference

Read more about the STATISTICS TIME statement at Microsoft Docs.