SQL Server DMV (Dynamic Management View)

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