search

erikdarlingdata / DarlingData

Open source SQL Server nonsense: sp_PressureDetector, sp_QuickieStore, sp_HumanEvents, etc.
https://www.erikdarling.com/
MIT License
443 stars 132 forks source link

sp_QuickieStore: Do we have any interest in replicating the 'Queries With High Variation' dashboard? #447

Closed ReeceGoding closed 2 weeks ago

ReeceGoding commented 2 weeks ago

Is your feature request related to a problem? Please describe. I'm mostly looking to start a discussion for this one.

In Query Store, we have the 'Queries With High Variation' dashboard. It is documented here. I find that I almost never use it. Is there any interest in replicating it in sp_QuickieStore?

Describe the solution you'd like The lazy solution of just adding it as an extra @sort_order is tempting. Just sort by the standard deviation of the duration and call it a day.

Describe alternatives you've considered I think that to do it properly, we'd need a new parameter that interacts with @sort_order. For example, let's say we called it @sort_by_variance_mode and made it a boolean. When true, it would cause us to sort by the standard deviation of the metric selected by @sort_order rather than the metric itself.

We might not even have to compute the standard deviation. It's already in one of the DMVs.

Are you ready to build the code for the feature? If someone can convince me it's valuable, yes. It sounds like an interesting problem and I could do with the excuse to dust off my statistician's hat.

IMPORTANT: If you're going to contribute code, please read the contributing guide first. https://github.com/erikdarlingdata/DarlingData/blob/main/CONTRIBUTING.md Sure.

erikdarlingdata commented 2 weeks ago

I find that I almost never use it. Is there any interest in replicating it in sp_QuickieStore?

Heh, I don't think I'd want to replicate a feature that neither of us use (to be honest, I've never come across anyone using it, but I am in a bit of a bubble). I sort of hate that view because the variance doesn't ever seem to be all that big, and you can find the big ones just looking at top resource consuming queries usually.

The regressed queries view would be more interesting, potentially, but also a lot more work to get right. The built in view will go and warn you about queries where there's a 100ms difference between plans, which isn't valuable.

ReeceGoding commented 2 weeks ago

Funny that you mention the regressed queries view. I've just opened an issue for it #448 . If we both agree that regressed queries is so much more worthwhile that it makes the variation dashboard useless, then feel free to either just close this or have one of us implement the lazy solution that I've suggested.