A Fine Slice Of SQL Server
Grant Fritchey answers a question:
I love questions. Most of all, I love questions I can answer. I spotted this question recently: How can I use Profiler to capture execution plans for queries over a certain duration?
Oh, that’s easy. You don’t use Profiler. You use Extended Events.
Click through to see how.
Leave a CommentGrant Fritchey takes us through a question:
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.
Do check out the comments as well.
Comments closedEitan Blumin uses Extended Events to track activity:
Extended events provides a solution similar to client side trace. It basically can capture all events that a trace can capture (and more), and it also supports a wider choice of target types. And that, is where its true power lies.
It just so happens that Extended Events has a target type called “ring buffer“, and it gives us exactly what we need.
The ring buffer is easy to set up and if you don’t need permanence, works great.
Comments closedErin Stellato recommends putting the system health extended event on a diet:
In Aaron’s post he refers to the security_error_ring_buffer_recorded event as “unactionable noise”, and frankly I can’t think of a better term for it. I’ve never used it to troubleshoot it any authentication/security issue, and I’m very confident that Microsoft isn’t using it in its current state either. In terms of the volume of that event, for the new client system that I looked at recently the five system_health files covered about four (4) hours of time. Of the 1,851,741 million events captured in that time frame, the security_error_ring_buffer_recorded event showed up 1,838,882 times. That’s 99.3% of the events…absolute noise.
Erin shows you how to turn this off and get rid of a mess of unhelpful messages.
Comments closedGrant Fritchey shows us how to use Extended Events to capture the T-SQL of a prepared statement:
Why would I be concerned with prepared statements? Wouldn’t sql_batch_completed and rpc_completed cover us? Well, no.
What happens when you use sp_prepare? What happens when you’re using an ORM tool that’s using prepared statements? You may see queries that look like this:
EXEC sp_execute 5, 48766;What the heck code is that executing? Let’s find out.
Read on for the code. It’s not the type of thing you want running 24/7 against all of your prepared statements (certainly not on a busy system!), but very useful for troubleshooting when you don’t have easy access to that next level up in the application chain.
Comments closedAaron Bertrand takes a look at a pre-configured tool and gives us ways to make it better:
I was recently trying to troubleshoot a SQL Server replication-related deadlock that our monitoring tool didn’t capture, and tried to find information about it in the system_health Extended Events session. With the default retention settings and the amount of noise contributed by security ring buffer events, I quickly discovered that the session only had data going back less than two hours. Meaning unless I started investigating an event immediately after it happened, all evidence had rolled out forever.
Read on for Aaron’s guidance around this. The natural next step is to build out your own extended events which capture what you need.
Comments closedGrant Fritchey gives us a few ways to find row counts after a query has finished:
But, if you really want to get picky, batches and procedures frequently have multiple statements. This means that the row count you’re getting isn’t for a particular table. If you want to get that, then you need to capture the statement level stuff, sp_statement_completed or sql_statement_completed, depending on if you’re looking at procedures or batches.
However, this is one of those “be careful what you wish for” moments. Because, if you capture all statements in a system, you’re going to have a very large amount of data to deal with. This is where filtering is your friend.
Click through for Grant’s full answer.
Comments closedJason Brimhall dumps a server-side trace in favor of extended events:
Some time ago, I wrote an article for SQL Server 2008 to help determine the use of the server since SQL Server 2008 was reaching End Of Life. In that article, I shared a reasonable use of server side trace to capture all of the logon events to the server. Afterall, you have to find out the source of connections and who is using the server if you need to migrate it to a newer SQL Server version. You can read that article here.
Soon after, from various sources, I received requests on how to perform a logon audit using the more preferred, robust, venerable, awesome tool called Extended Events (XEvents). In response, I would share a login audit session to each person. In this article, I will share my login audit solution and give a brief explanation. I use a solution like this on more than 90% of my client servers and I find it highly useful.
Click through to see how.
Comments closedGrant Fritchey shares plenty of links for people to learn about extended events:
However, it’s really important to me that you understand just how powerful and amazing a tool Extended Events is. Further, that you have some resources to get going on this. So, I reached out to a bunch of friends and acquaintances and just some rando’s on the internet to compile a list of the very best of their Extended Events posts.
I’ll be sharing and promoting these links in all my upcoming sessions. They’re in the published slide decks. However, I also want to share them here.
Read on for the list.
Comments closedGrant Fritchey shows us how to see the waits associated with a specific query:
And that my friends is only the waits associated with the one query. TA-DA indeed!
Now, we could get into filtering this stuff too. Toss the ones that have little to no duration, ensure that I only capture for a specific query or procedure, all would be helpful. However, this is how you can easily identify just the waits associated with a single query, and only that query.
Click through to see how. The one thing I’d caution here is that the query which received waits isn’t necessarily the query in the wrong—it might be the fourth or fifth session in a blocking chain. But this is a great technique for getting additional per-query info when you can control the experiment.
Comments closed