Details

Professional SQL Server 2012 Internals and Troubleshooting


Professional SQL Server 2012 Internals and Troubleshooting


1. Aufl.

von: Christian Bolton, Justin Langford, Glenn Berry, Gavin Payne, Amit Banerjee, Rob Farley

38,99 €

Verlag: Wiley
Format: EPUB
Veröffentl.: 18.10.2012
ISBN/EAN: 9781118240274
Sprache: englisch
Anzahl Seiten: 576

DRM-geschütztes eBook, Sie benötigen z.B. Adobe Digital Editions und eine Adobe ID zum Lesen.

Beschreibungen

<b>Hands-on troubleshooting methods on the most recent release of SQL Server</b> <p>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.</p> <ul class="noindent"> <li>Covers the core technical topics required to understand how SQL Server and Windows should be working</li> <li>Shares best practices so that you know how to proactively monitor and avoid problems</li> <li>Shows how to use tools to quickly gather, analyze, and effectively respond to the source of a system-wide performance issue</li> </ul> <p><i>Professional SQL Server 2012 Internals and Troubleshooting</i> helps you to quickly become familiar with the changes of this new release so that you can best handle database performance and troubleshooting.</p>
<p>Introduction xxix</p> <p><b>Part I: Internals</b></p> <p><b>Chapter 1: SQL Server Architecture 3</b></p> <p>Introduction 3</p> <p>Database Transactions 4</p> <p>ACID Properties 4</p> <p>SQL Server Transactions 5</p> <p>The Life Cycle of a Query 5</p> <p>The Relational and Storage Engines 6</p> <p>The Buffer Pool 6</p> <p>A Basic SELECT Query 7</p> <p>A Simple Update Query 15</p> <p>Recovery 18</p> <p>SQL Server’s Execution Model and the SQLOS 22</p> <p>Execution Model 22</p> <p>The SQLOS 25</p> <p>Summary 26</p> <p><b>Chapter 2: Demystifying Hardware 29</b></p> <p>The Importance of Hardware 29</p> <p>How Workload Affects Hardware and Storage Considerations 30</p> <p>Workload Types 30</p> <p>Server Model Selection 32</p> <p>Server Model Evolution 33</p> <p>Processor Vendor Selection 35</p> <p>Intel Processors 35</p> <p>AMD Processors and Numbering 43</p> <p>Choosing and Configuring Hardware for Redundancy 46</p> <p>Hardware Comparison Tools 48</p> <p>TPC-E Benchmark 48</p> <p>Geekbench Benchmark 50</p> <p>Summary 51</p> <p><b>Chapter 3: Understanding Memory 53</b></p> <p>Introduction 53</p> <p>Physical and Virtual Memory 54</p> <p>Physical Memory 54</p> <p>Maximum Supported Physical Memory 55</p> <p>Virtual Memory 56</p> <p>NUMA 59</p> <p>SQL Server Memory 63</p> <p>Memory Nodes 64</p> <p>Clerks, Caches, and the Buffer Pool 64</p> <p>Optimizing SQL Server Memory Configuration 70</p> <p>Min and Max Server Memory 70</p> <p>Lock Pages in Memory 72</p> <p>Optimize for Ad-Hoc Workloads 74</p> <p>Summary 76</p> <p><b>Chapter 4: Storage Systems 77</b></p> <p>Introduction 77</p> <p>SQL Server I/O 78</p> <p>Storage Technology 78</p> <p>SQL Server and the Windows I/O Subsystem 82</p> <p>Choosing the Right Storage Networks 84</p> <p>Shared Storage Arrays 86</p> <p>Capacity Optimization 86</p> <p>Storage Tiering 88</p> <p>Data Replication 89</p> <p>Remote Data Replication 92</p> <p>Windows Failover Clustering 93</p> <p>SQL Server AlwaysOn Availability Groups 94</p> <p>Risk Mitigation Planning 94</p> <p>Measuring Performance 95</p> <p>Storage Performance Counters 96</p> <p>Disk Drive Performance 97</p> <p>Sequential Disk Access 100</p> <p>Server Queues 101</p> <p>File Layout 101</p> <p>Partition Alignment 103</p> <p>NTFS Allocation Unit Size 104</p> <p>Flash Storage 104</p> <p>Storage Performance Testing 106</p> <p>Summary 110</p> <p><b>Chapter 5: Query Processing and Execution 111</b></p> <p>Introduction 111</p> <p>Query Processing 112</p> <p>Parsing 112</p> <p>Algebrizing 112</p> <p>Query Optimization 113</p> <p>Parallel Plans 114</p> <p>Algebrizer Trees 115</p> <p>sql_handle or plan_handle 115</p> <p>Understanding Statistics 116</p> <p>Plan Caching and Recompilation 117</p> <p>Influencing Optimization 123</p> <p>Query Plans 129</p> <p>Query Plan Operators 132</p> <p>Reading Query Plans 135</p> <p>Executing Your Queries 140</p> <p>SQLOS 140</p> <p>Summary 147</p> <p><b>Chapter 6: Locking and Concurrency 149</b></p> <p>Overview 149</p> <p>Transactions 150</p> <p>A is for Atomic 150</p> <p>C is for Consistent 151</p> <p>I is for Isolated 151</p> <p>D is for Durable 151</p> <p>Database Transactions 151</p> <p>Atomicity 151</p> <p>Consistency 152</p> <p>Isolation 152</p> <p>Durability 152</p> <p>The Dangers of Concurrency 153</p> <p>Lost Updates 153</p> <p>Dirty Reads 155</p> <p>Non-Repeatable Reads 156</p> <p>Phantom Reads 158</p> <p>Double Reads 161</p> <p>Halloween Effect 162</p> <p>Locks 163</p> <p>Monitoring Locks 163</p> <p>Lock Resources 165</p> <p>Lock Modes 167</p> <p>Compatibility Matrix 173</p> <p>Lock Escalation 174</p> <p>Deadlocks 175</p> <p>Isolation Levels 175</p> <p>Serializable 176</p> <p>Repeatable Read 177</p> <p>Read Committed 177</p> <p>Read Uncommitted/NOLOCK 178</p> <p>Snapshot 178</p> <p>Read Committed Snapshot 178</p> <p>Summary 179</p> <p><b>Chapter 7: Latches and Spinlocks 181</b></p> <p>Overview 181</p> <p>Symptoms 182</p> <p>Recognizing Symptoms 182</p> <p>Measuring Latch Contention 183</p> <p>Measuring Spinlock Contention 184</p> <p>Contention Indicators 185</p> <p>Susceptible Systems 185</p> <p>Understanding Latches and Spinlocks 186</p> <p>Definitions 186</p> <p>Latching Example 187</p> <p>Latch Types 194</p> <p>Latch Modes 194</p> <p>NL 195</p> <p>KP 195</p> <p>SH 195</p> <p>UP 195</p> <p>EX 195</p> <p>DT 195</p> <p>Latch Compatibility 196</p> <p>Grant Order 196</p> <p>Latch Waits 197</p> <p>SuperLatches/Sublatches 198</p> <p>Monitoring Latches and Spinlocks 199</p> <p>DMVs 199</p> <p>Performance Monitor 201</p> <p>Extended Events 202</p> <p>Latch Contention Examples 203</p> <p>Inserts When the Clustered Index Key is an Identity Field 203</p> <p>Queuing 205</p> <p>UP Latches in tempdb 208</p> <p>Spinlock Contention in Name Resolution 209</p> <p>Summary 209</p> <p><b>Chapter 8: Knowing Tempdb 211</b></p> <p>Introduction 211</p> <p>Overview and Usage 212</p> <p>User Temporary Objects 213</p> <p>Internal Temporary Objects 217</p> <p>The Version Store 217</p> <p>Troubleshooting Common Issues 220</p> <p>Latch Contention 220</p> <p>Monitoring Tempdb I/O Performance 229</p> <p>Troubleshooting Space Issues 231</p> <p>Configuration Best Practices 232</p> <p>Tempdb File Placement 232</p> <p>Tempdb Initial Sizing and Autogrowth 234</p> <p>Confi guring Multiple Tempdb Data Files 237</p> <p>Summary 237</p> <p><b>Part II: Troubleshooting Tools and Lessons From the Field</b></p> <p><b>Chapter 9: Troubleshooting Methodology and Practices 241</b></p> <p>Introduction 241</p> <p>Approaching Problems 242</p> <p>Ten Steps to Successful Troubleshooting 242</p> <p>Behavior and Attitude 244</p> <p>Success Criteria 245</p> <p>Working with Stakeholders 245</p> <p>Service-Level Agreements 246</p> <p>Engaging External Help 247</p> <p>Defining the Problem 248</p> <p>Guidelines for Identifying the Problem 248</p> <p>Isolating the Problem 249</p> <p>Performance Bottlenecks 250</p> <p>Data Collection 252</p> <p>Focused Data Collection 253</p> <p>Understanding Data Gathering 253</p> <p>Tools and Utilities 254</p> <p>Data Analysis 255</p> <p>Validating and Implementing Resolution 256</p> <p>Validating Changes 256</p> <p>Testing Changes in Isolation 256</p> <p>Implementing Resolution 257</p> <p>Summary 257</p> <p><b>Chapter 10: Viewing Server Performance With PerfMon and the Pal Tool 259</b></p> <p>Introduction 259</p> <p>Performance Monitor Overview 260</p> <p>Reliability and Performance Monitor 260</p> <p>New PerfMon Counters for SQL Server 2012 263</p> <p>Getting Started with PerfMon 268</p> <p>Getting More from Performance Monitor 278</p> <p>Bottlenecks and SQL Server 278</p> <p>Prescriptive Guidance 279</p> <p>Wait Stats Analysis 284</p> <p>Getting a Performance Baseline 285</p> <p>Performance Analysis of Logs 285</p> <p>Getting Started with PAL 285</p> <p>Other PerfMon Log Analysis Tools 289</p> <p>Using SQL Server to Analyze PerfMon Logs 289</p> <p>Combining PerfMon Logs and SQL Profiler Traces 289</p> <p>Using Relog 290</p> <p>Using LogMan 291</p> <p>Using LogParser 293</p> <p>Summary 293</p> <p><b>Chapter 11: Consolidating Data Capture With SQLdiag 295</b></p> <p>The Data Collection Dilemma 295</p> <p>An Approach to Data Collection 296</p> <p>Getting Friendly with SQLdiag 297</p> <p>Using SQLdiag in Snapshot Mode 298</p> <p>Using SQLdiag as a Command-line Application 299</p> <p>Using SQLdiag as a Service 303</p> <p>Using SQLdiag Configuration Manager 305</p> <p>Configuring SQLdiag Data Collection Using Diag Manager 307</p> <p>Adding Trace Filters to a SQLdiag Configuration 310</p> <p>Employing Best Practices 318</p> <p>Gearing Up for Long-Term Data Collection 319</p> <p>Filtering Out the Noise 320</p> <p>Alert-Driven Data Collection with SQLdiag 322</p> <p>Summary 323</p> <p><b>Chapter 12: Bringing It All Together With SQL Nexus 325</b></p> <p>Introducing SQL Nexus 325</p> <p>Getting Familiar with SQL Nexus 326</p> <p>Prerequisites 326</p> <p>Loading Data into a Nexus Database 328</p> <p>Analyzing the Aggregated Data 331</p> <p>Customizing SQL Nexus 340</p> <p>Using ReadTrace.exe 341</p> <p>Building Custom Reports for SQL Nexus 342</p> <p>Running SQL Nexus Using the Command Prompt 342</p> <p>Creating Your Own Tables in the SQL Nexus Database 342</p> <p>Writing Your Own Queries 344</p> <p>The OSTRESS Executable 344</p> <p>Resolving Common Issues 346</p> <p>Issue #1 346</p> <p>Issue #2 346</p> <p>Issue #3 346</p> <p>Issue #4 347</p> <p>Summary 348</p> <p><b>Chapter 13: Diagnosing SQL Server 2012 Using Extended Events 349</b></p> <p>Introduction to Extended Events 349</p> <p>Getting Familiar with Extended Events 350</p> <p>Why You Should Be Using Extended Events 351</p> <p>SQL Server Roadmap 351</p> <p>Graphical Tools 351</p> <p>Low Impact 351</p> <p>When You Might Use Extended Events 352</p> <p>What Are Extended Events? 352</p> <p>Where the Name Extended Events Comes From 353</p> <p>Extended Events Terminology 354</p> <p>Creating Extended Events Sessions in SQL Server 2012 363</p> <p>Introduction to the New Session Form 363</p> <p>Monitoring Server Logins 366</p> <p>Monitoring for Page Splits with Extended Events 367</p> <p>Counting the Number of Locks Acquired per Object 369</p> <p>Creating Sessions Using T-SQL 370</p> <p>Viewing Data Captured by Extended Events 371</p> <p>Viewing Event File Data 371</p> <p>Summary 376</p> <p><b>Chapter 14: Enhancing Your Troubleshooting Toolset With PowerShell 379</b></p> <p>Introducing PowerShell 379</p> <p>Getting Started with PowerShell 380</p> <p>The PowerShell Environment 381</p> <p>The Basics — Cmdlets, Variables, Advanced Functions, and Modules 383</p> <p>Working Remotely 390</p> <p>What’s New in SQL Server 2012 391</p> <p>Using PowerShell to Investigate Server Issues 393</p> <p>Interrogating Disk Space Utilization 393</p> <p>Interrogating Current Server Activity 394</p> <p>Interrogating for Warnings and Errors 396</p> <p>Interrogating Server Performance 396</p> <p>Proactively Tuning SQL Server Performance with PowerShell 397</p> <p>Index Maintenance 397</p> <p>Managing Disk Space Utilization of Backups 398</p> <p>Extracting DDL Using SMO 398</p> <p>Scheduling Script Execution 403</p> <p>Summary 404</p> <p><b>Chapter 15: Delivering A SQL Server Health Check 405</b></p> <p>The Importance of a SQL Server Health Check 405</p> <p>Running DMV and DMF Queries 406</p> <p>SQL Server Builds 408</p> <p>Database-Level Queries 426</p> <p>Summary 442</p> <p><b>Chapter 16: Delivering Manageability and Performance 445</b></p> <p>Improve Efficiency with SQL Server Manageability Features 445</p> <p>Manageability Enhancements in SQL Server 2012 446</p> <p>Policy-Based Management 447</p> <p>Overview 447</p> <p>Other Microsoft Tools for Managing SQL Server 460</p> <p>System Center Advisor 461</p> <p>System Center Operations Manager 464</p> <p>Summary 466</p> <p><b>Chapter 17: Running SQL Server In a Virtual Environment 469</b></p> <p>The Shift to Server Virtualization 469</p> <p>An Overview of Virtualization 470</p> <p>History of Virtualization 471</p> <p>The Breadth of Virtualization 472</p> <p>Platform Virtualization 472</p> <p>Cloud Computing 473</p> <p>Why Virtualize a Server? 473</p> <p>Business Benefits 474</p> <p>Technical Benefits 474</p> <p>Encapsulation 475</p> <p>SQL Server 2012 and Virtualization 476</p> <p>Limitations of Virtualization 477</p> <p>Common Virtualization Products 477</p> <p>VMware 477</p> <p>Microsoft Hyper-V 478</p> <p>Xen 479</p> <p>Hardware Support for Virtualization 479</p> <p>Virtualization Concepts 480</p> <p>Host Server 480</p> <p>Hypervisor 480</p> <p>Virtual Server (or Guest Server or Virtual Machine) 482</p> <p>Extended Features of Virtualization 483</p> <p>Snapshotting 483</p> <p>High-Availability Features 483</p> <p>Online Migration 484</p> <p>Highly Available Virtual Servers 486</p> <p>Host and Guest Clustering 487</p> <p>Deploying SQL Server with Virtualization’s High-Availability Features 487</p> <p>Managing Contention 488</p> <p>Good Contention 488</p> <p>Bad Contention 488</p> <p>Demand-Based Memory Allocation 489</p> <p>Weighting 490</p> <p>Identifying Candidates for Virtualization 491</p> <p>Guiding Principles 491</p> <p>Server Workload 491</p> <p>Gathering Sizing Data 492</p> <p>Sizing Tools 493</p> <p>Non-Performance Related Requirements 493</p> <p>Architecting Successful Virtual Database Servers 494</p> <p>Architecting Virtual Database Servers vs. Physical Database Servers 494</p> <p>Virtual Database Server Design 495</p> <p>Monitoring Virtualized Database Servers 502</p> <p>Information and Misinformation from Performance Monitor 503</p> <p>Summary 507</p> <p>Index 509</p>
<p><b>Christian Bolton</b> is the Technical Director for Coeo Ltd. and a Microsoft Certified Architect, Master, and MVP for SQL Server. <p><b>Justin Langford</b> is a Director at Coeo Ltd., a SQL Server MVP, and an author. <p><b>Glenn Berry</b> is a Principal Consultant at SQLSkills.com, a SQL Server MVP, and an author. <p><b>Gavin Payne</b> is a Senior Consultant for Coeo Ltd. and a Microsoft Certified Master for SQL Server. <p><b>Amit Banerjee</b> is a Premier Field Engineer at Microsoft and the owner of TroubleshootingSQL.com. <p><b>Rob Farley</b> is Owner/Principal of LobsterPot Solutions, a PASS Director, and a SQL Server MVP. <p><b>Wrox Professional guides</b> are written by working developers to address everyday needs. They provide examples, practical solutions, and expert education in new technologies, all designed to help programmers do a better job.
<p><b>Proven techniques and best practices for troubleshooting SQL Server 2012</b> <p>This book is packed with expert advice and real-world examples, showing you how to troubleshoot SQL Server 2012 based on a thorough understanding of SQL Server and Windows internals. It shows how to master the use of specific troubleshooting tools, and how to interpret their output to quickly identify and resolve any performance issue on any server running SQL Server. <p><i>Professional SQL Server 2012 Internals and Troubleshooting:</i> <ul> <li>Takes you through the life cycle of a query to help present SQL Server's architecture in a practical context</li> <li>Helps to demystify server hardware and storage systems and examines how SQL Server uses memory and how you should configure it</li> <li>Explores query processing and execution, locking, and how tempdb works</li> <li>Shows how to use troubleshooting tools to gather, analyze, and respond to the source of a system-wide performance issue</li> <li>Details how to enhance your troubleshooting toolset with Powershell and Extended Events, and how to manage SQL Server in a virtual environment</li> </ul>

Diese Produkte könnten Sie auch interessieren:

Cyber Security and Digital Forensics
Cyber Security and Digital Forensics
von: Mangesh M. Ghonge, Sabyasachi Pramanik, Ramchandra Mangrulkar, Dac-Nhuong Le
PDF ebook
190,99 €
Smart Systems for Industrial Applications
Smart Systems for Industrial Applications
von: C. Venkatesh, N. Rengarajan, P. Ponmurugan, S. Balamurugan
EPUB ebook
190,99 €
Green Internet of Things and Machine Learning
Green Internet of Things and Machine Learning
von: Roshani Raut, Sandeep Kautish, Zdzislaw Polkowski, Anil Kumar, Chuan-Ming Liu
EPUB ebook
190,99 €