Database Level Option For Storing Wait Stats
SQL Server 2016 introduced Query Store, and SQL Server 2017 CU3 brought us aggregated wait stats at the query level. This is great information for data driven DBAs who need to be able to track plan changes over time, etc.
With that in mind, an additional data collector for wait stats per database would be a great product feature, independent from Query Store data collection.
We all know the limitations of the wait stats DMVs now:
- They're cumulative over time
- They're instance-wide
- There's a lot of potential noise from wait types that aren't registered
If you wanna figure out what's going on with them, you have to log them to a table yourself, or spend a bunch of money on a monitoring tool.
SQL Server should have a database-level option to store wait stats at a user-defined interval, for a user-defined period of time, the same way that Query Store works now. There could be additional options to only log significant waits based on occurrences and duration, etc. To make it extra useful, data could be centralized in msdb (or via another system view, like sys.all_wait_stats or something).
This would be an invaluable feedback mechanism for SQL Server users at all levels, especially now that Availability Groups and cloud migrations are more and more common. Admins with SQL Server responsibility need historical data like this to gauge workload efficacy on different hardware.
Thanks for reading!
Erik Darling
shared this idea