SQL Server plan guide and execution plan

Hi Geeks,

Think of a situation when you want to apply query hint to a query that is part of application code but development team is unwilling to touch application code. Does this mean there is no way other than changes to code, the answer is NO. We can force a query to use a specific plan with the help of SQL Server plan guide.

We can use sp_create_plan_guide stored procedure to create a plan guide. If you want to drop or disable a plan guide sp_control_plan_guide can help you with. All plan guide details can be listed from sys.plan_guides catalog view.

Let’s see an example of plan guide now.

--USE [AdventureWorks2012]

--Step 1 : Create a stored procedure
CREATE PROC sp_GetEmail
AS
SELECT PR.FirstName,PR.LastName, EM.EmailAddress
FROM Person.Person PR
JOIN Person.EmailAddress EM
ON PR.BusinessEntityID = EM.BusinessEntityID

--Step 2 : Execute p_GetEmail
EXEC dbo.sp_GetEmail

Now we are going to create a plan guide and force our query statement to use LOOP JOIN.

USE [AdventureWorks2012]

--Step 1 : Creating a plan guide for sp_GetEmail
EXEC sp_create_plan_guide
	@name = N'GetEmail',
	@stmt = N'SELECT PR.FirstName,PR.LastName, EM.EmailAddress
			 FROM Person.Person PR
			 JOIN Person.EmailAddress EM
			 ON PR.BusinessEntityID = EM.BusinessEntityID',
	@type=N'OBJECT',
	@module_or_batch = N'sp_GetEmail',
	@params = NULL,
	@hints = N'OPTION (LOOP JOIN)'

--Step 2 : Execute p_GetEmail
EXEC dbo.sp_GetEmail

From the above figure, same stored procedure now returns Nested Loop (read here Part1, Part2) join instead of Hash Join in the execution plan. We are now safe to drop our plan guide and stored procedure.

USE [AdventureWorks2012]

EXEC sp_control_plan_guide N'DROP','GetEmail'

DROP PROC dbo.sp_GetEmail

You can find index to the execution plan series here and click on One operator a day to visit exclusive page for this series.

Signing off for today, Happy Learning!

Regards,

Kanchan

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook | Follow me on Twitter | Follow me on FaceBook