SQL Lion

Many times upon executing any T-SQL queries, we like to know the performance of the query at millisecond level. But SSMS provides us information at second level as below:

SELECT *
FROM   Purchasing.vVendor;

You will see this value “00:00:00” coming at right-bottom side of Query Window. The value indicates that the query took zero second to execute, but actually it means it took less than 1 second. And moreover, someone might be interested in tracking the number of logical or physical reads happening when the query executes.

 So, in order to monitor the exact compile time and execution time for a given query in T-SQL, enable the below options in SSMS. Navigate to Tools » Options » Query Execution » SQL Server » Advanced. Remember to close all query windows before configuring otherwise it will not reflect in the current query window.

But in order to reflect the changes in the current query window, right click anywhere on the query window and click on “Query Options…”

And in the dialog box, navigate to “Execution » Advanced” and enable “SET STATISTICS TIME” and “SET STATISTICS IO”.

Execute any query now:

SELECT *
FROM   Purchasing.vVendor;

OUTPUT (Click on “Messages” in output window):

Executing a view or procedure will also return the tables hit underneath the query along with the number of “logical reads” and “physical reads” happened at the table level.

Although this information is helpful but I would suggest to go for Query execution plan and SQL server profiler for serious optimization of queries.

To get a quick glance on the elapsed time for a bunch of queries, simply press “F4” to get Properties Window and you will get the necessary information. But here the elapsed time also includes connection time, printing results to output window, etc.

Waiting for your valuable comments. ☺