SQL Administration: Dynamic Management Views
Introduction
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. Here, we will see only about the frequently used DMVs.
Two types of dynamic management views:
a) Server-scoped DMV: Stored in Master Database
b) 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
- GRANT VIEW DATABASE STATE to
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.Getting Started
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:SELECT
(Physical_memory_in_bytes/1024.0)/1024.0 AS Physical_memory_in_Mb
FROM
sys.dm_os_sys_info
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.a) sys.dm_os_sys_info
b) sys.dm_os_hosts
c) sys.dm_os_schedulers
d) sys.dm_io_pending_io_requests
e) sys.dm_io_virtual_file_stats
f) sys.dm_os_memory_clerks
g) sys.dm_os_ring_buffers
a) sys.dm_os_sys_info
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 availableVirtual_memory_in_bytes
: Amount of virtual memory availableBpool_commited
: Committed physical memory in buffer poolOS_Priority_class
: Priority class for SQL Server processMax_workers_thread
: Maximum number of workers which can be created
b) sys.dm_os_hosts
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 registeredType
: Type of hosted component [SQL Native Interface/OLE DB/MSDART]Active_tasks_count
: Number active tasks host placedActive_ios_count
: I/O requests from host waiting