SET STATISTICS TIME ON – how to read results?

Leave a comment (0) Go to comments

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

In order to turn on, simple execute the statement:

 SET STATISTICS TIME ON

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).


Let’s understand this with example

SET STATISTICS TIME ON   how to read results? Understanding SET STATISTICS TIME ON

The results above, we first see “SQL Server Parse and Compile times” followed by "SQL Server Execution Times". The parse and compile relates to how long it took to compile the entire statement. This is information concerning how much time the SQL Server program took to do the following:

  1. Parse your SQL command by checking for syntax errors, breaking the command up into component parts, and producing an internal execution tree.
  2. Compile an execution plan in cache memory from the execution tree that has just been produced.

Note that this includes the amount of time that is required to move the data from the associated tables into the memory cache; therefore, if you execute the same query twice, and the table data is still resident in the cache, your Parse and Compile times will be considerably lower for the second execution of the query.

In this example it took 4ms CPU time and 4ms of elapsed time to complete parsing and compilation. Elapsed time includes (CPUS TIME , IO Time etc). So elapsed time >= CPU TIME. In case this is 0, which means the statement is using an existing execution plan, meaning it has already been compiled.

"SQL Server Execution Times", this is the the overall time it took to execute the statement.In out case it took 0 ms CPU time and 2ms of elapsed time, which means 2 ms for IO processing

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

Note :

Once this 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, this has a additional data to travel, in case you running this query on the remote session.

Microsoft SQL Server is unable to provide accurate statistics in fiber mode, which is activated when you enable the lightweight pooling configuration option

Tags : Understanding SET STATISTICS IO,set statistics time,Using SET STATISTICS TIME ON, Interpreting Results Of SET STATISTICS , Interpret Results Of SET STATISTICS ,How to output execution time for SQL statements in a batch?,How to calculate response time,CPU Time (sys.dm_exec_query_stats vs. SET STATISTICS TIME ON,Profiler vs SET STATISTICS IO,Need help interpreting results of SET STATISTICS TIME ON,SET STATISTICS TIME, analyse SET STATISTICS TIME, analyze SET STATISTICS TIME, understand SET STATISTICS TIME, describe SET STATISTICS TIME, understand result returned by SET STATISTICS TIME, SQL Server parse and compile time: ,CPU time = 4 ms, elapsed time = 4 ms.,SQL Server Execution Times:,CPU time = 0 ms,  elapsed time = 2 ms, SQL Server parse and compile time: CPU time = 4 ms, elapsed time = 4 ms SQL Server Execution Times: CPU time = 0 ms,  elapsed time = 2 ms

EOF - SET STATISTICS TIME ON – how to read results?, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.