SQL Server Advanced Troubleshooting and Performance Tuning

✍ Scribed by Dmitri Korotkevitch

• Publisher: O'Reilly Media, Inc.
• Year: 2022
• Tongue: English
• Leaves: 444
• Category: Library

✦ Synopsis

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

✦ Table of Contents

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