May 7, 2009 at 1:43 pm
I query the system DMVs sys.dm_exec_cached_plans and sys.dm_exec_query_plan to get the cached query plan for a stored procedure and I get NULL for the query plan.
Does that mean that the plan is no longer in the cache, possibly because of memory pressure?
I thought the latest plan of each module is always kept in cache...
Here is my query for getting the cached plan:
--http://msdn.microsoft.com/en-us/library/ms187404.aspx
--Returns query plans of a specified stored procedure
--Run in database of interest
DECLARE @procName VARCHAR(1000);
SET @procName = '%myProcName%';
SELECT
P.usecounts
,P.plan_handle
,H.query_plan
,LEFT([sql].[text], 1000) as [text]
FROM
sys.dm_exec_cached_plans P
CROSS APPLY
sys.dm_exec_query_plan(plan_handle) H
OUTER APPLY
sys.dm_exec_sql_text (p.plan_handle) [sql]
WHERE objtype ='Proc' AND LEFT([sql].[text], 1000) LIKE @procName;
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 7, 2009 at 3:32 pm
No, plans get aged out if there is no more memory available in plan cache due to the need to hold plans for more recently used queries. This is a particular problem in systems where there are a lot of passthrough sql queries slung at the engine, is this an issue here?
If you have an appropriate DEV environment (ie the data volumes are similar to live) then you could run the query and use show actual execution plan, or, if you know when it's certainly going to run in live (only if this is not the case) - you could consider running profiler in live with showplan option and get it from there. This puts a bit of a strain on the server, though, so don't use it lightly, and only briefly - it's a bit of a last resort IMHO.
hth
Andrew
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
May 8, 2009 at 7:17 am
andrew gothard (5/7/2009)
No, plans get aged out if there is no more memory available in plan cache due to the need to hold plans for more recently used queries. This is a particular problem in systems where there are a lot of passthrough sql queries slung at the engine, is this an issue here?If you have an appropriate DEV environment (ie the data volumes are similar to live) then you could run the query and use show actual execution plan, or, if you know when it's certainly going to run in live (only if this is not the case) - you could consider running profiler in live with showplan option and get it from there. This puts a bit of a strain on the server, though, so don't use it lightly, and only briefly - it's a bit of a last resort IMHO.
hth
Andrew
Hmm, so from what you are saying it is a memory issue then.
I like the idea of looking at cached plans, even though there is always the possibility of recompilation next time a procedure is executed. It's just a more "economical" way of looking at performance issues, at least as a 1st approximation.
I wonder if there is a way to increase the procedure cache, so issues like this are alleviated.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 8, 2009 at 8:40 am
How long since the last execution did you query the dmv's? If it's hours or minutes, you may have a serious memory issue. If it's days or weeks... not at all.
"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
May 8, 2009 at 10:43 am
Grant Fritchey (5/8/2009)
How long since the last execution did you query the dmv's? If it's hours or minutes, you may have a serious memory issue. If it's days or weeks... not at all.
I ran the following to get the last execution time:
SELECT
last_execution_time
,ph.query_plan
,[sql].[text]
FROM
sys.dm_exec_query_stats qs
CROSS APPLY
sys.dm_exec_query_plan(qs.plan_handle) ph
CROSS APPLY
sys.dm_exec_sql_text(qs.[sql_handle]) [sql]
WHERE
[sql].[text] LIKE '%mySprocName%'
I got 28 records back (all NULLs in query_plan column). The latest execution time was from 6:00 am this morning; that's about 6 hrs ago. It does look like a have a serious memory issue. BTW, the min/max memory settings have been left to the default values, so this instance is using anywhere up to a max of 2 GB of RAM. It does not seem that's enough.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 8, 2009 at 12:46 pm
Actually, if it's still in sys.dm_exec_sql_text and in sys.dm_exec_query_stats, then it's still in cache. It hasn't aged out yet. If it were not in cache, then not only would you be missing the execution plan, but you also wouldn't be seeing the information from the query stats and query text DMV's.
It sounds like the procedure itself is not getting an execution plan created for it. Is it a really simple query so that it's getting a trivial plan?
"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
May 8, 2009 at 1:11 pm
Grant Fritchey (5/8/2009)
Actually, if it's still in sys.dm_exec_sql_text and in sys.dm_exec_query_stats, then it's still in cache. It hasn't aged out yet. If it were not in cache, then not only would you be missing the execution plan, but you also wouldn't be seeing the information from the query stats and query text DMV's.It sounds like the procedure itself is not getting an execution plan created for it. Is it a really simple query so that it's getting a trivial plan?
Actually, it is a long procedure: 1,300 lines.
Also, I have noticed that the plans of other procedures that were last executed yesterday - before the last execution time of this procedure - are still in the cache.
Could it be that the cache of this sproc is too large to be kept in the cache for a sizable period of time?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 9, 2009 at 6:53 am
Marios Philippopoulos (5/8/2009)
Grant Fritchey (5/8/2009)
Actually, if it's still in sys.dm_exec_sql_text and in sys.dm_exec_query_stats, then it's still in cache. It hasn't aged out yet. If it were not in cache, then not only would you be missing the execution plan, but you also wouldn't be seeing the information from the query stats and query text DMV's.It sounds like the procedure itself is not getting an execution plan created for it. Is it a really simple query so that it's getting a trivial plan?
Actually, it is a long procedure: 1,300 lines.
Also, I have noticed that the plans of other procedures that were last executed yesterday - before the last execution time of this procedure - are still in the cache.
Could it be that the cache of this sproc is too large to be kept in the cache for a sizable period of time?
I've seen some awful snakepits of temp tables, cursors and other all round madness in 'queries' that size, but none where the optimiser has barfed so badly it can't generate a plan. How would it run?
I've had a shuffle round google and come across something - may this be your issue do you think?
"It is actually possible to get back NULL for the query_plan from sys.dm_exec_query_plan. Once scenario under which this can occur is which the plan handle is captured first, and then later passed as a parameter to sys.dm_exec_query_plan. In the time window between when the plan handle is captured and passed to the DMF, the plan may be booted out of the cache due to memory pressure. In such a case, we would get NULL for the query_plan. "
If you run the proc in Enterprise manager does it give you an estimated plan if you request one?
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
May 9, 2009 at 6:55 am
Marios Philippopoulos (5/8/2009)
Grant Fritchey (5/8/2009)
Actually, if it's still in sys.dm_exec_sql_text and in sys.dm_exec_query_stats, then it's still in cache. It hasn't aged out yet. If it were not in cache, then not only would you be missing the execution plan, but you also wouldn't be seeing the information from the query stats and query text DMV's.It sounds like the procedure itself is not getting an execution plan created for it. Is it a really simple query so that it's getting a trivial plan?
Actually, it is a long procedure: 1,300 lines.
Also, I have noticed that the plans of other procedures that were last executed yesterday - before the last execution time of this procedure - are still in the cache.
Could it be that the cache of this sproc is too large to be kept in the cache for a sizable period of time?
Umm - silly question. Query doesn't use WITH RECOMPILE, does it?
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
May 9, 2009 at 3:46 pm
andrew gothard (5/9/2009)
Marios Philippopoulos (5/8/2009)
Grant Fritchey (5/8/2009)
Actually, if it's still in sys.dm_exec_sql_text and in sys.dm_exec_query_stats, then it's still in cache. It hasn't aged out yet. If it were not in cache, then not only would you be missing the execution plan, but you also wouldn't be seeing the information from the query stats and query text DMV's.It sounds like the procedure itself is not getting an execution plan created for it. Is it a really simple query so that it's getting a trivial plan?
Actually, it is a long procedure: 1,300 lines.
Also, I have noticed that the plans of other procedures that were last executed yesterday - before the last execution time of this procedure - are still in the cache.
Could it be that the cache of this sproc is too large to be kept in the cache for a sizable period of time?
I've seen some awful snakepits of temp tables, cursors and other all round madness in 'queries' that size, but none where the optimiser has barfed so badly it can't generate a plan. How would it run?
I've had a shuffle round google and come across something - may this be your issue do you think?
"It is actually possible to get back NULL for the query_plan from sys.dm_exec_query_plan. Once scenario under which this can occur is which the plan handle is captured first, and then later passed as a parameter to sys.dm_exec_query_plan. In the time window between when the plan handle is captured and passed to the DMF, the plan may be booted out of the cache due to memory pressure. In such a case, we would get NULL for the query_plan. "
If you run the proc in Enterprise manager does it give you an estimated plan if you request one?
Yes, I do get an estimated plan, the XML is 15,000 lines long...
I will try to post the XML here, after I mask the object names appropriately. 🙂
For now, suffice it to say, the plan looks very exotic, to put it nicely.
I should also mention, this procedure is among the top costliest procedures in terms of I/O in the SQL instance, another indicator of a possibly suboptimal query plan.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 9, 2009 at 3:51 pm
andrew gothard (5/9/2009)
Marios Philippopoulos (5/8/2009)
Grant Fritchey (5/8/2009)
Actually, if it's still in sys.dm_exec_sql_text and in sys.dm_exec_query_stats, then it's still in cache. It hasn't aged out yet. If it were not in cache, then not only would you be missing the execution plan, but you also wouldn't be seeing the information from the query stats and query text DMV's.It sounds like the procedure itself is not getting an execution plan created for it. Is it a really simple query so that it's getting a trivial plan?
Actually, it is a long procedure: 1,300 lines.
Also, I have noticed that the plans of other procedures that were last executed yesterday - before the last execution time of this procedure - are still in the cache.
Could it be that the cache of this sproc is too large to be kept in the cache for a sizable period of time?
Umm - silly question. Query doesn't use WITH RECOMPILE, does it?
No, there is no RECOMPILE.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 10, 2009 at 4:49 am
Does this proc include the use of DTC or is it called such that it uses DTC or extended procedures? I'm thinking it might be that the execution plan is so large or it's using DTC and this is putting it into MemToLeave... I think. I'm still doing some research around this.
"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
May 10, 2009 at 5:04 am
OK. Forget the DTC. I'm just a moron.
However, size may be a factor. From the documentation:
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.
So, you said it was a very large query. Is it dynamically building strings that might exceed 8kb?
It's also possible that it can't show the plan in XML due to a limitation of 128 levels (again, outlined in the BOL on sys.dm_exec_query_plan). Can you run sys.dm_exec_text_query_plan and get the plan?
"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
May 11, 2009 at 9:19 am
Here is the plan (see attached). I have tried to obfuscate object names as much as possible, and, in doing so, I hope I have not messed up anything system-specific.
Here is something from the plan that caught my attention:
...
...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 11, 2009 at 9:40 am
Grant Fritchey (5/10/2009)
...So, you said it was a very large query. Is it dynamically building strings that might exceed 8kb?
It's also possible that it can't show the plan in XML due to a limitation of 128 levels (again, outlined in the BOL on sys.dm_exec_query_plan). Can you run sys.dm_exec_text_query_plan and get the plan?
It is executing dynamic SQL within a fast-forward, read-only cursor.
The dynamic SQL itself is not more than 7,000 characters long, but, could it be that because it is executed within the cursor, it is cached and quickly accumulates to exceed the 8-kb limit?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply