Feeds:
Posts
Comments

Archive for the ‘Query Performance’ Category

SQL Server new versions always come up with the new performance optimization techniques, enhancements and additions. FORCESEEK table hint enhancement is also one of the new enhancements that came up in SQL Server 2012.

Let me create an index on Purchasing. [PurchaseOrderDetail] table to demonstrate this enhancement.

USE [AdventureWorks2012]
GO
CREATE NONCLUSTERED INDEX [IX_PurchaseOrderDetail_PurchaseOrderID]
ON Purchasing.[PurchaseOrderDetail]
(
[PurchaseOrderID] ASC
) WITH (DROP_EXISTING = OFF) ON [PRIMARY]

FORCESEEK in earier version of SQL :
In the ealier version of SQL Server, FORCESEEK table hint needed only an index on the table and it could optimize your query. But sometimes you may have more than one index and you cannot define those indexes anywhere in the syntax of forceseek (Limitation). Let me script it to elaborate the same.

---This query will work on SQL Server 2008 and above.
USE [AdventureWorks2012]
GO
Select * from Purchasing.PurchaseOrderHeader as h
Inner join Purchasing.PurchaseOrderDetail as d with (forceseek)
On h.PurchaseOrderID =d.PurchaseOrderID
Where h.TotalDue >50

FORCESEEK in SQL Server 2012 :
In SQL Server 2012, you can define index name along with the column in forceseek table hint to further optimize your query. But you can also use the above syntax  and it will not break your code.

Let me explain it with examples :

Example 1 : (Define the index name in forceseek)

---This query will work on SQL Server 2012 and above.
USE [AdventureWorks2012]
GO
Select * from Purchasing.PurchaseOrderHeader as h
Inner join Purchasing.PurchaseOrderDetail as d with
(forceseek,INDEX(IX_PurchaseOrderDetail_PurchaseOrderID))
On h.PurchaseOrderID =d.PurchaseOrderID
Where h.TotalDue >50

Example 2 : (Define the index name along with the column name in forceseek)

---This query will work on SQL Server 2012 and above.
USE [AdventureWorks2012]
GO
Select * from Purchasing.PurchaseOrderHeader as h
Inner join Purchasing.PurchaseOrderDetail as d
with (forceseek(IX_PurchaseOrderDetail_PurchaseOrderID(PurchaseOrderID)))
On h.PurchaseOrderID =d.PurchaseOrderID
Where h.TotalDue >50

Conclusion:

In SQL Server 2012, FORCESEEK  came up with the very nice enhancement that you can define index name along with the column in FORCESEEK, to have further control on query optimization. However, remember this is the last option we should choose at the time of optimization. Finally, this is only recommended for experienced programmers.

Reference : MSDN

Read Full Post »