v
CONTENTS
Contents..............................................................................................................................................................
5
About the author...............................................................................................................................................9
acknowledgements..........................................................................................................................................11
Introduction.....................................................................................................................................................12
Foreword...........................................................................................................................................................15
Chapter 1: Execution Plan Basics...............................................................................................................16
What Happens When a Query is Submitted?...............................................................................16
Query Parsing....................................................................................................................................16
The Query Optimizer.....................................................................................................................17
Query Execution..............................................................................................................................18
Estimated and Actual Execution Plans......................................................................................18
Execution Plan Reuse.....................................................................................................................18
Why the Actual and Estimated Execution Plans Might Differ...........................................20
Execution Plan Formats.................................................................................................................21
Graphical Plans.................................................................................................................................21
Text Plans...........................................................................................................................................21
XML Plans.........................................................................................................................................21
Getting Started......................................................................................................................................21
Sample Code......................................................................................................................................22
Permissions Required to View Execution Plans......................................................................22
Working with Graphical Execution Plans......................................................................................22
Getting the Estimated Plan...........................................................................................................23
Getting the Actual Plan..................................................................................................................23
Interpreting Graphical Execution Plans....................................................................................24
Working with Text Execution Plans................................................................................................27
Getting the Estimated Text Plan.................................................................................................28
Getting the Actual Text Plan........................................................................................................28
Interpreting Text Plans...................................................................................................................28
Working with XML Execution Plans..............................................................................................29
Getting the Actual and Estimated XML Plans........................................................................29
Interpreting XML Plans.....................................................................................................................30
Saving XML Plans as Graphical Plans.......................................................................................32
Automating Plan Capture Using SQL Server Profiler...............................................................32
Execution Plan events.....................................................................................................................33
Capturing a Showplan XML Trace.............................................................................................33
vi
Summary.................................................................................................................................................36
Chapter 2: Reading Graphical Execution Plans for Basic Queries...................................................
37
The Language of Graphical Execution Plans..............................................................................37
Some Single table Queries.................................................................................................................
38
Clustered Index Scan......................................................................................................................38
Clustered Index Seek......................................................................................................................
39
Non-clustered Index Seek.............................................................................................................41
Key LookUp.....................................................................................................................................
42
Table Scan..........................................................................................................................................46
RID LookUp.....................................................................................................................................47
Table Joins..............................................................................................................................................50
Hash Match (Join)...........................................................................................................................
52
Clustered Index Seek......................................................................................................................
54
Nested Loops Join...........................................................................................................................55
Compute Scalar................................................................................................................................
55
Merge Join.........................................................................................................................................
56
Adding a WHERE Clause.................................................................................................................58
Execution Plans with GROUP BY and ORDER BY...............................................................
60
Sort......................................................................................................................................................
60
Hash Match (Aggregate)................................................................................................................62
Filter....................................................................................................................................................
63
Rebinds and Rewinds Explained......................................................................................................64
Insert, Update and Delete Execution Plans..................................................................................66
Insert Statements.............................................................................................................................
66
Update Statements...........................................................................................................................67
Delete Statements............................................................................................................................68
Summary.................................................................................................................................................70
Chapter 3: Text and XML Execution Plans for Basic Queries..........................................................
71
Text Execution Plans..........................................................................................................................
71
A Text Plan for a Simple Query..................................................................................................
71
A Slightly more Complex Query.................................................................................................73
XML Execution Plans........................................................................................................................
77
An Estimated XML Plan...............................................................................................................78
An Actual XML Plan......................................................................................................................81
Summary.................................................................................................................................................81
Chapter 4, Understanding More Complex Query Plans......................................................................83
Stored Procedures................................................................................................................................83
Derived Tables......................................................................................................................................84
vii
A Subselect without a Derived Table.........................................................................................84
A Derived Table using APPLY....................................................................................................87
Common Table Expressions.............................................................................................................90
Views.......................................................................................................................................................92
Standard Views.................................................................................................................................92
Indexed Views...................................................................................................................................93
Indexes....................................................................................................................................................95
Included Indexes: Avoiding Bookmark Lookups...................................................................95
Index Selectivity................................................................................................................................98
Statistics and Indexes...................................................................................................................
100
Summary..............................................................................................................................................
101
Chapter 5: Controlling Execution Plans with Hints...........................................................................103
Query Hints........................................................................................................................................
103
HASH|ORDER GROUP.........................................................................................................
103
MERGE |HASH |CONCAT UNION................................................................................
105
LOOP|MERGE|HASH JOIN...............................................................................................
106
FAST n.............................................................................................................................................
109
FORCE ORDER..........................................................................................................................110
MAXDOP.......................................................................................................................................111
OPTIMIZE FOR.........................................................................................................................
113
PARAMETERIZATION SIMPLE|FORCED..................................................................
116
RECOMPILE................................................................................................................................
116
ROBUST PLAN...........................................................................................................................
118
KEEP PLAN.................................................................................................................................
118
KEEPFIXED PLAN..................................................................................................................
118
EXPAND VIEWS........................................................................................................................118
MAXRECURSION.....................................................................................................................
119
USE PLAN.....................................................................................................................................119
Join Hints.............................................................................................................................................120
Table Hints..........................................................................................................................................122
Table Hint Syntax.........................................................................................................................
123
NOEXPAND................................................................................................................................
123
INDEX().........................................................................................................................................
124
FASTFIRSTROW.........................................................................................................................125
Summary..............................................................................................................................................
127
Chapter 6, Cursors Operations................................................................................................................
128
Simple Cursors...................................................................................................................................
128
viii
Logical Operators..........................................................................................................................129
Physical Operators.........................................................................................................................134
More Cursor Operations..................................................................................................................134
STATIC Cursor..............................................................................................................................134
KEYSET Cursor...........................................................................................................................137
READ_ONLY Cursor.................................................................................................................139
Cursors and Performance................................................................................................................139
Summary...............................................................................................................................................144
Chapter 7: XML in Execution Plans.......................................................................................................146
FOR XML............................................................................................................................................146
OPENXML.........................................................................................................................................151
XQuery.................................................................................................................................................154
Using the exist method................................................................................................................154
Using the query method..............................................................................................................156
Summary...............................................................................................................................................157
Chapter 8: Advanced Topics......................................................................................................................160
Reading Large Scale Execution Plans...........................................................................................160
Parallelism in Execution Plans........................................................................................................164
Max Degree of Parallelism..........................................................................................................164
Cost Threshold for Parallelism..................................................................................................165
Are Parallel Plans Good or Bad?...............................................................................................166
Examining a Parallel Execution Plan.......................................................................................166
How Forced Parameterization affects Execution Plans..........................................................169
Using Plan Guides to Modify Execution Plans.........................................................................172
Object Plan Guides.......................................................................................................................172
SQL Plan Guides...........................................................................................................................173
Template Plan Guides..................................................................................................................174
Plan Guide Administration.........................................................................................................175
Summary..........................................................................................................................................175
Using Plan Forcing to Modify Execution Plans........................................................................175
Summary...............................................................................................................................................177
Index................................................................................................................................................................178