views:

22

answers:

2

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?

+1  A: 

You can use SQL Server Profiler. Create a new trace, and capture the TSQL->Exec Prepared Sql and TSQL->Prepare Sql events. The former will tell you when it's reusing a query plan, the latter when it is regenerating the plan.

You can do the same for Stored Procedures as well, under the SP category of events.

Paul Kearney - pk
+1  A: 

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.

Remus Rusanu
You're the man, @Remus!
Bill Paetzke