Important DMVs

Here is a list of some DMVs that are important in troubleshooting server performance and can be useful in identifying poorly performing queries:

sys.dm_exec_requests - these DMVs can be used to view active sessions and requests running on a server, to identify long running sessions.

SELECT session_id ,status ,blocking_session_id

,wait_type ,wait_time ,wait_resource

,transaction_id

FROM sys.dm_exec_requests

WHERE status = N'suspended';

GO

sys.dm_exec_sessions - to find the users that are connected to the server:

SELECT login_name, COUNT(session_id) AS number_of_sessions

FROM sys.dm_exec_sessions

GROUP BY login_name;

sys.dm_exec_query_stats - it returns details of query execution statistics from the plan cache plan

SELECT st.text, total_logical_reads, total_physical_reads,

total_elapsed_time, total_rows

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

ORDER BY total_physical_reads DESC,total_logical_reads DESC

sys.dm_os_wait_stats - for reviewing serverwide wait statistics, returns information about all the waits encountered by threads that executed. The contents of this dynamic management view can be reset by running the following command:

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);

GO

SELECT * FROM sys.dm_os_wait_stats

WHERE wait_type IN ('SOS_SCHEDULER_YIELD','CXPACKET','LCK_X',

'LCK_M_U', 'LCK_M_X','PAGELATCH_X','LATCH_X','RESOURCE_SEMAPHORE','PAGEIOLATCH_X',

'WRITELOG', 'LOGBUFFER','ASYNC_IO_COMPLETION', 'IO_COMPLETION','ASYNC_NETWORK_IO')

ORDER BY wait_time_ms DESC

sys.dm_db_index_usage_stats - it shows usage statistics for each index, since the instance last restarted

SELECT * FROM sys.dm_db_index_usage_stats

WHERE database_id = DB_ID('DB_Name')

sys.dm_db_index_physical_stats - returns size and fragmentation information for the data and indexes

SELECT *

FROM sys.dm_db_index_physical_stats(DB_ID('DB_Name'), NULL, NULL, NULL , NULL);

Example:

How to see all statistics for all tables and indexes within the instance:

SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);

GO

sys.dm_db_missing_index_details - for suggesting new indexes, based on past workload

SELECT mig.*, statement AS table_name,

    column_id, column_name, column_usage

FROM sys.dm_db_missing_index_details AS mid

CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)

INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handle

ORDER BY mig.index_group_handle, mig.index_handle, column_id;

                                                     

sys.dm_os_buffer_descriptors - To see the number of pages cached in memory and the amount of RAM used in MB for each database:

SELECT count(*)AS Page_Count

,count(*) * 8 / 1024 as Cached_Size_MB

,CASE database_id

WHEN 32767 THEN 'ResourceDb'

ELSE db_name(database_id)

END AS Database_name

FROM sys.dm_os_buffer_descriptors

GROUP BY db_name(database_id) ,database_id

ORDER BY Page_Count DESC;