In this post, we’ll look at a feature in SQL Server Query Store that was introduced in SQL Server 2022. I absolutely love Query Store, I use it so much I can barely remember what life was like without it.

One of the things that has been great about Query Store since its early days is the way you can force an execution plan for a query. This has saved me a number of times when a query’s performance has suddenly dropped off a cliff, the code hasn’t changed but the data distribution has and everyone is looking at me, the DBA to fix it. I can’t count the number of times I’ve forced the last good plan to resolve the issue. This is only a sticking plaster approach as with most approaches where forcing the optimizer is concerned – the query should then be added to the backlog for resolution but nonetheless this has got me out of a pinch on a number of occasions.

Query Hints

The same is true for query hints. I can think of some times when data has been changed, for example, a cleanup or update script has run causing mass changes in the data which has caused regression to a query that has worked fine for a long time. In these cases, there have been a number of occasions when hints such as USE HINT 'FORCE_LEGACY_CARDINALITY_ESTIMATION', OPTION(RECOMPILE) or compatibility level hints have saved my bacon. However, in such scenarios, there can be some issues in that adding a hint is effectively a code change which isn’t always palatable depending on what is involved.

When I heard about the ability to inject query hints as a database administrator, I (figuratively) leapt with joy as it’s a great new tool in the box which can be quickly deployed to fix unexpected regressions, without the level of work and complication that a code change can bring.

Action

So with that, let’s look at this in action. I am using the Stack Overflow 2010 Database, provided under cc-by-sa 4.0 licence from Stack Exchange Data Dump and running in compatibility level 160 on a SQL Server 2022 instance installed on a VM with 8 cores and 35GB RAM. Note that on SQL Server 2022, Query Store is enabled by default and so I don’t need to explicitly enable it.

Let’s say our application has a catch-all query, like the one in my earlier blog post. The query allows an application user to find Stack Overflow users based on one or more criteria. As explained in that post, the problem here is that SQL Server has to compile an execution plan that is valid for any combination of parameters and in doing so the plan may bring sub-par performance.

Let’s see the query as is (note this is running via sp_executesql to mimic the way the query is executed SQL Server side when running as part of a .NET application) We’re filtering by reputation alone in this case:

EXEC sys.sp_executesql @stmt = N'
SELECT	[Id],
		[DisplayName],
		[Location],
		[Reputation],
		[LastAccessDate]
FROM	dbo.[Users]
WHERE	([Id] = @UserId OR @UserId IS NULL) AND
		([DisplayName] = @DisplayName OR @DisplayName IS NULL) AND
		([Location] = @Location OR @Location IS NULL) AND
		([Reputation] = @Reputation OR @Reputation IS NULL);
', @params = N'@UserId INT = NULL, @DisplayName NVARCHAR(40) = NULL, @Location NVARCHAR(100) = NULL, @Reputation INT = NULL', @Reputation = 1047863;

We also have three covering indexes to support the three predicates, as well as the clustered index on UserId:

CREATE INDEX IX_DisplayName ON dbo.Users
(
	[DisplayName]
)
INCLUDE
(
	[Location],
	[Reputation],
	[LastAccessDate]
);

CREATE INDEX IX_Location ON dbo.Users
(
	[Location]
)
INCLUDE
(
	[DisplayName],
	[Reputation],
	[LastAccessDate]
);

CREATE INDEX IX_Reputation ON dbo.Users
(
	[Reputation]
)
INCLUDE
(
	[DisplayName],
	[Location],
	[LastAccessDate]
);

We can see from the execution plan that we get a scan of the index and only a single record returned – very inefficient.

Our STATISTICS IO output is below:

Table 'Users'. Scan count 1, logical reads 2027, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Adding an OPTION(RECOMPILE) hint will give much better performance as it will recompile the plan once the parameters are known due to parameter embedding optimization (PEO).

However, picture the scenario – all the .NET developers are off for the afternoon and we have this issue. As a DBA, I don’t know where any of the app-side code is, nor would I even want to start delving into it and deploying without the developers present – so adding the OPTION(RECOMPILE) hint in the application layer is not an option.

Now with SQL Server 2022, this is no longer an issue – we can inject it using Query Store.

First, we need to find the query in Query Store as we need its query ID:

SELECT	q.query_id,
		t.query_sql_text,
		CONVERT(XML, p.query_plan),
		p.plan_id,
		h.last_query_hint_failure_reason_desc,
		h.source,
		h.query_hint_text
FROM	sys.query_store_query_text t
		JOIN sys.query_store_query q
			ON q.query_text_id = t.query_text_id
		JOIN sys.query_store_plan p
			ON p.query_id = q.query_id
		LEFT JOIN sys.query_store_query_hints h
			ON h.query_id = q.query_id
WHERE	t.query_sql_text LIKE '%@Reputation OR @Reputation%';

The results are below:

I can see this is query_id 5, so to add the hint, we run:

EXEC sys.sp_query_store_set_hints @query_id = 5, @query_hints = N'OPTION(RECOMPILE)';

Now if we execute our catch-all query again, we get the benefit that OPTION(RECOMPILE) gives us and we see a seek of the index:

We also get much better runtime stats:

Table 'Users'. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

With that, our users are happy and the DBA saves the day!

Conclusion

We looked at how we can use the Query Store 2022 Hints feature to inject a query hint to a query that was causing us problems without the need for a code change.

References / Further Reading

Aaron Bertrand – BackToBasics: An Updated Kitchen Sink Example

DualCoreDBA – Curse of the Catch-All query

Erik Darling – Indexing SQL Server Queries For Performance: Fixing Unpredictable Search Queries

Gail Shaw – Catch-All Queries

Gail Shaw – Revisiting Catch-All Queries

Microsoft – Query Store Hints

Posted in ,