Database Reference
In-Depth Information
See also
For more information using Resource Governor capabilities, refer to the
Administer
SQL Server workloads with Resource Governor
recipe in
Chapter
2
,
Administrating
the Core Database Engine
.
Implementing the plan guide to enhance
compile-time and run-time execution plans
SQL Server query optimizer is an efficient engine that plays an important role in producing an
efficient query execution plan for a query. By default, the optimizer decides the eligibility for
parallel operation by creating a parallel query plan. Many times, this may not be an efficient
plan based on the optimization goals that are specific to an application; thus, using the plan
guide process is a recommended method. In such cases, the query plan can be optimized or
tailor-made based on the response time, disk IO, memory, and CPU.
The troubleshooting aspects of query performance involve the review of many areas such as
design, table indexing, and query execution. In case of running a third-party application tool -
where we are limited to modify or make any changes to queries that are causing performance
problems - the plan guides are helpful as they allow applying hints to the query without
having a need to change the actual query text that is generated from the application.
The plan guides are also helpful within the upgrade of SQL Server
from previous versions to a higher version scenario. Due to the
nature of query execution, behavior between the versions may result
as suboptimal plans; plan guides are helpful to restrict this behavior.
SQL Server 2008 introduces the plan guides concept where both query and table hints can be
designated by using the
sp_create_plan_guide
statement within the stored procedures,
triggers, functions, and adhoc queries. In this recipe, we will go through the process of
implementing plan guides to enhance compile-time and run-time execution plans to help
query optimization.
How to do it...
The following steps will stand out as the process of implementing plan guides to enhance
compile-time and run-time execution plans to help query optimization (assuming that the
following TSQL is generated from the application that has been captured using the Activity
Monitor tool):
Search WWH ::
Custom Search