SQL Server

Some tips for using query hints in SQL Server 2016

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.