Thousands of SQL Agent Queries in Plan Cache

Code Wench

We are having issues with the size of a our Plan Cache, mostly due to poor
coding practices. While trying to identify the problematic queries, I
noticed that there are thousands of statements in the plan cache that seem to
be related to running SQL Agent jobs. These aren't queries that we've
written so they must be created by SQL Agent. Is there some way to
parameterize these? These are some examples:



UPDATE msdb.dbo.sysjobactivity SET run_requested_date = DATEADD(ms,
-DATEPART(ms, GetDate()), GetDate()), run_requested_source = 1, queued_date =
NULL, start_execution_date = NULL, last_executed_step_id = NULL,
last_executed_step_date = NULL, stop_execution_date = NULL, job_history_id =
NULL, next_scheduled_run_date = NULL WHERE job_id =
0x041E79BA76AED741AD7C8D1F8B72EB15 and session_id = 19



DECLARE @nextScheduledRunDate DATETIME SET @nextScheduledRunDate =
msdb.dbo.agent_datetime(20071120, 190200) UPDATE msdb.dbo.sysjobactivity SET
next_scheduled_run_date = @nextScheduledRunDate WHERE session_id = 19 AND
job_id = 0xC24E2C86BA0CB04593641E19D84AEDC8



DECLARE @startExecutionDate DATETIME SET @startExecutionDate =
msdb.dbo.agent_datetime(20071120, 190100) UPDATE msdb.dbo.sysjobactivity SET
start_execution_date = @startExecutionDate WHERE job_id =
0xC24E2C86BA0CB04593641E19D84AEDC8 and session_id = 19