SQL Administration: Dynamic Management Views
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
VIEW SERVER STATEand for database scoped DMV, the user must have
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
DENYcommand 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