The DMVs; newly introduced in SQL Server 2005 gives the database administrator information about the current state of the SQL Server machine. These values will help the administrator to diagnose problems and tune the server for optimal performance. DMVs are designed to be used instead of system tables and various other functions provided in SQL Server 2000. In this article, I will be explaining only about the frequently used DMVs.
Two types of dynamic management views:
- Server-scoped DMV: Stored in Master Database
- Database-scoped DMV: Specific to each database
Permission to Execute DMV [Security]
To query a server scoped DMV, the database user must have
SELECT privilege on
VIEW SERVER STATE and for database scoped DMV, the user must have
SELECT privilege on
VIEW DATABASE STATE.
- GRANT VIEW SERVER STATE to <Login>
- GRANT VIEW DATABASE STATE to <User>
If you want to deny a user permission to query certain DMVs, you can use the
DENY command to restrict access to a specific DMV.
All the DMVs exits in SYS schema and their names start with
DM_. So when you need to query a DMV, you should prefix the view name with
SYS. As an example, if you need to see the total physical memory of the SQL Server machine; then execute the below TSQL command:
(Physical_memory_in_bytes/1024.0)/1024.0 AS Physical_memory_in_Mb
In this article, I will be explaining some of the DMVs which can be used frequently to understand the current behavior of SQL Server:
- SQL Server related [Hardware Resources] DMV
- Database related DMV
- Index related DMV
- Execution related DMV
1. SQL Server Related DMV
This section details the DMVs associated with SQL Server system. SQL DMV is responsible to manage server level resources specific to a SQL Server instance.
This section covers DMVs related to OS, Disk and Memory.
This view returns the information about the SQL Server machine, available resources and the resource consumption.
This view returns information like the following:
- CPU Count: Number of logical CPUs in the server
- Hyperthread-ratio: Ratio of logical and physical CPUs
Physical_memory_in_bytes: Amount of physical memory available
Virtual_memory_in_bytes: Amount of virtual memory available
Bpool_commited: Committed physical memory in buffer pool
OS_Priority_class: Priority class for SQL Server process
Max_workers_thread: Maximum number of workers which can be created
This view returns all the hosts registered with SQL Server 2005. This view also provides the resources used by each host.
Name: Name of the host registered
Type: Type of hosted component [SQL Native Interface/OLE DB/MSDART]
Active_tasks_count: Number active tasks host placed
Active_ios_count: I/O requests from host waiting
Sys.dm_os_schedulers view will help you identify if there is any CPU bottleneck in the SQL Server machine. The number of runnable tasks is generally a nonzero value; a nonzero value indicates that tasks have to wait for their time slice to run. If the runnable task counts show high values, then there is a symptom of CPU bottleneck.
WHERE scheduler_id < 255
The above query will list all the available schedulers in the SQL Server machine and the number of runnable tasks for each scheduler.
This dynamic view will return the I/O requests pending in SQL Server side. It gives you information like:
Io_type: Type of pending I/O request
Io_pending: Indicates whether the I/O request is pending or has been completed by Windows
Scheduler_address: Scheduler on which this I/O request was issued
This view returns I/O statistics for data and log files [MDF and LDF file]. This view is one of the commonly used views and will help you to identify I/O file level. This will return information like:
Sample_ms: Number of milliseconds since the instance of SQL Server has started
Num_of_reads: Number of reads issued on the file
Num_of_bytes_read: Total number of bytes read on this file
Io_stall_read_ms: Total time, in milliseconds, that the users waited for reads issued on the file
Num_of_writes: Number of writes made on this file
Num_of_bytes_written: Total number of bytes written to the file
Io_stall_write_ms: Total time, in milliseconds, that users waited for writes to be completed on the file
Io_stall: Total time, in milliseconds, that users waited for I/O to be completed
Size_on_disk_bytes: Number of bytes used on the disk for this file
This DMV will help how much memory SQL Server has allocated through AWE.
SUM(awe_allocated_kb) / 1024 as [AWE allocated, Mb]
The same DMV can be used to get the memory consumption by internal components of SQL Server 2005.
SELECT TOP 10 type,
SUM(single_pages_kb) as [SPA Mem, Kb]
GROUP BY type
ORDER BY SUM(single_pages_kb) DESC
This DMV uses
RING_BUFFER_RESOURCE_MONITOR and gives information from resource monitor notifications to identify memory state changes. Internally, SQL Server has a framework that monitors different memory pressures. When the memory state changes, the resource monitor task generates a notification. This notification is used internally by the components to adjust their memory usage according to the memory state.
Record FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
The output of the above query will be in XML format. The output will help you in detecting any low memory notification.
RING_BUFFER_OOM: Ring buffer oom contains records indicating server out-of-memory conditions.
record FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_OOM'
2. Database Related DMV
This section details the DMVs associated with SQL Server Databases. These DMVs will help to identify database space usages, partition usages, session information usages, etc...
This DMV provides the space usage information of
This DMV provides the number of pages allocated and de-allocated by each session for the database
This DMV provides page and row-count information for every partition in the current database.
The below query shows all counts for all partitions of all indexes and heaps in the MSDB database:
SELECT * FROM sys.dm_db_partition_stats;
The following query shows all counts for all partitions of Backup set table and its indexes
SELECT * FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('backupset');
Returns the SQL Server / Database related counters maintained by the server.
The below sample query uses the
dm_os_performance_counters DMV to get the Log file usage for all databases in KB.
,cntr_value 'Log File(s) Used Size (KB)'
WHERE counter_name = 'Log File(s) Used Size (KB)'
3. INDEX Related DMV
This section details the DMVs associated with SQL Server Databases. These DMVs will help to identify database space usages, Partition usages, Session information usages, etc.
This DMV is used to get useful information about the index usage for all objects in all databases. This also shows the amount of seeks and scan for each index.
SELECT object_id, index_id, user_seeks, user_scans, user_lookups
ORDER BY object_id, index_id
All indexes which have not been used so far in as database can be identified using the below Query:
from sys.indexes i
left join sys.dm_db_index_usage_stats s
on s.object_id = i.object_id and
i.index_id = s.index_id and s.database_id = 5
where objectproperty(i.object_id, 'IsIndexable') = 1 and
s.index_id is null or
(s.user_updates > 0 and s.user_seeks = 0
and s.user_scans = 0 and s.user_lookups = 0)
order by object_name(i.object_id)
Database_id with the database you are looking at.
4. Execution Related DMV
Execution related DMVs will provide information regarding sessions, connections, and various requests which are coming into the SQL Server.
This DMV will give information on each session connected to SQL Server. This DMV is similar to running
sp_who2 or querying Master..
WHERE session_id >= 51 – All user Sessions
This DMV shows all the connection to SQL Server. The below query uses
sys.dm_exec_connections DMV to get connection information. This view returns one row for each user connection (
Sessionid > =51).
This DMV will give details on what each connection is actually performing in SQL Server.
WHERE session_id >= 51
This dynamic management function returns the text of a SQL statement given a SQL handle.
sys.dm_exec_sql_text(sql_handle) AS st
WHERE r.session_id = 51
Dynamic Management views (DMV) and Dynamic Management Functions (DMF) in SQL Server 2005 give a transparent view of what is going on inside various areas of SQL Server. By using them, we will be able to query the system for information about its current state in a much more effective manner and provide solutions much faster. DMVs can be used to performance tune and for troubleshooting server and queries. This article has shown an overview of what they are and how we can use them.