Hi,

We just upgraded our production servers to SQL2005 SP2 build 3239 (CU7) and
of course we have some issues. We had a 701 error this morning that I feel
is because of the number of adhoc (dynamic) SQL plans in the cache. I just
took a peek at our cached plans and can see a 5 to 1 ratio of adhoc plans to
stored proc plans. This is a 32 bit server with 4Gb allowed out of 8Gb for
SQL.

I am quite sure that we are swamping our cache with adhoc plans but I would
like to know what other sites ratio's are for adhoc to stored proc plans?

Thanks

Chris

Since you asked, here's what is in my cache for my production server.

objtype plans
Adhoc 2412
Check 6
Prepared 1457
Proc 461
Trigger 30
UsrTab 42
View 123

Now because of business requirements we have dynamic SQL within a few
stored procedures because of dynamic ordering requirements. Because
of this our ratios may be different than expected. All application
hits to the database are through stored procedures.

With a 32bit SQL Server, your plan cache can be no higher than 1 Gig.
This blog post and the article it points to explain the calculations.
(http://itknowledgeexchange.techtarge...-buffer-cache/)

Denny

On Tue, 15 Jul 2008 14:33:23 -0600, "Chris Wood"
<anonymous@microsoft.com> wrote:

>Hi,
>
>We just upgraded our production servers to SQL2005 SP2 build 3239 (CU7) and
>of course we have some issues. We had a 701 error this morning that I feel
>is because of the number of adhoc (dynamic) SQL plans in the cache. I just
>took a peek at our cached plans and can see a 5 to 1 ratio of adhoc plans to
>stored proc plans. This is a 32 bit server with 4Gb allowed out of 8Gb for
>SQL.
>
>I am quite sure that we are swamping our cache with adhoc plans but I would
>like to know what other sites ratio's are for adhoc to stored proc plans?
>
>Thanks
>
>Chris
>

Denny,

We went from build 3159 to build 3239. Did some of the fixes either increase
the size of compiled plans or increase the memory needed to compile plans so
that a stored procedure that worked before the upgrade would throw a 701
memory error? In CU8 I see a fix if it is a compile that fails.

Thanks

Chris

"Denny Cherry" <mrdenny.nospam@mrdenny.com> wrote in message
news:l3hq74tpt1f02v2k2e34vh5m1jsbu4jv3b@4ax.com...
> Since you asked, here's what is in my cache for my production server.
>
> objtype plans
> Adhoc 2412
> Check 6
> Prepared 1457
> Proc 461
> Trigger 30
> UsrTab 42
> View 123
>
> Now because of business requirements we have dynamic SQL within a few
> stored procedures because of dynamic ordering requirements. Because
> of this our ratios may be different than expected. All application
> hits to the database are through stored procedures.
>
> With a 32bit SQL Server, your plan cache can be no higher than 1 Gig.
> This blog post and the article it points to explain the calculations.
> (http://itknowledgeexchange.techtarge...-buffer-cache/)
>
> Denny
>
> On Tue, 15 Jul 2008 14:33:23 -0600, "Chris Wood"
> <anonymous@microsoft.com> wrote:
>
>>Hi,
>>
>>We just upgraded our production servers to SQL2005 SP2 build 3239 (CU7)
>>and
>>of course we have some issues. We had a 701 error this morning that I feel
>>is because of the number of adhoc (dynamic) SQL plans in the cache. I just
>>took a peek at our cached plans and can see a 5 to 1 ratio of adhoc plans
>>to
>>stored proc plans. This is a 32 bit server with 4Gb allowed out of 8Gb for
>>SQL.
>>
>>I am quite sure that we are swamping our cache with adhoc plans but I
>>would
>>like to know what other sites ratio's are for adhoc to stored proc plans?
>>
>>Thanks
>>
>>Chris
>>