Share via

T-SQL Programming – How to see Query execution time in milliseconds

  • Article

1)The objective of the Article

Many times, we need to work on a small set of masked data in the projects because of data privacy policies of the client, Technical data restrictions, International trade regulations etc. In such projects, Team will develop the application with a small test of masked data provided by the client. When the developed product on this small set of data goes to QA or production, we normally face Performance issues as the code was return on the based on a small set of data. Then there is a need to optimize all the stored procedure, functions & other DB objects which is turned into re-work. To avoid this issue at a certain extent, we can measure the performance of the query in milliseconds to understand the nature of data & its processing and can tune the query which will be helpful when we move the queries on a large database.

Microsoft SQL Server Management Studio provides a feature to measure the query performance in milliseconds.

2) Steps to measure the query performance in milliseconds

Following are the steps to measure the query performance in milliseconds.

1) Go to SQL Server management studio

2) Connect to the database

3) Click on New query

4) Paste the query on the window for which you want to analyze the performance

5) Click on the “Query” menu, select “Include Client Statistics”

  

6) Execute the Query. Below is the sample query from AdventureWorks2012 DB

SELECT ProductID,Name,ProductNumber FROM  ProductDetail

7) You can see the query output in the result tab and output in seconds. For smaller data, execution time will come 00:00:00 by which we will not be able to understand the exact query performance & there is a need to go to much dipper level.

8) For seeing the performance statistics in milliseconds, click on Client Statistics

9) Client statistics provides the reporting for multiple trials, you can see the data for Up to 10 trails. A maximum of 10 trials is averaged in the display window. When the 11th trial comes into the result, the very first trial is dropped, keeping the total average of latest 10 trials.