Why do I include Plan Guides in my Server Assessment

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.

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:

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.

        CREATE TABLE #PGInfo
          ( 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
          , Name
          , create_date
          , modify_date
          , is_disabled
          , query_text
          , scope_type
        FROM sys.plan_guides ‘
        SELECT *
        FROM #PGInfo
        IF @@ROWCOUNT = 0
         SELECT ‘No Plan Guides were found in any databases.’
        DROP TABLE #PGInfo


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!


ADS – Query History Extension

I just downloaded the latest release of Azure Data Studio.  A new extension came with it that I really like, Query History.  According to the documentation, the Query History extension does this: “Adds a Query History panel for viewing and running past executed queries”.

This installation of this extension is pretty simple and does not require a restart of Azure Data Studio.  However, if you disable or uninstall the extension, you will need to restart ADS.

If you look at the View menu, you will notice that there is not a menu item for “Query History”.

This will change once the extension is installed.  You will find the Query History item just under Output.

Once you install the extension, you will now begin to see query history.  If you run a query, you will see the history as seen below.  If you to not see this at the bottom of the screen, go to the menu item mentioned above.

When you run a query, it takes a very short period of time for it to appear in the history.  In the above image,  I ran three queries, two of which were the same query.  This window is not part of the query window.  Since it is a separate window, it will collect queries from all tabs that are open.  Each row in the history will return three things, the query, the server and database uses as well as the times stamp of when the query was ran.  The server can be seen in the green box below.  Sometimes a forth item is included in the list of items returned, the icon that is at the far left of each row.

Now that we have history, let’s take a moment to see what we can do.  First of all, if you float your cursor over one of the queries, you will see a pop up.  This pop up will display the query.

You will see a lot more options if you right click on one of the queries.

All the options are pretty straight forward and really don’t need a great deal of explanation.  The bottom option, Pause Query History Capture I can see being useful if you are running query that you don’t want to be part of the history or just running too many queries at the moment.

Since these options are pretty straight forward, let’s take a look at a few questions.

What happens to the history when Azure Data Studio is closed?

The history is no longer available

What if I execute a batch, will each query in the batch be a separate line?

All the queries for the batch will be on the same line. The query in the green box is actually two select statements.

To the left of the query, I see a green check mark.  What are other icons I might see? 

There is currently only one other icon you might see. The red X when there is an error.

When there is a error, you will see a small number next to the “Problem” tab.  When you clock to this tab, you can now see details on what the error was.

What’s the difference between “Run Query” and “Open Query”?

Open query will do just that. Open the query so you can review the TSQL.  Run query will simply run the query and you will see any results if there are any.

Over all I really like this extension and can really see the practical use of it.  Just remember, this extension does not stored the queries beyond the Azure Data Studio session.

Thanks for visiting my blog!!!