Search

Thursday, September 15, 2011

SQL Server Denali: FORCESEEK and FORCESCAN

FORCESEEK and FORCESCAN are the new functions in SQL Server Denali. 
Before Denali, FORCESEEK hint exists and enhanced in Denali CTP3. FORCESCAN table hint is new addition. 
FORCESEEK: It forces optimizer to use index seek only. Sometimes optimizer does not user proper plan and use index scan which cause high reads on the system. We can use forceseek here to force otpimizer to use index seek which can give better performance.
FORCESCAN: Specifies that the query optimizer use only an index scan operation as the access path to the referenced table or view. The FORCESCAN hint can be useful for queries in which the optimizer underestimates the number of affected rows and chooses a seek operation rather than a scan operation. Here are few examples of queries with hints of FORCESEEK and FORCESCAN.


-- FORCESEEK
SELECT  ObjectName FROM ObjectItems WITH (FORCESEEK) WHERE ObjectType = 'SQL_STORED_PROCEDURE'


-- FORCESEEK and specified index
SELECT ObjectName FROM ObjectItems WITH (FORCESEEK,INDEX (IX_ObjectType_CreateDate)) WHERE ObjectType = 'SQL_STORED_PROCEDURE'


-- FORCESEEK and specified index using at least the specified index columns
SELECT ObjectName FROM ObjectItems WITH (FORCESEEK(IX_ObjectType_CreateDate(ObjectType,CreateDate))) WHERE ObjectType = 'SQL_STORED_PROCEDURE'


-- FORCESCAN
SELECT  ObjectName FROM ObjectItems WITH (FORCESCAN) WHERE ObjectType = 'SQL_STORED_PROCEDURE'


-- FORCESCAN and specified index
SELECT  ObjectName FROM ObjectItems WITH (FORCESCAN, INDEX(IX_ObjectId)) WHERE ObjectType = 'SQL_STORED_PROCEDURE'

No comments:

Post a Comment