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
Indsendt af kl.
Etiketter: , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Total Pageviews