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

1)      Objective of the Article

Many times, we need to work on small set of masked data in the projects because of data privacy policies of client, Technical data restrictions, International trade regulations etc. In such projects, Team will develop the application with small set of masked data provided by client. When the developed product on this small set of data goes to QA or production, we normally face Performance issues as code was return on the based on small set of data. Then there is a need to optimize all the stored procedure, functions & other DB objects which is turn into re-work. To avoid this issue at certain extent, we can measure the performance of 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 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 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 Upto 10 trails. A maximum of 10 trials are averaged in the display window. When the11th trial comes into the result, the very first trial is dropped, keeping total average of latest 10 trials.

---------------------------------------------End of Article---------------------------------------------------------