Sunday, February 27, 2011

Plan Cache Pages and DBCC CACHESTATS to monitor plan cache size

You can monitor Plan Cache Size in SQL Server using two methods:
1. Run PerfMon to monitor the counter 'SQL Server: Plan Cache\Cache Pages(All_Instances)' to find number of pages current being used by plan cache

2. Run DBCC CACHESTATS to get Pages Used and additional information like Hit Ratio, Use Count etc.

This will be useful if you are having memory problems because of too many plans, parameterization, large size plans etc