March 8, 2010 at 10:40 pm
Hi,
I've been checking one of the servers with this query:
SELECT left(text, 30), count(*)
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
group by left(text, 30)
ORDER BY 2 desc;
to discover similar queries that don't re-use the same execution plan thus flooding procedure cache.
The most popular kind turned out to be this one:
delete dbo.FACT where party_id = 12345 and channel_code = 'ABCDEF';
I understand I should replace it with something like this:
EXEC sp_executesql N'delete dbo.FACT
where party_id = @x and channel_code = @y',
N'@x int', @x = 12345, N'@y varchar(10)', @y = 'ABCDEF'
so to avoid flooding cache with too many plans.
However some of the largest contributors were queries like these:
BEGIN TRAN
UPDATE msdb.dbo.sysjobsteps
SET last_run_outcome = 1, last_run_duration = 1, last_run_retries = 0, last_run_date = 20100309,
last_run_time = 151700 WHERE (job_id = 0x5F04ED65FBEACC4C9AE8D2245EDCADB7) AND (step_id = 1)
DECLARE @lastExecuteStepDate DATETIME
set @lastExecuteStepDate = msdb.dbo.agent_datetime(20100309, 151700)
UPDATE sysjobactivity SET last_executed_step_date = @lastExecuteStepDate, last_executed_step_id = 1
WHERE job_id = 0x5F04ED65FBEACC4C9AE8D2245EDCADB7 AND session_id = 102
COMMIT TRAN
(@param0 nvarchar(20), @param1 nvarchar(74), @param2 nvarchar(19), @param3 nvarchar(10), @param4 nvarchar(24), @param5 nvarchar(13),
@param6 nvarchar(28), @param7 nvarchar(7), @param8 nvarchar(7), @param9 nvarchar(7), @param10 nvarchar(7), @param11 nvarchar(7),
@param12 nvarchar(14), @param13 nvarchar(3), @param14 nvarchar(37), @param15 nvarchar(7), @param16 nvarchar(17), @param17 nvarchar(3),
@param18 nvarchar(40), @param19 nvarchar(7), @param20 nvarchar(14), @param21 nvarchar(3), @param22 nvarchar(37), @param23 nvarchar(7),
@param24 nvarchar(14), @param25 nvarchar(3), @param26 nvarchar(37), @param27 nvarchar(14), @param28 nvarchar(3), @param29 nvarchar(37))
declare @backup_set_id int declare @media_set_id int declare @media_count int
select @media_set_id = media_set_id from msdb.dbo.backupmediaset
where media_uuid = N'{5B120DCA-DC84-4E69-9A75-C727168089A4}'
if @media_set_id is null
begin
insert msdb.dbo.backupmediaset (media_uuid, media_family_count, software_name, software_vendor_id, MTF_major_version, mirror_count,
is_password_protected) values (N'{5B120DCA-DC84-4E69-9A75-C727168089A4}', 1, @param0, 4608, 1, 1, 0)
select @media_set_id = @@identity
end
select @media_count = media_count ...
which are system generated queries related to job history and backups. It appears I can't do much about them, however it looks strange that Microsoft allowed something like this.
Just wondering if there is any flag, that would stop such queries from generating an execution plan each time they execute?
Thanks.
March 18, 2010 at 1:19 am
Up! 🙂
March 18, 2010 at 5:13 am
If it really concerns you, take a look at Forced Parameterization. Be sure you fully understand the implications before testing it.
Unless you can show that procedure cache bloat is the primary cause of poor performance on your servers, this might be an example of premature optimization 😀
Complex subject - too much to cover in a forum post really.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 18, 2010 at 11:24 pm
Paul White NZ (3/18/2010)
If it really concerns you, take a look at Forced Parameterization. Be sure you fully understand the implications before testing it.Unless you can show that procedure cache bloat is the primary cause of poor performance on your servers, this might be an example of premature optimization 😀
Complex subject - too much to cover in a forum post really.
BTW, how do you show, that the poor performance is related to the cache bloat?
March 19, 2010 at 1:23 am
Roust_m (3/18/2010)
How do you show that the poor performance is related to the cache bloat?
Not easy. One way is to show that the procedure cache is using too much memory, restricting the size of the buffer pool, and that is leading to page life expectancy...and so on. You can show the size of the procedure cache with code like:
SELECT name,
[MB used] = SUM(single_pages_kb + multi_pages_kb)/1024.0
FROM sys.dm_os_memory_clerks
WHERE name IN (
N'Object Plans', -- Procedures, triggers, etc
N'SQL Plans' -- Ad-hoc stuff
)
GROUP BY
name
ORDER BY
[MB used] DESC;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 19, 2010 at 2:14 am
I blogged about this issue a little while back
http://sqlblogcasts.com/blogs/sqlandthelike/archive/2010/01/22/microsoft-follow-best-practices.aspx
Microsoft have closed the connect item as fixed next release (though if that means 2008 r2 i dont know)
This also came up in conversation with Itzik Ben-Gan , he suggested that DBCC FLUSHPROCINDB might be worth a try.
March 19, 2010 at 2:23 am
Another solution I have used in the past is to monitor the size of the ad-hoc plan cache using a job based on:
SELECT [MB used] = SUM(single_pages_kb + multi_pages_kb)/1024.0
FROM sys.dm_os_memory_clerks
WHERE name = N'SQL Plans';
When the value exceeds some size, call:
DBCC FREESYSTEMCACHE ('SQL Plans');
This dumps the ad-hoc plan cache, which is normally pretty safe since ad-hoc plans tend not to be re-used anyway.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 21, 2010 at 4:39 pm
Paul White NZ (3/19/2010)
Roust_m (3/18/2010)
How do you show that the poor performance is related to the cache bloat?Not easy. One way is to show that the procedure cache is using too much memory, restricting the size of the buffer pool, and that is leading to page life expectancy...and so on. You can show the size of the procedure cache with code like:
SELECT name,
[MB used] = SUM(single_pages_kb + multi_pages_kb)/1024.0
FROM sys.dm_os_memory_clerks
WHERE name IN (
N'Object Plans', -- Procedures, triggers, etc
N'SQL Plans' -- Ad-hoc stuff
)
GROUP BY
name
ORDER BY
[MB used] DESC;
Ok, this is what I got for this query:
name,MB used
SQL Plans,6356.898437
Object Plans,378.179687
It looks like SQL Plans are using the most of it. So I guess I do at least have some issues with the use of my procedure cache and the fact that there is so little allocated for Object plans probably proves these plans don't get enough of memory which is likely to cause a performance problem.
March 21, 2010 at 5:55 pm
Roust_m (3/21/2010)
Ok, this is what I got for this query:name,MB used
SQL Plans,6356.898437
Object Plans,378.179687
It looks like SQL Plans are using the most of it. So I guess I do at least have some issues with the use of my procedure cache and the fact that there is so little allocated for Object plans probably proves these plans don't get enough of memory which is likely to cause a performance problem.
Yes, 6GB+ of memory being used just for ad-hoc plans is quite a distance from optimal 🙂
378MB is not an unexpectedly small amount for Object Plans. It may be about right, in fact.
You can use queries based on the following code to see how often cached plans are being re-used. My guess is that you will find that ad-hoc plan re-use is very low.
SELECT objtype,
cacheobjtype,
times_used = SUM(usecounts),
total_size_MB = SUM(size_in_bytes) / 1024. / 1024.
FROM sys.dm_exec_cached_plans
WHERE cacheobjtype = N'Compiled Plan'
GROUP BY
objtype,
cacheobjtype;
The solution is to identify why large numbers of ad-hoc plans are being produced, and to take steps to reduce that. Alternatively, trim the ad-hoc plan cache from time to time, or when it reaches a certain size.
BTW, I seem to recall you expressed an interest in memory grants recently. Please read the following blog entry from the QP Team:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 19, 2010 at 4:13 am
he syntax and converts the query into relational algebric expressions. Then the query optmizer constructs the execution plan based on several rules and cost of executing the query. Once the execution plan is generated, action switches to the storage engine where query is actually executed, according to the plan.
There are two distinct types of execution plan. The plan that outputs from optimizer is known as estimated execution plan and the other plan represents the output from actual query execution.It is expensive for the Server to generate execution plans so SQL Server will keep and reuse plans wherever possible .As they are created, plans are stored in a section of memory called the plan cache.
The optimizer compares this estimated plan to actual execution plans that already exists in the plan cache . If an actual plan is found that matches the estimated one, then the optimizer will reuse the existing plan, since it's already been used before by the query engine. This reuse avoids the overhead of creating actual execution plans for large and complex queries or even simple plans for small queries.
January 20, 2011 at 10:22 am
SQLkiwi (3/21/2010)
Roust_m (3/21/2010)
Ok, this is what I got for this query:name,MB used
SQL Plans,6356.898437
Object Plans,378.179687
It looks like SQL Plans are using the most of it. So I guess I do at least have some issues with the use of my procedure cache and the fact that there is so little allocated for Object plans probably proves these plans don't get enough of memory which is likely to cause a performance problem.
Yes, 6GB+ of memory being used just for ad-hoc plans is quite a distance from optimal 🙂
378MB is not an unexpectedly small amount for Object Plans. It may be about right, in fact.
You can use queries based on the following code to see how often cached plans are being re-used. My guess is that you will find that ad-hoc plan re-use is very low.
SELECT objtype,
cacheobjtype,
times_used = SUM(usecounts),
total_size_MB = SUM(size_in_bytes) / 1024. / 1024.
FROM sys.dm_exec_cached_plans
WHERE cacheobjtype = N'Compiled Plan'
GROUP BY
objtype,
cacheobjtype;
The solution is to identify why large numbers of ad-hoc plans are being produced, and to take steps to reduce that. Alternatively, trim the ad-hoc plan cache from time to time, or when it reaches a certain size.
BTW, I seem to recall you expressed an interest in memory grants recently. Please read the following blog entry from the QP Team:
I ran the given above code and mentioned below is my result:
Compiled PlanAdhoc62924306.81250000000
Compiled PlanPrepared1591611892.69531250000
Compiled PlanProc 3536030767.51562500000
Compiled PlanTrigger 4 0.87500000000
i see only 767 mb is used my proc cached plan. How could that be when server has 43gb assigned to sql server?
April 5, 2011 at 11:02 am
Any suggestions?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply