Each of these sections contains the same information for the three types of metadata caches. The output provides this information:
“Active” reports the number of objects, indexes, or databases that were active during the sample interval.
“Max Ever Used Since Boot” reports the maximum number of descriptors used since the last restart of Adaptive Server.
“Free” reports the number of free descriptors in the cache.
“Reuse Requests” reports the number of times that the cache had to be searched for reusable descriptors:
“Failed” means that all descriptors in cache were in use and that the client issuing the request received an error message.
“Succeeded” means the request found a reusable descriptor in cache. Even though “Succeeded” means that the client did not get an error message, Adaptive Server is doing extra work to locate reusable descriptors in the cache and to read metadata information from disk.
You can use this information to set the configuration parameters number of open indexes, number of open objects, and number of open databases, as shown in Table 8-2.
sp_sysmon output |
Action |
|---|---|
Large number of “Free” descriptors |
Set parameter lower |
Very few “Free” descriptors |
Set parameter higher |
“Reuse Requests Succeeded” nonzero |
Set parameter higher |
“Reuse Requests Failed” nonzero |
Set parameter higher |
