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.