Blank values in the query_plan column

Question

Blank values in the query_plan column

Arsh

SSCertifiable

Points: 6354
More actions
August 9, 2017 at 8:43 am

#339220

Hi Everyone!
While querying the waits stats for the database during a performance bottleneck for one of the DB's I'm supporting , I noticed that some of the values for the query_plan column in the object sys.dm_exec_query_plan(plan_handle) were blank for some SP's/Code . Any comments on this ? Thank you.
I use the join of the objects below :
sys.dm_os_waiting_tasks
sys.dm_exec_requests
sys.dm_exec_sessions
sys.dm_exec_sql_text (sql_handle)
sys.dm_exec_query_plan (plan_handle)
Arshad

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

I found this article that nicely explains why we sometimes get null query_plan columns
https://blogs.msdn.microsoft.com/psssql/2016/07/13/why-am-i-getting-null-values-for-query_plan-from-sys-dm_exec_query_plan/

does that help?
i see the same behaviour when I run sp_whoisactive; I can get the sql_text, the sql_command, but some of my query_plan columns are null.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 2

Arsh - Wednesday, August 9, 2017 8:43 AM
Hi Everyone!
While querying the waits stats for the database during a performance bottleneck for one of the DB's I'm supporting , I noticed that some of the values for the query_plan column in the object sys.dm_exec_query_plan(plan_handle) were blank for some SP's/Code . Any comments on this ? Thank you.
I use the join of the objects below :
sys.dm_os_waiting_tasks
sys.dm_exec_requests
sys.dm_exec_sessions
sys.dm_exec_sql_text (sql_handle)
sys.dm_exec_query_plan (plan_handle)
Arshad

You may also not see a query plan if you have Optimize For Ad Hoc enabled. It will only store a plan stub until the second time a query gets called.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Arsh SSCertifiable Points: 6354 More actions · Answer 3

Grant Fritchey - Wednesday, August 9, 2017 10:17 AM
Arsh - Wednesday, August 9, 2017 8:43 AM
Hi Everyone!
While querying the waits stats for the database during a performance bottleneck for one of the DB's I'm supporting , I noticed that some of the values for the query_plan column in the object sys.dm_exec_query_plan(plan_handle) were blank for some SP's/Code . Any comments on this ? Thank you.
I use the join of the objects below :
sys.dm_os_waiting_tasks
sys.dm_exec_requests
sys.dm_exec_sessions
sys.dm_exec_sql_text (sql_handle)
sys.dm_exec_query_plan (plan_handle)
Arshad
You may also not see a query plan if you have Optimize For Ad Hoc enabled. It will only store a plan stub until the second time a query gets called.

Thank you Lowell and Grant for the useful info. Lowell , I followed the link you sent. Looks nearest to what I asked . Need to review one more time . Thank you.

Arshad