March 14, 2013 at 9:27 am
I am trying to get the execution plan for a very slow procedure that I have, and I can't. I am able to list the child queries and IO utilization, but my DMV can't display the graphical plan. Initially, I though was memory pressure on my PRO environment, but this is also happening on my test box, which it is totally isolated.
Here's my DMV, taken from "SQL Server DMVs In Action" book, wonderful book, by the way:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 100
[Total IO] = (qs.total_logical_reads + qs.total_logical_writes)
, [Average IO] = (qs.total_logical_reads + qs.total_logical_writes) /
qs.execution_count
, qs.execution_count
, SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE DB_NAME(qt.dbid) ='db_name' AND qt.text LIKE '%sproc_name%'
ORDER BY [Total IO] DESC, qs.execution_count DESC
The query_plan column displays NULL.
The store procedure uses temporary tables, not variable tables, but I do not think that's relevant anyway.
March 14, 2013 at 9:33 am
Presumably the plans are simply not in the cache any more. Can you re-run any of the queries and requery the DMV to see if it then appears?
'Only he who wanders finds new paths'
March 14, 2013 at 9:41 am
david.alcock (3/14/2013)
Presumably the plans are simply not in the cache any more. Can you re-run any of the queries and requery the DMV to see if it then appears?
Not following you ... sorry ...
I should get an execution plan for this store procedure, unless I have memory pressures.
Yeah, I've ran it several times and the DMV shows the slow T-SQL statement inside but never the main execution plan, which I really need so I can get the better picture.
March 14, 2013 at 10:39 am
Try with dm_exec_cached_plans:
SELECT
DatabaseName = db_name(st.dbid),
cp.objtype,
st.text,
qp.query_plan,
cp.usecounts,
cp.size_in_bytes,
cp.plan_handle
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle) qp
WHERE st.text NOT LIKE '%sys.%'
--AND st.dbid = DB_ID() -- current db
AND st.text LIKE '%YourTableOrProcedureName%'
ORDER BY st.text
March 14, 2013 at 10:42 am
There are a number of things that cause the plan not to be cached at all. If you want the estimated plan, easiest way is just to use the 'Display estimated execution plan' button in management studio.
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
March 14, 2013 at 1:20 pm
I might be confused, is it the xml plans that come up in the results, or an execution plan of the query you posted?
'Only he who wanders finds new paths'
March 14, 2013 at 3:59 pm
david.alcock (3/14/2013)
I might be confused, is it the xml plans that come up in the results, or an execution plan of the query you posted?
That column should provide a link to the graphical plan. Its coming NULL.
I guess I can get the actual or estimate, but wanted to know the technical reason why it's not being displayed via DMV.
March 14, 2013 at 4:00 pm
david.alcock (3/14/2013)
I might be confused, is it the xml plans that come up in the results, or an execution plan of the query you posted?
That column should provide a link to the graphical plan. Its coming NULL.
I guess I can get the actual or estimate, but wanted to know the technical reason why it's not being displayed via DMV.
March 15, 2013 at 3:23 am
Might help...
If the query plan that is specified by using plan_handle has been evicted from the plan cache, the query_plan column of the returned table is null. For example, this condition may occur if there is a time delay between when the plan handle was captured and when it was used with sys.dm_exec_query_plan.
•
Some Transact-SQL statements are not cached, such as bulk operation statements or statements containing string literals larger than 8 KB in size. XML Showplans for such statements cannot be retrieved by using sys.dm_exec_query_plan unless the batch is currently executing because they do not exist in the cache.
•
If a Transact-SQL batch or stored procedure contains a call to a user-defined function or a call to dynamic SQL, for example using EXEC (string), the compiled XML Showplan for the user-defined function is not included in the table returned by sys.dm_exec_query_plan for the batch or stored procedure. Instead, you must make a separate call to sys.dm_exec_query_plan for the plan handle that corresponds to the user-defined function.
'Only he who wanders finds new paths'
March 15, 2013 at 3:55 am
Please check is your query in the cache at all with this query:
-- Is my query/procedure in the cache ?
select * FROM sys.syscacheobjects
WHERE dbid<>32767 and dbid>4
and sql NOT LIKE '%sys.syscacheobjects%'
and sql NOT LIKE '%sys.dm%'
and sql LIKE '%MyTableOrProcedureName%' --<< here
ORDER BY sql
March 15, 2013 at 4:42 am
syscacheobjects is deprecated, will be removed in a future version, is included only for backward compatibility with SQL 2000 and should not be used. The replacement is the combination of sys.dm_exec_cached_plans, sys.dm_exec_plan_attributes, sys.dm_exec_sql_text and sys.dm_exec_cached_plan_dependent_objects
In fact, if you check the definition, syscacheobjects is just a view based on sys.dm_exec_cached_plans, sys.dm_exec_plan_attributes and sys.dm_exec_sql_text
CREATE VIEW sys.syscacheobjects (bucketid, cacheobjtype, objtype, objid, dbid, dbidexec, uid, refcounts,
usecounts, pagesused, setopts, langid, dateformat, status, lasttime, maxexectime, avgexectime, lastreads,
lastwrites, sqlbytes, sql) AS
SELECT pvt.bucketid, CONVERT(nvarchar(17), pvt.cacheobjtype), pvt.objtype,
CONVERT(int, pvt.objectid), CONVERT(smallint, pvt.dbid),
CONVERT(smallint, pvt.dbid_execute), CONVERT(smallint, pvt.user_id),
pvt.refcounts, pvt.usecounts, pvt.size_in_bytes / 8192,
CONVERT(int, pvt.set_options), CONVERT(smallint, pvt.language_id),
CONVERT(smallint, pvt.date_format), CONVERT(int, pvt.status),
CONVERT(bigint, 0), CONVERT(bigint, 0), CONVERT(bigint, 0), CONVERT(bigint, 0), CONVERT(bigint, 0),
CONVERT(int, LEN(CONVERT(nvarchar(max), fgs.text)) * 2), CONVERT(nvarchar(3900), fgs.text)
FROM (SELECT ecp.*, epa.attribute, epa.value
FROM sys.dm_exec_cached_plans ecp OUTER APPLY sys.dm_exec_plan_attributes(ecp.plan_handle) epa) as ecpa
PIVOT (MAX(ecpa.value) for ecpa.attribute IN ("set_options", "objectid", "dbid", "dbid_execute", "user_id", "language_id", "date_format", "status")) as pvt
OUTER APPLY sys.dm_exec_sql_text(pvt.plan_handle) fgs
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply