Wednesday, July 13, 2011

SQL Server 2008 Query Hints

SQL Server 2008 query hints override any execution plan the query optimizer might select for a
query. Because the query optimizer typically selects the best execution plan for a query, you
should use the query hints only as a last resort by experienced developers and database
administrators.
In this article, you can find some tips to use SQL Server 2008 query hints.

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 2008 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.

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 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 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)

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 UNION hint if you need that 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 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)

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)

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 2008 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.
This example shows how you can use the ROBUST PLAN hint:
SELECT a.notes FROM titles a JOIN titleauthor b
ON a.title_id = b.title_id JOIN authors c
ON b.au_id = c.au_id
WHERE au_fname = 'Johnson'
OPTION (ROBUST PLAN)

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)

Use the MERGE UNION hint if you need that 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)

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.

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.

No comments:

Post a Comment