Record:   Prev Next
Author Bolton, Christian
Title Professional SQL Server 2012 Internals and Troubleshooting
Imprint Somerset : John Wiley & Sons, Incorporated, 2012
Ā©2013
book jacket
Edition 1st ed
Descript 1 online resource (580 pages)
text txt rdacontent
computer c rdamedia
online resource cr rdacarrier
Note Intro -- 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
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
Description based on publisher supplied metadata and other sources
Electronic reproduction. Ann Arbor, Michigan : ProQuest Ebook Central, 2020. Available via World Wide Web. Access may be limited to ProQuest Ebook Central affiliated libraries
Link Print version: Bolton, Christian Professional SQL Server 2012 Internals and Troubleshooting Somerset : John Wiley & Sons, Incorporated,c2012 9781118177655
Subject SQL server.;Database management.;Client/server computing
Electronic books
Alt Author Berry, Glenn
Langford, Justin
Payne, Gavin
Banerjee, Amit
Farley, Rob
Rowland-Jones, James
Farley, Rob
Record:   Prev Next