sp_monitorconfig displays metadata cache usage statistics on certain shared server resources, including:
The number of databases, objects, and indexes that can be open at any one time
The number of auxiliary scan descriptors used by referential integrity queries
The number of free and active descriptors
The number of free memory pages
The percentage of active descriptors
The maximum number of descriptors used since the server was last started
The current size of the procedure cache and the amount actually used
The name of the instance on which you run sp_monitorconfig, if you are running in a clustered environment, or NULL if you are not running in a clustered environment.
For example, suppose the number of open indexes configuration parameter is 500. During a peak period, you can run sp_monitorconfig to get an accurate reading of the actual metadata cache usage for index descriptors:
sp_monitorconfig "number of open indexes"
Usage information at date and time: May 28 2010 1:12PM. Name Num_free Num_active Pct_act Max_Used Reuse_cnt Instance_Name ------------------------ ---------- ---------- ------- ---------- ----------- ------------------------------ number of open indexes 217 283 56.60 300 0 NULL
The maximum number of open indexes used since the server was last started is 300, even though Adaptive Server is configured for 500. Therefore, you can reset the number of open indexes configuration parameter to 330, which accommodates the 300 maximum used index descriptors, and allows for 10 percent more.
You can also determine the current size of the procedure cache using sp_monitorconfig ''procedure cache size''. This parameter describes the amount of space the procedure cache is currently configured for and the most it has ever actually used. In this example, the procedure cache is configured for 20,000 pages:
sp_configure "procedure cache size"
Parameter Name DefaultMemory Used Config Value Run Value Unit Type ------------------------------ ------------------- ---------- ------------------- -------------------- -------------------- -------------------- procedure cache size 7000 43914 20000 20000 memory pages(2k) dynamic
However, when you run sp_montorconfig “procedure cache size”, you find that the maximum procedure cache ever used is 14241 pages, which means you can lower the run value of the procedure cache, saving memory:
sp_monitorconfig "procedure cache size"
Usage information at date and time: May 28 2010 1:35PM. Name Num_free Num_active Pct_act Max_Used Reuse_cnt Instance_Name ------------------------ ---------- ---------- ------- ---------- ----------- ------------------------------ procedure cache size 5878 14122 70.61 14241 384 NULL
View the number of free memory pages (Num_free
)
to determine if your kernel resource memory configuration is adequate:
sp_monitorconfig "kernel resource memory"
Usage information at date and time: Oct 12 2010 1:35PM. Name Num_free Num_active Pct_act Max_Used Reuse_cnt Instance_Name ------------------------ ---------- ---------- ------- ---------- ----------- ------------------------------ kernel resource memory 9512 728 7.11 728 0 NULL
If the number of free pages is low, but the percent active
(Pct_act
) is high,
you may need to increase the value of kernel
resource memory
.