Professional SQL Server® 2012 Internals and Troubleshooting -- Copyright -- About the Authors -- About the Contributors -- About the Technical Editors -- Credits -- Acknowledgments -- Contents -- Introduction -- Who this Book is for -- What this Book Covers -- Understanding Internals -- Troubleshooting Tools and Lessons from the Field -- How this Book is Structured -- Chapter 1: SQL Server Architecture -- Chapter 2: Demystifying Hardware -- Chapter 3: Understanding Memory -- Chapter 4: Storage Systems -- Chapter 5: Query Processing and Execution -- Chapter 6: Locking and Concurrency -- Chapter 7: Latches and Spinlocks -- Chapter 8: Knowing Tempdb -- Chapter 9: Troubleshooting Methodology and Practices -- Chapter 10: Viewing Server Performance with PerfMon and the PAL Tool -- Chapter 11: Consolidating Data Capture with SQLdiag -- Chapter 12: Bringing It All Together with SQL Nexus -- Chapter 13: Diagnosing SQL Server 2012 Using Extended Events -- Chapter 14: Enhancing Your Troubleshooting Toolset with PowerShell -- Chapter 15: Delivering a SQL Server Health Check -- Chapter 16: Delivering Manageability and Performance -- Chapter 17: Running SQL Server in a Virtual Environment -- What you need to use this Book -- Conventions -- Source Code -- Errata -- P2P.Wrox.Com -- Part I: Internals -- Chapter 1: SQL Server Architecture -- Introduction -- Database Transactions -- ACID Properties -- SQL Server Transactions -- The Life Cycle of a Query -- The Relational and Storage Engines -- The Buffer Pool -- A Basic SELECT Query -- A Simple Update Query -- Recovery -- SQL Server's Execution Model and the SQLOS -- Execution Model -- The SQLOS -- Summary -- Chapter 2: Demystifying Hardware -- The Importance of Hardware -- How Workload Affects Hardware and Storage Considerations -- Workload Types -- Server Model Selection -- Server Model Evolution.

Processor Vendor Selection -- Intel Processors -- AMD Processors and Numbering -- Choosing and Configuring Hardware for Redundancy -- Hardware Comparison Tools -- TPC-E Benchmark -- Geekbench Benchmark -- Summary -- Chapter 3: Understanding Memory -- Introduction -- Physical and Virtual Memory -- Physical Memory -- Maximum Supported Physical Memory -- Virtual Memory -- NUMA -- SQL Server Memory -- Memory Nodes -- Clerks, Caches, and the Buffer Pool -- Optimizing SQL Server Memory Configuration -- Min and Max Server Memory -- Lock Pages in Memory -- Optimize for Ad-Hoc Workloads -- Summary -- Chapter 4: Storage Systems -- Introduction -- SQL Server I/O -- Storage Technology -- SQL Server and the Windows I/O Subsystem -- Choosing the Right Storage Networks -- Shared Storage Arrays -- Capacity Optimization -- Storage Tiering -- Data Replication -- Remote Data Replication -- Windows Failover Clustering -- SQL Server AlwaysOn Availability Groups -- Risk Mitigation Planning -- Measuring Performance -- Storage Performance Counters -- Disk Drive Performance -- Sequential Disk Access -- Server Queues -- File Layout -- Partition Alignment -- NTFS Allocation Unit Size -- Flash Storage -- Storage Performance Testing -- Summary -- Chapter 5: Query Processing and Execution -- Introduction -- Query Processing -- Parsing -- Algebrizing -- Query Optimization -- Parallel Plans -- Algebrizer Trees -- sql_handle or plan_handle -- Understanding Statistics -- Plan Caching and Recompilation -- Influencing Optimization -- Query Plans -- Query Plan Operators -- Reading Query Plans -- Executing Your Queries -- SQLOS -- Summary -- Chapter 6: Locking and Concurrency -- Overview -- Transactions -- A Is for Atomic -- C Is for Consistent -- I Is for Isolated -- D Is for Durable -- Database Transactions -- Atomicity -- Consistency -- Isolation -- Durability.

The Dangers of Concurrency -- Lost Updates -- Dirty Reads -- Non-Repeatable Reads -- Phantom Reads -- Double Reads -- Halloween Effect -- Locks -- Monitoring Locks -- Lock Resources -- Lock Modes -- Compatibility Matrix -- Lock Escalation -- Deadlocks -- Isolation Levels -- Serializable -- Repeatable Read -- Read Committed -- Read Uncommitted/NOLOCK -- Snapshot -- Read Committed Snapshot -- Summary -- Chapter 7: Latches and Spinlocks -- Overview -- Symptoms -- Recognizing Symptoms -- Measuring Latch Contention -- Measuring Spinlock Contention -- Contention Indicators -- Susceptible Systems -- Understanding Latches and Spinlocks -- Definitions -- Latching Example -- Latch Types -- Latch Modes -- NL -- KP -- SH -- UP -- EX -- DT -- Latch Compatibility -- Grant Order -- Latch Waits -- SuperLatches/Sublatches -- Monitoring Latches and Spinlocks -- DMVs -- Performance Monitor -- Extended Events -- Latch Contention Examples -- Inserts When the Clustered Index Key Is an Identity Field -- Queuing -- UP Latches in Tempdb -- Spinlock Contention in Name Resolution -- Summary -- Chapter 8: Knowing Tempdb -- Introduction -- Overview and Usage -- User Temporary Objects -- Internal Temporary Objects -- The Version Store -- Troubleshooting Common Issues -- Latch Contention -- Monitoring Tempdb I/O Performance -- Troubleshooting Space Issues -- Configuration Best Practices -- Tempdb File Placement -- Tempdb Initial Sizing and Autogrowth -- Configuring Multiple Tempdb Data Files -- Summary -- Part II: Troubleshooting Tools and Lessons from the Field -- Chapter 9: Troubleshooting Methodology and Practices -- Introduction -- Approaching Problems -- Ten Steps to Successful Troubleshooting -- Behavior and Attitude -- Success Criteria -- Working with Stakeholders -- Service-Level Agreements -- Engaging External Help -- Defining the Problem.

Guidelines for Identifying the Problem -- Isolating the Problem -- Performance Bottlenecks -- Data Collection -- Focused Data Collection -- Understanding Data Gathering -- Tools and Utilities -- Data Analysis -- Validating and Implementing Resolution -- Validating Changes -- Testing Changes in Isolation -- Implementing Resolution -- Summary -- Chapter 10: Viewing Server Performance with Perfmon and the PAL Tool -- Introduction -- Performance Monitor Overview -- Reliability and Performance Monitor -- New PerfMon Counters for SQL Server 2012 -- Getting Started with PerfMon -- Getting More from Performance Monitor -- Bottlenecks and SQL Server -- Prescriptive Guidance -- Wait Stats Analysis -- Getting a Performance Baseline -- Performance Analysis of Logs -- Getting Started with PAL -- Other PerfMon Log Analysis Tools -- Using SQL Server to Analyze PerfMon Logs -- Combining PerfMon Logs and SQL Profiler Traces -- Using Relog -- Using LogMan -- Using LogParser -- Summary -- Chapter 11: Consolidating Data Capture with SQLdiag -- The Data Collection Dilemma -- An Approach to Data Collection -- Getting Friendly with SQLdiag -- Using SQLdiag in Snapshot Mode -- Using SQLdiag as a Command-line Application -- Using SQLdiag as a Service -- Using SQLdiag Configuration Manager -- Configuring SQLdiag Data Collection Using Diag Manager -- Adding Trace Filters to a SQLdiag Configuration -- Employing Best Practices -- Gearing Up for Long-Term Data Collection -- Filtering Out the Noise -- Alert-Driven Data Collection with SQLdiag -- Summary -- Chapter 12: Bringing it all Together with SQL Nexus -- Introducing SQL Nexus -- Getting Familiar with SQL Nexus -- Prerequisites -- Loading Data into a Nexus Database -- Analyzing the Aggregated Data -- Customizing SQL Nexus -- Using ReadTrace.exe -- Building Custom Reports for SQL Nexus.

Running SQL Nexus Using the Command Prompt -- Creating Your Own Tables in the SQL Nexus Database -- Writing Your Own Queries -- The OSTRESS Executable -- Resolving Common Issues -- Issue # 1 -- Issue #2 -- Issue #3 -- Issue #4 -- Summary -- Chapter 13: Diagnosing SQL Server 2012 Using Extended Events -- Introduction to Extended Events -- Getting Familiar with Extended Events -- Why You Should Be Using Extended Events -- SQL Server Roadmap -- Graphical Tools -- Low Impact -- When You Might Use Extended Events -- What Are Extended Events? -- Where the Name Extended Events Comes From -- Extended Events Terminology -- Creating Extended Events Sessions in SQL Server 2012 -- Introduction to the New Session Form -- Monitoring Server Logins -- Monitoring for Page Splits with Extended Events -- Counting the Number of Locks Acquired per Object -- Creating Sessions Using T-SQL -- Viewing Data Captured by Extended Events -- Viewing Event File Data -- Summary -- Chapter 14: Enhancing Your Troubleshooting Toolset with PowerShell -- Introducing PowerShell -- Getting Started with PowerShell -- The PowerShell Environment -- The Basics - Cmdlets, Variables, Advanced Functions, and Modules -- Working Remotely -- What's New in SQL Server 2012 -- Using PowerShell to Investigate Server Issues -- Interrogating Disk Space Utilization -- Interrogating Current Server Activity -- Interrogating for Warnings and Errors -- Interrogating Server Performance -- Proactively Tuning SQL Server Performance with PowerShell -- Index Maintenance -- Managing Disk Space Utilization of Backups -- Extracting DDL Using SMO -- Scheduling Script Execution -- Summary -- Chapter 15: Delivering a SQL Server Health Check -- The Importance of a SQL Server Health Check -- Running DMV and DMF Queries -- SQL Server Builds -- Database-Level Queries -- Summary.

Chapter 16: Delivering Manageability and Performance.