Using Hints and Plan Guides

In this post we would see examples for using hints and plan guides

DBCC FREEPROCCACHE - Removes all plans in cache

--STEP 1
DROP TABLE TestTable
DROP TABLE TestTable2

--STEP 2
CREATE Table TestTable (
a int NOT NULL PRIMARY KEY,
name varchar(50))

CREATE Table TestTable2 (
a int NOT NULL PRIMARY KEY,
name varchar(50))

--STEP 3
Declare @i int
set @i = 1
While 1 =1
begin
insert into TestTable(a, name)
select @i, convert(varchar(2),@i)+ 'name'
insert into TestTable2(a, name)
select @i+5, convert(varchar(2),@i)+ 'name'
set @i = @i+1
if @i > 100
break;
end

sp_help TestTable
--PK__TestTable__627A25C7 clustered, unique, primary key located on PRIMARY

--STEP 4
CREATE INDEX IX_Name ON TestTable(name)

--Index Hint
We can specify the query to use a specific index as mentioned below.

SELECT a,name FROM TestTable WITH (INDEX(IX_Name)) WHERE a = 10
SELECT a,name FROM TestTable WITH (INDEX(PK__TestTable__627A25C7)) WHERE a = 10

--JOINT HINT
If I want my query to use a specific join you can provide it like mentioned below.

SELECT TT1.a,TT.name FROM TestTable TT JOIN TestTable2 TT1
ON TT.a = TT1.a
Option(MERGE JOIN)

--STEP 5
I need my query to stick to a plan, you can create a plan guide for your query as provided in below example.

--CREATE A PLAN GUIDE For the Same
sp_create_plan_guide @name = N'PlanGuidetest',
@stmt = N'SELECT TT1.a,TT.name FROM TestTable TT JOIN TestTable2 TT1
ON TT.a = TT1.a',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (Loop JOIN)'
GO

SELECT * FROM sys.plan_guides
GO

SELECT TT1.a,TT.name FROM TestTable TT JOIN TestTable2 TT1
ON TT.a = TT1.a

--Drop the plan guide.
EXEC sp_control_plan_guide N'DROP', N'PlanGuidetest'

References –
http://blogs.technet.com/andrew/archive/2008/11/14/sql-server-2008-plan-guides.aspx

Special thanks to balmukund for correcting me plan guide is case-sensitive, Even a little space adjusted in the query, it would not match with plan guide.