Wiley
Wiley.com
Print this page Share

Professional SQL Server 2012 Internals and Troubleshooting

ISBN: 978-1-118-17765-5
576 pages
November 2012
Professional SQL Server 2012 Internals and Troubleshooting (1118177657) cover image
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.

  • See More
    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

    See More

    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.

    See More
    Download TitleSizeDownload
    ReadMe 1.18 KB Click to Download
    Full Code Download 27.67 KB Click to Download
    Chapter 1 368 bytes Click to Download
    Chapter 3 589 bytes Click to Download
    Chapter 5 1.78 KB Click to Download
    Chapter 6 3.44 KB Click to Download
    Chapter 7 1.88 KB Click to Download
    Chapter 8 3.29 KB Click to Download
    Chapter 13 1.20 KB Click to Download
    Chapter 14 4.03 KB Click to Download
    Chapter 15 8.85 KB Click to Download
    See More

    Related Titles

    Back to Top