Essential Guide

Guide to SQL Server data management and data quality

A comprehensive collection of articles, videos and more, hand-picked by our editors

Manage your SQL Server databases with SQL DMVs

SQL DMVs (dynamic management views) can help simplify administration of SQL Server databases. Find out how with detailed examples.

Microsoft ships SQL Server with several built-in tools that database administrators (DBAs) can use to manage their

SQL Server environment. With SQL Server 2005, Microsoft introduced SQL dynamic management views, or DMVs, as well as dynamic management functions, or DMFs. They provide plenty of information about server and database state, which you can use to monitor the health of a SQL Server instance, diagnose problems and tune SQL Server instance or database performance.

There are two types of DMVs and DMFs: server-scoped and database-scoped. To query a server-scoped SQL DMV or DMF, the user needs VIEW SERVER STATE permission. To query a database-scoped SQL DMV or DMF requires VIEW DATABASE STATE permission. These DMVs and DMFs exist in sys schema and follow the naming convention dm_*. Moreover, all DMVs and DMFs are grouped by usage. In SQL Server 2005, there were 12 initial groups. In SQL Server 2008 there are 15, and in SQL Server 2012 there are now 20 groups. Some of those groups are tied to certain SQL Server functions, such as AlwaysOn Availability, database mirroring and security.

Common scenarios for SQL DMVs and DMFs
Instead of going through all SQL DMVs and DMFs here, I'll show some common situations in which you can use them. For details on all DMVs and DMFs, Microsoft has extensive documentation.

Retrieving connection information. We can use sys.dm_exec_connections to view information about the current connections to SQL Server. It helps you find which processes are currently connected to the instance. The following are the columns commonly used by sys.dm_exec_connections: session_id, most_recent_session_id, connection_time, client_net_address, last_read, last_write, auth_scheme, and most_recent_sql_handle. For example, the following query shows the most recent SQL text executed for each session connected to the SQL Server:

SELECT  ec.[session_id]
      ,ec.[connect_time]
      ,ec.[client_net_address]
      ,ec.[last_read]
      ,ec.[last_write]
      ,ec.[auth_scheme]
      ,qt.[text]
FROM [sys].[dm_exec_connections] ec
CROSS APPLY [sys].[dm_exec_sql_text](ec.[most_recent_sql_handle]) AS qt

The following figure shows my output from this query:

SQL Server connection information

Retrieving currently executing query and blocking information. It can be useful to find out what requests are currently executing on SQL Server at any given time. For this you can use the sys.dm_exec_requests dynamic management view. This SQL DMV includes detailed information about the query and query plan, the status of the request and information about the amount of time the query has been executing. The columns you are most likely to use are:

  • blocking_session_id: The Service Profile Identifier of the blocking session
  • wait_type: type of wait
  • wait_time: length of time request has been waiting (in milliseconds)
  • last_wait_type: if a wait has ended, its type is listed here
  • wait_resource: name of resource the request is waiting for
  • lock_timeout: length of time a lock can exist before timing out

The DMV is ideal for troubleshooting blocking, as the following example shows:

SELECT  [session_id]
       ,[blocking_session_id]
       ,[status]
       ,[wait_time]
       ,[wait_type]
       ,[wait_resource]
       ,[transaction_id]
       ,[lock_timeout]
FROM [sys].[dm_exec_requests]
WHERE [blocking_session_id] <> 0

The following figure shows example results for blocking sessions:

SQL Server blocking information

We can also use the sys.dm_os_waiting_tasks DMV to return the information about the tasks that are currently waiting for resources. The blocked process is listed in the session_id column. The blocking is listed in the blocking_session_id column. For example, use the following query to see which blocking processes are waiting for which resources:

SELECT  [session_id]
       ,[blocking_session_id]
       ,[wait_type]
       ,[resource_description]
FROM [sys].[dm_os_waiting_tasks]
WHERE [blocking_session_id] <> 0

The following figure shows the results for blocking session:

SQL Server blocked resources

If you do experience a locking issue in one of your databases, then you can use the sys.dm_trans_lock DMV to return information regarding locking status for the database in question. The request_status column of this DMV has one of three values: GRANT, WAIT or CONVERT. The value of CONVERT means that the requestor has been granted a request but is waiting to upgrade to the initial request to be granted. To locate information, you execute the following:

SELECT  l.[request_session_id]
       ,DB_NAME(l.[resource_database_id]) AS [DatabaseName]
       ,OBJECT_NAME(p.[object_id]) AS [ObjectName]
       ,l.[resource_type]
       ,l.[request_status]
       ,l.[request_mode]
       ,l.[request_type]
       ,l.[request_owner_type]
FROM [sys].[dm_tran_locks] l
LEFT JOIN [sys].[partitions] p
      ON p.[hobt_id] = l.[resource_associated_entity_id]
WHERE [request_status] <> 'GRANT'

The following figure shows my output from this query:

SQL Server locking status

You can use sys. dm_os_wait_stats to find top waits for the SQL Server instance since last restart or statistics clear. You can also use this DMV to calculate signal waits; remember that signal waits above 20% is generally sign of CPU pressure.

SELECT CAST(100.0 * SUM([signal_wait_time_ms])
      / SUM([wait_time_ms]) AS [numeric](20, 2)) AS [SignalWaitsPercent]
FROM [sys].[dm_os_wait_stats]

Retrieving SQL Server configuration information. You can use the sys.dm_os_sys_info DMV to get the basic hardware information about your SQL Server, as the following example shows:

SELECT  [cpu_count] / [hyperthread_ratio] AS [PhysicalCPU]
       ,[cpu_count] AS [LogicalCPU]
       ,[hyperthread_ratio] AS [HyperThreadRatio]
       ,[physical_memory_kb] / 1024 AS [PhysicalMemoryInMB]
       ,[virtual_memory_kb] / 1024 AS [VirtualMemoryInMB]
       ,[affinity_type_desc]
       ,[sqlserver_start_time]
FROM [sys].[dm_os_sys_info]

To determine the amount of memory or page file available for an SQL Server instance, use the sys.dm_os_sys_memory DMV. This SQL DMV helps evaluate SQL Server memory usage:

SELECT [total_physical_memory_kb] / 1024 AS [PhysicalMemoryInMB]
      ,[available_page_file_kb] / 1024 AS [AvailablePhysicalMemoryInMB]
      ,[total_page_file_kb] / 1024 AS [TotalPageFileInMB]
      ,[available_page_file_kb] / 1024 AS [AvailablePageFileMB]
      ,[kernel_paged_pool_kb] / 1024 AS [KernelPagedPoolMB]
      ,[kernel_nonpaged_pool_kb] / 1024 AS [KernelNonpagedPoolMB]
      ,[system_memory_state_desc] AS [SystemMemoryStateDesc]
FROM [master].[sys].[dm_os_sys_memory]

Similarly, you can use the following two DMVs to retrieve information about SQL Server services and ErrorLog configuration, respectively:

SELECT * FROM [sys].[dm_server_services]
SELECT * FROM [sys].[dm_os_server_diagnostics_log_configurations]

Knowing this information is useful for troubleshooting purposes.

Retrieving SQL Server Windows Cluster information. To return configuration information about SQL Server Windows cluster and its nodes, you use sys.dm_os_cluster_properties and sys.dm_os_cluster_nodes respectively. The information provided by these DMVs is useful when you are installing Windows or SQL Server updates.

Index-related SQL DMVs and DMFs
Microsoft SQL Server provides DBAs and developers with several index-related dynamic management views and functions, which they can use to maintain, modify, tune and identify missing indexes. Some of the dynamic management views and functions you might use are as follows:

  • sys.dm_db_index_operational_stats: Returns information about low-level I/O operations, locking and latching contention, and access methods.
  • sys.dm_db_index_physical_stats: Returns information about table and index size and fragmentation, including both in-row and LOB data.
  • sys.dm_db_index_usage_stats: Returns an index operations count, identifying operations performed and the last time each operation was performed.
  • sys.dm_db_missing_index_columns: Returns information about indexes that the query optimizer would use, if they were available, including how the columns are used by the query optimizer.
  • sys.dm_db_missing_index_details: Returns detailed information about missing indexes, including the table, columns used in equality operations, columns used in inequality operations and columns used in include operations.
  • sys.dm_db_missing_index_group_stats: Returns information about groups of missing indexes, which SQL Server updates with each query execution (not based on query compilation or recompilation).
  • sys.dm_db_missing_index_groups: Returns information about missing indexes contained in a missing index group.
This was first published in September 2013

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Essential Guide

Guide to SQL Server data management and data quality

1 comment

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close