Query Hashes and Parameter Names in SQL Server

Erik Darling here, with Darling Data. Removing my monitoring tool mogul superhero outfit costume, play suit, for a moment. Talk about, I don’t know, something interesting that I ran into while working with some stuff in Query Store, and I thought that I would share with you. And it is the behavior of how queries get hashed when parameters get used. And I think if you’re the type of person who goes looking through Query Store for specific things because you use store procedures like SPQuickieStore and not the Query Store GUI, which, unfortunately, as of this late date and its inception, still does not allow any real, does not really have any search capabilities. Down in the video description, you will see all sorts of links that will help you get closer to me.

You can hire me for consulting, which is probably the ultimate in closeness. You can buy my training. You can become a supporting member of the channel. You can ask me office hours questions. And, of course, as always, if there is someone in your life who you feel needs this brand of enlightenment or harassment or whatever I happen to be up to, please do share, like, subscribe, tell a friend, all that good stuff.

Anyway, I have a new, well, I guess, I mean, I don’t know, like a little over a month old now, SQL Server Monitoring Tool. Totally free, totally open source, no intrusive stuff in your life. Just a really great way to keep an eye on SQL Server performance for servers you care about, but may not be able to get budget for a paid monitoring tool that would do this job.

Collects all the stuff that you would want to know about, all the stuff that I would care about and get into during my consulting engagements. And then for those of you who have embraced the robots, there are opt-in MCP servers that can talk directly to your collected performance data so that you can, you know, have the robots to reduce some analysis on just what got collected. It’s all nice and sliced up over time.

So they’ll be able to do a much, much better job of looking at those things than they would if you just set them free to a running SQL Server and you said, go run some DMV queries, I trust you. I wouldn’t do that. As far as me getting out and about in the world, apparently there is still some hunger for in-person connections.

So I will be traveling around. I will be at SQL Day in Poland, May 11th through 13th. I will be at Data Saturday, Croatia, June 12th and 13th.

Those are my fabulous upcoming international events. I’m very excited. You know, you can get away for a little bit. I’ll also be at Pass On Tour, Chicago, the Illinois, May 7th and 8th.

And I will be at Pass Summit in Seattle, Washington, November 9th through 11th. So I believe tickets are on sale for every single one of these things. So there’s very little excuse for you to not come see me in person as long as, you know, I guess, as long as you can get there.

Can’t we all, can’t we all just get there? Anyway, it is time at long last to look at this interesting thing. So let’s go over to SQL Server Management Studio.

We haven’t seen one of these in a while. And the first thing I want to say here is that this is not a political statement. I was born the night of this election and I often use this as a sort of jokey reference point to things in my life. But we have two queries here that are fundamentally identical, right?

We’re saying select c equals count big from dbo.users as u, where u.id equals some parameter. Right? And the reason why this is interesting is because for a very, very long time, I was under the, I guess, mistaken impression that parameter names were taken into account when generating a query hash. But after asking my dear friend at Microsoft, who I guess in turn asked their internal AI chat bot about this, which I thought was funny.

They said, the chat bot came back and said no. At this point in the code, when SQL Server is trying to figure out what a query’s hash should be, it sort of substitutes the parameter with some just constant node in there. And it’s just like, it doesn’t matter what you’re named.

Right? You can be anything. You’re just a parameter. What good are you? Right? You can be anything. Right? It doesn’t matter. So I thought that was funny because it turns out I was wrong. And the reason why this came up is because I was working with a client and we came across a troublesome query.

And I was like, well, let’s, let’s, let’s look this up by query hash and query store. Let’s see what we can’t get out of this thing. Let’s see what this thing is fully up to out in the world.

And so we put the query hash in the SP quickie store and I said, go find it, SP quickie store. And as fast as lightning, SP quickie store was out there finding query hashes for me. And it came back with two rows, one for the query we were looking for and one for another query that looked rather similar, but had a different query, but had a different parameter assigned to it.

And I said, oh, well, that is fundamentally distressing. Anyway, enough about me. How about you? Let’s run these two queries.

And, you know, we’re going to get execution plans for them. Not that the, not because the execution plans are of any interest to us whatsoever here, but because when we go and look at the query plans, we are going to see some interesting things.

So if we get the properties of this one, right, we have all of the usual attendant, lovely information that Microsoft has provided to us in the properties tab. And if we zoom in over here, we will see this thing. We will see the query hash and query plan hash for this.

These are somewhat long and difficult to remember. But if you sort of just get a general sense for them, I like to remember like sort of the last four digits, the last four bytes of the hash.

So we have like 9E02 and 7383. They start with 452 and 134. All right.

And if we just sort of keep this in mind and we go over here, we notice that when I switch back and forth between them, the parameter names change, but the query hash and query plan hash don’t budge, right?

None of that changes, right? We get a different SQL handle for it down here, right? We can see the SQL handle change because I suppose that does take the parameter name into account, but the query hash and query plan hash stay absolutely identical, right?

Down here on Jimmy Carter, we have the same thing. Starts with 452, starts with 134, ends with 9E02 and ends with 7383. So if you’re ever looking through a query store in this, you know, unlikely scenario, you’re using SP Quickie Store available at code.erikdarling.com.

You’re looking through a query store and you say, I want to find something by this query hash. So you use my very thoughtful include query hashes parameter to do that.

And you find multiple queries, the same hash. Well, if they have different parameters, that’s why. Parameters don’t factor into the query hash.

You learn something new every decade at least. Alright, that’s about it for me. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something.

I’ll see you in tomorrow’s video. Have a good one.