Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Performance Investigation Expand / Collapse
Author
Message
Posted Wednesday, September 12, 2012 1:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, November 20, 2014 2:08 AM
Points: 1,380, Visits: 2,703
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
Post #1357816
Posted Wednesday, September 12, 2012 2:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 9:02 AM
Points: 114, Visits: 140
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
Post #1357834
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse