excessive recompiles - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

excessive recompiles

Author

Topic

vsatya25
Starting Member

1 Post

Posted - 2012-08-28 : 11:29:39

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,
Venkat

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-08-28 : 13:48:22

I can't help but chuckle. Is your server so memory starved that it can't handle the normal load SQL Server adds to a system? Those are from the SQL Agent, it's just doing its thing.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

komkrit
Yak Posting Veteran

60 Posts

Posted - 2012-08-28 : 13:59:39

Dear Venkat,

By default, SQL Server use Parameteriztion = Simple Mode.
That is mean only simple statement will be enforced to use parameterization.
In cache plan, it will be transform to be parameterized form.

If your statement could not be choosed by optimizer to be parameterized,
that is mean your statement is looking(for optimizer) not simple.
You have choices.

1. Enabling Forced Parameterization
example
--------------------------------------------------------
ALTER DATABASE YOUR_DATABASE SET PARAMETERIZATION FORCED
--------------------------------------------------------
this does not recommend because it will enforce all statements to be parameterization, even though it make performance worse.

2. Enabling Forced Parameterization for a single query using "Plan Guide"
example
--------------------------------------------------------
DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template
N'UPDATE MSDB.DBO.SYSJOBSCHEDULES SET NEXT_RUN_DATE = ....;',
@stmt OUTPUT,
@params OUTPUT;
EXEC sp_create_plan_guide
N'MyPlanGuide_JobResults',
@stmt,
N'TEMPLATE',
NULL,
@params,
N'OPTION(PARAMETERIZATION FORCED)';

--View all plans in your system
SELECT * FROM sys.plan_guides

-- monitor cached plan
SELECT *
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
INNER JOIN sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle
WHERE cp.plan_handle=qs.plan_handle
AND (ObjType = 'Adhoc' OR ObjType='Prepared')

-- clear cache
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Komkrit Yensirikul
Welcome for all questions. Let us know if our solution solved your problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources