You are previewing Professional SQL Server 2012 Internals and Troubleshooting.
O'Reilly logo
Professional SQL Server 2012 Internals and Troubleshooting

Book Description

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.

Table of Contents

  1. Cover
  2. Contents
  3. Part I: Internals
    1. Chapter 1: SQL Server Architecture
      1. Introduction
      2. Database Transactions
      3. The Life Cycle of a Query
      4. SQL Server’s Execution Model and the SQLOS
      5. Summary
    2. Chapter 2: Demystifying Hardware
      1. The Importance of Hardware
      2. How Workload Affects Hardware and Storage Considerations
      3. Processor Vendor Selection
      4. Choosing and Configuring Hardware for Redundancy
      5. Hardware Comparison Tools
      6. Summary
    3. Chapter 3: Understanding Memory
      1. Introduction
      2. Physical and Virtual Memory
      3. SQL Server Memory
      4. Optimizing SQL Server Memory Configuration
      5. Summary
    4. Chapter 4: Storage Systems
      1. Introduction
      2. SQL Server I/O
      3. Storage Technology
      4. Measuring Performance
      5. Summary
    5. Chapter 5: Query Processing and Execution
      1. Introduction
      2. Query Processing
      3. Query Optimization
      4. Query Plans
      5. Executing Your Queries
      6. Summary
    6. Chapter 6: Locking and Concurrency
      1. Overview
      2. Transactions
      3. Database Transactions
      4. The Dangers of Concurrency
      5. Locks
      6. Lock Escalation
      7. Deadlocks
      8. Isolation Levels
      9. Summary
    7. Chapter 7: Latches and Spinlocks
      1. Overview
      2. Symptoms
      3. Susceptible Systems
      4. Understanding Latches and Spinlocks
      5. Latch Types
      6. Latch Modes
      7. SuperLatches/Sublatches
      8. Monitoring Latches and Spinlocks
      9. Latch Contention Examples
      10. Summary
    8. Chapter 8: Knowing Tempdb
      1. Introduction
      2. Overview and Usage
      3. Troubleshooting Common Issues
      4. Configuration Best Practices
      5. Summary
  4. Part II: Troubleshooting Tools and Lessons From The Field
    1. Chapter 9: Troubleshooting Methodology and Practices
      1. Introduction
      2. Approaching Problems
      3. Defining the Problem
      4. Data Collection
      5. Data Analysis
      6. Validating and Implementing Resolution
      7. Summary
    2. Chapter 10: Viewing Server Performance with Perfmon and The PAL Tool
      1. Introduction
      2. Performance Monitor Overview
      3. Getting More from Performance Monitor
      4. Performance Analysis of Logs
      5. Other PerfMon Log Analysis Tools
      6. Summary
    3. Chapter 11: Consolidating Data Capture with SQLdiag
      1. The Data Collection Dilemma
      2. An Approach to Data Collection
      3. Getting Friendly with SQLdiag
      4. Using SQLdiag Configuration Manager
      5. Employing Best Practices
      6. Summary
    4. Chapter 12: Bringing It All Together with SQL Nexus
      1. Introducing SQL Nexus
      2. Getting Familiar with SQL Nexus
      3. Customizing SQL Nexus
      4. Resolving Common Issues
      5. Summary
    5. Chapter 13: Diagnosing SQL Server 2012 Using Extended Events
      1. Introduction to Extended Events
      2. Getting Familiar with Extended Events
      3. Why you should be Using Extended Events
      4. What are Extended Events?
      5. Creating Extended Events Sessions in SQL Server 2012
      6. Viewing Data Captured by Extended Events
      7. Summary
    6. Chapter 14: Enhancing Your Troubleshooting Toolset with Powershell
      1. Introducing PowerShell
      2. Getting Started with PowerShell
      3. Using PowerShell to Investigate Server Issues
      4. Proactively Tuning SQL Server Performance with PowerShell
      5. Summary
    7. Chapter 15: Delivering A SQL Server Health Check
      1. The Importance of a SQL Server Health Check
      2. Running DMV and DMF Queries
      3. SQL Server Builds
      4. Database-Level Queries
      5. Summary
    8. Chapter 16: Delivering Manageability and Performance
      1. Improve Efficiency with SQL Server Manageability Features
      2. Manageability Enhancements in SQL Server 2012
      3. Policy-Based Management
      4. Other Microsoft Tools for Managing SQL Server
      5. Summary
    9. Chapter 17: Running SQL Server in A Virtual Environment
      1. The Shift to Server Virtualization
      2. An Overview of Virtualization
      3. Why Virtualize a Server?
      4. Common Virtualization Products
      5. Virtualization Concepts
      6. Extended Features of Virtualization
      7. Managing Contention
      8. Identifying Candidates for Virtualization
      9. Architecting Successful Virtual Database Servers
      10. Monitoring Virtualized Database Servers
      11. Summary
  5. Introduction
  6. Advertisements