asked
Where is my query plan?
I whilst working on an issue today, it looked like query plans for a stored proc were not cached. I must note that the SP was complex and used temp tables. I used my favourite diagnosis code and even though the query was running there was no plan. My code: SELECT CURRENT_TIMESTAMP AS qtime , DB_NAME(er.database_id) AS db_name , er.session_id , er.blocking_session_id , er.status , host_name , login_name , er.wait_type , er.wait_resource , er.wait_time , er.start_time , er.logical_reads , er.Writes , er.CPU_time , eh.text AS sql_text , bh.text AS blocking_text , dm_qp.query_plan FROM ( sys.dm_exec_requests er OUTER APPLY sys.dm_exec_sql_text(sql_handle) eh ) LEFT OUTER JOIN ( sys.dm_exec_requests br OUTER APPLY sys.dm_exec_sql_text(sql_handle) bh ) ON er.blocking_session_id = br.session_id LEFT OUTER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id OUTER APPLY sys.dm_exec_query_plan(er.plan_handle) dm_qp STRIPPING OUT THE UNNECESSARY TO GIVE A SIMPLE WORKING EXAMPLE: SELECT er.* , dm_qp.query_plan FROM sys.dm_exec_requests er OUTER APPLY sys.dm_exec_query_plan(er.plan_handle) dm_qp Why is there no plan even though the query is running?? If there are temp tables does SQL Server generate the plan as temp tables are generated? At the end does SQL Server not cache the plan because there is no value in it? Cheers, Half
It might be that there's something about the query you're looking for - what is it?
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
dm_qp.query_plan returns NULL. The SP is company confidential and produced our developers. Although complex it is cleanly written. A couple of temp tables are generated by complexe queries. The results are fed into other SPs or depending on the logic route taken the SP returns results. BUT we can simplify the question further. Run the following query across a number of servers SELECT er.*,dm_qp.query_plan FROM sys.dm_exec_requests er OUTER APPLY sys.dm_exec_query_plan(er.plan_handle) dm_qp where status in ('running', 'runnable' , 'suspended') Ocassionly one of the query plans will be null. Naturally the probability of NULL seems to increase during a major incient when the one thing that you really need is to look at a query plan. If anybody has any ideas I would be glad to hear them.
In that instance there could be DDL queries which don't get plans.
If it is complex then this may have bearing... > Due to a limitation in the number of nested levels allowed in the xml data type, sys.dm_exec_query_plan cannot return query plans that meet or exceed 128 levels of nested elements. In earlier versions of SQL Server, this condition prevented the query plan from returning and generates error 6335. In SQL Server 2005 Service Pack 2 and later versions, the query_plan column returns NULL. You can use the sys.dm_exec_text_query_plan (Transact-SQL) dynamic management function to return the output of the query plan in text format. From BOL
17 People are following this question.
