admin guiding query plans
DESCRIPTION
TRANSCRIPT
![Page 1: Admin Guiding Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022051313/54845245b4af9fb1728b4759/html5/thumbnails/1.jpg)
![Page 2: Admin Guiding Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022051313/54845245b4af9fb1728b4759/html5/thumbnails/2.jpg)
Guiding Query Plans for optimized query performance Amit Bansal
CTOeDominer Systems P Ltd | Peopleware India
![Page 3: Admin Guiding Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022051313/54845245b4af9fb1728b4759/html5/thumbnails/3.jpg)
Who is Amit Bansal?
CTO, eDominer Systems & Peopleware IndiaConducted more than 200 workshops on SQL Server & BI for top notch IT companies world wideMicrosoft MVP for SQL ServerMicrosoft Certified Trainer Advisory Council memberSpeaker at TechED India, TechED US & TechED EuropeTechnical Reviewer – MSL courses on SQL ServerSME – SQL Server 2008 certificationsUG Lead (Delhi NCR) – CulminisManager – www.WeTogether.in, www.DelhiDevs.com, www.BlogBoard.in
![Page 4: Admin Guiding Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022051313/54845245b4af9fb1728b4759/html5/thumbnails/4.jpg)
Agenda & Session takeaways
Query Optimizer characteristicsUsing Query & Table HintsPlan Freezing conceptScenarios from the wildWhat are Plan Guides & different typesImplementing Plan GuidesManaging & Validating Plan GuidesPlan Guide considerationsSummary
![Page 5: Admin Guiding Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022051313/54845245b4af9fb1728b4759/html5/thumbnails/5.jpg)
Query Optimizer characteristics
Cost based optimizationDoes the Query Optimizer always do a fine job?
Does it always select the best query plan?Use Hints to affect the query plan selection
Use Hints as a last resort, Use with cautionTo be used only by experienced DBDs & DBAs
Is there really a problem?Check if there are other issuesIdentify the real cause of a poor planWhat if SQL Server fails to generate a valid plan?
(error 8622 is raised)
![Page 6: Admin Guiding Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022051313/54845245b4af9fb1728b4759/html5/thumbnails/6.jpg)
Join, Query & Table Hints
FAST N HintRECOMPILEOPTIMIZE FOR HintOPTIMIZE FOR UNKNOWN HintJOIN HintsFORCE ORDER HintINDEX HintFORCESEEK HintREADPAST HintUSE PLAN Hint
FunctionalityIndex HintsJoin HintsParallelismLockingCompilationTable Hints….
![Page 7: Admin Guiding Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022051313/54845245b4af9fb1728b4759/html5/thumbnails/7.jpg)
Plan Freezing concepts
Forcing / Locking down a query planProvides greater query performance stability & predictabilityEnables organizations to promote stable query plans
Hardware Server replacementsServer upgradesProduction Deployments
Plan Freezing canOptimize query performanceCopy query plans between serversPrevent the plan cache from growing excessively with large compiled plans that are not reused
![Page 8: Admin Guiding Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022051313/54845245b4af9fb1728b4759/html5/thumbnails/8.jpg)
Scenarios from the wild…
A query started mis-behaving after SQL Server upgradeQuery text cannot be modified to use hints
Eg: Application/vendor code, ISV, etcQuery performance degraded after hardware upgradeYou need to copy plans from one server to the otherOvercoming ‘Parameter Sniffing’ issuesAfter a service pack upgrade – “my query just starting running 4 times slower today and the plan is different from yesterday” ……sigh
![Page 9: Admin Guiding Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022051313/54845245b4af9fb1728b4759/html5/thumbnails/9.jpg)
What are Plan Guides?
Plan Freezing concept is implemented with Plan GuidesA DB object that associates a set of query hints with the queryCan be created using:
sp_create_plan_guidesp_create_plan_guide_from_handle
Fixed query plan (XML Showplan output) can be also be appliedAvailable in Standard, Enterprise, Evaluation & Developer edition
![Page 10: Admin Guiding Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022051313/54845245b4af9fb1728b4759/html5/thumbnails/10.jpg)
Types of Plan Guides
Object Plan Guidesmatches queries that execute in the context of a SQL Server object (SP, UDF, DML triggers etc)
SQL Plan Guidesmatches queries that execute in the context of stand-alone Transact-SQL statements and batches ( not part of a database object)
Template Plan Guidesmatches stand-alone queries that parameterize to a specified formTo override the current PARAMETERIZATION setting
![Page 11: Admin Guiding Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022051313/54845245b4af9fb1728b4759/html5/thumbnails/11.jpg)
Object Plan Guides
You are interested in a T-SQL statement appearing in the context of a SQL Server objectSQL Server object can be:
Stored ProcedureUser Defined Function (Scalar)Multi-statement table-valued user-defined functionsDML Triggers
![Page 12: Admin Guiding Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022051313/54845245b4af9fb1728b4759/html5/thumbnails/12.jpg)
Object Plan Guidessp_create_plan_guide @name = N'Guide1',@stmt = N'SELECT *FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, Sales.SalesTerritory AS t WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = @Country_region',@type = N'OBJECT',@module_or_batch = N'Sales.GetSalesOrderByCountry',@params = NULL,@hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))'
![Page 14: Admin Guiding Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022051313/54845245b4af9fb1728b4759/html5/thumbnails/14.jpg)
SQL Plan Guides
You are interested in a particular T-SQL statementStand-aloneOr part of a batch
T-SQL statements sent by CLR objects, extended SPs & Dynamic SQL (EXEC (sql_string)) are processed as batchesThe text has to be exactly the same:
Case, whitespace, etc.
![Page 15: Admin Guiding Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022051313/54845245b4af9fb1728b4759/html5/thumbnails/15.jpg)
SQL Plan Guidessp_create_plan_guide @name = N'Guide2', @stmt = N'SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC',@type = N'SQL',@module_or_batch = NULL, @params = NULL, @hints = N'OPTION (MAXDOP 1)';
![Page 17: Admin Guiding Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022051313/54845245b4af9fb1728b4759/html5/thumbnails/17.jpg)
Template Plan Guides
To override PARAMETERIZATION database settingCan only use the following hints:
PARAMETERIZATION FORCEDPARAMETERIZATION SIMPLE
Little trickySQL Server has to construct a template of the query in the same format that it will be in once it is parameterizedSpecial stored procedure: sp_get_query_template@params is only used for TEMPLATE plan guide
![Page 18: Admin Guiding Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022051313/54845245b4af9fb1728b4759/html5/thumbnails/18.jpg)
Template Plan GuidesEXEC sp_create_plan_guide @name = N'TemplateGuide1', @stmt = N'SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.SalesOrderID = @0', @type = N'TEMPLATE', @module_or_batch = NULL, @params = N'@0 int', @hints = N'OPTION(PARAMETERIZATION FORCED)';
![Page 20: Admin Guiding Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022051313/54845245b4af9fb1728b4759/html5/thumbnails/20.jpg)
Applying a Fixed Query Plan to a Plan Guide
Useful when you know about an existing execution plan that performs better than the one selected by the optimizer for a particular queryCan be applied to OBJECT or SQL plan guidesExtract the XML Showplan from the cache and supply it to the sp_create_plan_guide
![Page 22: Admin Guiding Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022051313/54845245b4af9fb1728b4759/html5/thumbnails/22.jpg)
Creating Plan Guides directly from the plan cache
Creates one or more plan guides from a query plan in the plan cacheCan be applied to OBJECT or SQL plan guidesExtract the plan handle from the cache and supply it to the sp_create_plan_guide_from_handle
![Page 24: Admin Guiding Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022051313/54845245b4af9fb1728b4759/html5/thumbnails/24.jpg)
Managing & Validating Plan Guides
Managing Plan Guidessp_control_plan_guide
ENABLE, DISABLE, DROPValidating Plan guides
sys.fn_validate_plan_guide
![Page 25: Admin Guiding Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022051313/54845245b4af9fb1728b4759/html5/thumbnails/25.jpg)
Plan Guides considerations
The statement text in the plan guide must exactly match the query textTo verify that your plan guide was used, see for specific elements in XML Showplan output
PlanGuideDB & PlanGuideName (or)TemplatePlanGuideDB & TemplatePlanGuideNameQuery Plan (properties window)
![Page 26: Admin Guiding Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022051313/54845245b4af9fb1728b4759/html5/thumbnails/26.jpg)
Summary
There are many scenarios where you cannot use hintsPlan Guides come to rescueThree different types (OBJECT, SQL, TEMPLATE)SQL Server 2008 enhancements
Applying a fixed query to a plan guide using XML ShowplanCreating a plan directly from a plan in the plan cacheValidating Plan Guides
Plan Guide Considerations
![Page 27: Admin Guiding Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022051313/54845245b4af9fb1728b4759/html5/thumbnails/27.jpg)
धन्यवा�दઆભા�ર ধন্য�বা�দ
ਧੰ�ਨਵਾ�ਦ
ଧନ୍ୟ�ବା�ଦ
நன்றி�
ధన్య�వాదాలు� ಧನ್ಯ�ವಾ�ದಗಳು
നി�ങ്ങള്ക്ക്� നിന്ദി�
![Page 29: Admin Guiding Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022051313/54845245b4af9fb1728b4759/html5/thumbnails/29.jpg)
© 2009 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS,
IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.