Using SET STATISTICS TIME ON

The SET STATISTICS TIME command is used in order to identify parse, compile, and execution times that occur on the server-side. It is important to note, these times do not include the time it takes to transfer the data to the client.

In order to turn on, simple execute the statement:

[cc lang=”sql”]
SET STATISTICS TIME ON
[/cc]

Once the statement is executed, it is turned on for the entire session, and will stay on until it is turned off. (In order to turn it off, replace the ON keyword above with OFF).

After the statement is turned on and queries are executed, the parse, compile & execution times are displayed in the messages tab next to the results tab of the query results.

Here is an example:

For the results above, we first see two “SQL Server Parse and Compile times”. For both of them in this example they are 0. The first result, relates to how long it took to compile the actual “EXEC” statement. The second parse and compile relates to how long it took to compile the entire procedure. Since it is 0, this means the stored procedure is using an existing execution plan, meaning it has already been compiled.

The next two lines relate to the two statements that exist in the stored procedure.  The first took 123 ms, while the next took 210 ms.  The last statement is the overall time it took to execute the procedure.

Overall, the SET STATISTICS TIME statement is an invaluable statement in your arsenal of SQL optimization.