Query Execution Performance

This is a useful script to test query execution time and view the IO statistics. This is especially useful for developers to create efficient queries. This utility simply measures the execution time for your query. It also returns IO Statistics, such as scan count, logical reads and more. Just paste in your query and execute.


SET STATISTICS IO ON

DECLARE @Start_alwaysUniqueVar DATETIME
DECLARE @End_alwaysUniqueVar DATETIME

SET @Start_alwaysUniqueVar = GETDATE();

----------------------------------------
-- Place code to test below this line.
----------------------------------------

SELECT  CustomerId
FROM    Customers
WHERE   CustomerId = 123456;

----------------------------------------
-- Place code to test above this line.
----------------------------------------

SET @End_alwaysUniqueVar = GETDATE();

PRINT 'Execution time:';
PRINT DATEDIFF(ms,@Start_alwaysUniqueVar, @End_alwaysUniqueVar);

SET STATISTICS IO OFF



Download the file here.

I recommend that developers closely analyze not just the execution duration, but also the IO statistics and the execution plan.

To learn more about these topics check out the following resources:

• SQL Server Books Online: Analyzing a Query

• SQL Server Books Online: Displaying Graphical Execution Plans

• SQL Server Books Online: SET STATISTICS IO