Dynamic Management Views (DMV)

Dynamic Management Views (DMV)

The Dynamic Management Views (DMV’s) gives administrator information about the current state of the SQL server machine. This information helps the administrator to diagnose the problems and tune the server for optimal performance.

The DMV’s exists in sys schema and starts with the name dm_.

For e.g.: 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

1. sys.dm_os_sys_info

This view returns the information about the SQL Server machine, available resources and the resource consumption.

select * from sys.dm_os_sys_info

2. sys.dm_os_hosts

This view returns all the hosts registered with SQL Server 2005.

select * from sys.dm_os_hosts

3. sys.dm_os_schedulers

Sys.dm_os_schedulers view will help you identify if there is any CPU bottleneck in the SQL Server machine.

SELECT * FROM sys.dm_os_schedulers

4. sys.dm_io_pending_io_requests

This dynamic view will return the I/O requests pending in SQL Server side.

select * from sys.dm_io_pending_io_requests

5. sys.dm_io_virtual_file_stats

This view returns I/O statistics for data and log files [both MDF and LDF files]

select * from sys.dm_io_virtual_file_stats(6,2)

6. sys.dm_os_memory_clerks

This DMV will help how much memory SQL Server has allocated through AWE.

To get the memory consumption by internal components of SQL Server 2005.

SELECT TOP 10 type,

SUM(single_pages_kb) as [SPA Mem, Kb]

FROM sys.dm_os_memory_clerks

GROUP BY type

ORDER BY SUM(single_pages_kb) DESC

7. sys.dm_os_ring_buffers

This DMV uses RING_BUFFER_RESOURCE_MONITOR and gives information from resource monitor notifications to identify memory state changes. Internally, SQL Server has a framework that monitors different memory pressures. When the memory state changes, the resource monitor task generates a notification. This notification is used internally by the components to adjust their memory usage according to the memory state.

The record column will be in the XML format.

SELECT * FROM sys.dm_os_ring_buffers

WHERE ring_buffer_type = ‘RING_BUFFER_RESOURCE_MONITOR’

8. sys.dm_db_file_space_usage

This DMV provides the space usage information of TEMPDB database.

select * from sys.dm_db_file_space_usage

9. sys.dm_db_session_space_usage

This DMV provides the number of pages allocated and de-allocated by each session for the database.

select * from sys.dm_db_session_space_usage

10. sys.dm_db_partition_stats

This DMV provides page and row-count information for every partition in the current database.

SELECT * FROM sys.dm_db_partition_stats

11. sys.dm_os_performance_counters

Returns the SQL Server / Database related counters maintained by the server.

SELECT * FROM sys.dm_os_performance_counters WHERE counter_name = ‘Log File(s) Used Size (KB)’

12. sys.dm_db_index_usage_stats

This DMV is used to get useful information about the index usage for all objects in all databases. This also shows the amount of seeks and scan for each index.

SELECT * FROM sys.dm_db_index_usage_stats ORDER BY object_id, index_id

13. sys.dm_exec_sessions

This DMV will show all the information about each session connected to SQL server.

SELECT * FROM sys.dm_exec_sessions WHERE session_id >= 51

14. sys.dm_exec_connections

This DMV shows all the connections to SQL server.

SELECT * FROM sys.dm_exec_connections

15. sys.dm_exec_requests

This will give details on what each connection is actually performing in SQL server.

SELECT * FROM sys.dm_exec_requests WHERE session_id >= 51

16. sys.dm_exec_sql_text

This dynamic management function returns the text of a SQL statement given a sql handle.

SELECT * FROM sys.dm_exec_requests r

CROSS APPLY

sys.dm_exec_sql_text(sql_handle) AS st

WHERE r.session_id > 51

 

Push forward your comments…