Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Excessive recompiles Expand / Collapse
Author
Message
Posted Tuesday, August 28, 2012 9:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 17, 2014 3:16 PM
Points: 19, Visits: 293
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
Post #1351048
Posted Tuesday, August 28, 2012 9:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
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
Post #1351055
Posted Tuesday, August 28, 2012 9:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 17, 2014 3:16 PM
Points: 19, Visits: 293
Yes.
Post #1351057
Posted Tuesday, August 28, 2012 11:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
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
Post #1351143
Posted Tuesday, August 28, 2012 11:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
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
Post #1351145
Posted Thursday, August 30, 2012 2:43 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:02 AM
Points: 7,135, Visits: 12,744
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
Post #1352501
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse