search

BrentOzarULTD / SQL-Server-First-Responder-Kit

sp_Blitz, sp_BlitzCache, sp_BlitzFirst, sp_BlitzIndex, and other SQL Server scripts for health checks and performance tuning.
http://FirstResponderKit.org
Other
3.38k stars 999 forks source link

sp_Blitz: Added check for Query Store not being in READ_WRITE state and check for it not being in desired state. #3536

Closed ReeceGoding closed 5 months ago

ReeceGoding commented 5 months ago

Closes item 7 in https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/3527 . Only item 6 is now awaiting a PR. This change includes two new checks. Adding a check for Query Store not being in its desired state was such a natural follow-up from checking for READ_WRITE that I couldn't resist.

I've made this a priority 200 issue under the 'Performance' header. This is the consistent with the check for having Query Store completely disabled. After all, these two issues effectively disable Query Store.

The URL I've added is the best one that I know for this topic. Both checks have the same URL.

I've only been able to test the check for the READ_WRITE state. I've only ever seen Query Store leave its desired state on busy production databases. I'm unsure how to simulate it to test it myself. I'm open to ideas. Maybe just make the settings for Query Store ridiculously low and hit your server very hard?

ReeceGoding commented 5 months ago

@BrentOzar That should fix it.

ReeceGoding commented 5 months ago

I'm unsure how to simulate it to test it myself. I'm open to ideas. Maybe just make the settings for Query Store ridiculously low and hit your server very hard?

Tested this. It worked. You don't even have to hit the server hard. Just make the Query Store settings super low.