Slow Performance of vendor-supported app - resolved by DBCC FREEPROCCACHE

Question

My internal business customers use mainly commercial apps provided by vendors.

• One of the vendor apps experience slow performance typically noticed by users on Monday. So I make sure to do daily stats update and index reorg on the DB during the week, and index rebuild on Sunday night. I use Ola Hallengren index maintenance. But performance issue persisted.
• Vendor then requested us to run DBCC FREEPROCCACHE on Monday morning, and that does resolve the issue throughout the week.
• For the root cause analysis, vendor said they copied the DB to their local environment and did not experience any performance issue. Thus they said the issue must be due to our local environment. I find that hard to agree with as the SQL server 2019 build is a vanilla build. No config options or flags were set, etc.
• What analysis can I do to determine the root cause issue instead of running DBCC FREEPROCCACHE which I feel is just a band-aid? How do I know which vendor queries are at the root cause? Can I do it without specific knowledge of the vendors app code and DB schema?

Answer 1

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.