The 2012 release of SQL Server is the most significant one since 2005 and introduces an abundance of new features. This critical book provides in-depth coverage of best practices for troubleshooting performance problems based on a solid understanding of both SQL Server and Windows internals and shows experienced DBAs how to ensure reliable performance. The team of authors shows you how to master the use of specific troubleshooting tools and how to interpret their output so you can quickly identify and resolve any performance issue on any server running SQL Server.
- Covers the core technical topics required to understand how SQL Server and Windows should be working
- Shares best practices so that you know how to proactively monitor and avoid problems
- Shows how to use tools to quickly gather, analyze, and effectively respond to the source of a system-wide performance issue
Professional SQL Server 2012 Internals and Troubleshooting helps you to quickly become familiar with the changes of this new release so that you can best handle database performance and troubleshooting.
Table of Contents
INTRODUCTION xxixPART I: INTERNALS
CHAPTER 1: SQL SERVER ARCHITECTURE 3
Introduction 3
Database Transactions 4
ACID Properties 4
SQL Server Transactions 5
The Life Cycle of a Query 5
The Relational and Storage Engines 6
The Buff er Pool 6
A Basic SELECT Query 7
A Simple Update Query 15
Recovery 18
SQL Server’s Execution Model and the SQLOS 22
Execution Model 22
The SQLOS 25
Summary 26
CHAPTER 2: DEMYSTIFYING HARDWARE 29
The Importance of Hardware 29
How Workload Aff ects Hardware
and Storage Considerations 30
Workload Types 30
Server Model Selection 32
Server Model Evolution 33
Processor Vendor Selection 35
Intel Processors 35
AMD Processors and Numbering 43
Choosing and Confi guring Hardware for Redundancy 46
Hardware Comparison Tools 48
TPC-E Benchmark 48
Geekbench Benchmark 50
Summary 51
CHAPTER 3: UNDERSTANDING MEMORY 53
Introduction 53
Physical and Virtual Memory 54
Physical Memory 54
Maximum Supported Physical Memory 55
Virtual Memory 56
NUMA 59
SQL Server Memory 63
Memory Nodes 64
Clerks, Caches, and the Buff er Pool 64
Optimizing SQL Server Memory Confi guration 70
Min and Max Server Memory 70
Lock Pages in Memory 72
Optimize for Ad-Hoc Workloads 74
Summary 76
CHAPTER 4: STORAGE SYSTEMS 77
Introduction 77
SQL Server I/O 78
Storage Technology 78
SQL Server and the Windows I/O Subsystem 82
Choosing the Right Storage Networks 84
Shared Storage Arrays 86
Capacity Optimization 86
Storage Tiering 88
Data Replication 89
Remote Data Replication 92
Windows Failover Clustering 93
SQL Server AlwaysOn Availability Groups 94
Risk Mitigation Planning 94
Measuring Performance 95
Storage Performance Counters 96
Disk Drive Performance 97
Sequential Disk Access 100
Server Queues 101
File Layout 101
Partition Alignment 103
NTFS Allocation Unit Size 104
Flash Storage 104
Storage Performance Testing 106
Summary 110
CHAPTER 5: QUERY PROCESSING AND EXECUTION 111
Introduction 111
Query Processing 112
Parsing 112
Algebrizing 112
Query Optimization 113
Parallel Plans 114
Algebrizer Trees 115
sql_handle or plan_handle 115
Understanding Statistics 116
Plan Caching and Recompilation 117
Infl uencing Optimization 123
Query Plans 129
Query Plan Operators 132
Reading Query Plans 135
Executing Your Queries 140
SQLOS 140
Summary 147
CHAPTER 6: LOCKING AND CONCURRENCY 149
Overview 149
Transactions 150
A Is for Atomic 150
C Is for Consistent 151
I Is for Isolated 151
D Is for Durable 151
Database Transactions 151
Atomicity 151
Consistency 152
Isolation 152
Durability 152
The Dangers of Concurrency 153
Lost Updates 153
Dirty Reads 155
Non-Repeatable Reads 156
Phantom Reads 158
Double Reads 161
Halloween Eff ect 162
Locks 163
Monitoring Locks 163
Lock Resources 165
Lock Modes 167
Compatibility Matrix 173
Lock Escalation 174
Deadlocks 175
Isolation Levels 175
Serializable 176
Repeatable Read 177
Read Committed 177
Read Uncommitted/NOLOCK 178
Snapshot 178
Read Committed Snapshot 178
Summary 179
CHAPTER 7: LATCHES AND SPINLOCKS 181
Overview 181
Symptoms 182
Recognizing Symptoms 182
Measuring Latch Contention 183
Measuring Spinlock Contention 184
Contention Indicators 185
Susceptible Systems 185
Understanding Latches and Spinlocks 186
Defi nitions 186
Latching Example 187
Latch Types 194
Latch Modes 194
NL 195
KP 195
SH 195
UP 195
EX 195
DT 195
Latch Compatibility 196
Grant Order 196
Latch Waits 197
SuperLatches/Sublatches 198
Monitoring Latches and Spinlocks 199
DMVs 199
Performance Monitor 201
Extended Events 202
Latch Contention Examples 203
Inserts When the Clustered Index Key Is an Identity Field 203
Queuing 205
UP Latches in tempdb 208
Spinlock Contention in Name Resolution 209
Summary 209
CHAPTER 8: KNOWING TEMPDB 211
Introduction 211
Overview and Usage 212
User Temporary Objects 213
Internal Temporary Objects 217
The Version Store 217
Troubleshooting Common Issues 220
Latch Contention 220
Monitoring Tempdb I/O Performance 229
Troubleshooting Space Issues 231
Confi guration Best Practices 232
Tempdb File Placement 232
Tempdb Initial Sizing and Autogrowth 234
Confi guring Multiple Tempdb Data Files 237
Summary 237
PART II: TROUBLESHOOTING TOOLS AND LESSONS FROM THE FIELD
CHAPTER 9: TROUBLESHOOTING METHODOLOGY AND PRACTICES 241
Introduction 241
Approaching Problems 242
Ten Steps to Successful Troubleshooting 242
Behavior and Attitude 244
Success Criteria 245
Working with Stakeholders 245
Service-Level Agreements 246
Engaging External Help 247
Defi ning the Problem 248
Guidelines for Identifying the Problem 248
Isolating the Problem 249
Performance Bottlenecks 250
Data Collection 252
Focused Data Collection 253
Understanding Data Gathering 253
Tools and Utilities 254
Data Analysis 255
Validating and Implementing Resolution 256
Validating Changes 256
Testing Changes in Isolation 256
Implementing Resolution 257
Summary 257
CHAPTER 10: VIEWING SERVER PERFORMANCE WITH
PERFMON AND THE PAL TOOL 259
Introduction 259
Performance Monitor Overview 260
Reliability and Performance Monitor 260
New PerfMon Counters for SQL Server 2012 263
Getting Started with PerfMon 268
Getting More from Performance Monitor 278
Bottlenecks and SQL Server 278
Prescriptive Guidance 279
Wait Stats Analysis 284
Getting a Performance Baseline 285
Performance Analysis of Logs 285
Getting Started with PAL 285
Other PerfMon Log Analysis Tools 289
Using SQL Server to Analyze PerfMon Logs 289
Combining PerfMon Logs and SQL Profi ler Traces 289
Using Relog 290
Using LogMan 291
Using LogParser 293
Summary 293
CHAPTER 11: CONSOLIDATING DATA CAPTURE WITH SQLDIAG 295
The Data Collection Dilemma 295
An Approach to Data Collection 296
Getting Friendly with SQLdiag 297
Using SQLdiag in Snapshot Mode 298
Using SQLdiag as a Command-line Application 299
Using SQLdiag as a Service 303
Using SQLdiag Confi guration Manager 305
Confi guring SQLdiag Data Collection Using Diag Manager 307
Adding Trace Filters to a SQLdiag Confi guration 310
Employing Best Practices 318
Gearing Up for Long-Term Data Collection 319
Filtering Out the Noise 320
Alert-Driven Data Collection with SQLdiag 322
Summary 323
CHAPTER 12: BRINGING IT ALL TOGETHER WITH SQL NEXUS 325
Introducing SQL Nexus 325
Getting Familiar with SQL Nexus 326
Prerequisites 326
Loading Data into a Nexus Database 328
Analyzing the Aggregated Data 331
Customizing SQL Nexus 340
Using ReadTrace.exe 341
Building Custom Reports for SQL Nexus 342
Running SQL Nexus Using the Command Prompt 342
Creating Your Own Tables in the SQL Nexus Database 342
Writing Your Own Queries 344
The OSTRESS Executable 344
Resolving Common Issues 346
Issue #1 346
Issue #2 346
Issue #3 346
Issue #4 347
Summary 348
CHAPTER 13: DIAGNOSING SQL SERVER 2012 USING
EXTENDED EVENTS 349
Introduction to Extended Events 349
Getting Familiar with Extended Events 350
Why You Should Be Using Extended Events 351
SQL Server Roadmap 351
Graphical Tools 351
Low Impact 351
When You Might Use Extended Events 352
What Are Extended Events? 352
Where the Name Extended Events Comes From 353
Extended Events Terminology 354
Creating Extended Events Sessions in SQL Server 2012 363
Introduction to the New Session Form 363
Monitoring Server Logins 366
Monitoring for Page Splits with Extended Events 367
Counting the Number of Locks Acquired per Object 369
Creating Sessions Using T-SQL 370
Viewing Data Captured by Extended Events 371
Viewing Event File Data 371
Summary 376
CHAPTER 14: ENHANCING YOUR TROUBLESHOOTING
TOOLSET WITH POWERSHELL 379
Introducing PowerShell 379
Getting Started with PowerShell 380
The PowerShell Environment 381
The Basics - Cmdlets, Variables, Advanced Functions,
and Modules 383
Working Remotely 390
What’s New in SQL Server 2012 391
Using PowerShell to Investigate Server Issues 393
Interrogating Disk Space Utilization 393
Interrogating Current Server Activity 394
Interrogating for Warnings and Errors 396
Interrogating Server Performance 396
Proactively Tuning SQL Server Performance with PowerShell 397
Index Maintenance 397
Managing Disk Space Utilization of Backups 398
Extracting DDL Using SMO 398
Scheduling Script Execution 403
Summary 404
CHAPTER 15: DELIVERING A SQL SERVER HEALTH CHECK 405
The Importance of a SQL Server Health Check 405
Running DMV and DMF Queries 406
SQL Server Builds 408
Database-Level Queries 426
Summary 442
CHAPTER 16: DELIVERING MANAGEABILITY AND PERFORMANCE 445
Improve Effi ciency with SQL Server Manageability Features 445
Manageability Enhancements in SQL Server 2012 446
Policy-Based Management 447
Overview 447
Other Microsoft Tools for Managing SQL Server 460
System Center Advisor 461
System Center Operations Manager 464
Summary 466
CHAPTER 17: RUNNING SQL SERVER IN A VIRTUAL ENVIRONMENT 469
The Shift to Server Virtualization 469
An Overview of Virtualization 470
History of Virtualization 471
The Breadth of Virtualization 472
Platform Virtualization 472
Cloud Computing 473
Why Virtualize a Server? 473
Business Benefi ts 474
Technical Benefi ts 474
Encapsulation 475
SQL Server 2012 and Virtualization 476
Limitations of Virtualization 477
Common Virtualization Products 477
VMware 477
Microsoft Hyper-V 478
Xen 479
Hardware Support for Virtualization 479
Virtualization Concepts 480
Host Server 480
Hypervisor 480
Virtual Server (or Guest Server or Virtual Machine) 482
Extended Features of Virtualization 483
Snapshotting 483
High-Availability Features 483
Online Migration 484
Highly Available Virtual Servers 486
Host and Guest Clustering 487
Deploying SQL Server with Virtualization’s High-Availability Features 487
Managing Contention 488
Good Contention 488
Bad Contention 488
Demand-Based Memory Allocation 489
Weighting 490
Identifying Candidates for Virtualization 491
Guiding Principles 491
Server Workload 491
Gathering Sizing Data 492
Sizing Tools 493
Non-Performance Related Requirements 493
Architecting Successful Virtual Database Servers 494
Architecting Virtual Database Servers vs. Physical Database Servers 494
Virtual Database Server Design 495
Monitoring Virtualized Database Servers 502
Information and Misinformation from Performance Monitor 503
Summary 507
INDEX 509
