Why Is My Query…

We’ve all started a question with a close approximation of those words. No matter how you finish that sentence, there’s some basic information that you need to collect to figure it out, like:

• Query plan
• Wait stats
• Other server activity

Those are a good place to start. It’s easy enough to get a query plan, either by running the query with actual plans on, getting an estimated plan, or retrieving it from the plan cache with sp_BlitzCache.

The last two can be tough, unless you’re observing the problem, or you have a monitoring tool in place.

No, I’m not trying to tell you to buy a monitoring tool.

SQL Server Should Do This

We’ve got Query Store. It tracks an insane amount of metrics about nearly every single query that runs.

Per database.

We’re talking aggregate metrics, the query plan, the text, set options, compile time and memory, and with SQL Server 2017, we get aggregate wait stats in there, too. That’s totally awesome information to have. You can go a long way with that information.

The trouble is that people aren’t really adopting it quickly. There are a lot of questions about the overhead, about the kind of information it collects and if it will expose user data (which is yet another check box if GDPR is a concern to you), how much space it will take up, and more.

That’s why I’ve opened this Connect Feedback item:

Database Level Option For Storing Wait Stats

Vote Early, Vote Often

I’m hoping that a feature like this could solve some intermediate problems that Query Store doesn’t.

Namely, being lower overhead, not collecting any PII, and not taking up a lot of disk space — after all, we’re not storing any massive stored proc text or query plans, here, just snapshots of wait stats.

This will help even if you’re already logging wait stats on your own. You still don’t have a clear picture of which database the problem is coming from. If you’ve got a server with lots of databases on it, figuring that out can be tough.

Understanding what waits (and perhaps bottlenecks) a single database is experiencing can also help admins figure out what kind of instance size they’d need as part of a migration, too.

Especially going to the cloud, configuring instances can feel a lot like hitting the “Random” button on your character configuration screen. You just keep pressing it until something makes you laugh.

Thanks for reading!