Table of Contents for Professional SQL Server 2012 Internals and Troubleshooting by Christian Bolton, Justin Langford, Glenn Berry, Gavin Payne, Amit Banerjee, Rob Farley


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

9.    CHAPTER 9: TROUBLESHOOTING METHODOLOGY AND PRACTICES 241

9.1.  Introduction 241

9.2.  Approaching Problems 242

9.2.1.     Ten Steps to Successful Troubleshooting 242

9.2.2.     Behavior and Attitude 244

9.2.3.     Success Criteria 245

9.2.4.     Working with Stakeholders 245

9.2.5.     Service-Level Agreements 246

9.2.6.     Engaging External Help 247

9.3.  Defining the Problem 248

9.3.1.     Guidelines for Identifying the Problem 248

9.3.2.     Isolating the Problem 249

9.3.3.     Performance Bottlenecks 250

9.4.  Data Collection 252

9.4.1.     Focused Data Collection 253

9.4.2.     Understanding Data Gathering 253

9.4.3.     Tools and Utilities 254

9.5.  Data Analysis 255

9.6.  Validating and Implementing Resolution 256

9.6.1.     Validating Changes 256

9.6.2.     Testing Changes in Isolation 256

9.6.3.     Implementing Resolution 257

9.7.  Summary 257

10.CHAPTER 10: VIEWING SERVER PERFORMANCE WITH PERFMON AND THE PAL TOOL 259

10.1.                Introduction 259

10.2.                Performance Monitor Overview 260

10.2.1.  Reliability and Performance Monitor 260

10.2.2.  New PerfMon Counters for SQL Server 2012 263

10.2.3.  Getting Started with PerfMon 268

10.3.                Getting More from Performance Monitor 278

10.3.1.  Bottlenecks and SQL Server 278

10.3.2.  Prescriptive Guidance 279

10.3.3.  Wait Stats Analysis 284

10.3.4.  Getting a Performance Baseline 285

10.4.                Performance Analysis of Logs 285

10.4.1.  Getting Started with PAL 285

10.5.                Other PerfMon Log Analysis Tools 289

10.5.1.  Using SQL Server to Analyze PerfMon Logs 289

10.5.2.  Combining PerfMon Logs and SQL Profi ler Traces 289

10.5.3.  Using Relog 290

10.5.4.  Using LogMan 291

10.5.5.  Using LogParser 293

10.6.                Summary 293

11.CHAPTER 11: CONSOLIDATING DATA CAPTURE WITH SQLDIAG 295

11.1.                The Data Collection Dilemma 295

11.2.                An Approach to Data Collection 296

11.3.                Getting Friendly with SQLdiag 297

11.3.1.  Using SQLdiag in Snapshot Mode 298

11.3.2.  Using SQLdiag as a Command-line Application 299

11.3.3.  Using SQLdiag as a Service 303

11.4.                Using SQLdiag Configuration Manager 305

11.4.1.  Configuring SQLdiag Data Collection Using Diag Manager 307

11.4.2.  Adding Trace Filters to a SQLdiag Configuration 310

11.5.                Employing Best Practices 318

11.5.1.  Gearing Up for Long-Term Data Collection 319

11.5.2.  Filtering Out the Noise 320

11.5.3.  Alert-Driven Data Collection with SQLdiag 322

11.6.                Summary 323

12.CHAPTER 12: BRINGING IT ALL TOGETHER WITH SQL NEXUS 325

12.1.                Introducing SQL Nexus 325

12.2.                Getting Familiar with SQL Nexus 326

12.2.1.  Prerequisites 326

12.2.2.  Loading Data into a Nexus Database 328

12.2.3.  Analyzing the Aggregated Data 331

12.3.                Customizing SQL Nexus 340

12.3.1.  Using ReadTrace.exe 341

12.3.2.  Building Custom Reports for SQL Nexus 342

12.3.3.  Running SQL Nexus Using the Command Prompt 342

12.3.4.  Creating Your Own Tables in the SQL Nexus Database 342

12.3.5.  Writing Your Own Queries 344

12.3.6.  The OSTRESS Executable 344

12.4.                Resolving Common Issues 346

12.4.1.  Issue #1 346

12.4.2.  Issue #2 346

12.4.3.  Issue #3 346

12.4.4.  Issue #4 347

12.5.                Summary 348

13.CHAPTER 13: DIAGNOSING SQL SERVER 2012 USING EXTENDED EVENTS 349

13.1.                Introduction to Extended Events 349

13.2.                Getting Familiar with Extended Events 350

13.3.                Why You Should Be Using Extended Events 351

13.3.1.  SQL Server Roadmap 351

13.3.2.  Graphical Tools 351

13.3.3.  Low Impact 351

13.3.4.  When You Might Use Extended Events 352

13.4.                What Are Extended Events? 352

13.4.1.  Where the Name Extended Events Comes From 353

13.4.2.  Extended Events Terminology 354

13.5.                Creating Extended Events Sessions in SQL Server 2012 363

13.5.1.  Introduction to the New Session Form 363

13.5.2.  Monitoring Server Logins 366

13.5.3.  Monitoring for Page Splits with Extended Events 367

13.5.4.  Counting the Number of Locks Acquired per Object 369

13.5.5.  Creating Sessions Using T-SQL 370

13.6.                Viewing Data Captured by Extended Events 371

13.6.1.  Viewing Event File Data 371

13.7.                Summary 376

14.CHAPTER 14: ENHANCING YOUR TROUBLESHOOTING TOOLSET WITH POWERSHELL 379

14.1.                Introducing PowerShell 379

14.2.                Getting Started with PowerShell 380

14.2.1.  The PowerShell Environment 381

14.2.2.  The Basics — Cmdlets, Variables, Advanced Functions, and Modules 383

14.2.3.  Working Remotely 390

14.2.4.  What’s New in SQL Server 2012 391

14.3.                Using PowerShell to Investigate Server Issues 393

14.3.1.  Interrogating Disk Space Utilization 393

14.3.2.  Interrogating Current Server Activity 394

14.3.3.  Interrogating for Warnings and Errors 396

14.3.4.  Interrogating Server Performance 396

14.4.                Proactively Tuning SQL Server Performance with PowerShell 397

14.4.1.  Index Maintenance 397

14.4.2.  Managing Disk Space Utilization of Backups 398

14.4.3.  Extracting DDL Using SMO 398

14.4.4.  Scheduling Script Execution 403

14.5.                Summary 404

15.CHAPTER 15: DELIVERING A SQL SERVER HEALTH CHECK 405

15.1.                The Importance of a SQL Server Health Check 405

15.2.                Running DMV and DMF Queries 406

15.3.                SQL Server Builds 408

15.4.                Database-Level Queries 426

15.5.                Summary 442

16.CHAPTER 16: DELIVERING MANAGEABILITY AND PERFORMANCE 445

16.1.                Improve Efficiency with SQL Server Manageability Features 445

16.2.                Manageability Enhancements in SQL Server 2012 446

16.3.                Policy-Based Management 447

16.3.1.  Overview 447

16.4.                 Other Microsoft Tools for Managing SQL Server 460

16.4.1.  System Center Advisor 461

16.4.2.  System Center Operations Manager 464

16.5.                Summary 466

17.CHAPTER 17: RUNNING SQL SERVER IN A VIRTUAL ENVIRONMENT 469

17.1.                The Shift to Server Virtualization 469

17.2.                An Overview of Virtualization 470

17.2.1.  History of Virtualization 471

17.2.2.  The Breadth of Virtualization 472

17.2.3.  Platform Virtualization 472

17.2.4.  Cloud Computing 473

17.3.                Why Virtualize a Server? 473

17.3.1.  Business Benefi ts 474

17.3.2.  Technical Benefi ts 474

17.3.3.  Encapsulation 475

17.3.4.  SQL Server 2012 and Virtualization 476

17.3.5.  Limitations of Virtualization 477

17.4.                Common Virtualization Products 477

17.4.1.  VMware 477

17.4.2.  Microsoft Hyper-V 478

17.4.3.  Xen 479

17.4.4.  Hardware Support for Virtualization 479

17.5.                Virtualization Concepts 480

17.5.1.  Host Server 480

17.5.2.  Hypervisor 480

17.5.3.  Virtual Server (or Guest Server or Virtual Machine) 482

17.6.                Extended Features of Virtualization 483

17.6.1.  Snapshotting 483

17.6.2.  High-Availability Features 483

17.6.3.  Online Migration 484

17.6.4.  Highly Available Virtual Servers 486

17.6.5.  Host and Guest Clustering 487

17.6.6.  Deploying SQL Server with Virtualization’s High-Availability Features 487

17.7.                Managing Contention 488

17.7.1.  Good Contention 488

17.7.2.  Bad Contention 488

17.7.3.  Demand-Based Memory Allocation 489

17.7.4.  Weighting 490

17.8.                Identifying Candidates for Virtualization 491

17.8.1.  Guiding Principles 491

17.8.2.  Server Workload 491

17.8.3.  Gathering Sizing Data 492

17.8.4.  Sizing Tools 493

17.8.5.  Non-Performance Related Requirements 493

17.9.                Architecting Successful Virtual Database Servers 494

17.9.1.  Architecting Virtual Database Servers vs. Physical Database Servers 494

17.9.2.  Virtual Database Server Design 495

17.10.            Monitoring Virtualized Database Servers 502

17.10.1.                   Information and Misinformation from Performance Monitor 503

17.11.            Summary 507