Plan Guides

Override Bad Table Hints With a Plan Guide

Unfortunately, we’ve all seen some third party applications pass some pretty awful SQL into databases. The worst of these always seem to be queries that cannot be edited or modified from within the application. Table/index and MAXDOP hints are a couple of the things I don’t like to see. These hints might be perfectly fine for a while, but as data grows and tables get larger, certain hints can prevent the query optimizer from working properly and providing the optimal execution plan.

I will be using the AdventureWorks2012 database for this example, and as part of the setup, I’ve already created the index below on the [Sales].[SalesOrderDetail] table. (It will be required for the FORCESEEK hint in my examples to work):

USE [AdventureWorks2012]
GO

CREATE NONCLUSTERED INDEX [idx_ProductID] 
ON [Sales].[SalesOrderDetail]
([ProductID] ASC)
GO

Let’s take this simple query with and without an added FORCESEEK hint as an example.

--SalesOrderDetail - 372,956 reads
SELECT * FROM Sales.SalesOrderDetail s WITH (FORCESEEK)
INNER JOIN Production.Product p ON s.ProductID = p.ProductID;

FORCESEEK_plan

--SalesOrderDetail - 1,246 reads
SELECT * FROM Sales.SalesOrderDetail s 
INNER JOIN Production.Product p ON s.ProductID = p.ProductID;

PlanGuide_plan

Using the WITH(FORCESEEK) hint, the query is doing 372,956 reads on the SalesOrderDetail table (with a pretty nasty Key Lookup), but without the hint, the query only does 1,246 reads (in this case, a scan is a good thing).

Let’s assume for this blog post that the application is passing in the query with the FORCESEEK hint, and that we cannot modify the application to remove the hint, even though we know, for a fact, that the query will perform much better without the hint. How are we supposed to remove the FORCESEEK hint and cut down on reads?

First, we need to take a look at adding another TABLE HINT to the query to override the existing FORCESEEK hint. There is a pretty nifty trick to do this. We can simply append ‘OPTION(TABLE HINT(s))’ to the end of the query and it’ll wipe out any prior TABLE HINT on the SalesOrderDetail table (aliased as ‘s’). Since we aren’t actually specifying any specific TABLE HINT, it basically resets all existing table hints on SalesOrderDetail. It brings us back down to 1,246 reads and gives us the query plan with the Clustered Index scan.

--SalesOrderDetail - 1,246 reads
SELECT * FROM Sales.SalesOrderDetail s WITH (FORCESEEK)
INNER JOIN Production.Product p ON s.ProductID = p.ProductID
OPTION(TABLE HINT (s)); --TABLE HINT reset

Now that we’ve figured out how to add to the existing query to remove the FORCESEEK hint, we need to figure out how to automatically have this statement appended to this query every time it is executed. Two words: Plan Guide.

Here is the basic syntax for getting this plan guide added to the database. You must give it a name, specify which SQL statement the hint will be added to, the type of plan guide, and the hint that will be added to the SQL Statement. (NOTE: the SQL statement must be passed into @stmt EXACTLY as the application will pass it in to the database. This even includes trailing spaces – which have caused me headaches many times.)

USE [AdventureWorks2012]
GO

EXEC sp_create_plan_guide 
@name = N'RemoveFORCESEEK', 
@stmt = N'SELECT * FROM Sales.SalesOrderDetail s WITH (FORCESEEK)
INNER JOIN Production.Product p ON s.ProductID = p.ProductID;', 
@type = N'SQL', 
@hints = N'OPTION(TABLE HINT (s))'

GO

Now that the plan guide is created, we can test running the original query with the FORCESEEK hint.

query_planguide

We can see from the execution plan properties that the query is now using the Plan Guide and doing the Clustered Index scan on SalesOrderDetail for 1,246 reads. We’ve successfully circumvented the bad FORCESEEK hint and saved a ton of reads in the process.

Checkout this MSDN link for further reading on Query Hints:
https://msdn.microsoft.com/en-us/library/ms181714.aspx

Advertisements

Find All Queries Using a Plan Guide in Cache

The inspiration for this post came a few days ago when a developer wanted to figure out if his application’s queries were still using plan guides that had been setup a couple of years ago. I asked him if he knew the specific queries in question, and of course, he did not.

My first thought was to create an Extended Events session to capture the “sqlserver.plan_guide_successful” and “sqlserver.plan_guide_unsuccessful” events. This would tell me, upon execution, if a query was successful or unsuccessful at using a plan guide. Unfortunately, the developer wasn’t able to execute the queries at the time, so this wouldn’t work in this case.

My next thought was to drill down into the database in Object Explorer -> Programmability -> Plan Guides and look to see what plan guides actually existed. Then I could drill into each individual plan guide and find the query that plan guide referenced. I could then lookup that query in cache and find the execution plan to see if the query was indeed using the plan guide. (Note – you can also query sys.plan_guides to get a list of all plan guides created on the database)

This can usually be done by looking at the properties window of the left-most operator (the Select operator in this case) of the query plan. If the query is using a plan guide, there will be two Properties: PlanGuideDB and PlanGuideName.

PlanGuideInPlan

This could be tedious and take a little bit of time if you needed to lookup queries for many different plan guides. There had to be another way.

If you look at the raw XML of the execution plan, you can see the same PlanGuideDB and PlanGuideName properties (see line #5 below):

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.2" Build="11.0.2100.60">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementText="select ? from Schema1.Object1" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="true" PlanGuideDB="AdventureWorks2012" PlanGuideName="PlanGuide_1" StatementSubTreeCost="0.00363" StatementEstRows="290" StatementOptmLevel="TRIVIAL" QueryHash="0x4578128A64C31C86" QueryPlanHash="0x4C7F74CCEDD0EAE0">
          <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
          <QueryPlan NonParallelPlanReason="MaxDOPSetToOne" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="120">
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="207295" EstimatedPagesCached="51823" EstimatedAvailableDegreeOfParallelism="1" />
            <RelOp NodeId="0" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="290" EstimateIO="0" EstimateCPU="2.9e-005" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00363" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
              <OutputList>
                <ColumnReference Column="Expr1003" />
              </OutputList>
              <ComputeScalar>
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Column="Expr1003" />
                    <ScalarOperator ScalarString="ScalarString1">
                      <Const ConstValue="Value1" />
                    </ScalarOperator>
                  </DefinedValue>
                </DefinedValues>
                <RelOp NodeId="1" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="290" EstimateIO="0.003125" EstimateCPU="0.000476" AvgRowSize="9" EstimatedTotalSubtreeCost="0.003601" TableCardinality="290" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
                  <OutputList />
                  <IndexScan Ordered="0" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">
                    <DefinedValues />
                    <Object Database="Database1" Schema="Schema1" Table="Object1" Index="Index1" IndexKind="NonClustered" />
                  </IndexScan>
                </RelOp>
              </ComputeScalar>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

Ah ha! We should be able to search through the execution plan xml to find all plans that contain %PlanGuideName% currently in cache. With that in mind I came up with the following query. (Note: This will only find queries using a plan guide that are currently cached. It is very possible that a query that is not in the procedure cache could use a plan guide, also. But if it isn’t cached, we won’t find it.)

--Could take a while to execute on a server with a large plan cache, be patient
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT qt.text
	,DB_NAME(qt.dbid) AS db_name
	,LEFT(SUBSTRING(CAST(qp.query_plan AS nvarchar(max)), CHARINDEX('PlanGuideName', 
	CAST(qp.query_plan AS nvarchar(max))) + 15, 100), 
	CHARINDEX('"', SUBSTRING(CAST(qp.query_plan AS nvarchar(max)), 
	CHARINDEX ('PlanGuideName', CAST(qp.query_plan AS nvarchar(max))) + 16, 100))) AS PlanGuideName
	,qs.total_logical_reads / qs.execution_count AS [Avg Logical Reads]
	,qs.total_elapsed_time / qs.execution_count / 1000 AS [Avg Elapsed ms]
	,qs.execution_count
	,qs.last_execution_time
	,qs.creation_time as Compile_Time
	,qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE CAST(qp.query_plan AS nvarchar(max)) LIKE ('%PlanGuideName%')
	AND qt.text NOT LIKE '%sys.dm%' --filter out any DBA user queries that may be hitting system tables
ORDER BY qs.last_execution_time desc
OPTION (RECOMPILE, MAXDOP 1);--prevent this query from filling the cache & try to minimize the cpu impact

Sample output:

queryoutput

VoilĂ ! Now we have a list of all the queries, currently in cache, that are using plan guides. Much easier than digging through each Plan Guide individually!