Some tips for using query hints in SQL Server 2016
If your query is very slow, try the following before using query hints:
– rebuild indexes used in the query (or defragment them using DBCC INDEXDEFRAG),
– update statistics on the relevant tables,
– consider creating new indexes for this query,
– rewrite your query to provide better execution plan.
Use the query hints only when the actions above do not provide good performance.
You can use the KEEP PLAN query hint if you want to reduce the estimated
recompile threshold for a query.
For example, using this hint reduces the number of recompiles when multiple
table updates occur.
This example shows how you can use the KEEP PLAN hint:
SELECT * FROM authors OPTION (KEEP PLAN)
Consider using a new query hint NO_PERFORMANCE_SPOOL.
By using this hint you can prevent a spool operator from being added to query
plans. This can improve performance when many concurrent queries are running
with spool operations.
Use the FAST n query hint if you need to quickly return only n rows.
You can quickly return n rows and can work with them, when the query continues
execution and produces its full result set.
This is the example to quickly return 5 rows from the titles and titleauthor
tables:
SELECT * FROM titles a JOIN titleauthor b
ON a.title_id = b.title_id OPTION (FAST 5)
Consider using a new query hint MAX_GRANT_PERCENT.
This hint is uses to specify the maximum memory grant size in PERCENT. The
query is guaranteed not to exceed this limit. The actual limit can be lower
if the resource governor setting is lower than this. Valid values are between
0.0 and 100.0.
Use the MERGE UNION hint if you need the all UNION operations will be
performed by merging UNION sets.
Keep in mind, that only the last query involving a UNION operator can have
the OPTION clause.
This example shows how you can use the MERGE UNION hint:
SELECT ‘warnings’ as ‘Severity’, COUNT(*) FROM sys.messages WHERE severity < 11
UNION
SELECT ‘user errors’ as ‘Severity’, COUNT(*) FROM sys.messages WHERE severity BETWEEN 11 AND 16
UNION
SELECT ‘software/hardware errors’ as ‘Severity’, COUNT(*)
FROM sys.messages WHERE severity BETWEEN 17 AND 25 OPTION (MERGE UNION)
If you want to set any hints for your query, do not remember to test the
query with hint and without the hint and compare results.
Because SQL Server 2016 query optimizer usually proposes the best execution
plan, you should test the query with hint and use this hint only when it
provides the best result. If the query hint provides the better execution
plan not stable, avoid using this hint and rely on the SQL Server query optimizer.
Consider using the IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX query hint.
The IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX query hint was first
introduced in SQL Server 2012. This query hint prevents the query from using a
nonclustered xVelocity memory optimized columnstore index.
Note. If the query contains an index hint to use a columnstore index and the
query hint to avoid use of the columnstore index, the hints are in conflict
and the query returns an error.
If you need that all UNION operations are performed by concatenation UNION
sets, use the CONCAT UNION hint.
Keep in mind, that only the last query involving a UNION operator can have
the OPTION clause.
This example shows how you can use the CONCAT UNION hint:
SELECT ‘warnings’ as ‘Severity’, COUNT(*) FROM sys.messages WHERE severity < 11
UNION
SELECT ‘user errors’ as ‘Severity’, COUNT(*) FROM sys.messages WHERE severity BETWEEN 11 AND 16
UNION
SELECT ‘software/hardware errors’ as ‘Severity’, COUNT(*)
FROM sys.messages WHERE severity BETWEEN 17 AND 25 OPTION (CONCAT UNION)
Use the HASH GROUP hint if you need that aggregation described in the GROUP BY,
DISTINCT, or COMPUTE clause of the query should use hashing.
This example shows how you can use the HASH GROUP hint:
SELECT title_id, count(*) FROM titleauthor
GROUP BY title_id OPTION (HASH GROUP)
Consider using the RECOMPILE query hint to discard the plan generated for the
query after it executes.
When the RECOMPILE hint is used the query plan is generated the next time the
same query is executed. Microsoft recommends using this hint for queries with
variable values that vary widely each time they are compiled and executed.
Use the HASH UNION hint if you need the all UNION operations will be performed
by hashing UNION sets.
Keep in mind, that only the last query involving a UNION operator can have the
OPTION clause.
This example shows how you can use the HASH UNION hint:
SELECT ‘warnings’ as ‘Severity’, COUNT(*) FROM sys.messages WHERE severity < 11
UNION
SELECT ‘user errors’ as ‘Severity’, COUNT(*) FROM sys.messages WHERE severity BETWEEN 11 AND 16
UNION
SELECT ‘software/hardware errors’ as ‘Severity’, COUNT(*)
FROM sys.messages WHERE severity BETWEEN 17 AND 25 OPTION (HASH UNION)
Consider using the PARAMETERIZATION query hint to specify the parameterization
rules that the query optimizer applies to the query when it is compiled.
The PARAMETERIZATION hint has two parameters SIMPLE and FORCED. When the
SIMPLE parameter is used with the PARAMETERIZATION hint, the query optimizer
attempts to use simple parameterization. When the FORCED parameter is used,
the query optimizer attempts to use forced parameterization. The PARAMETERIZATION
query hint is used to override the current setting of the PARAMETERIZATION
database option.
Note. The PARAMETERIZATION query hint cannot be specified directly within a
query, it can only be specified inside a plan guide.
Use the ORDER GROUP hint if you need that aggregation described in the
GROUP BY, DISTINCT, or COMPUTE clause of the query should use ordering.
This example shows how you can use the ORDER GROUP hint:
SELECT au_id, count(*) FROM titleauthor
GROUP BY au_id OPTION (ORDER GROUP)
If you want to specify that all join operations should be performed by merge
join, you can use the MERGE JOIN hint.
If you specify several join hints, SQL Server query optimizer selects the
least expensive join strategy.
This example shows how you can use the MERGE JOIN hint:
SELECT * FROM titles a JOIN titleauthor b
ON a.title_id = b.title_id OPTION (MERGE JOIN)
Use the KEEPFIXED PLAN query hint if you want to ensure that a query
will be recompiled only if the schema of the underlying tables is changed
or sp_recompile is executed against the underlying tables.
If you use this hint, the query will not be recompiled due to changes in
statistics or to the indexed columns.
This example shows how you can use the KEEPFIXED PLAN hint:
SELECT * FROM authors OPTION (KEEPFIXED PLAN)
If you want to specify that all join operations should be performed by hash
join, you can use the HASH JOIN hint.
If you specify several join hints, SQL Server query optimizer selects the
least expensive join strategy.
This example shows how you can use the HASH JOIN hint:
SELECT * FROM titles a JOIN titleauthor b
ON a.title_id = b.title_id OPTION (HASH JOIN)
Use the ROBUST PLAN query hint if you want to force the query optimizer
to attempt an execution plan that works for the maximum potential row size.
By default, SQL Server 2016 query optimizer may use the intermediate tables
to execute the query and the rows may be so wide that the particular operator
cannot process the row. In this case, you can encounter an error message
about exceeding the row size limit. To avoid such errors, consider using
the ROBUST PLAN query hint.
Note. Because the ROBUST PLAN query hint may hurt performance, use this hint
only if you have received an error message about exceeding the row size limit.
If you want to specify that all join operations should be performed by nested
loop join, you can use the LOOP JOIN hint.
If you specify several join hints, SQL Server query optimizer selects the
least expensive join strategy.
This example shows how you can use the LOOP JOIN hint:
SELECT * FROM titles a JOIN titleauthor b
ON a.title_id = b.title_id OPTION (LOOP JOIN)
Consider using a new query hint MIN_GRANT_PERCENT.
This hint is uses to specify the minimum memory grant size in PERCENT. The
query is guaranteed to get MAX (required memory, min grant) because at least
required memory is needed to start a query. Valid values are between 0.0 and 100.0.
Use the MAXDOP n query hint if you want to override the ‘max degree of
parallelism’ configuration option for this query.
The ‘max degree of parallelism’ option limits the number of processors to use
in parallel plan execution. This option by default is 0, which causes to use
the actual number of available CPUs.
This example sets the ‘max degree of parallelism’ option to 0, so all CPUs
will be used to run the query:
SELECT * FROM titles a JOIN titleauthor b
ON a.title_id = b.title_id OPTION (MAXDOP 0)
Consider using the OPTIMIZE FOR query hint to use a particular value for a
local variable when the query is compiled and optimized.
The OPTIMIZE FOR hint instructs the query optimizer to use a particular value
for a local variable only during query optimization, and not during query execution.