Thousands of SQL Agent Queries in Plan Cache

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:
>>
>

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:
>>
>