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

Jason Massie

You can set parameterization to forced.

ALTER DATABASE MSDB
set parameterization forced


--
Jason Massie
Web:
RSS:

"Code Wench" <> wrote in message
news:...
> 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
>

Code Wench

Thanks for your response! I did check that already. Parameterization is set
to forced on MSDB.


"Jason Massie" wrote:

> You can set parameterization to forced.
>
> ALTER DATABASE MSDB
> set parameterization forced
>
>
> --
> Jason Massie
> Web:
> RSS:
>

Code Wench

Thanks for your response! I did check that already. Parameterization is set
to forced on MSDB.


"Jason Massie" wrote:

> You can set parameterization to forced.
>
> ALTER DATABASE MSDB
> set parameterization forced
>
>
> --
> Jason Massie
> Web:
> RSS:
>

Jason Massie

I think the best workaround you are going to find would be running this from
a job periodically: DBCC FLUSHPROCINDB(4). I have hundreds, though not
1000's like you, a 32GB x64 system. I looked at sys.dm_exec_plan_attributes
for these plans and the difference between the ones being reused and the
ones with one use count was a value of 0 and 4 for db_exec.

Is this really a problem? These will be aged out of cache as space is
needed. Do you has sp2 installed? There are enhancements.


--
Jason Massie
Web:
RSS:



"Code Wench" <> wrote in message
news:...
> Thanks for your response! I did check that already. Parameterization is
> set
> to forced on MSDB.
>
>
> "Jason Massie" wrote:
>
>> You can set parameterization to forced.
>>
>> ALTER DATABASE MSDB
>> set parameterization forced
>>
>>
>> --
>> Jason Massie
>> Web:
>> RSS:
>>
>

Jason Massie

I think the best workaround you are going to find would be running this from
a job periodically: DBCC FLUSHPROCINDB(4). I have hundreds, though not
1000's like you, a 32GB x64 system. I looked at sys.dm_exec_plan_attributes
for these plans and the difference between the ones being reused and the
ones with one use count was a value of 0 and 4 for db_exec.

Is this really a problem? These will be aged out of cache as space is
needed. Do you has sp2 installed? There are enhancements.


--
Jason Massie
Web:
RSS:



"Code Wench" <> wrote in message
news:...
> Thanks for your response! I did check that already. Parameterization is
> set
> to forced on MSDB.
>
>
> "Jason Massie" wrote:
>
>> You can set parameterization to forced.
>>
>> ALTER DATABASE MSDB
>> set parameterization forced
>>
>>
>> --
>> Jason Massie
>> Web:
>> RSS:
>>
>

Code Wench

Thanks for your response! I did check that already. Parameterization is set
to forced on MSDB.


"Jason Massie" wrote:

> You can set parameterization to forced.
>
> ALTER DATABASE MSDB
> set parameterization forced
>
>
> --
> Jason Massie
> Web:
> RSS:
>

Code Wench

Thanks for your response! I did check that already. Parameterization is set
to forced on MSDB.


"Jason Massie" wrote:

> You can set parameterization to forced.
>
> ALTER DATABASE MSDB
> set parameterization forced
>
>
> --
> Jason Massie
> Web:
> RSS:
>

Jason Massie

I think the best workaround you are going to find would be running this from
a job periodically: DBCC FLUSHPROCINDB(4). I have hundreds, though not
1000's like you, a 32GB x64 system. I looked at sys.dm_exec_plan_attributes
for these plans and the difference between the ones being reused and the
ones with one use count was a value of 0 and 4 for db_exec.

Is this really a problem? These will be aged out of cache as space is
needed. Do you has sp2 installed? There are enhancements.


--
Jason Massie
Web:
RSS:



"Code Wench" <> wrote in message
news:...
> Thanks for your response! I did check that already. Parameterization is
> set
> to forced on MSDB.
>
>
> "Jason Massie" wrote:
>
>> You can set parameterization to forced.
>>
>> ALTER DATABASE MSDB
>> set parameterization forced
>>
>>
>> --
>> Jason Massie
>> Web:
>> RSS:
>>
>

Jason Massie

I think the best workaround you are going to find would be running this from
a job periodically: DBCC FLUSHPROCINDB(4). I have hundreds, though not
1000's like you, a 32GB x64 system. I looked at sys.dm_exec_plan_attributes
for these plans and the difference between the ones being reused and the
ones with one use count was a value of 0 and 4 for db_exec.

Is this really a problem? These will be aged out of cache as space is
needed. Do you has sp2 installed? There are enhancements.


--
Jason Massie
Web:
RSS:



"Code Wench" <> wrote in message
news:...
> Thanks for your response! I did check that already. Parameterization is
> set
> to forced on MSDB.
>
>
> "Jason Massie" wrote:
>
>> You can set parameterization to forced.
>>
>> ALTER DATABASE MSDB
>> set parameterization forced
>>
>>
>> --
>> Jason Massie
>> Web:
>> RSS:
>>
>