Building Diagnostic Queries Using DMVs and DMFs

Dynamic management views (DMVs) and dynamic management functions (DMFs) expose relevant real-time information that can unlock the secrets of T-SQL execution and SQL Database Engine health, even on a live production server. There are hundreds of DMVs and DMFs (collectively referred to as DMVs) available in the SQL Database Engine, and while they are mostly documented, it may not be obvious how they can be used by database developers and administrators to troubleshoot performance both in production systems and during the development process.

In this chapter, we will start by enumerating some of the DMVs that are most relevant for both T-SQL developers and database administrators alike to troubleshoot T-SQL query performance. Building on this information, we will provide real-world examples to explore how to use DMVs to troubleshoot different poor-performance scenarios, as well as give us the information needed to begin building our own...

Technical requirements

The examples used in this chapter are designed for use on SQL Server 2022 and Azure SQL Database, but they should work on any version of SQL Server, 2012 or later. The Developer edition of SQL Server is free for development environments and can be used to run all the code samples. There is also a free tier of Azure SQL Database you can use for testing at https://aka.ms/freedb.

You will need the sample databases AdventureWorks2016_EXT (referred to as AdventureWorks) and AdventureWorksDW2016_EXT (referred to as AdventureWorksDW), which can be found on GitHub at https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks. Code samples for this chapter can also be found on GitHub at https://github.com/PacktPublishing/Learn-T-SQL-Querying-Second-Edition/tree/main/ch6.

Introducing DMVs

SQL Server 2005 introduced a new concept in the Database Engine – the SQL Operating System (SQLOS). The SQLOS is an abstraction layer that encapsulates all the low-level resource management and monitoring tasks that the SQL Database Engine must perform while providing an application programming interface (API) for other components of the Database Engine to leverage these services. Not only does this centralization of resource management code make the SQL Database Engine more efficient, but it also provides a central location for monitoring various aspects of Database Engine performance. DMVs take advantage of this centralized architecture by providing the user with a mechanism to view this information in a way that is lightweight and accurate.

DMVs allow the user to query memory structures in SQLOS. Some DMVs show information that is only relevant for the specific point in time at which they are queried, while other DMVs show cumulative information that goes...

Exploring query execution DMVs

Several different DMVs may be relevant when analyzing the activity that is currently happening in a SQL Database Engine. In this section, we will cover a few of the most common DMVs, along with some examples of the information that they can provide.

sys.dm_exec_sessions

The sys.dm_exec_sessions DMV lists information about all the sessions that are currently active on the server. This includes both user sessions and system sessions, and it also includes idle sessions that are connected but are not currently executing any queries.

Tip

Idle sessions can be identified by looking for rows that have a status of sleeping. When using connection pooling especially, it is common to have several user sessions in a sleeping status.

This DMV can be used to view information that is relevant to the session, such as login_name, host_name, program_name, and other properties that would be set at the session level. This can be helpful when trying to identify...

Exploring query plan cache DMVs

Another set of DMVs that are helpful when troubleshooting T-SQL query performance is the query plan cache-related DMVs. While the execution DMVs we discussed in the previous section contain point-in-time information that changes frequently, these DMVs contain information about queries that are currently in the plan cache, which can contain information back to when the server was last restarted, depending on how long the query plans remain in the cache.

Note

The amount of time a plan remains in the cache depends on several factors such as memory pressure, recompilation, and schema changes. Provided that the server has been online for some time and no cache-flushing events have occurred, such as changing max degree of parallelism, or manually clearing the plan cache by running ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, these plan cache DMVs should give you a good idea of the overall query performance on the server.

Before describing...

Troubleshooting common scenarios with DMV queries

Now that we have reviewed some of the DMVs that are relevant for examining query performance, we can look at how to combine these views into larger queries that target specific troubleshooting scenarios.

Note

Many of the examples in this chapter are derived from queries on the Tiger Toolbox on GitHub (https://aka.ms/tigertoolbox). For more examples and comprehensive DMV scripts, be sure to download and explore this repository.

Investigating blocking

Blocking is a very common scenario in many database systems. This is what happens when one query holds exclusive access to a resource that another query also requires. It is normal for some blocking to occur, but severe blocking can cause major performance issues and should be investigated. When troubleshooting query performance, it’s a good idea to check for blocking first to see if queries are slow because they are expensive, or because they are being blocked by some other workload.

The key DMVs for investigating blocking are sys.dm_exec_requests and sys.dm_os_waiting_tasks. As we discussed previously, these DMVs show us which queries are currently running and what state they are in. They also have columns that will indicate which sessions may be causing blocking.

The following example shows a simple query that can be used to look for blocking on the system:

SELECT s.session_id, s.last_request_end_time, ISNULL(r.status,s.status) AS status, s...

Cached query plan issues

As we discussed earlier in the sys.dm_exec_query_stats section, the SQL Database Engine maintains execution statistics for all the queries that are currently in the cache. There is a wealth of information in this DMV that we can use to troubleshoot several different query performance-related issues. We will cover a few issues here, but be sure to reference the BPCheck script in the Tiger Toolbox (https://aka.ms/bpcheck) for a more comprehensive example of queries to identify these scenarios and others.

Single-use plans (query fingerprints)

In the EXECUTE vs. sp_executesql section of Chapter 5, Writing Elegant T-SQL Queries, we discussed how to send ad hoc T-SQL queries to the SQL Database Engine in a way that allows for plan reuse (also see the Plan caching and re-use section in Chapter 1, Understanding Query Processing, for the importance of plan reuse). If we are not sure whether or not our application is successfully parameterizing queries and leveraging...

Mining XML query plans

As we mentioned in the sys.dm_exec_query_plan section, query execution plans are stored as XML, and the sys.dm_exec_query_plan DMV returns them as a proper XML data type. This allows us to leverage XML Path Language (XPath) to generate queries that can search for elements and attributes within the query execution plans. Using these XPath queries, or XQueries, we can search for common query performance issues across all the query execution plans in the cache, rather than having to examine each graphical plan individually. In this section, we will cover a few common scenarios, but be sure to reference the Mining-PlanCache section of the Tiger Toolbox (https://aka.ms/tigertoolbox) for more examples.

Tip

The queries shown in this section can be used individually to search for specific issues, but running the entire BPCheck script from the Tiger Toolbox (https://aka.ms/bpcheck) will gather all this information and more in a single resultset.

Plans with missing...

Summary

While the examples in this chapter are only a small sample, hopefully at this point, we can see how DMVs and DMFs can be a powerful troubleshooting tool when it comes to diagnosing query performance issues. They are lightweight, easy to use, and provide a breadth of information that is useful for zeroing in on the performance issues that were covered in Chapter 5, Writing Elegant T-SQL Queries, and Chapter 6, Discovering T-SQL Anti-Patterns in Depth.

While DMVs are great for point-in-time and cumulative analysis, there are some issues that can only be diagnosed by catching queries and related data in real time. This is where tracing with Extended Events (XEvents) is useful. In the next chapter, we will introduce XEvents and discuss how to set up the new XEvent profiler trace that can capture all the queries that are executed against a server in real time.