Professional SQL Server 2012 Internals and Troubleshooting

Christian Bolton is the Technical Director for Coeo Ltd. and a Microsoft Certified Architect, Master, and MVP for SQL Server. Justin Langford is a Director at Coeo Ltd., a SQL Server MVP, and an author. Glenn Berry is a Principal Consultant at SQLSkills.com, a SQL Server MVP, and an author. Gavin Payne is a Senior Consultant for Coeo Ltd. and a Microsoft Certified Master for SQL Server. Amit Banerjee is a Premier Field Engineer at Microsoft and the owner of TroubleshootingSQL.com. Rob Farley is Owner/Principal of LobsterPot Solutions, a PASS Director, and a SQL Server MVP.

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