Today is Friday and tomorrow is the weekend. I just thought, let us explore something new but additionally give you all a quick puzzle to explore about index hints.
SQL Server Denali has new Query Hint – FORCESCAN. In earlier version of SQL Server we already have Query Hint FORCESEEK but now the counter part also exists. The quick understanding is there will be cases when FORCESEEK or FORCESCAN will be helpful and improve the performance of the query.
Now here is the quick contest-
1) Write a Query where using FORCESCAN hint which will improve the performance of the query.
2) Write a Query where using FORCESEEK hint which will improve the performance of the query.
The winner will get my printed book SQL Server Wait Stats [Amazon] printed book FREE (shipped anywhere in the world).
Index Hints: FORCESEEK, FORCESCAN
The FORCESEEK table hint forces the query optimizer to use only an index seek operation. Table hints override the default behavior of the query optimizer for the duration of the data manipulation language (DML) statement.
Update: The puzzle is over at this point of time, but you are welcome to submit your answers.
Please leave your notes in the comments section of this blog.
Reference: Pinal Dave (https://blog.sqlauthority.com)
7 Comments. Leave new
1)SELECT ObjectName FROM ObjectItems WITH (FORCESEEK)
WHERE ObjectType = ‘SQL_STORED_PROCEDURE’
2)SELECT ObjectName FROM ObjectItems WITH (FORCESCAN)
WHERE ObjectType = ‘SQL_STORED_PROCEDURE’
1.
FORSCAN
SELECT ObjName FROM Objitems
WITH (FORCESCAN) WHERE ObjectType = ‘SQL_STORED_PROCEDURE’
FORSCAN and specified index
SELECT ObjName FROM ObjItems
WITH (FORCESCAN, INDEX(IX_ObjectId))
WHERE ObjectType = ‘SQL_STORED_PROCEDURE’
2.
FORCESEEK
SELECT ObjName FROM ObjItems
WITH (FORCESEEK) WHERE ObjectType = ‘SQL_STORED_PROCEDURE’
FORCESEEK and specified index
SELECT ObjName FROM ObjItems
WITH (FORCESEEK,INDEX (IX_ObjectType_CreateDate)) WHERE ObjectType = ‘SQL_STORED_PROCEDURE’
FORCESEEK and specified index using at least the specified index columns
SELECT ObjName FROM ObjItems
WITH (FORCESEEK(IX_ObjectType_CreateDate(ObjectType,CreateDate))) WHERE ObjectType = ‘SQL_STORED_PROCEDURE’
ForceScan
++++++++++++
Select Emp.Name, Emp.Address, EmpPay.Salary
From EmplyeeMaster Emp
INNER JOIN EmployeePay EmpPay
WITH (FORCESCAN)
ON Emp.[ID] = EmpPay.[ID]
Where Emp.[Salary] > 100000
ForceSeek
+++++++++++
Select Emp.Name, Emp.Address, EmpPay.Salary
From EmplyeeMaster Emp
INNER JOIN EmployeePay EmpPay
WITH (FORCESEEK)
ON Emp.[ID] = EmpPay.[ID]
Where Emp.[Salary] > 100000
hello sir,
i have created a website in asp.net with mssql. and there is a .cs file is generated automatic in temp file in local system,when we are going to upload this website ,this website is not getting that path?is there any idea to generate this file into our web folder
Hi pinal,
I have not much idea about FORCESEEK & FORCESCAN.
I searched and got the below information:
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.
— FORCESEEK and specified index
SELECT ObjectName FROM ObjectItems WITH (FORCESEEK,INDEX (IX_ObjectType_CreateDate)) WHERE ObjectType = ‘SQL_STORED_PROCEDURE’
— FORCESCAN and specified index
SELECT ObjectName FROM ObjectItems WITH (FORCESCAN, INDEX(IX_ObjectId)) WHERE ObjectType = ‘SQL_STORED_PROCEDURE’
SELECT *
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID
WHERE h.TotalDue > 100
AND (d.OrderQty > 5 OR d.LineTotal 100
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);
Checking execution plan A clustered index seek operation is used to access the data in the Sales.SalesOrderDetail table.