Search This Blog & Web

Tuesday, March 31, 2009

Reading Output statistics for SQL SERVER

I have lot of questions in my mind while start optimization on procedures. On of the question is how can we see what happens with SQL SERVER when a procedure executes? How system calculates overall procedure execution time? and many others.

I have found many published material on these topics and most suitable command to read Statistics for procedure is as below

This command will show you detail about

  • Execution time
  • Logical reads
  • Physical reads
  • read-ahead reads
  • Scan Count
  • CPU time

and other options you need to note for excessive query optimization.

SET Statistics IO ON
SET Statistics TIME ON

exec procedurename "parameters...."

SET Statistics IO OFF
SET Statistics TIME OFF

No comments: