The general idea for this question came from dba.stackexchange.com: could we, and if we can, how, get row counts after execution. I was intrigued with the idea, so I ran some tests and did a little digging. I boiled it all down in the answer at the link, but I figured I could share a little here as well. Properly Retrieve Row Counts After Execution The right way to do this is obvious and simple. Before you need it, set up an Extended Events session. Done. The only question is what goes into the Session. First blush, sql_batch_completed and/or rpc_completed. Both will return a rows affected value. Although, interestingly, the row_count value is documented as rows returned. However, it's both. But, if you really want to get picky, batches and…
I am very excited to announce that I will be taking my paid precon content "Tools for SQL Server Query Performance Tuning" and presenting it for free. This is all a part of a new initiative from Redgate Software called Community Circle. Read about that here. I'll be livestreaming the precon once a week on the Redgate Youtube. So, please tune in, every Tuesday at 13:00CDT. Or, you can watch the recordings of the livestream, which we'll host on Youtube, free, forever. Between classes, you can ask questions, make comments, and generally interact through a Discord server I've set up here. We'll be covering a whole slew of topics from Query Store to DMVs, SQL Server Management Studio to Extended Events, Execution Plans and more, lots more. Whether you watch…
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…
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…
I've decided that, in fact, it is time to start moving people off the ancient technology, Profiler. Before, I always said, stay where you're comfortable. However, keeping people comfortable means that they're also going to keep promoting Profiler/Trace to new people on new platforms. That is a real problem. To fix the problem of old school, slow, inferior, methods of data collection, troubleshooting, and consuming metrics, we need to educate people. Extended Events are not simply a replacement for Trace. They're not simply another way to gather query metrics. No, in fact, this is a whole new tool, with new functionality and a very high level of support and engagement from Microsoft. Extended Events are where all new functionality since 2012 provides mechanisms for monitoring behavior. As technologists we should…
I wrote a short blog post about the misperception that Profiler was easier than Extended Events when it came to the core concept of "click, connect, BOOM, too much data". Go read it if you like, but I don't think it's actually an effective argument for how much easier Extended Events is than Profiler. Here, we're going to drill down on that concept in a real way. Let's start with a little clarification. I'm going to be a little lazy with my language. Trace is a scripted capture of events on a server. Profiler is a GUI for consuming a Trace, either live or from a file, and for creating Trace events. However, almost everyone refers to 'Profiler' when they mean either Trace or Profiler. I may do the same…
I know, I know, I'm on #teamexevents so I think that Extended Events can do no wrong, but let's address this thought that Profiler is easier. Now, if we're strictly talking knowledge, sure, if you've got a lot of experience with Profiler/Trace and very little with Extended Events, of course Profiler is easer. However, what I'm told is that Profiler doesn't require very much set up, while Extended Events does. That's just wrong, but let's put it to the test. The Test For the comparison, we're not going to do anything special with either tool. I'm just going to start collecting query data with the fewest possible clicks and/or key strokes. I'm going to use both tools short cuts to make this as fast as possible. The goal is, click,…
If you go through all the stuff I've written about Extended Events, you'll find that I use causality tracking quite a bit. However, I've never just talked about what causality tracking is and why I use it so frequently. Let's fix that issue now. Causality Tracking Causality tracking is quite simple to understand. It's property that you set for a given session. A session, of course, is defined by one or more events and a target. You can define things about a session, like it's name, when you define the session itself. Turning on, or enabling, causality tracking is just a matter of defining that the session will have causality tracking. It looks like this in the GUI: It looks like this in the T-SQL code: CREATE EVENT SESSION QueryBehavior…
The first time you see a new execution plan that you're examining to fix a performance problem, something broken, whatever, you should always start by looking at the first operator. First Operator The first operator is easily discerned (with an exception). It's the very first thing you see in a graphical execution plan, at the top, on the left. It says SELECT in this case: This is regardless of how you capture the execution plan (with an exception). Whether you're looking at an execution plan from the plan cache, Query Store, or through SSMS, the execution plan, regardless of complexity, has this first operator. In this case, it says UPDATE: If you get an execution plan plus runtime metrics (previously referred to as an "actual" execution plan), you'll still see…
A question that I've seen come up frequently just recently is, how to track CPU use over time. Further, like a disk filling up, people want to know how to predict their CPU usage, so that they can easily decide "now is when I upgrade the hardware". Well, the bad news is, that ain't easy. CPU Use Over Time There are a bunch of ways to look at processor usage. The simplest, and probably most common, is to use the Performance Monitor counters such as '% Processor Time'. Query this, you can get an average of the processor usage at a moment in time. Ta-da! Fixed it. I thought you said this was hard Grant. Well, hang on. Are you running on a single processor machine? If so, cool, maybe…