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: Wednesday, April 02, 2014 9:31 AM
Points: 19, Visits: 268
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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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: Wednesday, April 02, 2014 9:31 AM
Points: 19, Visits: 268
Yes.
Post #1351057
Posted Tuesday, August 28, 2012 11:27 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1352501
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse