Prepared SQL Plan vs. Procedure Plan

[Michael G via SQLMonster.com]

I am working on tuning the procedure cache hit ratio for my server. We have
added 4 Gb of memory to the server, which has helped. In addition, I have run
the DBCC FREEPROCACHE, which helped for a couple of days to get the hit ratio
up to about 84% (from 68%).

When I use the performance monitor on the server and look at SQL Server Cache
Manager:Buffer Hit Ratio, I see that the Prepared SQL Plan is around 97%, but
the Procedure Plan hit ratio is down around 55%. I've done some research on
different tuning techniques, but can't seem to find 1. a clear definition of
the difference between the prepared sql plan and the procedure plan and 2.
other than adding memory and running dbcc freeprocache, how can I get the
procedure plan cache raised? I do know that there are some procedures that
need to be modified to be called fully qualified (e.g. exec dbo.sp_###
instead of exec sp_###), but I don't think that those will increase the
procedure plan by 30% or more.

Any insight you can give would be greatly appreciated.

Thanks,
Michael

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...neral/200511/1

[Erland Sommarskog]

Michael G via SQLMonster.com (u13012@uwe) writes:

I am working on tuning the procedure cache hit ratio for my server. We
have added 4 Gb of memory to the server, which has helped. In addition,
I have run the DBCC FREEPROCACHE, which helped for a couple of days to
get the hit ratio up to about 84% (from 68%).

When I use the performance monitor on the server and look at SQL Server
Cache Manager:Buffer Hit Ratio, I see that the Prepared SQL Plan is
around 97%, but the Procedure Plan hit ratio is down around 55%. I've
done some research on different tuning techniques, but can't seem to
find 1. a clear definition of the difference between the prepared sql
plan and the procedure plan and 2. other than adding memory and running
dbcc freeprocache, how can I get the procedure plan cache raised? I do
know that there are some procedures that need to be modified to be
called fully qualified (e.g. exec dbo.sp_### instead of exec sp_###),
but I don't think that those will increase the procedure plan by 30% or
more.

It sounds like you have one or more application that is spewing out a
lot of dynamic SQL queries. Short of rewriting the applications there
is not much to do about that.

Note that if your applications mainly use stored procedures, that you
probably can get a lot of prepared things, if the procedure are called
with EXEC statements rather than the RPC mechanism.

I'm not really sure that I see the point with FREEPROCCACHE. Sure, it
removes old junk from the cache, but then it forces a lots of recompiles
to bring things bad.

If you stored procedures are really named sp_xxx, change that to something
else. The sp_ prefix is reserved for system objects, and SQL Server first
looks in master for these.

The best way to improve the cache hit ratio, is to find those queries
that pushes things out of the cache, because they unnecessarily scan
large tables.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx