SQL
Server 2008 includes several Dynamic Management Objects that provide
information about memory and the various caches. Like the Dynamic Management
Objects containing information about the schedulers
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 VAS 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 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 number 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.
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.
http://www.mybasicknowledge.com/2012/09/sql-server-configurations-and.html
http://www.mybasicknowledge.com/2012/09/sql-server-configurations-and.html
