SQL Server
2012
Query
Performance
Tuning
Grant
Fritchey
Contents
J
About the Author
xxiii
About the Technical
Reviewer
xxvAcknowledgments
xxvii
Introduction
xxix
Chapter
1:
SQL Query
Performance
Tuning
1
The Performance
Tuning
Process
2
The Core Process 2
Iterating
the Process 4Performance
vs.Price
5
Performance
Targets
5"Good
Enough" Tuning
8Performance
Baseline
8
Where
to Focus Efforts
9
SQL
Server Performance Killers
10
Poor
Indexing
11Inaccurate Statistics 11
Poor
Query Design
11Poor ExecutionPlans 12
Excessive
Blocking
and Deadlocks 12Non-Set-Based
Operations
12Poor Database
Design
12CONTENTS
Nonreusable Execution Plans 13
Frequent Recompilation
ofQueries
13Improper
UseofCursors 14Improper Configuration
oftheDatabaseLog
14 Excessive UseorImproper
Configuration
oftempdb
14Summary
14
Chapter
2:
System
Performance
Analysis
...15
Performance Monitor Tool
15
Dynamic Management
Objects
17
Hardware Resource Bottlenecks
18
Identifying
Bottlenecks 18Bottleneck Resolution 19
Memory
Bottleneck
Analysis
19
SQL Server
Memory Management
19Available
Bytes
23Pages/sec
andPage
Faults/sec 23Paging
File%Usage
andPage
File%Usage
23Buffer Cache Hit Ratio 24
Page
LifeExpectancy
24Checkpoint
Pages/sec
24Lazy
writes/sec 24Memory
GrantsPending
25Target
ServerMemory (KB)
and Total ServerMemory (KB)
25Additional
Memory Monitoring
Tools
25
DBCC
Memorystatus
25Dynamic
Management Objects
26Memory
Bottleneck Resolutions
27
Optimizing Application
Workload 27Allocating
MoreMemory
toSQL
Server 29Increasing
System
Memory
29Changing
froma32-bittoa64-bit Processor 29 DataCompression
29Enabling
3GB of Process AddressSpace
29Disk Bottleneck
Analysis
30
DiskCounters 30
% Disk Time 31
CurrentDisk
Queue
Length
31Disk Transfers/sec 31
Disk
Bytes/sec
32Avg.
Disk Sec/Read andAvg.
Disk Sec/Write 32Additional I/O
Monitoring
Tools
32
Sys.dm_io_virtuaLfile_stats
32Sys.dm_os_wait_stats
33Disk Bottleneck Resolutions
33
Optimizing
Application
Workload 33Using
aFaster I/O Path 34Using
aRAIDArray
34RaidO 34
Raid 1 35
Raid5 35
Raid 6 35
RAID 1+0
(RAID 10)
36Using
aSANSystem
36Using
SSD Disks 36Aligning
DisksProperly
36Adding System Memory
37Creating Multiple
Files andFilegroups
37Placing
the Table and Index onSeparate
Disks 39Moving
theLog
Filesto aSeparate Physical
Disk 40Partitioning
Tables 40y.CONTENTS
Processor Bottleneck
Analysis
40
% Processor Time 41
%
Privileged
Time 41Processor
Queue
Length
42Context
Switches/sec 42Batch
Requests/sec
42SQL
Compilations/sec
42 SQLRecompilations/sec
42Other Tools
for
Measuring
CPU Performance
43
Sys.dm_os_wait_stats
43Sys.dm_os_workers
andSys.dm_os_schedulers
43Processor Bottleneck
Resolutions
43
Optimizing
Application
Workload 43Eliminating
ExcessiveCompiles/Recompiles
44Using
MoreorFaster Processors 44Using
aLarge
L2/L3 Cache 44Running
More Efficient Controllers/Drivers 44 NotRunning
Unnecessary
Software 45Network Bottleneck
Analysis
45
Bytes
Total/sec 45% Net Utilization 45
Network
Bottleneck Resolutions
46
Optimizing Application
Workload 46SQL Server Overall
Performance
46
Missing
Indexes
47Database
Blocking
48Nonreusable Execution Plans 49
General Behavior 49
User Connections 49
Batch
requests/sec
50Considerations
for
Monitoring
Virtual
Machines
50
Creating
aBaseline
50
Creating
aReusableList of Performance Counters 51Creating
aCounterLog
Using
the List of Performance Counters 52 Performance Monitor Considerations 54System
BehaviorAnalysis Against
Baseline 55Summary
57
Chapter
3:
SQL
Query
Performance
Analysis
59
Extended Events
Wizard
59
Extended Events Sessions 60
Events 61
Global Fields 64
Event Filters 66
Datastorage
66Finishing
the Wizard andStarting
the Session 68Extended
Events Automation
70
Creating
aSessionScript Using
the GUI 70Defining
aSessionUsing
Stored Procedures 71Extended
Events Recommendations
72
Set Max File Size
Appropriately
72Avoid
Debug
Events 72Partition
Memory
in the Sessions 72Avoid Use of
No_Event_Loss
73Other Methods
for
Query
Performance Metrics
73
Costly
Queries
74
Identifying Costly
Queries 75Costly
QuerieswithaSingle
Execution 75Costly
Queries withMultiple
Executions 76Identifying
Slow-Running Queries
78CONTENTS
Execution Plans
79
Analyzing
aQuery
Execution Plan 81Identifying
theCostly
Steps
inan ExecutionPlan 83Analyzing Index
Effectiveness 85Analyzing
Join Effectiveness 86Hash Join 87
Actualvs. Estimated Execution Plans 90
Plan Cache 92
Query
Cost 93 Client Statistics 93 Execution Time 94 Statistics lo 95Summary
97
Chapter
4: Index
Analysis
99
What Is
anIndex?
99
The Benefit of Indexes 101
Index Overhead 103
Index
Design
Recommendations
105
Examinethe WHEREClauseandJOIN Criteria Columns 105
Use Narrow Indexes 107
Examine Column
Uniqueness
109Examine the Column Data
Type
112Consider Column Order 112
Consider the
Type
of Index 115Clustered Indexes
115
Heap
Tables 115Relationship
withNonclustered Indexes 116 Clustered Index Recommendations 118When to Usea Clustered Index 120
Poor
Design
PracticesforaClustered Index 121Nonclustered Indexes
122
Nonclustered
Index Maintenance 123Defining
the BookmarkLookup
123Nonclustered
Index Recommendations 123Clustered
vs.Nonclustered
Indexes
124
Benefits ofaClustered Indexover aNonclustered Index 124 Benefits ofa Nonclustered Indexover aClustered Index 126
Advanced
Indexing
Techniques
128
Covering
Indexes 128 APseudoclusteredIndex 130 Recommendations 130 Index Intersections 130 IndexJoins 132 Filtered Indexes 134 IndexedViews 136 IndexCompression
141 ColumnStore Indexes 142Special
Index
Types
144
Full-Text 144
Spatial
145XML 145
Additional
Characteristics of Indexes
145
Different Column Sort Order 145
Indexon
Computed
Columns 146IndexonBIT Data
Type
Columns 146 CREATE INDEX StatementProcessedAsaQuery
146Parallel IndexCreation 146
Online Index Creation 147
Considering
theDatabaseEngine Tuning
Advisor 147HCONTENTS
Chapter
5: Database
Engine
Tuning
Advisor
149
Database
Engine Tuning
Advisor Mechanisms
149
Database
Engine Tuning
Advisor
Examples
154
Tuning
aQuery
154Tuning
aTraceWorkload 158Tuning
from the Procedure Cache 161Database
Engine Tuning
Advisor
Limitations
161
Summary
162
Chapter
6:
Lookup
Analysis
163
Purpose
of
Lookups
163
Drawbacks of
Lookups
165
Analyzing
the Cause of
aLookup
166
Resolving
Lookups
168
Using
aClustered Index 168Using
aCovering
Index 168Using
an IndexJoin 172Summary
174
Chapter
7: Statistics
Analysis
175The Role of Statistics in
Query
Optimization
175
Statistics
onanIndexed Column 176 Benefits ofUpdated
Statistics 176 Drawbacks of Outdated Statistics 179Statistics
on aNonindexed Column
181
Benefits of Statisticson aNonindexed Column 181
Drawback of
Missing
Statisticson aNonindexed Column 186Analyzing
Statistics
188
Density
190Statisticson aMulticolumn Index 191
StatisticsonaFilteredIndex 192
Statistics Maintenance
193
Automatic Maintenance 194
Auto Create Statistics 194
Auto
Update
Statistics
195 AutoUpdate
StatisticsAsynchronously
195Manual
Maintenance
196Manage
StatisticsSettings
197Generate Statistics 198
Statistics Maintenance Status
199
Status
of
Auto Create Statistics 199Status of Auto
Update
Statistics 200Analyzing
the Effectiveness
of Statistics for
aQuery
200
Resolving
aMissing
Statistics Issue 200Resolving
anOutdated Statistics Issue 202Recommendations
205
Backward
Compatibility
of Statistics 205Auto Create Statistics 206
Auto
Update
Statistics
206 AutomaticUpdate
StatisticsAsynchronously
206Amountof
Sampling
to Collect Statistics 207Summary
208
Chapter
8:
Fragmentation
Analysis
211
Causes
of
Fragmentation
211
Page Split by
anUPDATE Statement 213Page
Split
by
anINSERT Statement 217Fragmentation
Overhead
218
Analyzing
the Amount of
Fragmentation
221
Analyzing
the
Fragmentation
of
aSmall Table
223
CONTENTS
Fragmentation
Resolutions
225
Dropping
andRe-creating
the Index 225Re-creating
the Index with theDROP_EXISTING Clause 226Executing
theALTER INDEX REBUILD Statement 227Executing
the ALTER INDEX REORGANIZEStatement 229Significance
of the Fill Factor
230
Automatic
Maintenance
233
Summary
239
Chapter
9: Execution Plan Cache
Analysis
241
Execution Plan
Generation
241
Parser 243
Binding
243Optimization
244ExecutionPlan
Caching
251Components
of
the Execution Plan
251
Query
Plan 251Execution Context 251
Aging
of the
Execution
Plan
251
Analyzing
the Execution Plan
Cache
252
Execution Plan
Reuse
253
Ad
Hoc
Workload
254
Prepared
Workload 254 PlanReusability
ofanAd HocWorkload 255 PlanReusability
ofaPrepared
Workload
262Parameter
Sniffing
270Query
Plan
Hash and
Query
Hash
273
Execution
Plan Cache Recommendations
276
Explicitly
Parameterize Variable Parts ofaQuery
277 Create StoredProcedures
toImplement
BusinessFunctionality
277 xivCode with
sp_executesql
to Avoid StoredProcedure
Maintenance 277Implement
thePrepare/Execute
Model to AvoidResending
aQuery
String
277Avoid Ad Hoc
Queries
278Prefer
sp_executesql
overEXECUTEforDynamic
Queries 278 Parameterize Variable Parts of Queries with Care 279Do Not Allow
Implicit
ResolutionofObjects
in Queries 279Summary
279
Chapter
10:
Query Recompilation
281
Benefits and
Drawbacks
of
Recompilation
281
Identifying
the Statement
Causing
Recompilation
284
Analyzing
Causes of
Recompilation
285
Schema or
Bindings Changes
287Statistics
Changes
287Deferred
Object
Resolution 289SET
Options
Changes
293Execution Plan
Aging
294Explicit
Call tosp_recompile
294Explicit
Use ofRECOMPILE 295Avoiding
Recompilations
297
Don't Interleave DDL and DML Statements 297
Avoiding Recompilations
Causedby
StatisticsChange
299Using
the KEEPFIXED PLANOption
299Disable
AutoUpdate
Statisticsonthe Table 301Using
Table Variables 301Avoiding Changing
SETOptions
WithinaStoredProcedure 304Using
OPTIMIZE FORQuery
Hint 305Using
Plan Guides 307Summary
311
CONTENTS
Chapter
11:Query Design
Analysis
...313
Query Design
Recommendations
313
Operating
onSmall Result Sets
314
Limit the Number of Columns in
selectjist
314
Use
Highly
Selective WHERE Clauses 315Using
IndexesEffectively
315 AvoidNonsargable
Search Conditions 316BETWEENvs.IN/OR 316
Avoid Arithmetic
Operators
onthe WHEREClauseColumn 319 Avoid Functionsonthe WHERE Clause Column 320Avoiding Optimizer
Hints
323
JOIN Hint 323
INDEX Hints 327
Using
Domain
and
Referential
Integrity
327
NOT
NULL
Constraint 328Declarative Referential
Integrity
331Avoiding
Resource-Intensive
Queries
333
Avoid Data
Type
Conversion 333Use EXISTSover
C0UNT(*)
toVerify
Data Existence335
Use UNIONALLInstead ofUNION 336 Use Indexes forAggregate
and Sort Conditions 337 Avoid Local Variablesin aBatchQuery
338 Be Careful WhenNaming
Stored Procedures 341Reducing
the Number of Network
Round-Trips
343
Execute
Multiple
QueriesTogether
343 UseSETN0C0UNT
343Reducing
the Transaction Cost
344
Reduce
Logging
Overhead 344Reduce LockOverhead 345
Summary
xvi
Chapter
12:
Blocking Analysis
349
Blocking
Fundamentals
349
Understanding
Blocking
350
Atomicity
350Consistency
353 Isolation 353Durability
354Locks
354
LockGranularity
355 Row-Level Lock 355Page-Level
Lock 357 Extent-Level Lock 358Heap
orB-tree Lock 358Table-Level Lock 358
Database-Level Lock 359
Lock
Operations
and Modes
359
Lock Escalation 359
Lock
Modes 359Exclusive
(X)
Mode 364Intent Shared
(IS),
Intent Exclusive(IX),
and Shared withIntent Exclusive(SIX)
Modes 364 Schema Modification(Sch-M)
andSchemaStability
(Sch-S)
Modes 365 BulkUpdate
(BU)
Mode 365Key-range
Mode 365 LockCompatibility
366Isolation Levels
366
Read Uncommitted 366 Read Committed 367Repeatable
Read 368 Serializable 371Snapshot
376 xviiCONTENTS
Effect of Indexes
onLocking
376
Effect ofa NonclusteredIndex 377
Effect ofaClustered Index 379
Effect of IndexesontheSerializable Isolation Level 380
Capturing Blocking
Information
380
Capturing
Blocking
Informationwith SQL 381 Extended Events and theblocked_process_report
Event 383Blocking
Resolutions
385
Optimize
the Queries 386Decreasethe Isolation Level 386
Partition the Contended Data
387
Recommendations
to Reduce
Blocking
388
Automation to Detect and Collect
Blocking
Information
389
Summary
392
Chapter
13: Deadlock
Analysis
393
Deadlock Fundamentals
393
Choosing
the Deadlock Victim 394Using
ErrorHandling
to CatchaDeadlock 394Deadlock
Analysis
395
Collecting
DeadlockInformation 395Analyzing
the Deadlock 398Avoiding
Deadlocks
403
Accessing
ResourcesintheSamePhysical
Order 403Decreasing
the Number of Resources Accessed 404 ConvertaNonclustered Index toaClustered Index 404 Use aCovering
Index foraSELECT Statement 404Minimizing
LockContention 404Summary
406
Chapter
14:
Cursor Cost
Analysis
407
Cursor Fundamentals
407
Cursor Location 409
Cursor
Concurrency
410Cursor
Types
411Cursor Cost
Comparison
414Cost
Comparison
on Cursor Location 414 CostComparison
on CursorConcurrency
416Read-Only
416Cost
Comparison
onCursorType
417Default Result Set
419
Benefits 420
Multiple
Active
ResultSets 420Drawbacks 421
Cursor
Overhead
423
Analyzing
OverheadwithT-SQL Cursors 423Cursor Recommendations 427
Summary
428
Chapter
15: Database
Performance
Testing
429
Database Performance
Testing
429
A
Repeatable
Process 430Distributed
Replay
430Capturing
Data with
the Server Side Trace
431
Distributed
Replay
for Database
Testing
434
Configuring
the Client 435Running
theDistributedTests 436Conclusion
436
CONTENTS
Chapter
16:
Database
Workload
Optimization..
437
Workload
Optimization
Fundamentals
437
Workload Optimization Steps
438Sample
Workload 439Capturing
the Workload
441
Analyzing
the
Workload
442
Identifying
the
Costliest
Query
444
Determining
theBaseline
Resource Use of the CostliestQuery
445Overall
Resource Use 445Detailed ResourceUse 446
Analyzing
and
Optimizing
External Factors
448
Analyzing
theConnection Options
Usedby
theApplication
449Analyzing
theEffectiveness of Statistics 449Analyzing
the Need forDefragmentation
450Analyzing
the Internal Behavior of the CostliestQuery
453Analyzing
theQuery
Execution Plan 454Identifying
theCostly Steps
in theExecution
Plan 456Analyzing
theProcessing
Strategy
456Optimizing
the CostliestQuery
457Modifying
anExisting
Index 457Analyzing
theApplication
ofaJoin Hint 459Avoiding
theClustered
Index ScanOperation
461Modifying
the Procedure 462Analyzing
the
Effect
onDatabase
Workload
464
Iterating Through
Optimization
Phases
466
Summary
468
Chapter
17:
SQL
Server
Optimization
Checklist
469
Database
Design
469
Balancing
Under- andOvemormalization
470Benefiting
fromEntity-Integrity
Constraints 471Benefiting
from Domain and ReferentialIntegrity
Constraints 472Adopting Index-Design
Best Practices 474Avoiding
the Use of the sp_ Prefix for Stored Procedure Names 476Minimizing
the Use ofTriggers
476Query
Design
476
Use the Command SET NOCOUNT ON 476
Explicitly
Define theOwner
of anObject
477Avoid
Nonsargable
Search Conditions 477Avoid
Arithmetic Expressions
onthe WHERE Clause Column 477Avoid
Optimizer
Hints 478Stay Away
fromNesting
Views 478Ensure No
Implicit
DataType
Conversions 479Minimize
Logging
Overhead 479Adopt
Best Practices forReusing
Execution Plans 479Adopt
Best Practices for Database Transactions 480 EliminateorReduce theOverhead of
Database Cursors 481Configuration
Settings
481
Affinity
Mask 481Memory Configuration Options
482 Cost Threshold for Parallelism 482 MaxDegree
ofParallelism 482Optimize
for Ad Hoc Workloads 482Query
Governor Cost Limit 482Fill Factor
(%)
482Blocked Process Threshold 483
CONTENTS
Database File
Layout
483Database
Compression
483Database Administration
483
Keep
the StatisticsUp-to-Date
484MaintainaMinimum Amount of Index Defragmentation 484
Cycle
the SQL ErrorLog
File 485 Avoid Database Functions SuchAsAUTO_CLOSE
orAUTO_SHRINK
485Database
Backup
485
Incremental