Hands-on troubleshooting methods on the most recent release of SQL Server

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.

Introduction xxix

Part 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 Buffer 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 Affects 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 Configuring 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 Buffer Pool 64

Optimizing SQL Server Memory Configuration 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

Influencing 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 Effect 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

Definitions 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

Configuration 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

Defining 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 Profiler 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 Configuration Manager 305

Configuring SQLdiag Data Collection Using Diag Manager 307

Adding Trace Filters to a SQLdiag Configuration 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 Efficiency 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 Benefits 474

Technical Benefits 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