Posted by Michael G via SQLMonster.com on 11/22/05 23:34

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.aspx/sql-server-general/200511/1

[Back to original message]