How can an improvement to the query cache be tracked?

Question

I am parameterizing my web app's ad hoc sql. As a result, I expect the query plan cache to reduce in size and have a higher hit ratio. Perhaps even other important metrics will be improved.

Could I use perfmon to track this? If so, what counters should I use? If not perfmon, how could I report on the impact of this change?

Answer 1

SQL Server, Plan Cache Object

Cache Hit Ratio Ratio between cache hits and lookups.
Cache Object Counts Number of cache objects in the cache.
Cache Pages Number of 8-kilobyte (KB) pages used by cache objects.
Cache Objects in use Number of cache objects in use.

Also sys.dm_os_memory_clerks and sys.dm_os_memory_cache_counters will give information about memory allocations (in general) and SQL caches (in general). You'll be interested in allocation for the plan cache memory clerk.

And finally there are the execution DMVs: sys.dm_exec_query_stats and sys.dm_exec_cached_plans.

These counters and DMVs should cover what you need, for more details see Execution Plan Caching and Reuse.