June 19, 2012 at 5:21 pm
Hi everyone,
My analysis of my server shows that ad-hoc query plan cache space take up 5GB of RAM (on a machine with 32GB of RAM). I freed the ad-hoc cache, and it filled up again in a matter of 2 hours, and this happened during the time of day when the least amount of activity is occurring. I think that there are some ad-hoc queries out there that could be written better or included in a SP so that their plan cache can be reused. My question is, how can list the ad-hoc queries are in the plan cache? Thanks in advance.
June 20, 2012 at 4:39 am
You can pull stuff straight out of the cache using dynamic management objects (DMO). The main couple that you're going to be interested in are sys.dm_exec_query_stats and sys.dm_exec_sql_text. Here's the basic query:
SELECT *,
SUBSTRING(dest.text, (deqs.statement_start_offset / 2) + 1,
(deqs.statement_end_offset - deqs.statement_start_offset)
/ 2 + 1) AS actualstatement
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE dest.objectid IS NULL;
You can pick and choose which columns you want to look at. I included the mechanism for plucking individual statements out of multi-statement queries. This will list all objects in cache that don't associate with an objectid. Some of these are going to be system calls, but most will be your ad hoc sql statements.
Suggestion, if you were on 2008 or better you can enable 'Optimize for Ad Hoc' which will store plan stubs instead of full plans in cache the first time an ad hoc statement is run.
"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
June 20, 2012 at 10:03 am
Most excellent, thank you very much!
BTW, when is your SS 2012 Performance Tuning book coming out?
June 20, 2012 at 10:12 am
Just as a reference, the query returned the following error:
Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.
I "fixed" it by adding the last line to the WHERE clause:
SELECT *,
Substring(dest.text, ( deqs.statement_start_offset / 2 ) + 1,
( deqs.statement_end_offset - deqs.statement_start_offset ) / 2 + 1) AS
actualstatement
FROM sys.dm_exec_query_stats AS deqs
CROSS apply sys.Dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE dest.objectid IS NULL
AND deqs.statement_end_offset > 0
Formatting courtesy of http://www.dpriver.com/pp/sqlformat.htm 🙂
June 20, 2012 at 10:34 am
shahgols (6/20/2012)
Most excellent, thank you very much!BTW, when is your SS 2012 Performance Tuning book coming out?
Just got released today.
"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
June 20, 2012 at 10:36 am
shahgols (6/20/2012)
Just as a reference, the query returned the following error:Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.
I "fixed" it by adding the last line to the WHERE clause:
SELECT *,Substring(dest.text, ( deqs.statement_start_offset / 2 ) + 1,
( deqs.statement_end_offset - deqs.statement_start_offset ) / 2 + 1) AS
actualstatement
FROM sys.dm_exec_query_stats AS deqs
CROSS apply sys.Dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE dest.objectid IS NULL
AND deqs.statement_end_offset > 0
Oops. Sorry about that. Slapped it together quick. You'd be better served doing a CASE on the end_offset to catch those statements that are all the query. You just eliminated some from the list.
Formatting courtesy of http://www.dpriver.com/pp/sqlformat.htm 🙂
"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
June 20, 2012 at 10:36 am
Congratulations! 🙂
I'm placing an order today too.
June 20, 2012 at 1:20 pm
Hi Grant, I have a question,
I see that sys.dm_exec_cached_plans has a column objtype, and one of the values for this column is "Adhoc". Could this object/column be used to find Adhoc queries in the plan cache? If so, it returns a different result set to the query that you posted, so which one should I trust?
June 20, 2012 at 2:03 pm
shahgols (6/20/2012)
Hi Grant, I have a question,I see that sys.dm_exec_cached_plans has a column objtype, and one of the values for this column is "Adhoc". Could this object/column be used to find Adhoc queries in the plan cache? If so, it returns a different result set to the query that you posted, so which one should I trust?
I think it depends how you define 'Adhoc'.
Objectid=NULL means this is not an object (SP or Trigger) and the sql statement is a plain text not associated with any object.
ObjectType=prepared, means query plan is already available (no recompilation needed) even if you change the parameter values
ObjectType=Adhoc, means everytime parameter's value changed, sql engine will recompile and generate new execution plan.
I think in your case, ObjectType=Adhoc is of your interest
I hope this helps
June 20, 2012 at 2:07 pm
got it, thanks!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply