The Geometry of Performance Issues
Video Summary
In this video, I delve into an experiment I’ve been working on to visualize performance issues in SQL Server’s query store using spatial data types. I explore how adding shapes based on metrics like CPU and duration could potentially help users better understand the relative impact of different queries. This is very much a work-in-progress—far from being production-ready, it’s more of a test to see if this approach holds any value for others. I’d love to hear your thoughts: do you think visualizing performance issues in this way would be useful? Or does it just add unnecessary complexity? Your feedback will help me decide whether to continue pursuing this idea or focus on other areas.
Full Transcript
Erik dishwaskeldarling here with Darlene Data. And in today’s video, so I’ve been tinkering with some big ideas lately, right? And one idea that I had was to try to use, so like SQL Server for years has had this spatial data stuff in it. And one idea that kind of occurred to me was no one ever uses this. I wonder what it’s all about. So what I wanted to do was try to attempt to add some shapes to query store. So that when you’re looking at the results, you might be able to infer a little bit of the sort of sense of proportion of performance problems that one query might be having or causing. So we’re going to talk about the geometry of performance issues in this video. And this video, honestly, is a little bit of market research because I would love to hear if you think this is something that would be useful to you and something that I should pursue working on. Right now is just sort of something that I’ve been testing out a bit and marinating on. It is nowhere near complete or what I want it to be. But I think you might be able to see a little bit of kind of where I’m going with it. So I did want to talk about it to sort of figure out if anyone would care that it’s there.
Or if you would just stare at it, you would just see a new tab show up called like spatial results and scratch your head and walk away. So we’re going to do that in this video. Down in the video description, if you would like to hire me for consulting, buy my training or support the content on this channel, you can do all three of those things there. Those do require giving me money. You can also do things for free like ask me office hours questions. And of course, like subscribe and tell all your friends. Hopefully your friends were not affected by the recent AWS outage. So we do, we do, we do pray for their AI souls if they were. Past Data Community Summit coming up in Seattle, November 17th to the 21st.
Kendra Little and I banging out two days of the most magnificent T-SQL pre-cons that have ever been endured on this planet. So I do hope to see you there. I will have all sorts of neat gifts to give you, to give those who show up. If you don’t show up, beat it. I don’t have no use for you. Anyway, I still haven’t, I still haven’t changed this. I still haven’t quite thought of a good November theme. My birthday is in November, so maybe it’ll be my birthday month theme. Maybe I’ll do that. That sounds nice, right? Cool. Anyway, let’s talk about what I’ve been doing over here. So apparently I just left that up.
So what I’ve been trying to do, and I just stapled this on to the very end of the last Quiky and Quickie store. This is by no means production ready code or what things will look like when they’re done. I’ve got a thoughtfully named column here called G, which attempts to draw a polygon using geometry in the ST Geom from text. And just to start off, I decided to use total CPU and total duration.
I really, in order to get the shapes to be visible, I really had to jack up. I had to do some math on the count of executions for this, because otherwise the squares were like this big, which wasn’t very helpful. And then I thought that, you know, maybe I would look at some slightly different metrics if what I was troubleshooting were a parameter sensitivity issue.
So I’ve got another column down here called P. I didn’t format P quite as nicely as G. Let’s fix that on the fly. There we go. And this one looks at some slightly different metrics, but I still have to really jack up the count of executions locally in order to get things to sort of behave the way I want them to. But anyway, when we run a query store now, and I’m going to just do this from scratch so you can see what happens.
We get some results back, and I haven’t set up to look at like a specific parameter sniffing thing or anything. I just want to show you kind of like what the general layout and stuff is, as well as some of the sort of limitations on spatial results that I can’t do much about. So we get this new tab back up here, right? It’s called spatial results. It shows up between results and messages.
And by default, it will go to the first spatial column. You can see over here, that is the column that I helpfully alias G. And if we want to change the label, we have to do that manually. I cannot currently change. I cannot currently set up a default label. Now, what’s annoying is when you first choose the label column, it doesn’t actually label anything.
But if you just click on the zoom thing, then it shows up. So that’s kind of interesting. But then now we can at least sort of see the queries that are in here. Another thing that I haven’t quite figured out yet is the stacking, right?
So like all these shapes sort of like blow together and they don’t maybe help differentiate things as well as they could. There’s also some that are clearly missing labels, right? I think the labels just get hidden if the shapes have a shape over them or something.
I’m not really sure how that works, but I don’t know. Like some or maybe I don’t even know where this one is supposed to be. And maybe this one was supposed to be here. I honestly don’t know what’s going on in spatial world.
But it is kind of cool that if you hover over, well, of course, it’s going to disappear. If you hover over one of the shapes, you get some information back. One reason why I might need to separate this out into a different result set is so that I can sort of choose the columns that I want here.
By default, it just lists out the columns and the ordinal position of the table, which is not the most helpful thing in the world. So I’m probably going to need a second result set if I want to show more pertinent information in here and not have like the beginning of the query plan XML showing up. Because why on earth would you need that?
Right. But not very helpful to anyone. So the other thing that I can’t do or maybe maybe I can, but I haven’t quite figured it out yet is a way to label the accesses on this thing. Right.
Because we have these two lines of numbers going up and down and across. But what are they? Right. Well, for this, it’s total CPU and total duration. Right. Which, you know, I know because I did it. But if you ran this and got this back, you would say, what are these numbers?
What do they mean? I don’t get it. Right. So maybe figuring out a way to label things and there would be helpful as well. But if we change the spatial column from G to P, we will get some slightly different squares back.
We still maintain the query text label, which is very helpful. And we still get back, I guess, depending on precisely where we click some, you know, the same sort of pop up with the columns that are in there. This block is apparently forgotten to be labeled as his.
Well, I mean, probably the one hiding behind this one has forgotten to be labeled as well. So obviously some limitations, obviously some stuff in here that needs to be like worked out probably mathematically. I’m not quite sure how to do that yet.
I’m also not quite sure how far to sort of pursue this. Given some of the limitations and some of the little annoyances, it might just be more confusing than it is helpful to a lot of people. It might have to be like an optional thing that gets sent back.
Like if you are like cool with however this works. And then, of course, there’s also this sort of dynamic bit of, you know, figuring out, you know, what metrics to draw the squares by. If the count of executions is sufficient to draw a square where the size, you know, is visible on the screen.
So there’s like some stuff that really has to like, you know, get, you know, figured out at first. But I do want to sort of get a general sense of if you, the community, the people who watch these videos, hopefully the people who use my store procedures like SP Quickie Store would find any of this stuff at all useful when troubleshooting performance things.
Like the query store GUI itself is a godawful disaster. And whoever designed it has clearly just a lot of disdain for humanity and user experience. But they do have colorful graphs, right?
They do have charts and graphs. There are some visuals there that people find helpful. The one thing that Quickie Store doesn’t have going for it, pretty pictures. So I figured I’ll try to draw some pretty pictures.
But there we are. Some things that I don’t want to do are leave people with needing to export data somewhere else, right? I don’t want to have to copy and paste data.
I don’t want people to have to like have like Grafana or Tableau or Power BI to go do this stuff because it’s an extra step, right? Most of the time when I’m working with clients, the last thing I need is an extra step with a dependency on it that might not exist. So what I want to do is just have something that works in SSMS, gives you some feedback on the proportion of a query’s performance impact, and gives you a way to sort of like judge based on whatever metrics you care about, like what stuff you should be dealing with.
So if you like it, let me know. If you don’t like it, think it’s stupid, say, Eric, stop wasting your time on these fanciful feats. Go back to work.
Go spend time with your wife and kids or something. I don’t know. But feedback is requested. So thank you for watching. I hope you enjoyed yourselves. I hope you learned something.
I hope that you will give the feedback that I have requested. And I don’t know. We’ll figure it out from here, won’t we? All right. Thank you for watching.
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.