Some Thoughts On Query Store Hints In SQL Server 2022

Young And Old

Query hints seem to get treated like prescription drugs. You hear all the problems they can solve, but then you get a long list of problems they can cause.

“Your cholesterol will be lower, but you might bleed to death from your eyes.”

I use query hints all the time to show people what different (and often better) query plans would look like, and why they weren’t chosen.

Sometimes it’s cardinality estimation, sometimes it’s costing, sometimes there was an optimization timeout, and other times…

Msg 8622, Level 16, State 1, Line 20
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

Because, you know, you can’t always get what you want.

Problem Statement

When you’re dealing with untouchable vendor code full of mistakes, ORM queries that God has turned away from, and other queries that for some reason can’t be tinkered with, we used to not have a lot of options.

In SQL Server 2022, Query Store gains a new super power: you can add hints to queries without intercepting the code in some other manner.

This is going to be a game changer in a lot of cases, because you can hint all sorts of useful changes to queries that would otherwise be stuck forever in their current hell.

Of course, not everything is supported. How could it all be? That would be insane.

According to the docs, here’s what’s supported currently:

These query hints are supported as Query Store hints:

{ HASH | ORDER } GROUP   
  | { CONCAT | HASH | MERGE } UNION   
  | { LOOP | MERGE | HASH } JOIN   
  | EXPAND VIEWS   
  | FAST number_rows   
  | FORCE ORDER   
  | IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX  
  | KEEP PLAN   
  | KEEPFIXED PLAN  
  | MAX_GRANT_PERCENT = percent  
  | MIN_GRANT_PERCENT = percent  
  | MAXDOP number_of_processors   
  | NO_PERFORMANCE_SPOOL   
  | OPTIMIZE FOR UNKNOWN  
  | PARAMETERIZATION { SIMPLE | FORCED }   
  | RECOMPILE  
  | ROBUST PLAN   
  | USE HINT   ( '<hint_name>' [ , ...n ] )

And here’s what’s not supported:

The following query hints are currently unsupported:

OPTIMIZE FOR(@var = val)
MAXRECURSION
USE PLAN (instead, consider Query Store’s original plan forcing capability, sp_query_store_force_plan).
DISABLE_DEFERRED_COMPILATION_TV
DISABLE_TSQL_SCALAR_UDF_INLINING
Table hints (for example, FORCESEEK, READUNCOMMITTED, INDEX)

This is where things get… tough. There aren’t any super-important query hints missing, but not being able to use ANY table hints is bad news for a number of reasons.

Duck Hint

Included in the potential table hints are all these delights:

WITH ( <table_hint> [ [, ]…n ] )

<table_hint> ::=

{ NOEXPAND [ , INDEX ( <index_value> [ ,…n ] ) | INDEX = ( <index_value> ) ]

| INDEX ( <index_value> [ ,…n ] ) | INDEX = ( <index_value> )

| FORCESEEK [ ( <index_value> ( <index_column_name> [,… ] ) ) ]

| FORCESCAN

| FORCESEEK

| HOLDLOCK

| NOLOCK

| NOWAIT

| PAGLOCK

| READCOMMITTED

| READCOMMITTEDLOCK

| READPAST

| READUNCOMMITTED

| REPEATABLEREAD

| ROWLOCK

| SERIALIZABLE

| SNAPSHOT

| SPATIAL_WINDOW_MAX_CELLS = <integer_value>

| TABLOCK

| TABLOCKX

| UPDLOCK

| XLOCK

}

No index hints, no locking hints, no isolation level hints, no access method hints, and… no NOEXPAND hint 😭

The prior being made even more aggravating because EXPAND VIEWS is a query hint.

No one ever expands indexed views.

Gridlock

This feature has me pretty excited for SQL Server 2022. In particular for Entity Framework queries, I can see myself using:

FORCE ORDER
NO_PERFORMANCE_SPOOL
RECOMPILE

Maybe even all together. The more the merrier! Like beans.

I do hope that at some point there is a workaround for some of the table hints getting used, but in 4 years when folks finally start adopting this newfangled version, I’ll have a grand time fixing problems that used to be out of my reach.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.