SQL Server Advanced Troubleshooting and Performance Tuning

Book description

This practical book provides a comprehensive overview of troubleshooting and performance tuning best practices for Microsoft SQL Server. Database engineers, including database developers and administrators, will learn how to identify performance issues, troubleshoot the system in a holistic fashion, and properly prioritize tuning efforts to attain the best system performance possible.

Author Dmitri Korotkevitch, Microsoft Data Platform MVP and Microsoft Certified Master (MCM), explains the interdependencies between SQL Server database components. You'll learn how to quickly diagnose your system and discover the root cause of any issue. Techniques in this book are compatible with all versions of SQL Server and cover both on-premises and cloud-based SQL Server installations.

  • Discover how performance issues present themselves in SQL Server
  • Learn about SQL Server diagnostic tools, methods, and technologies
  • Perform health checks on SQL Server installations
  • Learn the dependencies between SQL Server components
  • Tune SQL Server to improve performance and reduce bottlenecks
  • Detect poorly optimized queries and inefficiencies in query execution plans
  • Find inefficient indexes and common database design issues
  • Use these techniques with Microsoft Azure SQL databases, Azure SQL Managed Instances, and Amazon RDS for SQL Server

Publisher resources

View/Submit Errata

Table of contents

  1. 1. SQL Server Setup and Configuration
    1. Hardware and Operating System Considerations
      1. CPU
      2. Memory
      3. Disk Subsystem
      4. Network
      5. Operating Systems and Applications
      6. Virtualization and Clouds
    2. Configuring Your SQL Server
      1. SQL Server Version and Patching Level
      2. Instant File Initialization
      3. Tempdb Configuration
      4. Trace Flags
      5. Server Options
    3. Configuring Your Databases
      1. Database Settings
      2. Transaction Log Settings
      3. Data Files and Filegroups
    4. Analyzing SQL Server Error Log
    5. Consolidating Instances and Databases
    6. Observer Effect
    7. Summary
    8. Troubleshooting Checklist
  2. 2. SQL Server Execution Model and Wait Statistics
    1. SQL Server: High-Level Architecture
    2. SQLOS and the Execution Model
    3. Wait Statistics
    4. Execution Model–Related Data Management Views
      1. sys.dm_os_wait_stats
      2. sys.dm_exec_session_wait_stats
      3. sys.dm_os_waiting_tasks
      4. sys.dm_exec_requests
      5. sys.dm_os_schedulers
    5. Resource Governor Overview
    6. Summary
      1. Troubleshooting Checklist
  3. 3. Troubleshooting Disk Subsystem Issues
    1. Anatomy of the SQL Server I/O Subsystem
      1. Scheduling and I/O
      2. Data Reads
      3. Data Writes
    2. The Storage Subsystem: A Holistic View
      1. sys.dm_io_virtual_file_stats view
      2. Performance Counters and OS Metrics
      3. Virtualization, HBA, and Storage Layers
    3. Checkpoint Tuning
    4. I/O Waits
      1. ASYNC_IO_COMPLETION waits
      2. IO_COMPLETION waits
      3. WRITELOG waits
      4. WRITE_COMPLETION waits
      5. PAGEIOLATCH waits
    5. Summary
      1. Troubleshooting Checklist
  4. 4. Detecting Inefficient Queries
    1. The Impact of Inefficient Queries
    2. Plan-Cache-Based Execution Statistics
    3. SQL Traces and Extended Events
    4. Query Store
      1. Query Store SSMS Reports
      2. Working with Query Store DMVs
    5. Third-Party Tools
    6. Summary
      1. Troubleshooting Checklist
  5. 5. Intro to Query Tuning
    1. Data Storage and Access Patterns
      1. Row-Based Storage Tables
      2. B-Tree Indexes
      3. Composite Indexes
      4. Nonclustered Indexes
    2. Index Fragmentation
    3. Statistics and Cardinality Estimation
      1. Statistics Maintenance
      2. Cardinality Estimation Models
    4. Analyzing Your Execution Plan
      1. Row Mode and Batch Mode Execution
      2. Live Query Statistics and Execution Statistics Profiling
    5. Common Issues and Inefficiencies
      1. Inefficient Code
      2. Inefficient Index Seek
      3. Incorrect Join Type
      4. Excessive Key Lookups
    6. Indexing the Data
    7. Summary
      1. Troubleshooting Checklist
  6. 6. High CPU Load
    1. Non-Optimized Queries and T-SQL Code
      1. Inefficient T-SQL Code
      2. Scripts for Troubleshooting High CPU Load
      3. Non-Optimized Query Patterns to Watch For
    2. Query Compilation and Plan Caching
      1. Parameter-Sensitive Plans
      2. Parameter-Value Independence
    3. Compilation and Parameterization
      1. Auto-Parameterization
      2. Simple parameterization
      3. Forced Parameterization
    4. Parallelism
    5. Summary
      1. Troubleshooting Checklist
  7. 7. Troubleshooting Memory Issues
    1. SQL Server Memory Usage and Configuration
      1. Configuring SQL Server Memory
      2. How Much Memory Is Enough?
    2. Memory Allocations
      1. Memory Clerks
      2. DBCC MEMORYSTATUS command
    3. Query Execution and Memory Grants
      1. Optimizing Memory Intensive Queries
      2. Memory Grant Feedback
      3. Controlling Memory Grant Size
    4. In-Memory OLTP Memory Usage and Troubleshooting
    5. Summary
      1. Troubleshooting Checklist
  8. 8. Troubleshooting TempDB Usage and Performance
    1. Temporary Objects: Usage and Best Practices
      1. Temporary Tables and Table Variables
      2. Temporary Object Caching
      3. Table-Valued Parameters
      4. Regular Tables in TempDb and Transaction Logging
    2. Internal TempDB Consumers
      1. Version Store
      2. Spills
    3. Common TempDB Issues
      1. System Pages Contention
      2. Running out of Space
    4. TempDB Configuration
    5. Summary
      1. Troubleshooting Checklist
  9. 9. Latches
    1. Introduction to Latches
    2. Page Latches
      1. Addressing Hotspots: OPTIMIZE_FOR_SEQUENTIAL_KEY Index Option
      2. Addressing Hotspots: Hash Partitioning
      3. Addressing Hotspots: In-Memory OLTP
    3. Other Latch Types
    4. Summary
      1. Troubleshooting Checklist
  10. 10. Transaction Log
    1. Transaction Log Internals
      1. Data Modifications and Transaction Logging
      2. Explicit and Autocommitted Transactions and Log Overhead
      3. Delayed Durability
      4. In-Memory OLTP Transaction Logging
      5. VIRTUAL LOG FILES
    2. Transaction Log Configuration
    3. Log Truncation Issues
      1. LOG_BACKUP Log Reuse Wait
      2. ACTIVE_TRANSACTION Log Reuse Wait
      3. AVAILABILITY_REPLICA Log Reuse Wait
      4. DATABASE_MIRRORING Log Reuse Wait
      5. REPLICATION Log Reuse Wait
      6. ACTIVE_BACKUP_OR_RESTORE Log Reuse Wait
      7. Other Mitigation Strategies
    4. Transaction Log Throughout
    5. Summary
      1. Troubleshooting Checklist
  11. 11. AlwaysOn Availability Groups
    1. AlwaysOn Availability Groups Overview
    2. Availability Group Queues
    3. Synchronous Replication and the Danger of the HADR_SYNC_COMMIT Wait
      1. Availability Group Extended Events
    4. Asynchronous Replication and Readable Secondaries
      1. The Impact of the Readable Secondaries
    5. Parallel Redo
    6. Troubleshooting Failover Events
      1. Availability Groups and Windows Server Failover Cluster
      2. Troubleshooting Failovers
      3. When Failover Does Not Occur
    7. Summary
      1. Troubleshooting Checklist

Product information

  • Title: SQL Server Advanced Troubleshooting and Performance Tuning
  • Author(s): Dmitri Korotkevitch
  • Release date: April 2022
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781098101923