You Can't Do That In Profiler Archives

17Oct 2022 by Grant Fritchey 4 Comments

Monitor Cardinality Feedback in SQL Server 2022

SQL Server, You Can't Do That In Profiler

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…

27Sep 2021 by Grant Fritchey 1 Comment

Login Timeouts

You Can't Do That In Profiler

I was recently approached at work about a company that was seeing tons of timeouts in SQL Server. I was asked how to troubleshoot this. There are lots of posts by people on this topic, but I found something I didn't see anywhere else, let me share it with you. Extended Events You must have known I was going to bring up Extended Events. Surely. Well, I am. Now, if you search up "timeout" in the events, you find lock timeouts, execution plan timeouts (ooh), and stuff like that. It's not related to the login timeout. So, look up "connection" or "log in". You get a lot of information, but again, none of it is related to timeouts. In fact, the best info is in process_login_finish. It does include login…

19Jul 2021 by Grant Fritchey No Comments

Filtering Extended Events Using Actions

SQL Server, You Can't Do That In Profiler

Did you know, you can use Actions to Filter Extended Events? Well, you can. Filtering is one of the greatest ways in which Extended Events differentiates itself from other mechanisms of gathering information about the behavior of SQL Server. You can put Actions to work in your filtering. Best of all, the Actions don't have to be collected in order to put them to work filtering your Extend Events. Using Actions To Filter Extended Events Actions, also called Global Fields, are additional bits of data that you can add to a given Event when you're setting up an Extended Events Session. They are programmatic additions to the Event, as described here. Think of them sort of like triggers. In practice, adding an Action, database_name, to an Event, like the rpc_completed…

10May 2021 by Grant Fritchey No Comments

Query Compile Time

SQL Server, You Can't Do That In Profiler

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…

26Apr 2021 by Grant Fritchey 2 Comments

Using Extended Events Live Data With Azure

Azure, SQL Server, You Can't Do That In Profiler

In my last post I showed some shortcomings of Extended Events, however, it is possible to use Live Data with Azure. Let's explore exactly how that works. To get started, you'll need to follow the directions here to get set up with Azure Storage as the output target of your Extended Events session within your Azure SQL Database. There is a little bit of prep work, but it's all laid out in Microsoft's document. I found the Powershell to be a bit sketchy, but it shows you what's needed. The T-SQL just works. Live Data With Azure Once you've created an Extended Events Session that is output to Azure Storage, you've done most of the work. The trick is really simple. Get the Azure Storage account set up with a…

19Apr 2021 by Grant Fritchey No Comments

Extended Events and Azure SQL Database

SQL Server, You Can't Do That In Profiler

Knowledge of how your system behaves is vital to better control, maintain, and grow the system. While Azure provides all sorts of wonderful assistance within Azure SQL Database, you're still going to need that same knowledge. When it comes to getting detailed information about Azure SQL Database, the tools are a little more limited than with an on-premises instance of SQL Server, or any virtual instance of SQL Server. There are no trace events. To see individual query calls, recompile events, query store behaviors, and so much more, you're going to have to use Extended Events. I'm going to write a series of posts on using Extended Events with Azure SQL Database. Tradition would call for this first post to be an initial how-to. Instead, I want to take a…

24Aug 2020 by Grant Fritchey 2 Comments

Extended Events: Filter on Stored Procedure Name

SQL Server, You Can't Do That In Profiler

I just received a question about Extended Events: What about filtering on the stored procedure name. You know I love writing and talking about Extended Events. The answer is, well, sure, we can do that. However, as with all things, there may be wrinkles worth being aware of. Let's examine this. Filter on Stored Procedure Name Let's create an Extended Event session that captures rpc_starting and rpc_completed: CREATE EVENT SESSION StoredProcedureName ON SERVER ADD EVENT sqlserver.rpc_completed (ACTION ( sqlserver.database_name ) WHERE (object_name = N'AddressByCity') ), ADD EVENT sqlserver.rpc_starting (SET collect_statement = (1) ACTION ( sqlserver.database_name ) WHERE (object_name = N'AddressByCity') ); I've added a WHERE clause to the Extended Event to capture only those procedures that have an object_name equal to 'AddressByCity'. If we look at the output from these…

30Mar 2020 by Grant Fritchey 2 Comments

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…

23Mar 2020 by Grant Fritchey No Comments

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…

16Mar 2020 by Grant Fritchey 6 Comments

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…

Category: You Can’t Do That In Profiler