Extended Events: Queries and Waits

SQL Server, You Can't Do That In Profiler
Wouldn't it be great to be able to put together queries and waits at the same time? You all capture query metrics using some method. Most of us query sys.dm_os_wait_stats or sys.dm_db_wait_stats. Combining them is hard. You could query the wait stats. Store the results in a table variable. Run the query in question. Then query the wait stats again into a different table variable. Join the two table variables together to find the differences. Ta-da, you have query waits. Well. Probably. If you're the only one running queries on the system. Also, you're not seeing system waits or other noise caused by activity on the system. Or, we could put Extended Events to work. Queries and Waits Just like Profiler/Trace, you can capture stored procedures, batches, and individual statements…
Read More

Extended Events: Live Data Explorer, Grouping

SQL Server, You Can't Do That In Profiler
Of all the things that Extended Events does, I've found the ability to quickly and easily gather a little bit of data and then use the Data Explorer window Live Data grouping to aggregate it to be one of the greatest. Sure, if we're talking about using Extended Events on a busy production server, this method probably isn't going to work well. There, you are going to be better off querying the XML (I know, I know, but I have ways to help there too). But in development, when doing testing and query tuning, the Live Data window is a gift of the gods on par with fire or beer (it's not as good as whiskey). Live Data Grouping Let's imagine a scenario like this. You're working on some query…
Read More
Extended Events: Live Data Explorer, Getting Started

Extended Events: Live Data Explorer, Getting Started

SQL Server, You Can't Do That In Profiler
One reason a lot of people don't like Extended Events is because the output is in XML. Let's face it, XML is a pain in the bottom. However, there are a bunch of ways around dealing with the XML data. The first, and easiest, is to ignore it completely and use the Live Data window built into SQL Server Management Studio. I've written about the Live Data window before, and I've been using it throughout this series of posts on Extended Events. There's a lot more to this tool than is immediately apparent. Today, we're going to explore the basics around this tool Live Data There are two easy ways to get the Live Data window open. The first, for any Extended Event session that's running, you can right click…
Read More

Extended Events: system_health and a Long Running Query

SQL Server, You Can't Do That In Profiler
Wouldn't it be great to just quickly and easily take a look at your system to see if you had any queries that ran for a long time, but, without actually doing any work to capture query metrics? Oh, yeah, I can do that right now, and so can you. All we need is something that is built into every single server you currently have under management (sorry, not Azure SQL Database) that is SQL Server 2008 or better: system_health system_health Extended Event Session The system_health Extended Events session is very well documented by Microsoft here. I've talked about it before, in relation to Azure SQL Database, and, on the same topic we're going over today, long running queries and waits (cause if you have a long running query, you…
Read More

Extended Events: Database Auditing

SQL Server, You Can't Do That In Profiler
Extended Events can do things that simply are not possible with Profiler and another example comes from the stack of audit events that exist only in Extended Events. One of these is a set of expanded events for database auditing. Comparing the list of things exposed through Extended Events to those exposed through Trace/Profiler isn't entirely fair. All new functionality is only in Extended Events since Trace hasn't been updated since 2008. However, these events that you can use to audit your database, aren't new functionality, they're just new events for watching old functionality. The addition of new events is just one more reason why moving to use Extended Events is a must. Auditing Databases In this instance, when I say audit the database, what I mean is keep an…
Read More

Extended Events: Histogram Output

SQL Server, You Can't Do That In Profiler
The single most important thing to remember about Extended Events is that this functionality is not simply a replacement for Profiler/Trace, but a whole new tool with new functionality. My first example for functionality that you simply cannot get in Profiler/Trace is the ability to output to a Histogram. Profiler/Trace can output to a table or to a file. Extended Events can have a target that is a file, same as Profiler. However, you can also have a target: etw_classic_sync_targetevent_counterhistogrampair_matchingring_buffer Read about each of the types in the Microsoft documentation here. I'm going to focus for the moment on the histogram target because it lets you do some fun stuff and easily collect data that you simply can't collect using Profiler/Trace without hopping through a bunch of flaming hoops. Setting…
Read More