Capturing Query Compilations With sp_HumanEvents
Video Summary
In this video, I delve into the issue of frequent query compilations using my go-to stored procedure, `SP_human_events`, to help troubleshoot various SQL Server problems. We explore how to identify queries that are compiled frequently and discuss why this can be a significant issue, even if it might not seem like the biggest problem at first glance. I walk through setting up an extended event session with specific parameters to track query compilations in real-time, demonstrating how to interpret the results to pinpoint problematic queries. This video is part of a series where we cover different tools and techniques for diagnosing SQL Server issues, including recompiles and wait stats, ensuring you have a comprehensive toolkit for optimizing your database performance.
Full Transcript
Erik Darling here with Darling Data and I have tried to start recording this video no fewer than five times before being hit with a sneezing fit. So if I get through this one, it’ll be nothing short of a miracle. This video is a continuation of this series of videos where I’m talking about how you can use my amazing, the most rockinest, shockinest, hip hopinest store procedure on the planet. So we’re going to be looking at the planet. SP underscore human events. To troubleshoot various SQL Server problems, we’ve talked about using it to troubleshoot blocking, we’ve talked about using it to troubleshoot query performance issues. This is going to be a short one to look at finding queries that are compiled frequently. Now, query compilations may not be the biggest problem on your system, but a lot of the systems that I look at, they are not going to be the biggest problem on your system, but a lot of the systems that I look at, they are not going to be the biggest problem on your system. more than a few times they’ve ended up being a pretty big issue.
You look at a query performance counter like batch requests a second, and then another query performance counter like compilations a second, and you might find that the number of compilations a second pretty closely follows batch requests a second.
That means that SQL Server is just constantly sitting there coming up with brand new query plans for queries that could be parameterized. Now, I know a lot of you out here are going to hear parameterized queries and have a mild freakout because you’re afraid of parameter sniffing, and God, I wish you’d stop being such babies about it.
But here we are, you being big babies, afraid of a sniff parameter, like there’s no way to fix parameter sniffing in the world, and not solving systemic workload problems.
like frequently compiling queries, not compilating, that’s my mistake. Frequently compiling queries when you could, because you don’t want SQL Server just sitting there coming up with query plans constantly every time a batch compiles.
It’s not a good feeling. So we’ve talked about a few other ways that SP underscore, human events, can help you troubleshoot other issues.
We looked at blocking, we looked at query performance. We’re going to look at compilations now. We will most likely look at recompiles next, and we will look at wait stats for the final installment in this video, where I’m going to be talking about different ways that I utilize SQL Server community tools when I am helping clients with their problems.
So I’ve already kind of done some of the legwork here, so that you don’t have to sit there and watch me hit F5 and wait for stuff to happen. So I’ve set up this extended event, and I’ve used this extended event with the keep alive parameter just to have a persistent session going.
And over in this window, I have set up a query to execute. And when this query executes, every single time, it will receive a new literal value. And this new literal value, because we are not getting simple parameterization for this query, every time this thing executes, it will compile a new query plan, which for this query is not that big a deal, because it’s not very big, it’s not very complex.
But at the same time, it’s an easy demo. And you’re going to have to suspend disbelief a little bit and live with an easy demo that shows you how this thing works and what you might want to do to fix it.
All right. So thanks. Thanks for helping me with that. So this just runs in a simple loop. Doing this little bit of…
I mean, this isn’t dynamic SQL exactly. This is like the bad kind of dynamic SQL that you don’t want to be doing, and this will definitely cause frequent query compilation.
Because every time this query runs, SQL Server will see a different literal value and say, Ooh, a brand new query. Have a brand new query plan.
Even though probably every query plan is exactly the same, because we’re just going to seek to a specific value in the clustered index. But nevertheless, SQL Server is just coming up with new plans. All the darn time for this thing.
So what does our extended event show us? Well, great question, because I’m about to show you. So some newer versions of SQL Server have an event called query parameterization data, which will give you some additional feedback about if the query has literal values, if the query could be parameterized via force parameterization, and if it’s a recompile event.
Now, I know I’m skipping ahead a tiny little bit here. Again, you’ll have to forgive me. And we will see that this query…
Let’s zoom in here. Let’s have a lot of fun. This query has literal values, is parameterizable, and is not recompiled. So this is not a recompilation event.
This is a compilation event. This is SQL Server thinking that it has discovered a brand new query and giving us a brand new query plan to go along with it, which is, I’m going to be honest with you, quite strange, because we really don’t need a brand new query plan for this, do we?
We’re just seeking to a single value in a clustered primary key. Why on earth would we need a new query plan for that? That seems weird there, SQL Server.
But here we are trying to troubleshoot this issue. The stock and standard extended event that comes along with this one on all versions of SQL Server, newer versions of SQL Server, I think 2016 or something plus, have a query parameterization data event in there.
The SQL statement post-compile event is in all of them. And that will just say, is a recompile false, and recompile cause not a recompile.
So very helpful stuff there. I’m glad I chose to show those two columns in the output. That’s smart of me. But this is one way that we can track down queries that are compiling rather than using an existing plan.
Well, we can track down queries that are compiling and sort of figure out if they’re ones that could be using an existing plan. It would be helpful if I put this statement for these things in here, wouldn’t it?
Oh, hey, look. We found some other things that are showing up in here. That’s fun. There we go.
Those are the ones I wanted to show you. This is stuff that happened afterwards because I was a ding-dong and I didn’t stop the session. So these ones in here, please, once again, suspend disbelief.
Ignore these ones a little bit. There we go. We can just cut those off. I feel better about that now. So every time this query came in, it gave us a brand new query plan.
It compiled as a brand new query. All 10 of these things, SQL Server said, oh, this number. What could I possibly do with this number? Brand new query plan for all of you.
And that’s not great. Now, the query parameterization data event also has this SQL text field. But for the query that I wrote to generate this, it is probably a bit less useful than you would find it to be either in the batch or store procedure where the compilation event is happening.
You would probably see a lot of dynamic SQL being generated, the bad kind of dynamic SQL that’s not parameterized doing this stuff. You might catch queries from that third-party vendor, which you might be that third-party vendor, so welcome to the party, pal, that are not parameterized in the application or wherever your queries may originate.
I try not to think too hard about that because my brain would turn to a puddle of mush and drip out my ears.
That just wouldn’t be fun for anyone. So this is a good way to track that stuff down. The forced parameterization setting can help in some cases where it wouldn’t help where a query is partially parameterized and there are some literal values.
You’re kind of hosed there. Forced parameterization won’t fix those. But if you’re lucky enough to have a bunch of queries that have no parameters attached to them and forced parameterization is not turned on for your database, and this could be a good way to fix it.
Otherwise, it would either be you as the, well, would be first or second part. I’m still not clear on what a second-party vendor would be. First party is you made it.
Third party is someone else made it. Second party, I don’t know. Someone made it for you maybe? I don’t know how to interpret that. And God help us if we ever figure out that there’s a fourth party involved.
So, man, there’s a lot going on here. Anyway, if you look at your SQL Server and you see that compilations are a, compilations per second is a significant portion of batch requests per second, then you may want to think about running this to see what kind of queries are causing those compiles and then come up with a reasonable solution either with the database level forced parameterization setting, yelling at developers either first, second, third, maybe even fourth or fifth party to properly parameterize their queries or, um, I don’t know.
Just shut SQL Server down. See what happens. Maybe no one will complain. Maybe everyone will say, cool, the server’s down. I’m going to go outside and enjoy myself.
I’m going to touch some grass, as my friend Arthur likes to say. Anyway, thanks for watching. Again, next video, we’ll look at recompiles or recompilations, whatever you’re into.
Video after that, we’ll look at weight stats and, um, I don’t know. From there, we’ll go on and look at a different, a different tool that I think is good to use for troubleshooting SQL Server stuff.
Thanks for watching. I appreciate you.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.