Amit Bansal CTO Peopleware India - Microsoft Home Page | Devices

29 Pages·2009·1.36 MB·English

by  Amit Bansal

Amit Bansal
CTO
eDominer Systems P Ltd |
Peopleware India
Who is Amit Bansal?
CTO, eDominer Systems & Peopleware India
Conducted more than 200 workshops on SQL Server &
BI for top notch IT companies world wide
Microsoft MVP for SQL Server
Microsoft Certified Trainer Advisory Council member
Speaker at TechED India, TechED US & TechED Europe
Technical Reviewer – MSL courses on SQL Server
SME – SQL Server 2008 certifications
UG Lead (Delhi NCR) – Culminis
Manager – www.WeTogether.in, www.DelhiDevs.com,
www.BlogBoard.in
Agenda & Session takeaways
Query Optimizer characteristics
Using Query & Table Hints
Plan Freezing concept
Scenarios from the wild
What are Plan Guides & different types
Implementing Plan Guides
Managing & Validating Plan Guides
Plan Guide considerations
Summary
Query Optimizer characteristics
Cost based optimization
Does 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 caution
To be used only by experienced DBDs & DBAs
Is there really a problem?
Check if there are other issues
Identify the real cause of a poor plan
What if SQL Server fails to generate a valid plan?
(error 8622 is raised)
Join, Query & Table Hints
FAST N Hint
Functionality
RECOMPILE
Index Hints
OPTIMIZE FOR Hint
Join Hints
OPTIMIZE FOR UNKNOWN Hint
Parallelism
JOIN Hints
FORCE ORDER Hint
Locking
INDEX Hint
Compilation
FORCESEEK Hint
Table Hints
READPAST Hint
….
USE PLAN Hint
Plan Freezing concepts
Forcing / Locking down a query plan
Provides greater query performance stability &
predictability
Enables organizations to promote stable query plans
Hardware Server replacements
Server upgrades
Production Deployments
Plan Freezing can
Optimize query performance
Copy query plans between servers
Prevent the plan cache from growing excessively with large
compiled plans that are not reused
Scenarios from the wild…
A query started mis-behaving after SQL Server upgrade
Query text cannot be modified to use hints
Eg: Application/vendor code, ISV, etc
Query performance degraded after hardware upgrade
You need to copy plans from one server to the other
Overcoming ‘Parameter Sniffing’ issues
After a service pack upgrade – “my query just starting
running 4 times slower today and the plan is different
from yesterday” ……sigh 
What are Plan Guides?
Plan Freezing concept is implemented with Plan Guides
A DB object that associates a set of query hints with
the query
Can be created using:
sp_create_plan_guide
sp_create_plan_guide_from_handle
Fixed query plan (XML Showplan output) can be also
be applied
Available in Standard, Enterprise, Evaluation &
Developer edition
Types of Plan Guides
Object Plan Guides
matches queries that execute in the context of a SQL Server
object (SP, UDF, DML triggers etc)
SQL Plan Guides
matches queries that execute in the context of stand-alone
Transact-SQL statements and batches ( not part of a
database object)
Template Plan Guides
matches stand-alone queries that parameterize to a
specified form
To override the current PARAMETERIZATION setting