SQL Server 2005 includes several dynamic management objects that provide information about memory and the various caches. Like the dynamic management objects containing information about the schedulers, these objects are primarily intended for use by Customer Support Services to see what SQL Server is doing, but you can use them for the same purpose. To select from these objects, you must have the View Server State permission. Once again, I will list some of the more useful or interesting columns for each object; most of these descriptions are taken from SQL Server 2005 Books Online.
sys.dm_os_memory_clerks This view returns one row per memory clerk that is currently active in the instance of SQL Server. You can think of a clerk as an accounting unit. Each store described earlier is a clerk, but some clerks are not stores, such as those for the CLR and for full-text search. The following query returns a list of all the types of clerks:
SELECT DISTINCT type FROM sys.dm_os_memory_clerks Interesting columns include the following:
■ single_pages_kb The amount of single-page memory allocated, in kilobytes. This is the amount of memory allocated by using the single-page allocator of a memory node. This single-page allocator steals pages directly from the buffer pool.
■ multi_pages_kb The amount of multiple-page memory allocated, in kilobytes. This is the amount of memory allocated by using the multiple-page allocator of the memory nodes. This memory is allocated outside the buffer pool and takes advantage of the virtual allocator of the memory nodes.
■ virtual_memory_reserved_kb The amount of virtual memory reserved by a memory clerk. This is the amount of memory reserved directly by the component that uses this clerk. In most situations, only the buffer pool reserves virtual address space directly by using its memory clerk.
■ virtual_memory_committed_kb The amount of memory committed by the clerk. The amount of committed memory should always be less than the amount of Reserved Memory.
■ awe_allocated_kb The amount of memory allocated by the memory clerk by using AWE. In SQL Server, only buffer pool clerks (MEMORYCLERK_SQLBUFFERPOOL) use this mechanism, and only when AWE is enabled.
sys.dm_os_memory_cache_counters This view returns a snapshot of the health of each cache of type userstore and cachestore. It provides run-time information about the cache entries allocated, their use, and the source of memory for the cache entries. Interesting columns include the following:
■ single_pages_kb The amount of the single page memory allocated, in kilobytes. This is the amount of memory allocated by using the single-page allocator. This refers to the 8-KB pages that are taken directly from the buffer pool for this cache.
■ multi_pages_kb The amount of multiple-page memory allocated, in kilobytes. This is the amount of memory allocated by using the multiple-page allocator of the memory node. This memory is allocated outside the buffer pool and takes advantage of the virtual allocator of the memory nodes.
■ multi_pages_in_use_kb The amount of multiple-page memory being used, in kilobytes. ■ single_pages_in_use_kb The amount of single-page memory being used, in kilobytes. ■ entries_count The number of entries in the cache.
■ entries_in_use_count: The number of entries in use in the cache.
sys.dm_os_memory_cache_hash_tables This view returns a row for each active cache in the instance of SQL Server. This view can be joined to sys.dm_os_memory_cache_counters on the cache_address column. Interesting columns include the following:
■ buckets_count The number of buckets in the hash table.
■ buckets_in_use_count The number of buckets currently being used. ■ buckets_min_length The minimum number of cache entries in a bucket. ■ buckets_max_length The maximum number of cache entries in a bucket.
■ buckets_avg_length The average number of cache entries in each bucket. If this num- ber gets very large, it might indicate that the hashing algorithm is not ideal.
■ buckets_avg_scan_hit_length The average number of examined entries in a bucket before the searched-for item was found. As above, a big number might indicate a less- than-optimal cache. You might consider running DBCC FREESYSTEMCACHE to remove all unused entries in the cache stores. You can get more details on this command in Books Online.
sys.dm_os_memory_cache_clock_hands This DMV, discussed earlier, can be joined to the other cache DMVs using the cache_address column. Interesting columns include the following:
■ clock_hand The type of clock hand, either external or internal. Remember that there are two clock hands for every store.
■ clock_status The status of the clock hand: suspended or running. A clock hand runs when a corresponding policy kicks in.
■ rounds_count The number of rounds the clock hand has made. All the external clock hands should have the same (or close to the same) value in this column.
■ removed_all_rounds_count The number of entries removed by the clock hand in all rounds.
Another tool for observing memory use is the command DBCC MEMORYSTATUS, which is greatly enhanced in SQL Server 2005. The book’s companion content includes a Knowledge Base article that describes the output from the enhanced command.