PART I: INTERNALS
1.
CHAPTER 1: SQL SERVER ARCHITECTURE 3
1.1. Introduction
3
1.2. Database
Transactions 4
1.2.1.
ACID Properties 4
1.2.2.
SQL Server Transactions 5
1.3. The
Life Cycle of a Query 5
1.3.1.
The Relational and Storage Engines 6
1.3.2.
The Buffer Pool 6
1.3.3.
A Basic SELECT Query 7
1.3.4.
A Simple Update Query 15
1.3.5.
Recovery 18
1.4. SQL
Server’s Execution Model and the SQLOS 22
1.4.1.
Execution Model 22
1.4.2.
The SQLOS 25
1.5. Summary
26
2.
CHAPTER 2: DEMYSTIFYING HARDWARE 29
2.1. The
Importance of Hardware 29
2.2. How
Workload Affects Hardware and Storage Considerations 30
2.2.1.
Workload Types 30
2.2.2.
Server Model Selection 32
2.2.3.
Server Model Evolution 33
2.3. Processor
Vendor Selection 35
2.3.1.
Intel Processors 35
2.3.2.
AMD Processors and Numbering 43
2.4. Choosing
and Configuring Hardware for Redundancy 46
2.5. Hardware
Comparison Tools 48
2.5.1.
TPC-E Benchmark 48
2.5.2.
Geekbench Benchmark 50
2.6. Summary
51
3.
CHAPTER 3: UNDERSTANDING MEMORY 53
3.1. Introduction
53
3.2. Physical
and Virtual Memory 54
3.2.1.
Physical Memory 54
3.2.2.
Maximum Supported Physical Memory 55
3.2.3.
Virtual Memory 56
3.2.4.
NUMA 59
3.3. SQL
Server Memory 63
3.3.1.
Memory Nodes 64
3.3.2.
Clerks, Caches, and the Buffer Pool 64
3.4. Optimizing
SQL Server Memory Configuration 70
3.4.1.
Min and Max Server Memory 70
3.4.2.
Lock Pages in Memory 72
3.4.3.
Optimize for Ad-Hoc Workloads 74
3.5. Summary
76
4.
CHAPTER 4: STORAGE SYSTEMS 77
4.1. Introduction
77
4.2. SQL
Server I/O 78
4.3. Storage
Technology 78
4.3.1.
SQL Server and the Windows I/O Subsystem 82
4.3.2.
Choosing the Right Storage Networks 84
4.3.3.
Shared Storage Arrays 86
4.3.4.
Capacity Optimization 86
4.3.5.
Storage Tiering 88
4.3.6.
Data Replication 89
4.3.7.
Remote Data Replication 92
4.3.8.
Windows Failover Clustering 93
4.3.9.
SQL Server AlwaysOn Availability Groups 94
4.3.10.
Risk Mitigation Planning 94
4.4. Measuring
Performance 95
4.4.1.
Storage Performance Counters 96
4.4.2.
Disk Drive Performance 97
4.4.3.
Sequential Disk Access 100
4.4.4.
Server Queues 101
4.4.5.
File Layout 101
4.4.6.
Partition Alignment 103
4.4.7.
NTFS Allocation Unit Size 104
4.4.8.
Flash Storage 104
4.4.9.
Storage Performance Testing 106
4.5. Summary
110
5.
CHAPTER 5: QUERY PROCESSING AND EXECUTION 111
5.1. Introduction
111
5.2. Query
Processing 112
5.2.1.
Parsing 112
5.2.2.
Algebrizing 112
5.3. Query
Optimization 113
5.3.1.
Parallel Plans 114
5.3.2.
Algebrizer Trees 115
5.3.3.
sql_handle or plan_handle 115
5.3.4.
Understanding Statistics 116
5.3.5.
Plan Caching and Recompilation 117
5.3.6.
Influencing Optimization 123
5.4. Query
Plans 129
5.4.1.
Query Plan Operators 132
5.4.2.
Reading Query Plans 135
5.5. Executing
Your Queries 140
5.5.1.
SQLOS 140
5.6. Summary
147
6.
CHAPTER 6: LOCKING AND CONCURRENCY 149
6.1. Overview
149
6.2. Transactions
150
6.2.1.
A Is for Atomic 150
6.2.2.
C Is for Consistent 151
6.2.3.
I Is for Isolated 151
6.2.4.
D Is for Durable 151
6.3. Database
Transactions 151
6.3.1.
Atomicity 151
6.3.2.
Consistency 152
6.3.3.
Isolation 152
6.3.4.
Durability 152
6.4. The
Dangers of Concurrency 153
6.4.1.
Lost Updates 153
6.4.2.
Dirty Reads 155
6.4.3.
Non-Repeatable Reads 156
6.4.4.
Phantom Reads 158
6.4.5.
Double Reads 161
6.4.6.
Halloween Effect 162
6.5. Locks
163
6.5.1.
Monitoring Locks 163
6.5.2.
Lock Resources 165
6.5.3.
Lock Modes 167
6.5.4.
Compatibility Matrix 173
6.6. Lock
Escalation 174
6.7. Deadlocks
175
6.8. Isolation
Levels 175
6.8.1.
Serializable 176
6.8.2.
Repeatable Read 177
6.8.3.
Read Committed 177
6.8.4.
Read Uncommitted/NOLOCK 178
6.8.5.
Snapshot 178
6.8.6.
Read Committed Snapshot 178
6.9. Summary
179
7.
CHAPTER 7: LATCHES AND SPINLOCKS 181
7.1. Overview
181
7.2. Symptoms
182
7.2.1.
Recognizing Symptoms 182
7.2.2.
Measuring Latch Contention 183
7.2.3.
Measuring Spinlock Contention 184
7.2.4.
Contention Indicators 185
7.3. Susceptible
Systems 185
7.4. Understanding
Latches and Spinlocks 186
7.4.1.
Definitions 186
7.4.2.
Latching Example 187
7.5. Latch
Types 194
7.6. Latch
Modes 194
7.6.1.
NL 195
7.6.2.
KP 195
7.6.3.
SH 195
7.6.4.
UP 195
7.6.5.
EX 195
7.6.6.
DT 195
7.6.7.
Latch Compatibility 196
7.6.8.
Grant Order 196
7.6.9.
Latch Waits 197
7.7. SuperLatches/Sublatches
198
7.8. Monitoring
Latches and Spinlocks 199
7.8.1.
DMVs 199
7.8.2.
Performance Monitor 201
7.8.3.
Extended Events 202
7.9. Latch
Contention Examples 203
7.9.1.
Inserts When the Clustered Index Key Is an
Identity Field 203
7.9.2.
Queuing 205
7.9.3.
UP Latches in tempdb 208
7.9.4.
Spinlock Contention in Name Resolution 209
7.10.
Summary 209
8.
CHAPTER 8: KNOWING TEMPDB 211
8.1. Introduction
211
8.2. Overview
and Usage 212
8.2.1.
User Temporary Objects 213
8.2.2.
Internal Temporary Objects 217
8.2.3.
The Version Store 217
8.3. Troubleshooting
Common Issues 220
8.3.1.
Latch Contention 220
8.3.2.
Monitoring Tempdb I/O Performance 229
8.3.3.
Troubleshooting Space Issues 231
8.4. Configuration
Best Practices 232
8.4.1.
Tempdb File Placement 232
8.4.2.
Tempdb Initial Sizing and Autogrowth 234
8.4.3.
Configuring Multiple Tempdb Data Files 237
8.5. Summary
237
PART II:
TROUBLESHOOTING TOOLS AND LESSONS FROM THE FIELD
