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…
It's possible for you to see new technology at work if you use Extended Events to monitor cardinality feedback. To put it simply, cardinality, the number of rows being returned, is estimated by SQL Server. Sometimes, it gets these estimates right. Sometimes, it gets them wrong. New functionality within SQL Server 2022 uses Query Store to see how well those estimates are working. If they're off, the optimizer can actually change plans to get you different behaviors based on this feedback. There's even more than one way to monitor cardinality feedback. Let's talk about it. Extended Events First up, to really see the full set of behaviors in action, we can use Extended Events: CREATE EVENT SESSION [CardinalityFeedback] ON SERVER ADD EVENT sqlserver.query_ce_feedback_telemetry, ADD EVENT sqlserver.query_feedback_analysis, ADD EVENT sqlserver.query_feedback_validation, ADD…
Strictly speaking, a recompile isn't really a performance tuning problem. In fact, a lot of time, recompiles are desired because they reflect changes in statistics which are likely to need a new plan. However, you can get really excessive levels of recompiles because of a variety of different issues. So, identifying the causes can be a pain. Here's one way to get it done. Extended Events for Recompile Causes You knew I was going there. Seriously though, we know that, since SQL Server 2005, all recompiles are at the statement level (unless you call for a recompile of a procedure or create that proc with a RECOMPILE hint). So, capturing the recompiles and getting grotty on the details means capturing statements. Understand that this can be expensive, especially in terms…
A question that came up recently was how to track the query compile time. It's actually a pretty interesting question because, there aren't that many ways to tell how long it took to compile the query, and they don't necessarily agree. For most of us, most of the time, compile time for a given query doesn't matter. However, I love telling the story of the query I had on an old system that could run in 90ms, but took 5 minutes to compile. In short, sometimes compile time matters. How To See Query Compile Time If you want to see how long it takes a query to compile, you have, to my knowledge, three options. The first, and possibly easiest, is to look at the plan properties on an execution…
I was recently asked a question on a forum by a person who was frustrated with all the tool choices we have for measuring performance. Moreover, they were frustrated that a simple and clear combination of the tools to achieve synergy was extremely challenging. In fact, they said that, just using the query_hash as an example, they never saw a single match between the DMVs, Query Store and Extended Events. Now, that's pretty unlikely and I'm sure we could talk about why that might be the case. However, this idea of combining the tools, I shared a bunch of thoughts on it. I decided, maybe it's worth sharing here too. Achieving Synergy Honestly, this is tough. I work for a company that makes a monitoring tool. We are trying to…
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…
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…
A question that comes up on the forums all the time: Which query used the most CPU. You may see variations on, memory, I/O, or just resources in general. However, people want to know this information, and it's not readily apparent how to get it. While you can look at what's in cache through the DMVs to see the queries there, you don't get any real history and you don't get any detail of when the executions occurred. You can certainly take advantage of the Query Store for this kind of information. However, even that data is aggregated by hour. If you really want a detailed analysis of which query used the most CPU, you need to first set up an Extended Events session and then consume that data. A…
I was recently asked if we could tell why a plan was removed from cache. If you read this blog, you know what I'm going to say next. I checked the extended events and there are actually two different events that will tell us information about a plan removed from cache; sp_cache_remove and query_cache_removal_statistics. Let's talk about how these work. Removed From Cache Just so we can see ALL the activity, I'm creating an Extended Events session that captures a little more than just the two events: CREATE EVENT SESSION PlanCacheRemoval ON SERVER ADD EVENT sqlserver.query_cache_removal_statistics (WHERE (sqlserver.database_name = N'AdventureWorks2017')), ADD EVENT sqlserver.rpc_completed (WHERE (sqlserver.database_name = N'AdventureWorks2017')), ADD EVENT sqlserver.rpc_starting (WHERE (sqlserver.database_name = N'AdventureWorks2017')), ADD EVENT sqlserver.sp_cache_hit (WHERE (sqlserver.database_name = N'AdventureWorks2017')), ADD EVENT sqlserver.sp_cache_insert (WHERE (sqlserver.database_name = N'AdventureWorks2017')), ADD EVENT…
Using the appropriate data type to avoid conversions or implicit conversions is a fundamental approach to good T-SQL coding practices. Implicit conversions can lead to poor performance. However, other than looking at every execution plan in your system, is there a way to see if you are experiencing implicit conversions? Actually, yeah, it's right there in Extended Events. plan_affecting_convert Built right into the Extended Events is an event that captures conversions that would affect execution plans, plan_affecting_convert. This event will show both CONVERT and CONVERT_IMPLICIT warnings that you would normally only see within an execution plan. You can capture this event with others. Capturing events together along with causality tracking makes it very easy to track queries that have the issue. Here's one example of how you might capture implicit…