SQL Server 2012 Internals and Troubleshooting

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

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

Algebrizing 112

Query Optimization 113

Parallel Plans 114

Algebrizer Trees 115

Infl uencing Optimization 123

Database Transactions 151

Atomicity 151

Consistency 152

Isolation 152

Durability 152

The Dangers of Concurrency 153

Lost Updates 153

Dirty Reads 155

Isolation Levels 175

Serializable 176

Repeatable Read 177

Read Committed 177

Defi nitions 186

Latching Example 187

Latch Types 194

Latch Modes 194

NL 195

KP 195

SH 195

UP 195

EX 195

Working with Stakeholders 245

Service-Level Agreements 246

Engaging External Help 247

Defi ning the Problem 248

Guidelines for Identifying the Problem 248

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

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

Creating Extended Events Sessions in SQL Server 2012 363

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

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

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

Sizing Tools 493

Non-Performance Related Requirements 493

Architecting Successful Virtual Database Servers 494

Information and Misinformation from Performance Monitor 503

Summary 507

INDEX 509