August 28, 2012 at 9:30 am
I see several entries of "UPDATE MSDB.DBO.SYSJOBSCHEDULES SET NEXT_RUN_DATE = ...." in cache.
You can find those using this query:
select * from sys.dm_exec_cached_plans a
cross apply sys.dm_exec_sql_text (a.plan_handle)
where text like '%update msdb.dbo.sysjobschedules%'
Every time when it compiles, it is occupying 24kb and filling up the cache memory.
Do you guys know how to parameterize it?
Thanks,
Ramu
August 28, 2012 at 9:37 am
Do you have a bunch of SQL Agent jobs that run extremely frequently? That's likely what's causing it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 28, 2012 at 9:39 am
Yes.
August 28, 2012 at 11:27 am
That's a system update. I don't think there's really anything you can do about it directly.
Depending on what else the server is used for, you might relieve cache-pressure by using the optimize for ad hoc setting: http://msdn.microsoft.com/en-us/library/cc645587.aspx
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 28, 2012 at 11:30 am
As an alternative (sorry for the fragmented posting, it's been a long day), you might want to look into what those jobs are doing, and see if there's a better way to accomplish it than by running them so frequently. It's unusual to have enough updates to that table for it to matter at all, much less cause measurable issues.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 30, 2012 at 2:43 pm
I had this problem on SQL 2005 and could not do anything with the jobs in place so went this route to keep plan cache under control:
Plan cache, adhoc workloads and clearing the single-use plan cache bloat
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply