70-451–MCITP–Designing a Database for Optimal Performance–Resolve Performance Problems by Using Plan Guides

Requirements

Resolve performance problems by using plan guides.
This objective may include but is not limited to: object plan guides, SQL plan guides, templates plan guides, dynamic management views

My Take

It seems to me that we will simply be exploring plan guides as a potential to help out with optimization. I’m going to assume that the DMV portion of this section revolves around those DMVs that are relevant to plan guides.

Plan Guides (General)

Plan guides can be used to help optimize queries that are already in production, or that you might not otherwise be able to adjust (such as from a third party vendor or tool). With these guides you can specify optimization hints (which replace any stated optimization hints in the original query) or even a give execution plan. It seems like most of the information necessary for this section can be found in the article Understanding Plan Guides. Note that plan guides should not be overused. It is generally better to use the hint within your code or application if possible. As such, their use should probably be limited to the “critical” queries that are in need of optimization.

You can Use SQL Server Profiler to Create and Test Plan Guides. To see if the plan guide is being used, search for the PlanGuideDB and PlanGuideName or TemplatePlanGuideDB and TemplatePlanGuideName attributes in the <StmtSimple> element of the execution plan.

Creating the plan guide will take all related plans out of the plan cache.

Plan Guide System Tables, Stored Procedures and Functions

  • sp_create_plan – the generic stored procedure used to create a plan guide
  • sp_create_plan_from_handle – the stored procedure used to create a plan guide from an existing execution plan already in the plan cache.
  • sp_control_plan_guide – the stored procedure used to control if the plan guides are disabled, enabled or dropped.
  • sys.plan_guide – the system table used to examine the plan guides in the database. It holds one row per plan guide.
  • fn_validate_plan_guide – a function used to validate that a given plan guide can be used by the query optimizer.

Object Plan Guides

With an object plan guide, you are providing hints or the specific plan to use for queries that are executed within stored procedures, scalar UDFs, multi-statement TVFs and DML Triggers.

SQL Plan Guides

SQL Plan guides are used for statements and batches of T-SQL code that isn’t included in the Object Plan Guides above. Note that with SQL Plan guides the statement being matched for the guide is matched character for character, so be mindful of formatting.

Templates Plan Guides

A template plan guide is used to override the parameterization parameter that is set for the database, from simple to forced or vice versa. Note that with SQL Plan guides the statement being matched for the guide is matched character for character, so be mindful of formatting. Template plan guides will only ever match statements that are sent in batches consisting of a single statement only.

It is good to realize that Template plan guides can be used as the basis for optimization with an SQL or Object Plan Guide. Also, you cannot attach a fixed plan with a template. You can, however, attach a fixed plan to an SQL or Object Plan Guide based on the template.

See the article Specifying Query Parameterization Behavior by Using Plan Guides.

Dynamic Management Views

I believe that this section is meant to discuss the dynamic management views that might be used to represent the ways in which we can both create plan guides from the execution plan in cache (either through the text guide or the plan handle) and those that will verify that the changes we have made are actually being used by inspecting the execution plan in the plan cache after creating the plan guide.

If so, then the dynamic management objects of interest would be the combination of sys.dm_exec_query_stats, sys.dm_exec_sql_text and sys.dm_exec_text_query_plan. From these, we can obtain the plan handle and starting offset that is used in both the sp_create_plan_guide_from_handle. We could then look at either these same dmv’s or use the sys.dm_exec_query_plan to view the xml execution plan, potentially along with the sys.dm_exec_requests or with sys.dm_exec_cached_plans.

Example

USE [AdventureWorks];
GO
/* Determine what plan guides currently exist in the database. */
SELECT * FROM sys.[plan_guides];
GO
/* Run the creat plan guide procedure.  Use the EXACT format of the query you want to optimize. */
EXEC sp_create_plan_guide
@name = N'Test Guide'
, @stmt = N'WITH [BOM_cte]([ProductAssemblyID], [ComponentID], [ComponentDesc], [PerAssemblyQty], [StandardCost], [ListPrice], [BOMLevel], [RecursionLevel]) -- CTE name and columns
AS (
SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], 0 -- Get the initial list of components for the bike assembly
FROM [Production].[BillOfMaterials] b
INNER JOIN [Production].[Product] p
ON b.[ComponentID] = p.[ProductID]
WHERE b.[ProductAssemblyID] = @StartProductID
AND @CheckDate >= b.[StartDate]
AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
UNION ALL
SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], [RecursionLevel] + 1 -- Join recursive member to anchor
FROM [BOM_cte] cte
INNER JOIN [Production].[BillOfMaterials] b
ON b.[ProductAssemblyID] = cte.[ComponentID]
INNER JOIN [Production].[Product] p
ON b.[ComponentID] = p.[ProductID]
WHERE @CheckDate >= b.[StartDate]
AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
)
SELECT b.[ProductAssemblyID], b.[ComponentID], b.[ComponentDesc], SUM(b.[PerAssemblyQty]) AS [TotalQuantity] , b.[StandardCost], b.[ListPrice], b.[BOMLevel], b.[RecursionLevel]
FROM [BOM_cte] b
GROUP BY b.[ComponentID], b.[ComponentDesc], b.[ProductAssemblyID], b.[BOMLevel], b.[RecursionLevel], b.[StandardCost], b.[ListPrice]
ORDER BY b.[BOMLevel], b.[ProductAssemblyID], b.[ComponentID]
OPTION (MAXRECURSION 25)'
, @type=N'Object'
, @module_or_batch = 'dbo.uspGetBillOfMaterials'
, @params = NULL
, @hints = N'OPTION(HASH JOIN)'
;
GO
/* See if the plan guide is valid.  An empty result set shows that it IS valid */
SELECT * FROM sys.[plan_guides]
CROSS APPLY fn_validate_plan_guide(plan_guides.[plan_guide_id]);
GO
/* Run the stored procedure to get it into the plan cache */
EXECUTE dbo.[uspGetBillOfMaterials]
@StartProductID = 0
, @CheckDate = '2011-03-02 04:29:30'
;
GO
/* Investigate the plan cache to determine if the stored procedure used the plan guide */
WITH
XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
SELECT
OBJECT_NAME(dm_exec_query_plan.objectid) AS ObjectName
, query_plan
, query_plan.value('(//sp:StmtSimple/@PlanGuideName)[1]', 'varchar(500)') AS PlanGuideName
, query_plan.value('(//sp:StmtSimple/@PlanGuideDB)[1]', 'varchar(500)') AS PlanGuideName
FROM
sys.[dm_exec_cached_plans]
CROSS APPLY
sys.[dm_exec_query_plan](dm_exec_cached_plans.[plan_handle])
WHERE
objectid = OBJECT_ID('dbo.uspGetBillOfMaterials')
;
GO
/* Drop the guide we created for the test. */
EXECUTE sp_control_plan_guide
@name = 'Test Guide'
, @operation = 'Drop'
;
GO

Advertisements
This entry was posted in SQL and tagged , , , . Bookmark the permalink.

One Response to 70-451–MCITP–Designing a Database for Optimal Performance–Resolve Performance Problems by Using Plan Guides

  1. Pingback: MCITP 70-451 Links Page | Destination: Change

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s