Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help with Plan Cache Query


Help with Plan Cache Query

Author
Message
UncleBoris
UncleBoris
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 696
I have had times when I would like to get the Query Plan from the Plan Cache.

I have Query 1 below (MSDN) which give me the query text and last execution times but not the Query Plan.

Or Query 2 below (also from MSDN) that give me the Query Plans but not the Last_execution times or other info like READ Write stats etc etc

I am having to run a separate query to get this [SELECT * FROM sys.dm_exec_query_plan(plan_handle) ] using the returned Plan Handle.

Is their any way to get everything in the one query? I have tried to cross apply sys.dm_exec_query_stats to Query 2 to give me the execution times but that was a guess and not right.

thanks



Query 1
SELECT dbid,
sql_handle,
(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,
( (CASE WHEN statement_end_offset = -1
THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement,
execution_count,
plan_generation_num,
last_execution_time,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes,
plan_handle
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
WHERE s2.objectid is null
ORDER BY s1.last_execution_time DESC

Query 2
SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan,*
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47206 Visits: 44367
Just join sys.dm_exec_query_stats (as used in the first query) to sys.dm_exec_cached_plans (used in the second query) on the plan_handle


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


SQLSACT
SQLSACT
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1456 Visits: 2931
Try this

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


GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47206 Visits: 44367
You might want to edit that query, as it has filters and sorting that was no where in either of the queries the OP posted. It's a query on those two DMVs sure, but it's not the same as what the OP wants.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


SQLSACT
SQLSACT
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1456 Visits: 2931
GilaMonster (6/13/2013)
You might want to edit that query, as it has filters and sorting that was no where in either of the queries the OP posted. It's a query on those two DMVs sure, but it's not the same as what the OP wants.


Apologies - Done
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47206 Visits: 44367
It still has a different top, different where, different select list and a different order to the OP's queries.

Boris, try this, it should be a combination of your two. I did replace the database ID with the database name.

Do note that the two DMVs return different numbers of rows, query stats returns a row per statement, cached plans a row per batch. Hence if you have procedures or batches with more than one statement, you'll see the same plan multiple times.

SELECT  DB_NAME(st.dbid) AS DatabaseName ,
SUBSTRING(st.text, ( qs.statement_start_offset / 2 ) + 1, ( ( CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset ) / 2 ) + 1) AS sql_statement ,
execution_count ,
plan_generation_num ,
last_execution_time ,
total_worker_time ,
last_worker_time ,
min_worker_time ,
max_worker_time ,
total_physical_reads ,
last_physical_reads ,
min_physical_reads ,
max_physical_reads ,
total_logical_writes ,
last_logical_writes ,
min_logical_writes ,
max_logical_writes ,
UseCounts ,
Cacheobjtype ,
Objtype ,
query_plan
FROM sys.dm_exec_query_stats AS qs
INNER JOIN sys.dm_exec_cached_plans AS cp ON qs.plan_handle = cp.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE st.objectid IS NULL
ORDER BY qs.last_execution_time DESC




Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


UncleBoris
UncleBoris
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 696
Thanks to both Posters.

Out of curiosity -- what does this line "WHERE st.objectid IS NULL" do?

I notice when this line is in the query no database name is returned but when this line is not in the Query or changed to IS NOT NULL I see the database name from DB_NAME(st.dbid) AS DatabaseName


thanks
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47206 Visits: 44367
UncleBoris (6/13/2013)
Out of curiosity -- what does this line "WHERE st.objectid IS NULL" do?


Filters out rows in the statement text that have object IDs and database IDs, ie stored procedures. It's an odd way of filtering for ad-hoc SQL statements only, was in your original query so I left it there, but it's a really odd filter to use. A more obvious way of doing that would be to filter for ObyType IN ('Adhoc','Prepared')


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search