Question related to STATISTICS IO

• shahm10

  SSCrazy

  Points: 2217

  More actions

  February 12, 2010 at 5:32 am

  #138748

  Eg:

  SET STATISTICS IO ON

  SELECT * FROM Table-Name

  SET STATISTICS IO OFF

  STATISTICS IO generates IO related information.

  Is there any way of inserting those information directly into a table?

  Thanks for your time.

• Shriji

  SSCommitted

  Points: 1881

  More actions

  February 12, 2010 at 7:58 am

  #1118048

  You can get the same information by running following query

  SELECT * FROM

  sys.dm_exec_query_stats

  CROSS APPLY

  sys.dm_exec_sql_text(sql_handle)

  I hope this helps

• Shriji

  SSCommitted

  Points: 1881

  More actions

  February 12, 2010 at 8:00 am

  #1118051

  this one as well..

  SELECT SUBSTRING(text,

  -- starting value for substring

  CASE WHEN statement_start_offset = 0

  OR statement_start_offset IS NULL

  THEN 1

  ELSE statement_start_offset/2 + 1 END,

  -- ending value for substring

  CASE WHEN statement_end_offset = 0

  OR statement_end_offset = -1

  OR statement_end_offset IS NULL

  THEN LEN(text)

  ELSE statement_end_offset/2 END -

  CASE WHEN statement_start_offset = 0

  OR statement_start_offset IS NULL

  THEN 1

  ELSE statement_start_offset/2 END + 1

  ) AS TSQL,

  *

  FROM sys.dm_exec_query_stats

  CROSS APPLY sys.dm_exec_sql_text(sql_handle);