26Nov 2018 by Grant Fritchey 5 Comments

Forcing a Plan That Has a Plan Guide

SQL Server 2016, SQL Server 2017, T-SQL
The question that came up during a recent class I was teaching was: What if you have a plan guide to get the plan you want, but then decide, instead of using the plan guide, you'll just force the plan? Ummmm…. No idea. Let's test it. First, Create a Plan Guide I have a couple of queries I use to teach about how statistics affects plan choice, so we'll use that here. I'm going to also define and create a plan guide that makes this plan use a small row count for all queries against it: CREATE OR ALTER PROC dbo.AddressByCity @City NVARCHAR(30) AS SELECT a.AddressID, a.AddressLine1, a.AddressLine2, a.City, sp.Name AS StateProvinceName, a.PostalCode FROM Person.Address AS a JOIN Person.StateProvince AS sp ON a.StateProvinceID = sp.StateProvinceID WHERE a.City = @City; GO…
Read More
31May 2016 by Grant Fritchey 3 Comments

Precedence Goes to Query Store or Plan Guide?

SQL Server 2016
While presenting at SQLDay in Wroclaw, Poland, on the Query Store, I was asked a pretty simple question, which takes precedence, the Query Store or a Plan Guide? One of my favorite answers to questions is "I don't know" because it gives me the opportunity to learn. Let's figure this one out together. I'll post the code to recreate this experiment within AdventureWorks at the end of the article. I'm doing this because the code for forcing execution plans using Plan Guides can be pretty doggone long (you may need to generate your own XML from a plan on your own system, fair warning). I have a stored procedure that I use frequently to demonstrate parameter sniffing and bad parameter sniffing, AddressByCity (listed below). This query when passed the value…
Read More