Ratio of Adhoc plans to Stored proc plans in Cache

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris Wood - 15 Jul 2008 21:33 GMT

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

Reply to this Message

Denny Cherry - 16 Jul 2008 01:50 GMT

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.techtarget.com/sql-server/article-sql-server-memory-c
onfigurations-for-procedure-cache-and-buffer-cache/)

Denny

>Hi,
>
[quoted text clipped - 11 lines]
>
>Chris

Reply to this Message

Chris Wood - 16 Jul 2008 14:52 GMT

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

> Since you asked, here's what is in my cache for my production server.
>
[quoted text clipped - 36 lines]
>>
>>Chris

Reply to this Message