To obtain output from the cache wizard, use:
sp_sysmon begin_sample sp_sysmon { end_sample | interval } [, 'cache wizard' [, top_N [, filter] ] ]
top_N – varchar datatype that limits the list of objects reported in the Object Section based on the ranking criteria for the number of logical reads in the specified interval (as displayed in the LR/sec column).
The order of ranking is ascending or descending, based on whether the specified value is a positive or negative integer. Obtain the entire list of objects occupying the cache at the end of the interval by specifying a value of 0 (zero). The default value is 10.
filter – varchar datatype that allows you to specify a pattern for the caches included in the report.
For example, if you specify filter as default data cache, the report will contain only information about the default data cache. If you specify a filter value of emp%, the output includes information on all caches with a name matching this pattern.
To display output for all caches, leave filter blank. The default data cache appears first, and is followed by the other caches in alphabetical order.
For more information, see “Sample output”.
This example runs the Cache wizard against the default data cache for a 5-minute interval, showing the top 15 objects in the cache:
sp_sysmon '00:05:00','cache wizard','15','default data cache'