The “Cache Wizard” section can help you monitor and configure caches for optimal performance.
sp_sysmon cache wizard requires that you enable the object lockwait timing configuration parameter.
Cache Wizard allows you to identify:
Hot objects (objects that are often accessed). The output is ranked by the number of logical reads in a named cache or default data cache.
Spinlock contention on the cache.
The usage of the cache and buffer pools.
The percentage of hits at a cache, buffer pool, and object level.
The effectiveness of large I/O.
The effectiveness of asynchronous prefetch (APF).
The cache occupancy by the various objects.
The “Cache Wizard” section appears in sp_sysmon output only when you specify the cache wizard in sp_sysmon syntax. You can include two parameters with Cache Wizard, topN and filter. sp_sysmon does not include other sections (“Kernel Utilization,” “Worker Process Management,” and so on) when you include cache wizard in the syntax.
sp_syntax includes a recommendation section at the end of its printout.