Optimizer Hints

Optimizer hints have a somewhat unfortunate name. They are not just hints; they are actually directives for the query execution. You can use them with the SELECT statement and the data modification language statements. There are three kinds of hints: table hints, query hints, and join hints.

————————————–

IMPORTANT: Use Optimizer Hints Carefully When you use a hint, you change the query. SQL Server must execute the query or part of the query always in the same way. The query could be part of an application, so it could be difficult to change it. The data distribution might change over time, and although the hint might have improved the performance in the past, it might harm the performance over time. Use all other means, such as creating appropriate indexes, creating and updating statistics, and even using plan guides before moving to the hints. Use hints as the last resort, and after you use them, validate whether they are still useful from time to time.

——————————————

You specify query hints as part of the OPTION clause of the SELECT, INSERT, UPDATE, DELETE,and MERGE statements. You cannot use query hints in subqueries, only in the outermost query. If multiple queries are involved in the UNION operation, you can specify the OPTION clause only after the last query. You can specify query hints in an INSERT statement except when a SELECT clause is used inside the statement.

More Info: Query Hints Details It is out of the scope of this book to go into details for each of the hints. However, you will get familiar with some of them and how to use them through sample code and through the practice for this lesson.

—————————————————————

The following two queries return the same aggregated rowset; the first one allows SQL Server to decide which aggregation technique to use—SQL Server decides to use the hash aggregation—whereas the second one forces the stream aggregation. — Hash match aggregate SELECT qty, COUNT(*) AS num FROM Sales.OrderDetails GROUP BY qty; — Forcing stream aggregate SELECT qty, COUNT(*) AS num FROM Sales.OrderDetails GROUP BY qty OPTION (ORDER GROUP);

Figure 17-21 shows the execution plan for both queries together because they were executed in a batch.

Figure 17-21 The plan for a hash aggregation and a forced stream aggregation. In the second query, SQL Server used the Stream Aggregate operator. However, because this operator expects ordered input, SQL Server also added the Sort operator to the plan. Although the stream aggregation might be faster than the hash aggregation, the second query might be slower because of the additional sort operation. You can give SQL Server a hint for a single table in a query. Table hints influence locking and the access method for a single table or view only. You can use the table hints in the FROM clause, and introduce them by using the WITH keyword. SQL Server supports the following table hints: ■■ NOEXPAND ■■ INDEX ( index_value [ ,…n ] ) | INDEX = ( index_value ) ■■ FORCESEEK [ ( index_value ( index_column_name [ ,… ] ) ) ] ■■ FORCESCAN ■■ FORCESEEK ■■ KEEPIDENTITY ■■ KEEPDEFAULTS ■■ IGNORE_CONSTRAINTS ■■ IGNORE_TRIGGERS ■■ HOLDLOCK ■■ NOLOCK ■■ NOWAIT ■■ PAGLOCK

Maybe the most popular optimizer hint is the table hint that forces a specific index usage. The following two queries show an example of leaving it to SQL Server to choose the access method and of forcing usage of a nonclustered index. — Clustered index scan SELECT orderid, productid, qty FROM Sales.OrderDetails WHERE productid BETWEEN 10 AND 30 ORDER BY productid; — Forcing a nonclustered index usage SELECT orderid, productid, qty FROM Sales.OrderDetails WITH (INDEX(idx_nc_productid)) WHERE productid BETWEEN 10 AND 30 ORDER BY productid; Figure 17-22 shows the execution plan for this batch. SQL Server 2012 also supports the following join hints in the FROM clause: ■■ LOOP ■■ HASH ■■ MERGE ■■ REMOTE

Figure 17-22 A plan for a scan and a forced nonclustered index seek.

The following two queries return the same result set again. For the first query, the selection of the join algorithm is left to SQL Server—SQL Server decides to use a nested loops

About Techveze.com

Techveze is a test-preparation site for the IT certifications. It offers complete exam knowledge and a customized practice ground for those who are preparing for the certifications and want to rapid progress in preparation. Our Web site holds lots of questions with answers for you to practice, and progress report customized to serve you with your exam readiness. You can increase your scores through practice.