Useful Dynamic Management Views in SQL Server 2008

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 and sys.dm_exec_sessions
-these DMVs can be used to view active sessions and requests running on a server, to identify long running sessions.
Example:
How to find information about blocked requests:
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

Example:
How 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
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
sys.dm_db_index_usage_stats
-it shows usage statistics for each index, since the instance last restarted
Example:
How to see usage statistics for each index in a specific database:
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
Example:
How to see all statistics for all tables and indexes for one database:
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
sys.dm_os_buffer_descriptors
-shows how many data pages each database has in the buffer Pool.
Read related post:
Remove all clean buffers from the buffer pool using DBCC DROPCLEANBUFFERS
Example:
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;

Did you find this post helpful? If so, please share it. Thanks!