Database Reference
In-Depth Information
SQL Server 2008 introduced a new feature called
FORCESEEK
table hint that will force seek
over a scan during the query execution. Not all the time, query optimizer may choose an
effective plan. Also, if the data platform is highly volatile, it requires the table statistics to be
updated on a regular basis, otherwise a bad plan is generated during the frequently running
query execution. The best place to provide table hints when the query intent to perform a
single-time lookup with a specific value on a large table with more than a million rows, and by
default the optimizer will scan the entire index before returning the value. In such cases, the
disk IO costs from that index scan can prove costly, causing further slow response. In such
situations, ensure that we manage to execute updated statistics on that table, then consider
using the new table hint
FORCESEEK
. In this recipe, we will go through the demonstration of
implementing table hints that will help the query performance tuning.
How to do it...
As the table hint is used during the duration of the DML statement's execution, the process is
to use the TSQL statement. The following steps are required in implementing table hints for
advanced query performance tuning:
1. In this recipe, we will use the
Production.TransactionHistoryArchive
table
from the
AdventureWorks2008R2
database.
2. The table consist of one unique clustered index,
PK_TransactionHistoryArchive_
TransactionID
, on the
TransactionID
column, and two nonclustered indexes. The
two indexes are a non-unique
IX_TransactionHistoryArchive_ProductID
on
the
ProductID
column and non-unique
IX_TransactionHistoryArchive_Upd_
ActualCost_ProductId
on the
ActualCost
and
ProductId
columns.
3. As a reporting requirement, we need to obtain the data pertaining to a specific
product and product modified date.
4. On SQL Server Management Studio (SSMS) from Query, choose Display Estimated
Execution Plan for the following TSQL statements:
SET STATISTICS IO ON
GO
USE Adventureworks2008R2
GO
SELECT TransactionID, ProductID,TransactionDate,ModifiedDate FROM
Production.TransactionHistoryArchive
WHERE ProductID >710 AND
ModifiedDate >= '2007-01-01'
GO
Search WWH ::
Custom Search