December 6, 2012

SQL Server Operating System Related Dynamic Management Views

Se more view version maps here: Microsoft Sql System View Version Maps

sys.dm_os_buffer_descriptors

Returns information about all the data pages that are currently in the SQL Server buffer pool. The output of this view can be used to determine the distribution of database pages in the buffer pool according to database, object, or type. When a data page is read from disk, the page is copied into the SQL Server buffer pool and cached for reuse. Each cached data page has one buffer descriptor. Buffer descriptors uniquely identify each data page that is currently cached in an instance of SQL Server. sys.dm_os_buffer_descriptors returns cached pages for all user and system databases. This includes pages that are associated with the Resource database.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
database_id int ID of database associated with the page in the buffer pool. Is nullable.
file_id int ID of the file that stores the persisted image of the page. Is nullable
page_id int ID of the page within the file. Is nullable.
page_level int Index level of the page. Is nullable
allocation_unit_id bigint ID of the allocation unit of the page. This value can be used to join sys.allocation_units. Is nullable.
Note sys.dm_os_buffer_descriptors might show nonexistent values in allocation_unit_id for clustered indexes that are created in versions of SQL Server earlier than SQL Server 2005.
page_type nvarchar(60) Type of the page, such as: Data page or Index page. Is nullable. For more information, see Pages and Extents1.
row_count int Number of rows on the page. Is nullable.
free_space_in_bytes int Amount of available free space, in bytes, on the page. Is nullable.
is_modified bit 1 = Page has been modified after it was read from the disk. Is nullable.
numa_mode       int Nonuniform Memory Access node for the buffer.
numa_node     int Nonuniform Memory Access node for the buffer.
read_microsec       bigint The actual time (in microseconds) required to read the page into the buffer. This number is reset when the buffer is reused. Is nullable.

TSQL

Sql 2005
SELECT [database_id], [file_id], [page_id], [page_level], [allocation_unit_id], [page_type], [row_count], [free_space_in_bytes], [is_modified] FROM sys.dm_os_buffer_descriptors
Sql 2008
SELECT [database_id], [file_id], [page_id], [page_level], [allocation_unit_id], [page_type], [row_count], [free_space_in_bytes], [is_modified], [numa_mode] FROM sys.dm_os_buffer_descriptors
Sql 2008 R2
SELECT [database_id], [file_id], [page_id], [page_level], [allocation_unit_id], [page_type], [row_count], [free_space_in_bytes], [is_modified], [numa_node] FROM sys.dm_os_buffer_descriptors
Sql 2012
SELECT [database_id], [file_id], [page_id], [page_level], [allocation_unit_id], [page_type], [row_count], [free_space_in_bytes], [is_modified], [numa_node], [read_microsec] FROM sys.dm_os_buffer_descriptors

Back to Top


sys.dm_os_child_instances

user instance that has been created from the parent server instance. Important This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. The information returned from sys.dm_os_child_instances can be used to determine the state of each User Instance (heart_beat) and to obtain the pipe name (instance_pipe_name) that can be used to create a connection to the User Instance using SQL Server Management Studio or SQLCmd. You can only connect to a User Instance after it has been started by an external process, such as a client application. SQL management tools cannot start a User Instance. Note User Instances are a feature of SQL Server 2012 Express only. ColumnData typeDescription owning_principal_name nvarchar(256) The name of the user that this user instance was created for. owning_principal_sidnvarchar(256)SID (Security-Identifier) of the principal who owns this user instance. This matches Windows SID. owning_principal_sid_binary varbinary(85)Binary version of the SID for the user who owns the user Instance instance_name nvarchar(128) The name of this user instance. instance_pipe_name nvarchar(260) When a user instance is created, a named pipe is created for applications to connect to. This name can be used in a connect string to connect to this user instance. os_process_id Int The process number of the Windows process for this user instance. os_process_creation_date Datetime The date and time when this user instance process was last started. heart_beat nvarchar(5) Current state of this user instance; either ALIVE or DEAD.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
owning_principal_name nvarchar(256) The name of the user that this user instance was created for.
owning_principal_sid nvarchar(256) SID (Security-Identifier) of the principal who owns this user instance. This matches Windows SID.
owning_principal_sid_binary_ varbinary(85) Binary version of the SID for the user who owns the user Instance
instance_name nvarchar(128) The name of this user instance.
instance_pipe_name nvarchar(260) When a user instance is created, a named pipe is created for applications to connect to. This name can be used in a connect string to connect to this user instance.
os_process_id int The process number of the Windows process for this user instance.
os_process_creation_date datetime The date and time when this user instance process was last started.
heart_beat nvarchar(5) Current state of this user instance; either ALIVE or DEAD.

TSQL

Sql 2005
SELECT [owning_principal_name], [owning_principal_sid], [owning_principal_sid_binary_], [instance_name], [instance_pipe_name], [os_process_id], [os_process_creation_date], [heart_beat] FROM sys.dm_os_child_instances
Sql 2008
SELECT [owning_principal_name], [owning_principal_sid], [owning_principal_sid_binary_], [instance_name], [instance_pipe_name], [os_process_id], [os_process_creation_date], [heart_beat] FROM sys.dm_os_child_instances
Sql 2008 R2
SELECT [owning_principal_name], [owning_principal_sid], [owning_principal_sid_binary_], [instance_name], [instance_pipe_name], [os_process_id], [os_process_creation_date], [heart_beat] FROM sys.dm_os_child_instances
Sql 2012
SELECT [owning_principal_name], [owning_principal_sid], [owning_principal_sid_binary_], [instance_name], [instance_pipe_name], [os_process_id], [os_process_creation_date], [heart_beat] FROM sys.dm_os_child_instances

Back to Top


sys.dm_os_cluster_nodes

node in the failover cluster instance configuration. If the current instance is a failover clustered instance, it returns a list of nodes on which this failover cluster instance (formerly "virtual server") has been defined. If the current server instance is not a failover clustered instance, it returns an empty rowset.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
nodename sysname Name of a node in the SQL Server failover cluster instance (virtual server) configuration.
status       int Status of the node in a SQL Server failover cluster instance. For more information, see GetClusterNodeState Function1.
0
1
2
3
-1
status_description       nvarchar(20) Description of the status of the SQL Server failover cluster node.
0 = up
1 = down
2 = paused
3 = joining
-1 = unknown
is_current_owner       bit 1 means this node is the current owner of the SQL Server failover cluster resource.

TSQL

Sql 2005
SELECT [nodename] FROM sys.dm_os_cluster_nodes
Sql 2008
SELECT [nodename] FROM sys.dm_os_cluster_nodes
Sql 2008 R2
SELECT [nodename] FROM sys.dm_os_cluster_nodes
Sql 2012
SELECT [nodename], [status], [status_description], [is_current_owner] FROM sys.dm_os_cluster_nodes

Back to Top


sys.dm_os_hosts

Returns all the hosts currently registered in an instance of SQL Server. This view also returns the resources that are used by these hosts.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
host_address varbinary(8) Internal memory address of the host object.
type nvarchar(60) Type of hosted component. For example,
SOSHOST_CLIENTID_SERVERSNI= SQL Native Client Interface
SOSHOST_CLIENTID_SQLOLEDB = SQL Native Client OLE DB Provider
SOSHOST_CLIENTID_MSDART = Microsoft Data Access Run Time
name nvarchar(32) Name of the host.
enqueued_tasks_count int Total number of tasks that this host has placed onto queues in SQL Server.
active_tasks_count int Number of currently running tasks that this host has placed onto queues.
completed_ios_count int Total number of I/Os issued and completed through this host.
completed_ios_in_bytes bigint Total byte count of the I/Os completed through this host.
active_ios_count int Total number of I/O requests related to this host that are currently waiting to complete.
default_memory_clerk_address varbinary(8) Memory address of the memory clerk object associated with this host. For more information, see sys.dm_os_memory_clerks1.

TSQL

Sql 2005
SELECT [host_address], [type], [name], [enqueued_tasks_count], [active_tasks_count], [completed_ios_count], [completed_ios_in_bytes], [active_ios_count], [default_memory_clerk_address] FROM sys.dm_os_hosts
Sql 2008
SELECT [host_address], [type], [name], [enqueued_tasks_count], [active_tasks_count], [completed_ios_count], [completed_ios_in_bytes], [active_ios_count], [default_memory_clerk_address] FROM sys.dm_os_hosts
Sql 2008 R2
SELECT [host_address], [type], [name], [enqueued_tasks_count], [active_tasks_count], [completed_ios_count], [completed_ios_in_bytes], [active_ios_count], [default_memory_clerk_address] FROM sys.dm_os_hosts
Sql 2012
SELECT [host_address], [type], [name], [enqueued_tasks_count], [active_tasks_count], [completed_ios_count], [completed_ios_in_bytes], [active_ios_count], [default_memory_clerk_address] FROM sys.dm_os_hosts

Back to Top


sys.dm_os_latch_stats

Returns information about all latch waits organized by class.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
latch_class nvarchar(120 ) Name of the latch class.
waiting_requests_count bigint Number of waits on latches in this class. This counter is incremented at the start of a latch wait.
wait_time_ms bigint Total wait time, in milliseconds, on latches in this class.
Note: This column is updated every five minutes during a latch wait and at the end of a latch wait.
max_wait_time_ms bigint Maximum time a memory object has waited on this latch. If this value is unusually high, it might indicate an internal deadlock.

TSQL

Sql 2005
SELECT [latch_class], [waiting_requests_count], [wait_time_ms], [max_wait_time_ms] FROM sys.dm_os_latch_stats
Sql 2008
SELECT [latch_class], [waiting_requests_count], [wait_time_ms], [max_wait_time_ms] FROM sys.dm_os_latch_stats
Sql 2008 R2
SELECT [latch_class], [waiting_requests_count], [wait_time_ms], [max_wait_time_ms] FROM sys.dm_os_latch_stats
Sql 2012
SELECT [latch_class], [waiting_requests_count], [wait_time_ms], [max_wait_time_ms] FROM sys.dm_os_latch_stats

Back to Top


sys.dm_os_loaded_modules

module loaded into the server address space.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
base_address varbinary(8) Address of the module in the process.
file_version varchar(23) Version of the file. Appears in the following format:
x.x:x.x
product_version varchar(23) Version of the product. Appears in the following format:
x.x:x.x
debug bit 1 = Module is a debug version of the loaded module.
patched bit 1 = Module has been patched.
prerelease bit 1 = Module is a pre-release version of the loaded module.
private_build bit 1 = Module is a private build of the loaded module.
special_build bit 1 = Module is a special build of the loaded module.
language int Language of version information of the module.
company nvarchar(256) Name of company that created the module.
description nvarchar(256) Description of the module.
name nvarchar(255) Name of module. Includes the full path of the module.

TSQL

Sql 2005
SELECT [base_address], [file_version], [product_version], [debug], [patched], [prerelease], [private_build], [special_build], [language], [company], [description], [name] FROM sys.dm_os_loaded_modules
Sql 2008
SELECT [base_address], [file_version], [product_version], [debug], [patched], [prerelease], [private_build], [special_build], [language], [company], [description], [name] FROM sys.dm_os_loaded_modules
Sql 2008 R2
SELECT [base_address], [file_version], [product_version], [debug], [patched], [prerelease], [private_build], [special_build], [language], [company], [description], [name] FROM sys.dm_os_loaded_modules
Sql 2012
SELECT [base_address], [file_version], [product_version], [debug], [patched], [prerelease], [private_build], [special_build], [language], [company], [description], [name] FROM sys.dm_os_loaded_modules

Back to Top


sys.dm_os_memory_cache_clock_hands

Returns the status of each hand for a specific cache clock.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
cache_address varbinary(8) Address of the cache associated with the clock. Is not nullable.
name nvarchar(256) Name of the cache. Is not nullable.
type nvarchar(60) Type of cache store. There can be several caches of the same type. Is not nullable.
clock_hand nvarchar(60) Type of hand. This is one of the following:
External

Internal

Is not nullable.
clock_status nvarchar(60) Status of the clock. This is one of the following:
Suspended

Running

Is not nullable.
rounds_count bigint Number of sweeps made through the cache to remove entries. Is not nullable.
removed_all_rounds_count bigint Number of entries removed by all sweeps. Is not nullable.
updated_last_round_count bigint Number of entries updated during the last sweep. Is not nullable.
removed_last_round_count bigint Number of entries removed during the last sweep. Is not nullable.
last_tick_time bigint Last time, in CPU ticks, that the clock hand moved. Is not nullable.
round_start_time bigint Time, in CPU ticks, of the previous sweep. Is not nullable.
last_round_start_time bigint Total time, in CPU ticks, taken by the clock to complete the previous round. Is not nullable.

TSQL

Sql 2005
SELECT [cache_address], [name], [type], [clock_hand], [clock_status], [rounds_count], [removed_all_rounds_count], [updated_last_round_count], [removed_last_round_count], [last_tick_time], [round_start_time], [last_round_start_time] FROM sys.dm_os_memory_cache_clock_hands
Sql 2008
SELECT [cache_address], [name], [type], [clock_hand], [clock_status], [rounds_count], [removed_all_rounds_count], [updated_last_round_count], [removed_last_round_count], [last_tick_time], [round_start_time], [last_round_start_time] FROM sys.dm_os_memory_cache_clock_hands
Sql 2008 R2
SELECT [cache_address], [name], [type], [clock_hand], [clock_status], [rounds_count], [removed_all_rounds_count], [updated_last_round_count], [removed_last_round_count], [last_tick_time], [round_start_time], [last_round_start_time] FROM sys.dm_os_memory_cache_clock_hands
Sql 2012
SELECT [cache_address], [name], [type], [clock_hand], [clock_status], [rounds_count], [removed_all_rounds_count], [updated_last_round_count], [removed_last_round_count], [last_tick_time], [round_start_time], [last_round_start_time] FROM sys.dm_os_memory_cache_clock_hands

Back to Top


sys.dm_os_memory_cache_counters

Returns a snapshot of the health of a cache in SQL Server 2012. sys.dm_os_memory_cache_counters provides run-time information about the cache entries allocated, their use, and the source of memory for the cache entries.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
cache_address varbinary(8) Address (primary key) of the counters associated with a specific cache. Is not nullable.
name nvarchar(256) Name of the cache. Is not nullable.
type nvarchar(60) Type of cache that is associated with this entry. Is not nullable.
single_pages_kb   bigint Amount, in kilobytes, of the single-page memory allocated. 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. Is not nullable.
multi_pages_kb   bigint Amount, in kilobytes, of the multipage memory allocated. 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. Is not nullable.
single_pages_in_use_kb   bigint Amount, in kilobytes, of the single-page memory that is being used. Is nullable. This information is not tracked for objects of type USERSTORE_<*> and these values will be NULL.
multi_pages_in_use_kb   bigint Amount, in kilobytes, of the multipage memory that is being used. NULLABLE. This information is not tracked for objects of type USERSTORE_<*>, and these values will be NULL.
entries_count bigint Number of entries in the cache. Is not nullable.
entries_in_use_count bigint Number of entries in the cache that is being used. Is not nullable.
pages_kb       bigint Specifies the amount, in kilobytes, of the memory allocated in the cache. Is not nullable.
pages_in_use_kb       bigint Specifies the amount, in kilobytes, of the memory that is allocated and in use in the cache. Is nullable. Values for objects of type USERSTORE_<*> are not tracked. NULL is reported for them.

TSQL

Sql 2005
SELECT [cache_address], [name], [type], [single_pages_kb], [multi_pages_kb], [single_pages_in_use_kb], [multi_pages_in_use_kb], [entries_count], [entries_in_use_count] FROM sys.dm_os_memory_cache_counters
Sql 2008
SELECT [cache_address], [name], [type], [single_pages_kb], [multi_pages_kb], [single_pages_in_use_kb], [multi_pages_in_use_kb], [entries_count], [entries_in_use_count] FROM sys.dm_os_memory_cache_counters
Sql 2008 R2
SELECT [cache_address], [name], [type], [single_pages_kb], [multi_pages_kb], [single_pages_in_use_kb], [multi_pages_in_use_kb], [entries_count], [entries_in_use_count] FROM sys.dm_os_memory_cache_counters
Sql 2012
SELECT [cache_address], [name], [type], [pages_kb], [pages_in_use_kb], [entries_count], [entries_in_use_count] FROM sys.dm_os_memory_cache_counters

Back to Top


sys.dm_os_memory_cache_entries

Returns information about all entries in caches in SQL Server 2012. Use this view to trace cache entries to their associated objects. You can also use this view to obtain statistics on cache entries.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
cache_address varbinary(8) Address of the cache. Is not nullable.
name nvarchar(256) Name of the cache. Is not nullable.
type varchar(60) Type of cache. Is not nullable.
in_use_count int Number of concurrent users of this cache entry. Is not nullable.
is_dirty bit 1 = This cache entry has changes that have not been persisted.
Is not nullable.
entry_address varbinary(8) Address of the descriptor of the cache entry. Is not nullable.
entry_data_address varbinary(8) Address of the user data in the cache entry.
0x00000000 = Entry data address is not available.
Is not nullable.
disk_ios_count int Number of I/Os incurred because of this entry. Is not nullable.
context_switches_count int Number of context switches incurred because of this entry. Is not nullable.
original_cost int Original cost of the entry. This value is an approximation of the number of I/Os incurred, CPU instruction cost, and the context switch count. The greater the cost, the lower the chance that the item will be removed from the cache. Is not nullable.
current_cost int Current cost of the cache entry. This value is updated during the process of entry purging. Current cost is reset to its original value on entry reuse. Is not nullable.
memory_object_address varbinary(8) Address of the associated memory object. Is nullable.
pages_allocated_count   bigint Number of 8-KB pages to store this cache entry. Is not nullable.
entry_data nvarchar(2048) Serialized representation of the cached entry. This information is cache implementation dependant. Is nullable.
pool_id     int The ID of the resource pool.
pages_kb       bigint Amount of memory in kilobytes (KB) used by this cache entry. Is not nullable.

TSQL

Sql 2005
SELECT [cache_address], [name], [type], [in_use_count], [is_dirty], [entry_address], [entry_data_address], [disk_ios_count], [context_switches_count], [original_cost], [current_cost], [memory_object_address], [pages_allocated_count], [entry_data] FROM sys.dm_os_memory_cache_entries
Sql 2008
SELECT [cache_address], [name], [type], [in_use_count], [is_dirty], [entry_address], [entry_data_address], [disk_ios_count], [context_switches_count], [original_cost], [current_cost], [memory_object_address], [pages_allocated_count], [entry_data] FROM sys.dm_os_memory_cache_entries
Sql 2008 R2
SELECT [cache_address], [name], [type], [entry_address], [entry_data_address], [in_use_count], [is_dirty], [disk_ios_count], [context_switches_count], [original_cost], [current_cost], [memory_object_address], [pages_allocated_count], [entry_data], [pool_id] FROM sys.dm_os_memory_cache_entries
Sql 2012
SELECT [cache_address], [name], [type], [entry_address], [entry_data_address], [in_use_count], [is_dirty], [disk_ios_count], [context_switches_count], [original_cost], [current_cost], [memory_object_address], [pages_kb], [entry_data], [pool_id] FROM sys.dm_os_memory_cache_entries

Back to Top


sys.dm_os_memory_cache_hash_tables

active cache in the instance of SQL Server.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
cache_address varbinary(8) Address (primary key) of the cache entry. Is not nullable.
name nvarchar(256) Name of the cache. Is not nullable.
type nvarchar(60) Type of cache. Is not nullable.
table_level int Hash table number. A particular cache may have multiple hash tables that correspond to different hash functions. Is not nullable.
buckets_count int Number of buckets in the hash table. Is not nullable.
buckets_in_use_count int Number of buckets that are currently being used. Is not nullable.
buckets_min_length int Minimum number of cache entries in a bucket. Is not nullable.
buckets_max_length int Maximum number of cache entries in a bucket. Is not nullable.
buckets_avg_length int Average number of cache entries in each bucket. Is not nullable.
buckets_max_length_ever int Maximum number of cached entries in a hash bucket for this hash table since the server was started. Is not nullable.
hits_count bigint Number of cache hits. Is not nullable.
misses_count bigint Number of cache misses. Is not nullable.
buckets_avg_scan_hit_length int Average number of examined entries in a bucket before the searched for an item was found. Is not nullable.
buckets_avg_scan_miss_length int Average number of examined entries in a bucket before the search ended unsuccessfully. Is not nullable.

TSQL

Sql 2005
SELECT [cache_address], [name], [type], [table_level], [buckets_count], [buckets_in_use_count], [buckets_min_length], [buckets_max_length], [buckets_avg_length], [buckets_max_length_ever], [hits_count], [misses_count], [buckets_avg_scan_hit_length], [buckets_avg_scan_miss_length] FROM sys.dm_os_memory_cache_hash_tables
Sql 2008
SELECT [cache_address], [name], [type], [table_level], [buckets_count], [buckets_in_use_count], [buckets_min_length], [buckets_max_length], [buckets_avg_length], [buckets_max_length_ever], [hits_count], [misses_count], [buckets_avg_scan_hit_length], [buckets_avg_scan_miss_length] FROM sys.dm_os_memory_cache_hash_tables
Sql 2008 R2
SELECT [cache_address], [name], [type], [table_level], [buckets_count], [buckets_in_use_count], [buckets_min_length], [buckets_max_length], [buckets_avg_length], [buckets_max_length_ever], [hits_count], [misses_count], [buckets_avg_scan_hit_length], [buckets_avg_scan_miss_length] FROM sys.dm_os_memory_cache_hash_tables
Sql 2012
SELECT [cache_address], [name], [type], [table_level], [buckets_count], [buckets_in_use_count], [buckets_min_length], [buckets_max_length], [buckets_avg_length], [buckets_max_length_ever], [hits_count], [misses_count], [buckets_avg_scan_hit_length], [buckets_avg_scan_miss_length] FROM sys.dm_os_memory_cache_hash_tables

Back to Top


sys.dm_os_memory_clerks

Returns the set of all memory clerks that are currently active in the instance of SQL Server.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
memory_clerk_address varbinary(8) Unique memory address of the memory clerk. This is the primary key column. Is not nullable.
type nvarchar(60) Type of memory clerk. Every clerk has a specific type, such as CLR Clerks MEMORYCLERK_SQLCLR. Is not nullable.
name nvarchar(256) Internally assigned name of this memory clerk. Every component can have several memory clerks of a specific type. A component might choose to use specific names to identify memory clerks of the same type. Is not nullable.
memory_node_id smallint ID of the memory node. Is not nullable.
single_pages_kb   bigint Amount of single page memory allocated in kilobytes (KB). 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. Is not nullable.
multi_pages_kb   bigint Amount of multipage memory allocated in KB. 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. Is not nullable.
virtual_memory_reserved_kb bigint Amount of virtual memory that is 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. Is not nullable.
virtual_memory_committed_kb bigint Amount of virtual memory that is committed by a memory clerk. This is the amount of memory committed by the clerk. The amount of committed memory should always be less than the amount of reserved memory. Not NULLABLE.
awe_allocated_kb bigint Amount of memory that is allocated by the memory clerk by using Address Windowing Extensions (AWE). In SQL Server, only buffer pool clerks (MEMORYCLERK_SQLBUFFERPOOL) use this mechanism, and only when AWE is enabled. Is not nullable.
shared_memory_reserved_kb bigint Amount of shared memory that is reserved by a memory clerk. The amount of memory reserved for use by shared memory and file mapping. Is not nullable.
shared_memory_committed_kb bigint Amount of shared memory that is committed by the memory clerk. Is not nullable.
page_size_bytes   bigint Size of the page that can be allocated by a memory clerk. Only one size, 8192 bytes, is supported. Is not nullable.
page_allocator_address varbinary(8) Address of the page allocator. This address is unique for a memory clerk and can be used in sys.dm_os_memory_objects to locate memory objects that are bound to this clerk. Is not nullable.
host_address varbinary(8) Memory address of the host for this memory clerk. For more information, see sys.dm_os_hosts1. Components, such as Microsoft SQL Native Client, access SQL Server memory resources through the host interface.
0x00000000 = Memory clerk belongs to SQL Server.
Is not nullable.
pages_kb       bigint Specifies the amount of page memory allocated in kilobytes (KB) for this memory clerk. Is not nullable.
page_size_in_bytes       bigint Specifies the granularity of the page allocation for this memory clerk. Is not nullable.

TSQL

Sql 2005
SELECT [memory_clerk_address], [type], [name], [memory_node_id], [single_pages_kb], [multi_pages_kb], [virtual_memory_reserved_kb], [virtual_memory_committed_kb], [awe_allocated_kb], [shared_memory_reserved_kb], [shared_memory_committed_kb], [page_size_bytes], [page_allocator_address], [host_address] FROM sys.dm_os_memory_clerks
Sql 2008
SELECT [memory_clerk_address], [type], [name], [memory_node_id], [single_pages_kb], [multi_pages_kb], [virtual_memory_reserved_kb], [virtual_memory_committed_kb], [awe_allocated_kb], [shared_memory_reserved_kb], [shared_memory_committed_kb], [page_size_bytes], [page_allocator_address], [host_address] FROM sys.dm_os_memory_clerks
Sql 2008 R2
SELECT [memory_clerk_address], [type], [name], [memory_node_id], [single_pages_kb], [multi_pages_kb], [virtual_memory_reserved_kb], [virtual_memory_committed_kb], [awe_allocated_kb], [shared_memory_reserved_kb], [shared_memory_committed_kb], [page_size_bytes], [page_allocator_address], [host_address] FROM sys.dm_os_memory_clerks
Sql 2012
SELECT [memory_clerk_address], [type], [name], [memory_node_id], [pages_kb], [virtual_memory_reserved_kb], [virtual_memory_committed_kb], [awe_allocated_kb], [shared_memory_reserved_kb], [shared_memory_committed_kb], [page_size_in_bytes], [page_allocator_address], [host_address] FROM sys.dm_os_memory_clerks

Back to Top


sys.dm_os_memory_objects

Returns memory objects that are currently allocated by SQL Server. You can use sys.dm_os_memory_objects to analyze memory use and to identify possible memory leaks.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
memory_object_address varbinary(8) Address of the memory object. Is not nullable.
parent_address varbinary(8) Address of the parent memory object. Is nullable.
pages_allocated_count   int Number of pages that are allocated by this object. Is not nullable.
creation_options int Internal use only. Is nullable.
bytes_used bigint Internal use only. Is nullable.
type nvarchar(60) Type of memory object.
This indicates a component that this memory object belongs to, or the function of the memory object. Is nullable.
name varchar(128) Internal use only. Is nullable.
memory_node_id smallint ID of a memory node that is being used by this memory object. Is not nullable.
creation_time datetime Internal only. NULLABLE.
page_size_in_bytes int Size of pages allocated by this object. Is not nullable.
max_pages_allocated_count   int Maximum number of pages allocated by this memory object. Is not nullable.
page_allocator_address varbinary(8) Memory address of page allocator. Is not nullable. For more information, see sys.dm_os_memory_clerks1.
creation_stack_address varbinary(8) Internal use only. Is nullable.
sequence_num int Internal use only. Is nullable.
pages_in_bytes       bigint Amount of memory in bytes that is allocated by this instance of the memory object. Is not nullable.
max_pages_in_bytes       bigint Maximum amount of memory ever used by this memory object. Is not nullable.

TSQL

Sql 2005
SELECT [memory_object_address], [parent_address], [pages_allocated_count], [creation_options], [bytes_used], [type], [name], [memory_node_id], [creation_time], [page_size_in_bytes], [max_pages_allocated_count], [page_allocator_address], [creation_stack_address], [sequence_num] FROM sys.dm_os_memory_objects
Sql 2008
SELECT [memory_object_address], [parent_address], [pages_allocated_count], [creation_options], [bytes_used], [type], [name], [memory_node_id], [creation_time], [page_size_in_bytes], [max_pages_allocated_count], [page_allocator_address], [creation_stack_address], [sequence_num] FROM sys.dm_os_memory_objects
Sql 2008 R2
SELECT [memory_object_address], [parent_address], [pages_allocated_count], [creation_options], [bytes_used], [type], [name], [memory_node_id], [creation_time], [page_size_in_bytes], [max_pages_allocated_count], [page_allocator_address], [creation_stack_address], [sequence_num] FROM sys.dm_os_memory_objects
Sql 2012
SELECT [memory_object_address], [parent_address], [pages_in_bytes], [creation_options], [bytes_used], [type], [name], [memory_node_id], [creation_time], [page_size_in_bytes], [max_pages_in_bytes], [page_allocator_address], [creation_stack_address], [sequence_num] FROM sys.dm_os_memory_objects

Back to Top


sys.dm_os_memory_pools

object store in the instance of SQL Server. You can use this view to monitor cache memory use and to identify bad caching behavior

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
memory_pool_address varbinary(8) Memory address of the entry that represents the memory pool. Is not nullable.
pool_id int ID of a specific pool within a set of pools. Is not nullable.
type nvarchar(60) Type of object pool. Is not nullable. For more information, see sys.dm_os_memory_clerks1.
name nvarchar(256) System-assigned name of this memory object. Is not nullable.
max_free_entries_count bigint Maximum number of free entries that a pool can have. Is not nullable.
free_entries_count bigint Number of free entries currently in the pool. Is not nullable.
removed_in_all_rounds_count bigint Number of entries removed from the pool since the instance of SQL Server was started. Is not nullable.

TSQL

Sql 2005
SELECT [memory_pool_address], [pool_id], [type], [name], [max_free_entries_count], [free_entries_count], [removed_in_all_rounds_count] FROM sys.dm_os_memory_pools
Sql 2008
SELECT [memory_pool_address], [pool_id], [type], [name], [max_free_entries_count], [free_entries_count], [removed_in_all_rounds_count] FROM sys.dm_os_memory_pools
Sql 2008 R2
SELECT [memory_pool_address], [pool_id], [type], [name], [max_free_entries_count], [free_entries_count], [removed_in_all_rounds_count] FROM sys.dm_os_memory_pools
Sql 2012
SELECT [memory_pool_address], [pool_id], [type], [name], [max_free_entries_count], [free_entries_count], [removed_in_all_rounds_count] FROM sys.dm_os_memory_pools

Back to Top


sys.dm_os_performance_counters

Returns a row per performance counter maintained by the server. For information about each performance counter, see Use SQL Server Objects1.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
object_name nchar(128) Category to which this counter belongs.
counter_name nchar(128) Name of the counter.
instance_name nchar(128) Name of the specific instance of the counter. Often contains the database name.
cntr_value bigint Current value of the counter.
Note: For per-second counters, this value is cumulative. The rate value must be calculated by sampling the value at discrete time intervals. The difference between any two successive sample values is equal to the rate for the time interval used.
cntr_type int Type of counter as defined by the Windows performance architecture. See WMI Performance Counter2 Types or your Windows Server documentation for more information on performance counter types.

TSQL

Sql 2005
SELECT [object_name], [counter_name], [instance_name], [cntr_value], [cntr_type] FROM sys.dm_os_performance_counters
Sql 2008
SELECT [object_name], [counter_name], [instance_name], [cntr_value], [cntr_type] FROM sys.dm_os_performance_counters
Sql 2008 R2
SELECT [object_name], [counter_name], [instance_name], [cntr_value], [cntr_type] FROM sys.dm_os_performance_counters
Sql 2012
SELECT [object_name], [counter_name], [instance_name], [cntr_value], [cntr_type] FROM sys.dm_os_performance_counters

Back to Top


sys.dm_os_schedulers

Returns one row per scheduler in SQL Server where each scheduler is mapped to an individual processor. Use this view to monitor the condition of a scheduler or to identify runaway tasks.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
scheduler_address varbinary(8) Memory address of the scheduler. Is not nullable.
parent_node_id int ID of the node that the scheduler belongs to, also known as the parent node. This represents a nonuniform memory access (NUMA) node. Is not nullable.
scheduler_id int ID of the scheduler. All schedulers that are used to run regular queries have ID numbers less than 255. Those schedulers that have IDs greater than or equal to 255 are used internally by SQL Server, such as the dedicated administrator connection scheduler. Is not nullable.
cpu_id smallint ID of the CPU with which this scheduler is associated. If SQL Server is configured to run with affinity, the value is the ID of the CPU on which the scheduler is supposed to be running.
255 = Affinity mask is not specified.
Is not nullable.
status nvarchar(60) Indicates the status of the scheduler. Can be one of the following values:
HIDDEN ONLINE

HIDDEN OFFLINE

VISIBLE ONLINE

VISIBLE OFFLINE

VISIBLE ONLINE (DAC)

Is not nullable.
HIDDEN schedulers are used to process requests that are internal to the Database Engine. VISIBLE schedulers are used to process user requests.
OFFLINE schedulers map to processors that are offline in the affinity mask and are, therefore, not being used to process any requests. ONLINE schedulers map to processors that are online in the affinity mask and are available to process threads.
DAC indicates the scheduler is running under a dedicated administrator connection.
is_online bit If SQL Server is configured to use only some of the available processors on the server, this configuration can mean that some schedulers are mapped to processors that are not in the affinity mask. If that is the case, this column returns 0. This value means that the scheduler is not being used to process queries or batches.
Is not nullable.
is_idle bit 1 = Scheduler is idle. No workers are currently running. Is not nullable.
preemptive_switches_count int Number of times that workers on this scheduler have switched to the preemptive mode.
To execute code that is outside SQL Server (for example, extended stored procedures and distributed queries), a thread has to execute outside the control of the non-preemptive scheduler. To do this, a worker switches to preemptive mode.
context_switches_count int Number of context switches that have occurred on this scheduler. Is not nullable.
To allow for other workers to run, the current running worker has to relinquish control of the scheduler or switch context.
Note: If a worker yields the scheduler and puts itself into the runnable queue and then finds no other workers, the worker will select itself. In this case, the context_switches_count is not updated, but the yield_count is updated.
idle_switches_count int Number of times the scheduler has been waiting for an event while idle. This column is similar to context_switches_count. Is not nullable.
current_tasks_count int Number of current tasks that are associated with this scheduler. This count includes the following:
Tasks that are waiting for a worker to execute them.

Tasks that are currently waiting or running (in SUSPENDED or RUNNABLE state).

When a task is completed, this count is decremented. Is not nullable.
runnable_tasks_count int Number of workers, with tasks assigned to them, that are waiting to be scheduled on the runnable queue. Is not nullable.
current_workers_count int Number of workers that are associated with this scheduler. This count includes workers that are not assigned any task. Is not nullable.
active_workers_count int Number of workers that are active. An active worker is always nonpreemptive, must have an associated task, and is either running, runnable, or suspended. Is not nullable.
work_queue_count bigint Number of tasks in the pending queue. These tasks are waiting for a worker to pick them up. Is not nullable.
pending_disk_io_count int Number of pending I/Os that are waiting to be completed. Each scheduler has a list of pending I/Os that are checked to determine whether they have been completed every time there is a context switch. The count is incremented when the request is inserted. This count is decremented when the request is completed. This number does not indicate the state of the I/Os. Is not nullable.
load_factor int Internal value that indicates the perceived load on this scheduler. This value is used to determine whether a new task should be put on this scheduler or another scheduler. This value is useful for debugging purposes when it appears that schedulers are not evenly loaded. In SQL Server 2000, a task is routed to a particular scheduler. However, in SQL Server 2005, the routing decision is made based on the load on the scheduler. SQL Server 2005 also uses a load factor of nodes and schedulers to help determine the best location to acquire resources. When a task is enqueued, the load factor is increased. When a task is completed, the load factor is decreased. Using the load factors helps SQL Server OS balance the work load better. Is not nullable.
yield_count int Internal value that is used to indicate progress on this scheduler. This value is used by the Scheduler Monitor to determine whether a worker on the scheduler is not yielding to other workers on time. This value does not indicate that the worker or task transitioned to a new worker. Is not nullable.
last_timer_activity bigint In CPU ticks, the last time that the scheduler timer queue was checked by the scheduler. Is not nullable.
failed_to_create_worker bit Set to 1 if a new worker could not be created on this scheduler. This generally occurs because of memory constraints. Is nullable.
active_worker_address varbinary(8) Memory address of the worker that is currently active. Is nullable. For more information, see sys.dm_os_workers1.
memory_object_address varbinary(8) Memory address of the scheduler memory object. Not NULLABLE.
task_memory_object_address varbinary(8) Memory address of the task memory object. Is not nullable. For more information, see sys.dm_os_memory_objects2.
quantum_length_us     bigint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. Exposes the scheduler quantum used by SQLOS.

TSQL

Sql 2005
SELECT [scheduler_address], [parent_node_id], [scheduler_id], [cpu_id], [status], [is_online], [is_idle], [preemptive_switches_count], [context_switches_count], [idle_switches_count], [current_tasks_count], [runnable_tasks_count], [current_workers_count], [active_workers_count], [work_queue_count], [pending_disk_io_count], [load_factor], [yield_count], [last_timer_activity], [failed_to_create_worker], [active_worker_address], [memory_object_address], [task_memory_object_address] FROM sys.dm_os_schedulers
Sql 2008
SELECT [scheduler_address], [parent_node_id], [scheduler_id], [cpu_id], [status], [is_online], [is_idle], [preemptive_switches_count], [context_switches_count], [idle_switches_count], [current_tasks_count], [runnable_tasks_count], [current_workers_count], [active_workers_count], [work_queue_count], [pending_disk_io_count], [load_factor], [yield_count], [last_timer_activity], [failed_to_create_worker], [active_worker_address], [memory_object_address], [task_memory_object_address] FROM sys.dm_os_schedulers
Sql 2008 R2
SELECT [scheduler_address], [parent_node_id], [scheduler_id], [cpu_id], [status], [is_online], [is_idle], [preemptive_switches_count], [context_switches_count], [idle_switches_count], [current_tasks_count], [runnable_tasks_count], [current_workers_count], [active_workers_count], [work_queue_count], [pending_disk_io_count], [load_factor], [yield_count], [last_timer_activity], [failed_to_create_worker], [active_worker_address], [memory_object_address], [task_memory_object_address], [quantum_length_us] FROM sys.dm_os_schedulers
Sql 2012
SELECT [scheduler_address], [parent_node_id], [scheduler_id], [cpu_id], [status], [is_online], [is_idle], [preemptive_switches_count], [context_switches_count], [idle_switches_count], [current_tasks_count], [runnable_tasks_count], [current_workers_count], [active_workers_count], [work_queue_count], [pending_disk_io_count], [load_factor], [yield_count], [last_timer_activity], [failed_to_create_worker], [active_worker_address], [memory_object_address], [task_memory_object_address], [quantum_length_us] FROM sys.dm_os_schedulers

Back to Top


sys.dm_os_stacks

This dynamic management view is used internally by SQL Server to do the following: Keep track of debug data such as outstanding allocations. Assume or validate logic that is used by SQL Server components in places where the component assumes that a certain call has been made.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
stack_address varbinary(8) Unique address for this stack allocation. Is not nullable.
frame_index int Each line represents a function call that, when sorted in ascending order by frame index for a particular stack_address, returns the full call stack. Is not nullable.
frame_address varbinary(8) Address of the function call. Is not nullable.

TSQL

Sql 2005
SELECT [stack_address], [frame_index], [frame_address] FROM sys.dm_os_stacks
Sql 2008
SELECT [stack_address], [frame_index], [frame_address] FROM sys.dm_os_stacks
Sql 2008 R2
SELECT [stack_address], [frame_index], [frame_address] FROM sys.dm_os_stacks
Sql 2012
SELECT [stack_address], [frame_index], [frame_address] FROM sys.dm_os_stacks

Back to Top


sys.dm_os_sys_info

Returns a miscellaneous set of useful information about the computer, and about the resources available to and consumed by SQL Server.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
cpu_ticks bigint Current CPU tick count. CPU ticks are obtained from the processor's RDTSC counter. It is a monotonically increasing number.
ms_ticks bigint Number of milliseconds since the computer was started.
cpu_count int Number of logical CPUs on the system.
cpu_ticks_in_ms       bigint Number of CPU ticks in milliseconds.
hyperthread_ratio int Ratio of the number of logical and physical processors.
physical_memory_in_bytes   bigint Amount of physical memory available.
virtual_memory_in_bytes   bigint Amount of virtual memory available to the process in user mode. This can be used to determine whether SQL Server was started by using a 3-GB switch.
bpool_committed   int Number of 8-KB buffers in the buffer pool. This amount represents committed physical memory in the buffer pool. Does not include reserved memory in the buffer pool.
bpool_commit_target   int Number of 8-KB buffers needed by the buffer pool. The target amount is calculated using a variety of inputs such as the current state of the system, including its load, the memory requested by current processes, the amount of memory installed on the computer, and configuration parameters. If the bpool_commit_target is larger than the bpool_committed value, the buffer pool will try to obtain additional memory. If the bpool_commit_target is smaller than the bpool_committed value, the buffer pool will shrink.
bpool_visible   int Number of 8-KB buffers in the buffer pool that are directly accessible in the process virtual address space. When not using the Address Windowing Extensions (AWE), when the buffer pool has obtained its memory target (bpool_committed = bpool_commit_target), the value of bpool_visible equals the value of bpool_committed.
When using AWE on a 32-bit version of SQL Server, bpool_visible represents the size of the AWE mapping window used to access physical memory allocated by the buffer pool. The size of this mapping window is bound by the process address space and, therefore, the visible amount will be smaller than the committed amount, and can be further reduced by internal components consuming memory for purposes other than database pages. If the value of bpool_visible is too low, you might receive out of memory errors.
stack_size_in_bytes int Size of the call stack for each thread created by SQL Server.
os_quantum bigint Quantum for a non-preemptive task, measured in CPU ticks. Quantum (in seconds) = os_quantum / CPU clock speed.
os_error_mode int Error mode for the SQL Server process.
os_priority_class int Priority class for the SQL Server process.
max_workers_count int Maximum number of workers that can be created.
scheduler_count int Number of user schedulers configured in the SQL Server process.
scheduler_total_count int Total number of schedulers in SQL Server.
deadlock_monitor_serial_number int ID of the current deadlock monitor sequence.
sqlserver_start_time_ms_ticks   bigint ms_tick number when SQL Server last started. Compare to the current ms_ticks column.
sqlserver_start_time   datetime Date and time SQL Server last started.
affinity_type     int Describes the affinity type that SQL Server uses.

ValueDescription
1Manual
2Auto
affinity_type_description       varchar(60) Describes the value in the affinity_type column. For more information, see ALTER SERVER CONFIGURATION (Transact-SQL)2.

ValueDescription
MANUALAffinity has been set for at least one CPU.
AUTOSQL Server moves threads between CPUs as required.
process_kernel_time_ms     bigint Total time in milliseconds spent by all SQL Server threads in kernel mode.
Note
This value can be larger than a single processor clock because it includes the time for all processors on the server.
process_user_time_ms     bigint Total time in milliseconds spent by all SQL Server threads in user mode.
Note
This value can be larger than a single processor clock because it includes the time for all processors on the server.
time_source     int Describes the API that SQL Server is using to retrieve wall clock time.

ValueDescription
0Indicates the QueryPerformanceCounter3 API.
1Indicates this multimedia timer4 API.
time_source_desc     nvarchar(60) Describes the value in the time_source column.

ValueDescription
QUERY_PERFORMANCE_COUNTERThe QueryPerformanceCounter3 API retrieves wall clock time.
MULTIMEDIA_TIMERThe multimedia timer4 API that retrieves wall clock time.
virtual_machine_type     int Indicates whether SQL Server is running in a virtualized environment. Not nullable.

ValueDescription
0NONE
1HYPERVISOR
2Other
virtual_machine_type_desc     nvarchar(60) Describes the virtual_machine_type column. Not nullable.

ValueDescription
NoneSQL Server is not running inside a virtual machine.
HYPERVISORSQL Server is running inside a hypervisor, which implies a hardware-assisted virtualization. If the instance is running on the host OS, the description will still return HYPERVISOR.
OtherSQL Server is running inside a virtual machine that does not employ hardware assistant such as Microsoft Virtual PC.
physical_memory_kb       bigint Specifies the total amount of physical memory on the machine. Not nullable.
virtual_memory_kb       bigint Specifies the total amount of virtual address space available to the process in user mode. Not nullable.
committed_kb       int Represents the committed memory in kilobytes (KB) in the memory manager. Does not include reserved memory in the memory manager. Not nullable.
committed_target_kb       int Represents the amount of memory, in kilobytes (KB), that can be consumed by SQL Server memory manager. The target amount is calculated using a variety of inputs like:
the current state of the system including its load
the memory requested by current processes
the amount of memory installed on the computer
configuration parameters
If committed_target_kb is larger than committed_kb, the memory manager will try to obtain additional memory. If committed_target_kb is smaller than committed_kb, the memory manager will try to shrink the amount of memory committed. The committed_target_kb always includes stolen and reserved memory. Not nullable.
visible_target_kb       int Is the same as committed_target_kb. Not nullable.
affinity_type_desc       varchar(60) Describes the affinity_type column. Not nullable.
MANUAL = affinity has been set for at least one CPU.
AUTO = SQL Server can freely move threads between CPUs.

TSQL

Sql 2005
SELECT [cpu_ticks], [ms_ticks], [cpu_count], [cpu_ticks_in_ms], [hyperthread_ratio], [physical_memory_in_bytes], [virtual_memory_in_bytes], [bpool_committed], [bpool_commit_target], [bpool_visible], [stack_size_in_bytes], [os_quantum], [os_error_mode], [os_priority_class], [max_workers_count], [scheduler_count], [scheduler_total_count], [deadlock_monitor_serial_number] FROM sys.dm_os_sys_info
Sql 2008
SELECT [cpu_ticks], [ms_ticks], [cpu_count], [hyperthread_ratio], [physical_memory_in_bytes], [virtual_memory_in_bytes], [bpool_committed], [bpool_commit_target], [bpool_visible], [stack_size_in_bytes], [os_quantum], [os_error_mode], [os_priority_class], [max_workers_count], [scheduler_count], [scheduler_total_count], [deadlock_monitor_serial_number], [sqlserver_start_time_ms_ticks], [sqlserver_start_time] FROM sys.dm_os_sys_info
Sql 2008 R2
SELECT [cpu_ticks], [ms_ticks], [cpu_count], [hyperthread_ratio], [physical_memory_in_bytes], [virtual_memory_in_bytes], [bpool_committed], [bpool_commit_target], [bpool_visible], [stack_size_in_bytes], [os_quantum], [os_error_mode], [os_priority_class], [max_workers_count], [scheduler_count], [scheduler_total_count], [deadlock_monitor_serial_number], [sqlserver_start_time_ms_ticks], [sqlserver_start_time], [affinity_type], [affinity_type_description], [process_kernel_time_ms], [process_user_time_ms], [time_source], [time_source_desc], [virtual_machine_type], [virtual_machine_type_desc] FROM sys.dm_os_sys_info
Sql 2012
SELECT [cpu_ticks], [ms_ticks], [cpu_count], [hyperthread_ratio], [physical_memory_kb], [virtual_memory_kb], [committed_kb], [committed_target_kb], [visible_target_kb], [stack_size_in_bytes], [os_quantum], [os_error_mode], [os_priority_class], [max_workers_count], [scheduler_count], [scheduler_total_count], [deadlock_monitor_serial_number], [sqlserver_start_time_ms_ticks], [sqlserver_start_time], [affinity_type], [affinity_type_desc], [process_kernel_time_ms], [process_user_time_ms], [time_source], [time_source_desc], [virtual_machine_type], [virtual_machine_type_desc] FROM sys.dm_os_sys_info

Back to Top


sys.dm_os_tasks

task that is active in the instance of SQL Server.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
task_address varbinary(8) Memory address of the object.
task_state nvarchar(60) State of the task. This can be one of the following:
PENDING: Waiting for a worker thread.
RUNNABLE: Runnable, but waiting to receive a quantum.
RUNNING: Currently running on the scheduler.
SUSPENDED: Has a worker, but is waiting for an event.
DONE: Completed.
SPINLOOP: Stuck in a spinlock.
context_switches_count int Number of scheduler context switches that this task has completed.
pending_io_count int Number of physical I/Os that are performed by this task.
pending_io_byte_count bigint Total byte count of I/Os that are performed by this task.
pending_io_byte_average int Average byte count of I/Os that are performed by this task.
scheduler_id int ID of the parent scheduler. This is a handle to the scheduler information for this task. For more information, see sys.dm_os_schedulers1.
session_id smallint ID of the session that is associated with the task.
exec_context_id int Execution context ID that is associated with the task.
request_id int ID of the request of the task. For more information, see sys.dm_exec_requests2.
worker_address varbinary(8) Memory address of the worker that is running the task.
NULL = Task is either waiting for a worker to be able to run, or the task has just finished running.
For more information, see sys.dm_os_workers3.
host_address varbinary(8) Memory address of the host.
0 = Hosting was not used to create the task. This helps identify the host that was used to create this task.
For more information, see sys.dm_os_hosts4.
parent_task_address     varbinary(8) Memory address of the task that is the parent of the object.

TSQL

Sql 2005
SELECT [task_address], [task_state], [context_switches_count], [pending_io_count], [pending_io_byte_count], [pending_io_byte_average], [scheduler_id], [session_id], [exec_context_id], [request_id], [worker_address], [host_address] FROM sys.dm_os_tasks
Sql 2008
SELECT [task_address], [task_state], [context_switches_count], [pending_io_count], [pending_io_byte_count], [pending_io_byte_average], [scheduler_id], [session_id], [exec_context_id], [request_id], [worker_address], [host_address] FROM sys.dm_os_tasks
Sql 2008 R2
SELECT [task_address], [task_state], [context_switches_count], [pending_io_count], [pending_io_byte_count], [pending_io_byte_average], [scheduler_id], [session_id], [exec_context_id], [request_id], [worker_address], [host_address], [parent_task_address] FROM sys.dm_os_tasks
Sql 2012
SELECT [task_address], [task_state], [context_switches_count], [pending_io_count], [pending_io_byte_count], [pending_io_byte_average], [scheduler_id], [session_id], [exec_context_id], [request_id], [worker_address], [host_address], [parent_task_address] FROM sys.dm_os_tasks

Back to Top


sys.dm_os_threads

Returns a list of all SQL Server Operating System threads that are running under the SQL Server process.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
thread_address varbinary(8) Memory address (Primary Key) of the thread.
started_by_sqlservr bit Indicates the thread initiator.
1 = SQL Server started the thread.
0 = Another component started the thread, such as an extended stored procedure from within SQL Server.
os_thread_id int ID of the thread that is assigned by the operating system.
status int Internal status flag.
instruction_address varbinary(8) Address of the instruction that is currently being executed.
creation_time datetime Time when this thread was created.
kernel_time bigint Amount of kernel time that is used by this thread.
usermode_time bigint Amount of user time that is used by this thread.
stack_base_address varbinary(8) Memory address of the highest stack address for this thread.
stack_end_address varbinary(8) Memory address of the lowest stack address of this thread.
stack_bytes_committed int Number of bytes that are committed in the stack.
stack_bytes_used int Number of bytes that are actively being used on the thread.
affinity bigint CPU mask on which this thread is supposed to be running. This depends on the value in sp_configure affinity mask configuration option.
locale int Cached locale LCID for the thread.
priority int Priority value of this thread.
token varbinary(8) Cached impersonation token handle for the thread.
is_impersonating int Indicates whether this thread is using Win32 impersonation.
1 = The thread is using security credentials that are different from the default of the process. This indicates that the thread is impersonating an entity other than the one that created the process.
is_waiting_on_loader_lock int Operating system status of whether the thread is waiting on the loader lock.
fiber_data varbinary(8) Current Win32 fiber that is running on the thread. This is only applicable when SQL Server is configured for lightweight pooling.
thread_handle varbinary(8) Internal use only.
event_handle varbinary(8) Internal use only.
scheduler_address varbinary(8) Memory address of the scheduler that is associated with this thread. For more information, see sys.dm_os_schedulers1.
worker_address varbinary(8) Memory address of the worker that is bound to this thread. For more information, see sys.dm_os_workers2.
fiber_context_address varbinary(8) Internal fiber context address. This is only applicable when SQL Server is configured for lightweight pooling.
self_address varbinary(8) Internal consistency pointer.
processor_group     smallint Stores the processor group ID.

TSQL

Sql 2005
SELECT [thread_address], [started_by_sqlservr], [os_thread_id], [status], [instruction_address], [creation_time], [kernel_time], [usermode_time], [stack_base_address], [stack_end_address], [stack_bytes_committed], [stack_bytes_used], [affinity], [locale], [priority], [token], [is_impersonating], [is_waiting_on_loader_lock], [fiber_data], [thread_handle], [event_handle], [scheduler_address], [worker_address], [fiber_context_address], [self_address] FROM sys.dm_os_threads
Sql 2008
SELECT [thread_address], [started_by_sqlservr], [os_thread_id], [status], [instruction_address], [creation_time], [kernel_time], [usermode_time], [stack_base_address], [stack_end_address], [stack_bytes_committed], [stack_bytes_used], [affinity], [locale], [priority], [token], [is_impersonating], [is_waiting_on_loader_lock], [fiber_data], [thread_handle], [event_handle], [scheduler_address], [worker_address], [fiber_context_address], [self_address] FROM sys.dm_os_threads
Sql 2008 R2
SELECT [thread_address], [started_by_sqlservr], [os_thread_id], [status], [instruction_address], [creation_time], [kernel_time], [usermode_time], [stack_base_address], [stack_end_address], [stack_bytes_committed], [stack_bytes_used], [affinity], [priority], [locale], [token], [is_impersonating], [is_waiting_on_loader_lock], [fiber_data], [thread_handle], [event_handle], [scheduler_address], [worker_address], [fiber_context_address], [self_address], [processor_group] FROM sys.dm_os_threads
Sql 2012
SELECT [thread_address], [started_by_sqlservr], [os_thread_id], [status], [instruction_address], [creation_time], [kernel_time], [usermode_time], [stack_base_address], [stack_end_address], [stack_bytes_committed], [stack_bytes_used], [affinity], [priority], [locale], [token], [is_impersonating], [is_waiting_on_loader_lock], [fiber_data], [thread_handle], [event_handle], [scheduler_address], [worker_address], [fiber_context_address], [self_address], [processor_group] FROM sys.dm_os_threads

Back to Top


sys.dm_os_virtual_address_dump

Returns information about a range of pages in the virtual address space of the calling process. Note This information is also returned by the VirtualQuery Windows API.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
region_base_address varbinary(8) Pointer to the base address of the region of pages. Is not nullable.
region_allocation_base_address varbinary(8) Pointer to the base address of a range of pages allocated by the VirtualAlloc Windows API function. The page pointed to by the BaseAddress member is contained within this allocation range. Is not nullable.
region_allocation_protection varbinary(8) Protection attributes when the region was first allocated. The value is one of the following:
PAGE_READONLY

PAGE_READWRITE

PAGE_NOACCESS

PAGE_WRITECOPY

PAGE_EXECUTE

PAGE_EXECUTE_READ

PAGE_EXECUTE_READWRITE

PAGE_EXECUTE_WRITECOPY

PAGE_GUARD

PAGE_NOCACHE

Is not nullable.
region_size_in_bytes bigint Size of the region, in bytes, starting at the base address in which all the pages have the same attributes. Is not nullable.
region_state varbinary(8) Current state of the region. This is one of the following:
MEM_COMMIT

MEM_RESERVE

MEM_FREE

Is not nullable.
region_current_protection varbinary(8) Protection attributes. The value is one of the following:
PAGE_READONLY

PAGE_READWRITE

PAGE_NOACCESS

PAGE_WRITECOPY

PAGE_EXECUTE

PAGE_EXECUTE_READ

PAGE_EXECUTE_READWRITE

PAGE_EXECUTE_WRITECOPY

PAGE_GUARD

PAGE_NOCACHE

Is not nullable.
region_type varbinary(8) Identifies the types of pages in the region. The value can be one of the following:
MEM_PRIVATE

MEM_MAPPED

MEM_IMAGE

Is not nullable.

TSQL

Sql 2005
SELECT [region_base_address], [region_allocation_base_address], [region_allocation_protection], [region_size_in_bytes], [region_state], [region_current_protection], [region_type] FROM sys.dm_os_virtual_address_dump
Sql 2008
SELECT [region_base_address], [region_allocation_base_address], [region_allocation_protection], [region_size_in_bytes], [region_state], [region_current_protection], [region_type] FROM sys.dm_os_virtual_address_dump
Sql 2008 R2
SELECT [region_base_address], [region_allocation_base_address], [region_allocation_protection], [region_size_in_bytes], [region_state], [region_current_protection], [region_type] FROM sys.dm_os_virtual_address_dump
Sql 2012
SELECT [region_base_address], [region_allocation_base_address], [region_allocation_protection], [region_size_in_bytes], [region_state], [region_current_protection], [region_type] FROM sys.dm_os_virtual_address_dump

Back to Top


sys.dm_os_wait_stats

Returns information about all the waits encountered by threads that executed. You can use this aggregated view to diagnose performance issues with SQL Server and also with specific queries and batches.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
wait_type nvarchar(60) Name of the wait type.
waiting_tasks_count bigint Number of waits on this wait type. This counter is incremented at the start of each wait.
wait_time_ms bigint Total wait time for this wait type in milliseconds. This time is inclusive of signal_wait_time_ms.
max_wait_time_ms bigint Maximum wait time on this wait type.
signal_wait_time_ms bigint Difference between the time the waiting thread was signaled and when it started running.

TSQL

Sql 2005
SELECT [wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms], [signal_wait_time_ms] FROM sys.dm_os_wait_stats
Sql 2008
SELECT [wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms], [signal_wait_time_ms] FROM sys.dm_os_wait_stats
Sql 2008 R2
SELECT [wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms], [signal_wait_time_ms] FROM sys.dm_os_wait_stats
Sql 2012
SELECT [wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms], [signal_wait_time_ms] FROM sys.dm_os_wait_stats

Back to Top


sys.dm_os_waiting_tasks

Returns information about the wait queue of tasks that are waiting on some resource.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
waiting_task_address varbinary(8) Address of the waiting task.
session_id smallint ID of the session associated with the task.
exec_context_id int ID of the execution context associated with the task.
wait_duration_ms int Total wait time for this wait type, in milliseconds. This time is inclusive of signal_wait_time.
wait_type nvarchar(60) Name of the wait type.
resource_address varbinary(8) Address of the resource for which the task is waiting.
blocking_task_address varbinary(8) Task that is currently holding this resource
blocking_session_id smallint ID of the session that is blocking the request. If this column is NULL, the request is not blocked, or the session information of the blocking session is not available (or cannot be identified).
-2 = The blocking resource is owned by an orphaned distributed transaction.
-3 = The blocking resource is owned by a deferred recovery transaction.
-4 = Session ID of the blocking latch owner could not be determined due to internal latch state transitions.
blocking_exec_context_id int ID of the execution context of the blocking task.
resource_description nvarchar(1024) Description of the resource that is being consumed.

TSQL

Sql 2005
SELECT [waiting_task_address], [session_id], [exec_context_id], [wait_duration_ms], [wait_type], [resource_address], [blocking_task_address], [blocking_session_id], [blocking_exec_context_id], [resource_description] FROM sys.dm_os_waiting_tasks
Sql 2008
SELECT [waiting_task_address], [session_id], [exec_context_id], [wait_duration_ms], [wait_type], [resource_address], [blocking_task_address], [blocking_session_id], [blocking_exec_context_id], [resource_description] FROM sys.dm_os_waiting_tasks
Sql 2008 R2
SELECT [waiting_task_address], [session_id], [exec_context_id], [wait_duration_ms], [wait_type], [resource_address], [blocking_task_address], [blocking_session_id], [blocking_exec_context_id], [resource_description] FROM sys.dm_os_waiting_tasks
Sql 2012
SELECT [waiting_task_address], [session_id], [exec_context_id], [wait_duration_ms], [wait_type], [resource_address], [blocking_task_address], [blocking_session_id], [blocking_exec_context_id], [resource_description] FROM sys.dm_os_waiting_tasks

Back to Top


sys.dm_os_workers

Returns a row for every worker in the system.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
worker_address varbinary(8) Memory address of the worker.
status int Internal use only.
is_preemptive bit 1 = Worker is running with preemptive scheduling. Any worker that is running external code is run under preemptive scheduling.
is_fiber bit 1 = Worker is running with lightweight pooling. For more information, see sp_configure (Transact-SQL)1.
is_sick bit 1 = Worker is stuck trying to obtain a spin lock. If this bit is set, this might indicate a problem with contention on a frequently accessed object.
is_in_cc_exception bit 1 = Worker is currently handling a non-SQL Server exception.
is_fatal_exception bit Specifies whether this worker received a fatal exception.
is_inside_catch bit 1 = Worker is currently handling an exception.
is_in_polling_io_completion_routine bit 1 = Worker is currently running an I/O completion routine for a pending I/O. For more information, see sys.dm_io_pending_io_requests2.
context_switch_count int The number of scheduler context switches that are performed by this worker.
pending_io_count int The number of physical I/Os that are performed by this worker.
pending_io_byte_count bigint Total number of bytes for all pending physical I/Os for this worker.
pending_io_byte_average int Average number of bytes for physical I/Os for this worker.
wait_started_ms_ticks int Point in time, in ms_ticks3, when this worker entered the SUSPENDED state. Subtracting this value from ms_ticks in sys.dm_os_sys_info3 returns the number of milliseconds that the worker has been waiting.
wait_resumed_ms_ticks int Point in time, in ms_ticks3, when this worker entered the RUNNABLE state. Subtracting this value from ms_ticks in sys.dm_os_sys_info3 returns the number of milliseconds that the worker has been in the runnable queue.
task_bound_ms_ticks bigint Point in time, in ms_ticks3, when a task is bound to this worker.
worker_created_ms_ticks bigint Point in time, in ms_ticks3, when a worker is created.
exception_num int The error number of the last exception that this worker encountered.
exception_severity int The severity of the last exception that this worker encountered.
exception_address varbinary(8) The code address that threw the exception
locale   int Locale LCID setting for the worker.
affinity bigint Affinity setting for the worker. For more information, see sys.dm_os_schedulers4.
state nvarchar(60) Worker state. Can be one of the following values:
INIT = Worker is currently being initialized.
RUNNING = Worker is currently running either nonpreemptively or preemptively.
RUNNABLE = The worker is ready to run on the scheduler.
SUSPENDED = The worker is currently suspended, waiting for an event to send it a signal.
start_quantum bigint Time, in cpu_ticks3, at the start of the current run of this worker.
end_quantum bigint Time, in cpu_ticks3, at the end of the current run of this worker.
last_wait_type nvarchar(60) Type of last wait. For a list of wait types, see sys.dm_os_wait_stats5.
return_code int Return value from last wait. Can be one of the following values:
0 =SUCCESS
3 = DEADLOCK
4 = PREMATURE_WAKEUP
258 = TIMEOUT
quantum_used bigint Internal use only.
max_quantum bigint Internal use only.
boost_count int Internal use only.
tasks_processed_count int Number of tasks that this worker processed.
fiber_address varbinary(8) Memory address of the fiber with which this worker is associated.
NULL = SQL Server is not configured for lightweight pooling.
task_address varbinary(8) Memory address of the current task. For more information, see sys.dm_os_tasks6.
memory_object_address varbinary(8) Memory address of the worker memory object. For more information, see sys.dm_os_memory_objects7.
thread_address varbinary(8) Memory address of the thread associated with this worker. For more information, see sys.dm_os_threads8.
signal_worker_address varbinary(8) Memory address of the worker that last signaled this object. For more information, see sys.dm_os_workers9.
scheduler_address varbinary(8) Memory address of the scheduler. For more information, see sys.dm_os_schedulers4.
processor_group     smallint Stores the processor group ID that is assigned to this thread.

TSQL

Sql 2005
SELECT [worker_address], [status], [is_preemptive], [is_fiber], [is_sick], [is_in_cc_exception], [is_fatal_exception], [is_inside_catch], [is_in_polling_io_completion_routine], [context_switch_count], [pending_io_count], [pending_io_byte_count], [pending_io_byte_average], [wait_started_ms_ticks], [wait_resumed_ms_ticks], [task_bound_ms_ticks], [worker_created_ms_ticks], [exception_num], [exception_severity], [exception_address], [locale], [affinity], [state], [start_quantum], [end_quantum], [last_wait_type], [return_code], [quantum_used], [max_quantum], [boost_count], [tasks_processed_count], [fiber_address], [task_address], [memory_object_address], [thread_address], [signal_worker_address], [scheduler_address] FROM sys.dm_os_workers
Sql 2008
SELECT [worker_address], [status], [is_preemptive], [is_fiber], [is_sick], [is_in_cc_exception], [is_fatal_exception], [is_inside_catch], [is_in_polling_io_completion_routine], [context_switch_count], [pending_io_count], [pending_io_byte_count], [pending_io_byte_average], [wait_started_ms_ticks], [wait_resumed_ms_ticks], [task_bound_ms_ticks], [worker_created_ms_ticks], [exception_num], [exception_severity], [exception_address], [locale], [affinity], [state], [start_quantum], [end_quantum], [last_wait_type], [return_code], [quantum_used], [max_quantum], [boost_count], [tasks_processed_count], [fiber_address], [task_address], [memory_object_address], [thread_address], [signal_worker_address], [scheduler_address] FROM sys.dm_os_workers
Sql 2008 R2
SELECT [worker_address], [status], [is_preemptive], [is_fiber], [is_sick], [is_in_cc_exception], [is_fatal_exception], [is_inside_catch], [is_in_polling_io_completion_routine], [context_switch_count], [pending_io_count], [pending_io_byte_count], [pending_io_byte_average], [wait_started_ms_ticks], [wait_resumed_ms_ticks], [task_bound_ms_ticks], [worker_created_ms_ticks], [exception_num], [exception_severity], [exception_address], [locale], [affinity], [state], [start_quantum], [end_quantum], [last_wait_type], [return_code], [quantum_used], [max_quantum], [boost_count], [tasks_processed_count], [fiber_address], [task_address], [memory_object_address], [thread_address], [signal_worker_address], [scheduler_address], [processor_group] FROM sys.dm_os_workers
Sql 2012
SELECT [worker_address], [status], [is_preemptive], [is_fiber], [is_sick], [is_in_cc_exception], [is_fatal_exception], [is_inside_catch], [is_in_polling_io_completion_routine], [context_switch_count], [pending_io_count], [pending_io_byte_count], [pending_io_byte_average], [wait_started_ms_ticks], [wait_resumed_ms_ticks], [task_bound_ms_ticks], [worker_created_ms_ticks], [exception_num], [exception_severity], [exception_address], [affinity], [state], [start_quantum], [end_quantum], [last_wait_type], [return_code], [quantum_used], [max_quantum], [boost_count], [tasks_processed_count], [fiber_address], [task_address], [memory_object_address], [thread_address], [signal_worker_address], [scheduler_address], [processor_group] FROM sys.dm_os_workers

Back to Top

No comments:

Post a Comment

Total Pageviews