General Failure Failed Forced Plans in Query Store Cause Even Slower Compile Times
🔥 UPDATE: Microsoft has announced the general availability of the Next-gen General Purpose service tier for Azure SQL Managed Instance, which includes improvements to I/O latency, IOPS, and transaction log throughput. This post describes the original General Purpose blob storage. You don't want that.
This post demonstrates two related bugs with plan forcing in Query Store which increase the likelihood of slower query execution and application timeouts in SQL Server environments.
These bugs are most likely to impact you if:
- You use the Automatic Plan Correction feature in SQL Server, which automatically forces query plans.
- Anyone manually forces query plans with Query Store.
- You have slow storage, which can increase your likelihood of having longer compilation times.
The General Purpose tier of Azure SQL Managed Instance and Azure SQL Database feature both slow storage and Automatic Plan Correction enabled by default. So, weirdly enough, your risks of suffering from this problem are high if you are an Azure SQL customer.
Thanks to Erik Darling for his help in diagnosing and reproducing these issues– and his ‘slow compiler’ query used in this post was incredibly helpful to isolate and narrow down these problems.
