Se more view version maps here:
Microsoft Sql System View Version Maps
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
