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

SPExPlan

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

SPExPlanWithGuides

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

Avatar

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

Leave a Reply

Your email address will not be published. Required fields are marked *