Building XEvent Profiler Traces

In Chapter 7, Building Diagnostic Queries Using DMVs and DMFs, we learned how to gain insights into query performance using the built-in system views. This information is valuable, but because these views mostly represent the current point in time, they are not always sufficient to answer every question we have about the performance of our queries. In this chapter, we will introduce Extended Events (XEvents), the lightweight infrastructure that exposes relevant just-in-time information from every component of the SQL Database Engine, focusing on those related to T-SQL execution. We will explore real-world examples of how to use these XEvents to troubleshoot different poor performance scenarios, leverage collection and analysis tools such as the XEvent Profiler, SQL LogScout and Replay Markup Language (RML) utilities for event analysis, and drop a note on the infamously deprecated SQL Server Profiler.

In this chapter, we’re going to cover the...

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 AdventureWorks2016_EXT (referred to as AdventureWorks) and AdventureWorksDW2016_EXT (referred to as AdventureWorksDW) sample databases, 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/ch8.

Introducing XEvents

When we connect to the SQL Database Engine and run a query, it fires a series of events – a user logs in, a connection is established, a query begins executing, a plan is found in the cache, a plan is recompiled, and a query completes execution (these are just a few examples). Virtually everything that happens within the Database Engine is an event.

While Dynamic Management Views (DMVs) are powerful tools, they don’t always give a complete picture of what is going on within the engine. Most DMVs provide a snapshot in time, a picture of what is going on the moment they are queried. They may have some history that goes back to the last time the server was restarted, but even then, the information is typically cumulative; they can’t tell us what the server looked like a few minutes before, and they can’t tell us the events that led up to the current state. This is where tracing comes in. Tracing allows us to capture all the occurrences...

Getting up and running with XEvent Profiler

Those of us who have been working with SQL Server for some time are likely to have experience with SQL Server Profiler. Profiler is a tool that has been around since the early versions of SQL Server and leverages the SQL Trace infrastructure to provide event-based monitoring of SQL Server. While it has been deprecated since SQL Server 2012, many users still prefer it over XEvents due to its ease of use, familiarity, and the rich set of tools that have been built over the years to capture, analyze, and replay trace data.

While SQL Server Profiler is still available in the product, its use has declined over the years as XEvents gained feature parity. Starting with SQL Server 2012, all the events that can be captured with Profiler can also be captured with XEvents, and with less overhead on the server. In fact, XEvents have a much wider range of events than Profiler and a rich set of actions that can be captured along with the events to provide...

Remote collection with SQL LogScout

While configuring an XEvent session is simple enough when you have access to the server, if you find yourself in a situation where you need to analyze server or application performance remotely, XEvents can be a challenge. As we discussed in the Introducing XEvents section, we can save the XEvent session as a script file and send it to someone to run, but to analyze the data, we’ll need a file target, and configuring one requires knowledge of the disk layout of the system. Also, we would need to ensure that the person we send the script to has at least basic SQL Database Engine knowledge such as how to open, edit, and execute a T-SQL script along with the rights to create an XEvent session. If the person who has access to the server is not a database professional, this might be a challenge.

This is the type of troubleshooting that Microsoft Support must do every day. To make the job easier, they created a tool called SQL LogScout, which...

Analyzing traces with RML Utilities

RML Utilities is a suite of tools that can be used to analyze and replay SQL Database Engine workloads. We first introduced the RML Utilities in Chapter 6, Discovering T-SQL Anti-Patterns in Depth, in the Avoiding unnecessary overhead with stored procedures section where we used the ostress tool to simulate a multithreaded workload on the server. The input to ostress can be a single query or T-SQL script, but ostress can also take a prepared trace file (either SQL Trace or XEvents) as input. This allows you to capture a workload from a production server, and then replay that workload on a test server so that you can experiment with various settings or performance tuning options – or even test how a new version of the SQL Database Engine would perform with the same workload.

Another tool that is part of RML Utilities is ReadTrace. The ReadTrace tool is used to analyze and prepare traces for replay via ostress, but it can also be used to...

Summary

In this chapter, we reviewed the Extended Events engine in the SQL Database Engine and how you can leverage XEvent traces to gather detailed data about query execution and performance. We also discussed the various free tools from Microsoft that can be used to configure, capture, and analyze XEvent traces quickly and easily. Together with DMVs, we now have several tools in our toolbelt that can be used to diagnose and troubleshoot the various issues covered throughout the book.

In the next chapter, we will review yet another tool that is part of SQL Server designed to help diagnose query performance issues – using SSMS for the analysis of query plans.