The concept of Dynamic Management View (DMV) introduced in SQL Server 2005. The DMV designed to provide information about the current state of SQL Server. This helps to monitor the health of a server instance, diagnose problems and tune performance.
The DVMs are composed of both views and table-valued functions. There are two types of dynamic management views:
1. Server Scoped DVM
2. Database Scoped DVM
All DVMs exist in the “sys” schema and follow the naming convention “dm_*”. To query DVMs requires SELECT permission on object and VIEW SERVER STATE or VIEW DATABASE STATE permission.DVMs can be referenced in Transact-SQL statements.
Some DVMs:
sys.dm_os_sys_info: Returns a miscellaneous set of useful information about the computer, and about the resources available to and consumed by SQL Server
sys.dm_os_buffer_descriptors: Returns information about all the data pages (distributed according to database, object, or type) that are currently in the SQL Server buffer pool
Related Queries:
- To check the space used by each database in the data cache:
SELECT count(*)*8/1024 AS 'Cached Size (MB)'
,CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END AS 'Database'
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY 'Cached Size (MB)' DESC
- To check the dirty pages exists in each database:
SELECT db_name(database_id) AS 'Database'
,count(page_id) AS 'Dirty Pages'
FROM sys.dm_os_buffer_descriptors
WHERE is_modified =1
GROUP BY db_name(database_id)
ORDER BY count(page_id) DESC
- To check the SQL Server machine information and its available resources:
SELECT cpu_count AS 'Number of logical CPUs in the server'
,hyperthread_ratio AS 'Ratio of logical and physical CPUs'
,physical_memory_in_bytes AS 'Amount of physical memory available'
,virtual_memory_in_bytes AS 'Amount of virtual memory available'
,bpool_committed AS 'Committed physical memory in buffer pool'
,os_priority_class AS 'Priority class for SQL Server process'
,max_workers_count AS 'Maximum number of workers which can be created'
FROM sys.dm_os_sys_info