I give performance presentations at many different events to all levels of SQL Server professionals. Over time I’ve noticed that some DBAs and developers have never looked at the dynamic management views (dmv’s) that are available within SQL Server starting with SQL Server 2005. These DMVs are useful means to determine what is going on with a particular SQL Server instance. So, in this post I am going to list a few of my very favorite DMVs as a reference for those who may be new to them.
Performance Tuning
These dynamic management views are what I use first when looking to any performance issues or I need to know what is going on right now with my SQL Server instance. Usage examples borrowed from docs.microsoft.com.
sys.dm_exec_sessions – This returns current session information such as Session ids, login information like name, host, client info. It also returns some performance information like memory usage, cpu time, reads, writes and lots of other useful info.
SELECT login_name ,COUNT(session_id) AS session_count FROM sys.dm_exec_sessions GROUP BY login_name;
sys.dm_exec_connections This DMV gives you information on currently connected sessions to SQL Server. Information like when the connection started, last read, last write, client net address, most recent sql_handle (the token that uniquely identifies the SQL batch) and other network connection related information.
SELECT; c.session_id, c.net_transport, c.encrypt_option, c.auth_scheme, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.nt_domain, s.nt_user_name, s.original_login_name, c.connect_time, s.login_time FROM sys.dm_exec_connections AS c JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id WHERE c.session_id = @@SPID -- @@SPID returns your current session SPID
sys.dm_exec_requests – This one use I a lot when looking at blocking and I don’t have sp_whosactive available. It’s great for looking into blocking, wait times and looking at what is currently running on your SQL Server. It will provide wait types, backuprestoreDBCC Checkdb percent completes, database and session information. It gives specifics like statement commands like SELECT, INSERT, UPDATE, DELETE etc as well as execution plan information with reads/writes and resource usage.
USE master GO SELECT * FROM sys.dm_exec_requests WHERE session_id = 54; GO
sys.dm_os_wait_stats– This gives you all the waits currently happening in your system. It gives to a great trail of what is going on to diagnose problems the system is having. It provides wait type and times as well as how many tasks are waiting.
USE master GO SELECT * FROM sys.dm_os_wait_stats GO
sys.dm_os_performance_counters– Looking into the OS through SQL server is always handy. With this one you can get to performance monitor counters captured by SQL Server. This does not replace perfmon for detailed performance analysis, but it includes things like buffer counts, locks, file growth events, column store counters, cache hit ratios… lots of goodies in this one.
USE master GO SELECT * FROM sys.dm_os_performance_counters GO
Query Specific Tuning
When starting to look at query tuning you need to dive into query plans statements. These are what I used to find out what queries are running and to get to their query plans.
sys.dm_exec_query_stats -This is an important one. It returns summed performance statistics for query plans that currently exist in the plan cache. It gives statistics tied to a single plan like reads, writes, total rows, time it took to complete, plan hash, memory grant info, threads, column store stats, spills, and degree of parallelism (DOP, which was added in SQL Server 2016).
SELECT TOP 5 query_stats.query_hash AS "Query Hash", SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time", MIN(query_stats.statement_text) AS "Statement Text" FROM (SELECT QS.*, SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text) ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS QS CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats GROUP BY query_stats.query_hash ORDER BY 2 DESC;
sys.dm_exec_query_plan?-This gives you the actual execution plan in XML format.
USE master GO SELECT *FROM sys.dm_exec_query_plan (your plan handle goes here) GO
sys.dm_exec_cached_plans? – These are the plans that are currently available in your cache for reuse. You can use this to retrieve the plan handles needed to get the actual plan from sys.dm_exec_query_plan. Here you can also see the use counts of how many times that plan has been reused while in cache.
USE master; GO SELECT *FROM sys.dm_exec_cached_plans AS cpCROSS APPLY sys.dm_exec_query_plan(cp.plan_handle); GO
sys.dm_exec_sql_text – This returns the actual query text in an execution plan and is referred to a a dynamic management function instead of view.
— acquire sql_handle
SELECT sql_handle FROM sys.dm_exec_requests WHERE session_id = 59 — modify this value with your actual spid
— pass sql_handle to sys.dm_exec_sql_text
SELECT * FROM sys.dm_exec_sql_text(your plan handle goes here)
Index
sys.dm_db_index_usage_stats – Ever wonder is your index is actually getting used? This one will tell you all the scans, seeks and writes done on your index and the last time it was used.
USE master GO SELECT * FROM sys.dm_db_index_usage_stats
sys.dm_db_missing_index_details – This will give you a list of all the missing indexes in your SQL Server. This DMV should not be considered a list of indexes to add, as it will record many potentially overlapping indexes. However, this DMV gives you a great starting point into your servers indexing needs for you to dive into.
USE master GO SELECT * FROM sys.dm_db_missing_index_details
These are just a few of many that SQL Server has to offer. But if you are just starting out these are definitely DMVs you should take a look at and add to your arsenal for performance tuning and monitoring your SQL Servers. Lastly, one thing to keep in mind when reading the data from these queries. Like many other things inside SQL Server this data is a good as your last reboot or service restart.