When I complete a server assessment there are a number of areas I look at. These areas include security, performance and configuration. Most of the specific items fall into one of these categories. In my opinion, a complete server assessment should not only return the state of the server but should also return any configuration settings that may have an impact on performance. Plan Guides fall into this area. They can have a negative impact on performance just a quickly as they can have a possible impact on performance.
I my over 20 years of working with SQL Server I have never seen a plan guide actually used in production. This doesn’t mean they are not being used, just that i haven’t seen it.
Before getting into why I look for Plan Guides, let’s take a few moments to go over exactly what a plan guide is.
Below is the definition from Microsoft:
“Plan guides let you optimize the performance of queries when you cannot or do not want to directly change the text of the actual query in SQL Server 2019 (15.x). Plan guides influence the optimization of queries by attaching query hints or a fixed query plan to them.”
Basically Plan Guides allow you to utilize optimizer hints without modifying the source code. For example, Plan Guides allow for the use of a particular physical join type. The optimizer utilizes a Hash Match join and you feel that the query might perform in a more optimal manner with a Nested Loop, the Plan Guide will allow this to happen without changing any code.
While this really isn’t a post about how to create and use Plan Guides, I think it is important to have a basic understanding of them. This is a link to Microsoft that will provide more detail.
When using the Object Explorer, Plan Guides can be found under Programmability in each database.
When I execute the query below in the AdventureWorks2014 database the actual execution plan will utilize a Merge Join operator. This is without a plan guide to tell the optimizer to do anything different. Of course, as you can see, an optimizer hint is also not used in the query.
SELECT *
FROM Sales.Customer AS c
INNER JOIN Sales.Customer AS ca
ON c.CustomerID = ca.CustomerID
WHERE ca.TerritoryID = 5
When a Plan Guide is created, you will be asked to provide the desired hint. In the case below, the hint is to use a Nest Loop whenever the above query is sent to the server.
When the plan exists, the execution plan will include the use of the Nest Loop join. This is because the query that matched the one in the defined in the Plan Guide.
Something to keep in mind, Plan Guides are both case sensitive and space sensitive. If the plan guide includes the query below:
SELECT *
FROM Sales.Customer AS c
INNER JOIN Sales.Customer AS ca
ON c.CustomerID = ca.CustomerID
WHERE ca.TerritoryID = 5
And the following query is executed:
select *
FROM Sales.Customer AS c
INNER JOIN Sales.Customer AS ca
ON c.CustomerID = ca.CustomerID
WHERE ca.TerritoryID = 5
Notice that the SELECT keyword is not capitalized. This will force the plan guide to be ignored. The plan guide will also be ignored if there is a trailing space at the end of one of the lines.
Also, plan guides can also be enabled\disabled. This can be done by simply right clicking on the plan guide in Management Studio and going to “Enable” or “Disable”.
When to use Plan Guides
While I haven’t used Plan Guides I can see where they have the potential to improve performance of a particular query in certain situations. Many vendors don’t allow you to change the code of the stored procedures to add a query hint, not to mention this also may not be the best idea in the first place.
So why do a look for Plan Guides during the server assessment?
During the server assessment, I look for these more for awareness than anything else. If a particular query performs poorly, it could be because of the plan guide. If they do exist then it is important to determine if is helping or contributing to the poor performance. As mentioned earlier, if a Plan Guide does exist, you will also need to determine of it is enabled or disabled.
The code below will check every database and return a row for each plan guide found. In addition, it will return the query and whether or not it is enabled.
( DBName VARCHAR(128)
, GuideName VARCHAR(500)
, CreateDate DATETIME
, ModifiedDate DATETIME
, IsEnabled BIT
, QueryStatement VARCHAR(300)
, Type NVARCHAR(Max))
EXECUTE master.sys.sp_MSforeachdb ‘USE [?] INSERT INTO #PGInfo SELECT
DB_Name()
, Name
, create_date
, modify_date
, is_disabled
, query_text
, scope_type
FROM sys.plan_guides ‘
FROM #PGInfo
BEGIN
SELECT ‘No Plan Guides were found in any databases.’
END
As stated earlier, looking for Plan Guides is more about awareness than anything else. If there is a performance issue with a particular query, knowing that these exist important.
Thanks for visiting my blog!
