Query Execution Steps

Parse time is the time spent during checking SQL statement for syntax errors, breaking the command up into component parts, and producing an internal execution tree

Compile time is time spent during compiling an execution plan in cache memory from the execution tree that has just been produced.

Execution time is total time spent during execution of compiled plan.

It is really a good idea to split this kind of stored procedure to sub procedures that would help to reduce the parse and compile time.

--Clear the cache and buffer

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

 

--How much time a query is taking can be best measured by setting STATISTICS TIME ON

 

SET STATISTICS TIME ON

 

--Execute the Stored procedure

 

EXEC ExecutionTimeTest 'Supervisor', 123037

 

SET STATISTICS TIME  OFF

SQL Server parse and compile time:

   CPU time = 281 ms, elapsed time = 365 ms.

 

SQL Server Execution Times:

   CPU time = 140 ms,  elapsed time = 209 ms.

 

SQL Server Execution Times:

  CPU time = 421 ms,  elapsed time = 574 ms.