As you might have already grasped, the reason DBCC FREEPROCCACHE helped is that this way you threw out all query plans. This means that the plans for all queries had to be recompiled.
When SQL Server compiles a plan, it looks at the values of the parameters and use them as guidance when building the plan. This is known as parameter sniffing. Often this is beneficiary, but sometimes it can backfire, and if a query gets optimised for an atypical value, you can be stuck with bad performance of one query.
Thankfully, SQL Server 2019 comes with a very good tool for troubleshooting these kind of issues. Issue this command:
ALTER DATABASE YourDB SET QUERY_STORE = ON
The next day, or a couple of hours the next day, go to Object Explorer and find the database. You should now find a node Query Store, with a couple of options. I will not say too much, but leave it to your own to explore them. Here you can find which are your slowest query etc. While I myself prefer to run queries against the Query Store tables , the UI still has a lot to offer.
But, wait, there is more! Say that you find all of a sudden a query has started top the tops by far at the same time as the user has started to complain how slow everything is. You can now force the plan from yesterday to restore performance in an instant.
One thing that can be a little tricky for Query Store is how much size you need. The default for SQL 2019 is 1GB if memory serves. Data is by default retained for 30 days. But depending on the workload, 1GB may not be enough. This is something you should monitor, and if Query Store becomes read-only, increase the size.
Query Store is indeed designed to be lightweight, but it does come with a small overhead, and there are workloads for which Query Store is not really appropriate. That would be workloads that spew out many unparameterised short queries. Such workloads are generally the result of a bad design, but I digress.
Anyway, starting with SQL 2022, Query Store is turned out by default for new databases, and if memory serves, Query Stored has been on by default in Azure SQL Database for a few years. Microsoft would not take this step, if they were not confident that the overhead from QS is acceptable for the vast majority of workloads.
So, yes, you can turn out Query Store for multiple databases on the same server, but it's not a bad idea to have a bit of attention for the first few days. Once you have seen what you can get out of Query Store, it is not unlikely that you will be very insistent and Query Store should be on for all databases in your estate.
Sign in to comment