SQL Server 2005 Query Hints
SQL Server 2005 Query Hints
By : Kasim Wirama, MCDBA, MVP SQL Server
In general, without hints SQL Server performs best, but in some cases default execution plan is not so appropriate, if you are sure that uses of query hints will have control over query optimizer to produce proper execution plan, uses of hints could be considered. Below I give you some information about hints in from previous version of SQL Server 2005 to SQL Server 2005 version.
If you need to force execution with predictable execution strategies and choose to navigational behavior of query optimizer you can use SET FORCEPLAN ON. This example below gives clue to query optimizer to navigate from customer to orders based on their order.
SET FORCEPLAN ON
Select ordered
From customer as c, orders as o
Where c.customerid = o.orderid and c.lastname=’smith’;
If you don’t want your query block to other executing transaction for higher consistency with lower concurrency such as REPEATABLE READ or SERIALIZABLE isolation level, you can issue NOLOCK with your query like sample below:
SELECT CustomerName FROM Customer WITH (NOLOCK) where CustomerID = ‘ALFKI’;
If you want to force particular index usage on a query you can issue hint WITH (INDEX = <index name>), for example :
SELECT CustomerName FROM Customer WITH (INDEX = IX_CustomerID) where CustomerID = ‘ALFKI’;
Sometimes, you want to force internal join operation (HASH, MERGE, LOOP) like this example below :
select o.customerid,companyname
from orders as o inner MERGE join customers as c
on o.customerid = c.customerid
select o.customerid,companyname
from orders as o inner HASH join customers as c
on o.customerid = c.customerid
select o.customerid,companyname
from orders as o inner LOOP join customers as c
on o.customerid = c.customerid
sometimes, you need to force group operation with hash aggregation, with note your tempdb database follows best practice, compare this execution below :
select city, count(*)
from customers
group by city
OPTION (HASH GROUP)
select city, count(*)
from customers
group by city
first query uses hash match instead of Stream Aggregate operator of second query, with using OPTION (HASH GROUP) hints.
If you need to force order of execution based on order in query, use hints OPTION (FORCE ORDER) as sample shown below :
select o.customerid,companyname, o.employeeid,lastname
from customers as c
inner join orders as o
on c.customerid = o.customerid
inner join employees as e
on o.employeeid = e.employeeid
OPTION (FORCE ORDER)
if you want to limit parallelism to produce only serial execution plan, issue hint OPTION (MAXDOP 1), for example
select customerid, count(*)
from orders
group by customerid
OPTION (MAXDOP 1)
if your application just really need few rows from a bunch of rows, you can instruct query optimizer to generate plan based on these few rows, although you will get entire results, the results still do not affected with hints OPTION (FAST n).
select *
from orders
OPTION (FAST 2)
if you do not reuse query plan issue hints OPTION (RECOMPILE) on your query, for example
select *
from orders
where orderdate between ‘20060101’ and ‘20060128’
OPTION (RECOMPILE)
if you want your query give optimal balance upon nonuniform data distribution, you can issue hints OPTION (OPTIMIZE FOR (<parameter name> <operator> <value>)), for example :
select o.customerid, companyname
from customers as c join orders as o
on c.customerid =o.customerid
where c.city = @city
OPTION (OPTIMIZE FOR (@city = ‘Jakarta’));