Excessive recompiles

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Excessive recompiles

Excessive recompiles

Rate Topic

Display Mode

Topic Options

Author

Message

Ramu Poonjolai

Posted Tuesday, August 28, 2012 9:30 AM

Grasshopper

Group: General Forum Members
Last Login: Today @ 5:25 AM
Points: 19, Visits: 223

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

GSquared

Posted Tuesday, August 28, 2012 9:37 AM

SSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571

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

Ramu Poonjolai

Posted Tuesday, August 28, 2012 9:39 AM

Grasshopper

Group: General Forum Members
Last Login: Today @ 5:25 AM
Points: 19, Visits: 223

Yes.

Post #1351057

GSquared

Posted Tuesday, August 28, 2012 11:27 AM

SSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571

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

GSquared

Posted Tuesday, August 28, 2012 11:30 AM

SSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571

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

opc.three

Posted Thursday, August 30, 2012 2:43 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 6,730, Visits: 11,779

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 »

Permissions