Query Performance Investigation

• SQLSACT

  SSC-Insane

  Points: 21838

  More actions

  September 12, 2012 at 1:24 am

  #261431

  Hi All

  I'm using the following script to investigate the plan cache and query performance

  I wanted to confirm something,

  What is the difference between the Statement Text column and the text column?

  Any help would be great

  Thanks

  SELECT top 20

  SUBSTRING(t.text, ( s.statement_start_offset / 2 ) + 1,

  ( ( CASE statement_end_offset

  WHEN -1 THEN DATALENGTH(t.text)

  ELSE s.statement_end_offset

  END - s.statement_start_offset ) / 2 ) + 1)

  AS statement_text,

  text,

  objtype,

  cacheobjtype,

  usecounts,

  last_execution_time,

  total_worker_time,

  total_worker_time / execution_count AS [Avg CPU Time],

  execution_count ,

  qp.query_plan

  FROM sys.dm_exec_query_stats AS s

  inner join sys.dm_exec_cached_plans cp

  on s.plan_handle = cp.plan_handle

  CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t

  CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) qp

  order by total_worker_time desc

  Thanks

• t.brown 89142

  SSCommitted

  Points: 1530

  More actions

  September 12, 2012 at 2:17 am

  #1536594

  It appears that statement_text is what is actually executed, but text contains additional parts of the batch, such as parameters, or IF statement.

  Checking your script on my own database, I see things like (to take a trivial example)

  Statement text = SELECT * FROM mytable

  text = IF (1=1) BEGIN SELECT * FROM mytable END ELSE SELECT * FROM YourTable END

  So I'd guess it only caches the plan for statement_text