I am in Love with Query Store Hints

Query Store is an excellent troubleshooting feature. Among other things, by using Query Store, you can easily identify and even fix performance regression caused by plan changes, you can find whether some query or procedure behave badly over time and in case there were issues, to get a high-level picture, why it happened, and you can learn a lot about your workload’s performance. Although introduced in 2016, I would say that this great tool is still underused, at least not used as much as it deserves.

The most appealing use case for Query Store is its ability to resolve performance issues caused by plan changes through the use of the plan forcing functionality. If Query Store has an old, good plan for a query and a new plan for the same query does not look good, you can easily force the old plan. Although this option should be considered as a mitigation rather than a solution, it is still valuable and can save money in case an issue with the plan occurs during peak hours and heavy workload. In addition, there are cases where changing the Transact-SQL code is not easy, such as when it is compiled as a DLL as part of a service or is unavailable in third-party tools. Even if it is possible to change it, it comes with risks. Therefore, this option is very valuable.

However, there are three problems or limitations with Query Store plan forcing:

• To fix the plan regression for a query, Query Store must have the old plan for that query.
• Not all plan regressions can be fixed with plan forcing, only plan shape changes.
• You cannot improve the performance of an existing query whose plan was never good.

All of these problems are resolved with a new SQL Server 2022 feature - Query Store hints. Query Store hints allow you to tune queries without touching the code! To be fair, this tuning is a bit limited – not all hints are supported – but this is a great and important feature for many 24/7 environments.??

I mentioned earlier that it’s not always easy to make code changes in the production system under heavy workload. Even if you have access to the code, in many companies, due to risk for business, there are strict and conservative procedures for code changes in such situations, requiring approvals and involving many people. And usually, you have no time for it.

Let’s assume that the following simple query is a part of a functionality that is recently rolled out. It is slow, but it was not frequently called until the peak time. At the peak time, its slowness became a problem for the server. Here are execution times:

ALTER DATABASE TSQLTips SET COMPATIBILITY_LEVEL = 160;

GO

SELECT * FROM Orders WHERE Status IN (0, 3);

GO 3

/*

SQL Server Execution Times: CPU time = 2250 ms,?elapsed time = 7023 ms.

SQL Server Execution Times: CPU time = 2171 ms,?elapsed time = 8265 ms.

SQL Server Execution Times: CPU time = 2188 ms,?elapsed time = 8219 ms.

*/

Let's assume further that you cannot modify the code, and this is a serious issue. Unfortunately, this query does not have a good old plan, so you cannot use the plan-forcing feature. And then comes the lovely Query Store hints! I would expect and hope that Microsoft improves both the Cardinality Estimator and the entire database engine in each new version, but I am afraid that there will still be corner cases (and less corner cases) where the old CE does a better job. This query performed better in SQL Server 2012 under the old CE, and now I can use QS hints to address the issue and fix the query without touching the code, with no risk and with very little effort.

EXEC sp_query_store_set_hints

??????@query_id = 1,

??????@value = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';

SELECT * FROM Orders WHERE Status IN (0, 3);

GO 3

/*

SQL Server Execution Times: CPU time = 0 ms,?elapsed time = 1 ms.

SQL Server Execution Times: CPU time = 0 ms,?elapsed time = 0 ms.

SQL Server Execution Times:?CPU time = 0 ms,?elapsed time = 0 ms.

*/

Brilliant! I adore this feature! And of course, I understand that QS hints would not be so important for all of you. If your business lets you have a real maintenance window, or if you do not need to fix all prod issues immediately, or you can change SQL code whenever you want, Query Store hints could be just a small, cool feature and nothing else. Not all admins or persons in charge of performance troubleshooting had to live with limitations I mentioned earlier, but for us in real 24/7 environments, with strict changing policies, it is extremely useful. Query Store hints are my favorite SQL Server 2022 feature!

P.S. There is another small advantage to using Query Store hints compared to applying the hint by changing the code. As soon as you touch the code, the query_id is changed, and you cannot use the Query Store user interface to track the changes. Applying a hint to a query using QS hints does not change the query_id. This is not a big deal as you can still write queries to validate whether the applied hints did the job, but it is handy to be able to use the Tracked Queries report for this purpose.