How to force a query to use index hints with in a query to boost the performance.

Problem: How to force a query to use index hints with in a query to boost the performance.

Solution: Table Hint , Starting SQL Server 2008 or higher.

In some cases, you want to override the SQL server query optimizer plans to choose plans.

There are table hints which can force the query optimizer to choose the specific control you want to consider based on you selection. This can sometimes benefit better query plans and show expected behavior instead of the query analyzer decide what indexes to choose for querying the table.

Caution:
Because the SQL Server query optimizer typically selects the best execution plan for a query, we recommend that hints be used only as a last resort by experienced developers and database administrators.

Syntax:

WITH  ( <table_hint> [ [, ]...n ] ) 

<table_hint> ::=  

[ NOEXPAND ] {  

    INDEX  ( index_value [ ,...n ] )  

  | INDEX =  ( index_value )   

Example:

In this example, let’s see how can use with index in the join between two tables based on the indexes you want to use for the selection criteria.

-- Example 1: using with table hints

USE AdventureWorks2014

GO

SELECT p.FirstName+' '+p.LastName as fullname, ph.PhoneNumber

FROM Person.Person p

WITH (INDEX([IX_Person_LastName_FirstName_MiddleName]))

INNER JOIN Person.PersonPhone ph

WITH (INDEX([IX_PersonPhone_PhoneNumber]))

ON p.BusinessEntityID = ph.BusinessEntityID

GO

--Example 2: Using OPTION clause

USE AdventureWorks2014

GO

SELECT p.FirstName+' '+p.LastName as fullname, ph.PhoneNumber

FROM Person.Person p

INNER JOIN Person.PersonPhone ph

ON p.BusinessEntityID = ph.BusinessEntityID

OPTION (TABLE HINT(ph, INDEX ([IX_PersonPhone_PhoneNumber])),

TABLE HINT(p, INDEX ([IX_Person_LastName_FirstName_MiddleName])))

GO

Results: