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:

1. SQL Server related [Hardware Resources] DMV
2. Database related DMV
3. Index related DMV
4. 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:

1. CPU Count: Number of logical CPUs in the server
2. Hyperthread-ratio: Ratio of logical and physical CPUs
3. Physical_memory_in_bytes: Amount of physical memory available
4. Virtual_memory_in_bytes: Amount of virtual memory available
5. Bpool_commited: Committed physical memory in buffer pool
6. OS_Priority_class: Priority class for SQL Server process
7. Max_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.

1. Name: Name of the host registered
2. Type: Type of hosted component [SQL Native Interface/OLE DB/MSDART]
3. Active_tasks_count: Number active tasks host placed
4. Active_ios_count: I/O requests from host waiting

c)   sys.dm_os_schedulers