Wrox.Professional.SQL.Server.2012.Internals.and.Troubleshooting.Oct.2012.pdf

i i 10/3/2012 3:52:36 PM 10/3/2012 3:52:36 PM PROFESSIONAL SQL SERVER? 2012 INTERNALS AND TROUBLESHOOTING INTRODUCTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxix ? PART I INTERNALS CHAPTER 1 SQL Server Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 CHAPTER 2 Demystifying Hardware . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 CHAPTER 3 Understanding Memory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 CHAPTER 4 Storage Systems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77 CHAPTER 5 Query Processing and Execution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111 CHAPTER 6 Locking and Concurrency. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149 CHAPTER 7 Latches and Spinlocks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181 CHAPTER 8 Knowing Tempdb . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211 ?PART II TROUBLESHOOTING TOOLS AND LESSONS FROM THE FIELD CHAPTER 9 Troubleshooting Methodology and Practices . . . . . . . . . . . . . . . . . . . . . 241 CHAPTER 10 Viewing Server Performance with PerfMon and the PAL Tool . . . . . . 259 CHAPTER 11 Consolidating Data Capture with SQLdiag . . . . . . . . . . . . . . . . . . . . . . . 295 CHAPTER 12 Bringing It All Together with SQL Nexus . . . . . . . . . . . . . . . . . . . . . . . . . 325 CHAPTER 13 Diagnosing SQL Server 2012 Using Extended Events . . . . . . . . . . . . . 349 CHAPTER 14 Enhancing Your Troubleshooting Toolset with PowerShell . . . . . . . . . 379 CHAPTER 15 Delivering a SQL Server Health Check . . . . . . . . . . . . . . . . . . . . . . . . . . 405 CHAPTER 16 Deliver