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.aspxSpecial 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.
