One of the big principles of our SQL ConstantCare® monitoring product – which happens to be free this month – is that we only wanna tell you things you’re actually gonna take action on.
Anytime we email you advice, there a few “Mute” links that you can click on to never hear this advice. You can mute the entire rule, or mute it for specific servers or databases.
Here are the top 10 things you’re muting, and the percentage of the audience that’s muting ’em. Keep in mind that they may not be muting the recommendation for everything – might just be for specific servers or databases.
- #10: Missing Indexes: 15% – I actually thought more than 15% of the audience would mute these recommendations because I figured a lot of y’all were managing some third party databases that you didn’t want advice on. Maybe this number’s low because we’re not just repeating what Clippy says here. We’re actually analyzing the indexes, merging things together, and skipping dumb ideas. I’m excited that 85% of y’all want to hear the index advice!
- #9: Databases Offline or Restoring: 16% – We warn you if a database flips into an unusual status, but that’s to be expected if you’re playing around with log shipping, or flipping AG databases back & forth.
- #8: Fill Factor Too Low: 17% – There are still a lot of folks out there that believe sub-80% fill factor is smart, when in reality they’re wasting >20% of their memory, making backups take 20% longer, etc. I like to hope that the fill factor was set by vendors, but … you can override that easily, and the vendor would probably never even notice.
- #7: No Recent Full Backups: 19% – Okay, now, this is a bummer because we’re talking about a week between backups, and only databases that are actively online (not restored recently, not offline, etc.) I know you think your dev databases aren’t that important, but … at least grab a backup weekly. Sooner or later, you’re gonna wish you did.
- #6: Transaction Log File Too Large: 22% – As in, larger than the data, and larger than 5GB. I’m totally okay with people muting this as long as they understand the implications for longer restore times.
- #5: Cumulative Update Available: 23% – This one really surprises me because we only alert people once a week.
- #4: No Recent Corruption Checks: 24% – I want to believe that y’all are offloading corruption checks elsewhere.
- #3: One Serious Query to Tune: 29% – This one pops up when the majority of your SQL Server’s workload is due to one specific query, which means you can make a difference quickly by tuning just that one. I totally understand people muting this because you might have taken your best shot at tuning it, and decided it’s as good as it gets. You might also be okay with the performance of that server overall.
- #2: Stored Procedure With Recompile: 34% – Recompile hints are a totally valid solution to some performance issues, and I understand why folks mute this advice. As long as you’re aware that the code won’t show up well in monitoring tools, that’s okay, and besides, we can still catch it with Query Store. Which brings us to…
- #1: Consider Enabling Query Store: 53% – Holy smokes! Look at the jump between #2 (34%) and #1 (53%), and it’s even more eye-opening. Most of y’all really do not want to hear about Query Store.
So I gotta ask the question, and it’s only targeted at people in that last category: why are y’all so dead set against using Query Store? Let me know in the comments. (If you’re a Query Store fan, that’s great, and I’m with you, but stay out of the comments to let others speak. If you feel like leaving a comment about how great Query Store is, this video may help you cope.)
And if you’re curious about what you’re missing, you can get a free trial of SQL ConstantCare®.
22 Comments. Leave new
For us its size. Query Store grows fast, and yes you can set limits, but then it just stops working. Maybe we just haven’t taken the time to groom it properly. We will turn it on for a while, and then end up turning it off to reclaim the space.
Gotcha. I’m guessing, just guessing, that you have the unparameterized query problem. Try this: https://www.brentozar.com/archive/2018/03/why-multiple-plans-for-one-query-are-bad/
Speaking as someone who is only quasi using query store these days, and using it late at that – I was leery of it and didn’t turn it on right away, even on test servers because in the earlier days (from what I remember) there were issues with it not cleaning up after itself, taking forever to startup or shutdown, and even crashing servers from running them out of space. Plus there’s always overhead to more monitoring, so a test server isn’t going to be a good indicator of the overhead it’s going to have on production.
Newer versions are better about all of those things, with better default settings and whatnot. But given that it’s still *yet another* new thing to spend time to learn, and with the historical issues, I could see people just squinting their eyes at it and saying to themselves nah don’t have time for that right now, and ignoring it. Regardless of the benefits.
I think that’s totally fair – I recommended against it in the beginning because it was so buggy.
Not a constant care user but unfortunately we do not enable query store.
We have alot of queries that improperly parameterize dynamic SQL and would take a bunch of dev work to correct.
Makes sense. I understand the tradeoff there, for sure.
For me on query store, dealing with a third party application that directly in lines all the variables so every query is unique.
The plan cache is so unstable it makes a banana republic look like the British crown in longevity.
On another, EF core blowing out the plan cache with its IN bs.
Yeah, makes sense there!
(And I’m still laughing about the banana republic line)
I have over 1,600 databases on a single server. Enabling query store for all them creates a significant amount of overhead on the server.
Makes sense – and I’m guessing you have the same app for all of them, so it’s not like one of them has a dramatically different workload.
Hi Brett, just on the one serious query to tune, for us it’s always the sb blitz query which we mute on our servers.
Hmm, what sp_Blitz query are you running that often, and why?
I am really surprised about the number of people ignoring query store. The comments about query store size and slow start/stop speeds can probably be resolved with configuration and trace flags. I find query store absolutely evaluable when I inherit a new application. it is also good for the occasional fix that will work most of the time for a poorly selected plan. I use it less now that I have some better monitoring tools, but still do use it from time to time and its essentially a free thing that can help with performance or monitoring even at a very low level of familiarity with the tool – such as I have.
We’ve adopted query store from the very beginning, when it was claimed that it loads query store data async on startup (1gb size limit on 2018), and it was crashing our server without the option to recover from it (data loaded sync although the wait state was qs_async IMHO ).
Yet, I find QS very useful for identifying bottle necks / multi plans and more. Not to mention the pinning plan option.
Must have tool for any DBA for investigation purposes.
Us old school folk persist performance metrics from dmv’s and catalog views to troubleshoot performance issues. Once you get to know the database workload, you will know what metrics to monitor and look for, we’ve had to build our own “query store” framework. The beauty of this is, the new starts get to better understand the backend. Query store is like having roadside assist to come and change your flat tire on the highway. Roll up those sleeves, get dirty and learn how to do it yourself. There are two types of people in this world. People that Google how to fix the issue and be gone and people that want to understand how it works. Latter might take more time initially but pays off later on in life because when new issues pop up, you won’t have to consult the University of Google.
I firmly disagree with that assessment of what query store is. On its own, query store doesn’t fix anything and what ability it has to fix anything under any scenario is limited. I think of it more like a black box recorder that keeps a mostly complete record of the recent query history in a SQL server AND their associated execution plans. Without an execution plan, there is very little to go off of to determine why a specific query is slow without supporting it with conjecture.
To your point about the why – sometimes the ‘why’ doesn’t matter. When you have a third party or legacy app that can’t be modified that intermittently picks a terrible execution plan, it doesn’t matter why it is sometimes slow, it matters that it is sometimes slow and query store is one of the few options available that can do anything about that specific scenario.
I’m guessing that those who have muted #2 (procedures with WITH RECOMPILE) have a few of Erik Darling’s procedures, like sp_PressureDetector, in a utility database. Or the First Responder Kit…
At least, that was the reason for this puny mortal to mute it.
A word for RECOMPILE… did you know that your sp_Blitz show up on your emails? It’s pretty funny and I would think SQLConstant care would skip the tools within the First Responder Kit.
😀
Why would I hide our code? That would be really slimy.
I have gone through as many of your blog posts as I could find about fill factor and haven’t found one that mentions the “Server Properties” > “Database Settings” > “Default index fill factor” option. I triple checked the SQL Server Setup Checklist that comes with the first responder kit and don’t see it mentioned there either. Would you recommend setting that option to 100?
100 is the default (same as 0). Leave ‘er there. Cheers!