Today we will see different ways to calculate the execution times of a SQL Query.

Typically it happens that we are tasked to performance tune certain queries to find out which queries or procedures are taking more time than expected. Here are 3 choices one could follow:

1. Using SQL Profiler: Look for the events SQL:BatchCompleted, RPC:completed and then capture the exectution times in Milliseconds.

2. Using Getdate():

[cc lang=”sql”]
Declare @start datetime, @end datetime, @timediffinMS int
select @start = getdate()
select *  from sys.syscomments
— OR
EXEC SP_WHO2
select @end = getdate()
select @timediffinMS = datediff (ms, @start, @end)
select @timediffinMS — this can be put in a table to analyze the time taken.

[/cc]

3. SET Command:

[cc lang=”sql”]
SET STATISTICS TIME ON
select * from sys.sysobjects
SET STATISTICS TIME OFF
[/cc]

Look for the last total CPU time and CPU elapsed times.

Note: Sometimes when you run these queries or procedures they remain in cache and hence run fast next time they are run, so please make sure to free up procedure cache using DBCC Dropcleanbuffers or DBCC Freeproccache or DBCC Freesystemcache (warning!! it has its own disadvantages of using above commands in production systems as they will remove all plans from plancache and all procedures will take more time or rarely less time to execute, so use with caution). The issue is that all procs get affected and not just the single proc you are trying to tune.

Also try to use CLIENT statistics option in SSMS that will average out based on a number of runs giving accurate picture avg. running time of the query.