Problem: What are plan guides
and how to use a custom plan guide?
Plan Guides:
For every query, there will be an execution plan which is
actual and estimated by the SQL Server Optimizer. Plan guides lets you change
or optimize a query execution plan performance without changing the query of a
procedure. A plan guide has a major influence on optimization of specific query
text with the help of query hints attached to the plan guides.
You can see the below screenshot to see where a plan guide
resides in a SQL Server Management studio.
Benefits
of plan guides:
·
Plan guides can be useful when a small subset of queries in a
database application provided by a third-party vendor are not performing as
expected.
·
You can disable or enable the plan guides when you think that
this plan guide no longer remains implied for the queries.
How to
Create a Plan guide:
In
the plan guide, you specify the Transact-SQL statement that you want optimized
and either an OPTION clause that contains the query hints you want to use or a
specific query plan you want to use to optimize the query. When the query
executes, SQL Server matches the Transact-SQL statement to the plan guide and
attaches the OPTION clause to the query at run time or uses the specified query
plan.
you can use gui from SSMS to create a plan guide as well, by right clicking on the plan guide folder.
Important Notes:
The
total number of plan guides you can create is limited only by available system
resources.
Downsides
of Plan guides
Nevertheless,
plan guides should be limited to mission-critical queries that are targeted for
improved or stabilized performance. Plan guides should not be used to influence
most of the query load of a deployed application.
Syntax:
sp_create_plan_guide [ @name = ] N'plan_guide_name'
, [ @stmt = ] N'statement_text'
, [ @type = ] N'{ OBJECT | SQL | TEMPLATE }'
, [ @module_or_batch = ]
{
N'[ schema_name. ] object_name'
| N'batch_text'
| NULL
}
, [ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL }
, [ @hints = ] { N'OPTION ( query_hint [ ,...n ] )'
| N'XML_showplan'
| NULL }
Example:
In this example, I would like to show we can create an
explicit plan guides to reduce the degree of parallelism using MAXDOP OPTION
Hint.
To explain what I am trying to achieve let me show how a
query optimizer chose to return the results before I have a specific plan guide
on the SQL Statement and then create a plan guide for that query and observe
the difference.
Before plan guides
This is how the queries execution plan looks before I have a
specific execution plan. As you can see it reached to a degree of parallelism to
8. Which means it uses 8 parallel operators to get the task done which will
chew the CPU for a while.
Creating a
plan guide to use attached option of MAXDOP to the query:
--
creates a plan guide based on a SQL
statement
EXEC sp_create_plan_guide
@name = N'maxdop plan guide on
Address table', --name of the plan
guide
@stmt = N'select * from [dbo].[Address]
order
by AddressID DESC', --statement
to improve the query optimization
@type = N'SQL', -- object or SQL or
Template this is the scope of the Plan
@module_or_batch =
NULL, --Specifies either the name of the object in which
statement_text appears, or the batch text in which statement_text appears.
@params = NULL, --Specifies the definitions of all parameters that are
embedded in statement_text.
@hints = N'OPTION (MAXDOP 1)'; --Specifies an
OPTION clause to attach to a query that matches
After the plan guide
This is a forced execution plan as our query statement is
specified on the plan guide it can choose the best optimized plan guide by
reducing the parallelism.
0 comments:
Post a Comment