SQL Server Advanced Troubleshooting and Performance Tuning

SQL Server Advanced Troubleshooting and Performance Tuning

by Dmitri Korotkevitch
Released May 2022
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781098101923

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Buy on Amazon Buy on ebooks.com
Start your free trial

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. Preface
    1. Who This Book Is For
    2. Overview of the Chapters
    3. Conventions Used in This Book
    4. Using Code Examples
    5. O’Reilly Online Learning
    6. How to Contact Us
      1. How to Contact the Author
    7. Acknowledgments
  2. 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 the SQL Server Error Log
    5. Consolidating Instances and Databases
    6. Observer Effect
    7. Summary
    8. Troubleshooting Checklist
  3. 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 Dynamic 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
  4. 3. Disk Subsystem Performance
    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
  5. 4. Inefficient Queries
    1. The Impact of Inefficient Queries
    2. Plan Cache–Based Execution Statistics
    3. Extended Events and SQL Traces
    4. Query Store
      1. Query Store SSMS Reports
      2. Working with Query Store DMVs
    5. Third-Party Tools
    6. Summary
      1. Troubleshooting Checklist
  6. 5. Data Storage and 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
  7. 6. CPU Load
    1. Nonoptimized Queries and T-SQL Code
      1. Inefficient T-SQL Code
      2. Scripts for Troubleshooting High CPU Load
      3. Nonoptimized 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
  8. 7. 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. The 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
  9. 8. Locking, Blocking, and Concurrency
    1. Lock Types and Locking Behavior
      1. Major Lock Types
      2. Lock Compatibility
      3. Transaction Isolation Levels and Locking Behavior
    2. Blocking Issues
      1. Troubleshooting Real-Time Blocking
      2. Working with Blocked Process Reports
      3. Event Notifications and Blocking Monitoring Framework
    3. Deadlocks
      1. Troubleshooting Deadlocks
      2. Locking and Indexes
    4. Optimistic Isolation Levels
      1. READ COMMITTED SNAPSHOT Isolation Level
      2. SNAPSHOT Isolation Level
    5. Schema Locks
    6. Lock Escalation
      1. Lock Escalation Troubleshooting
    7. Locking-Related Waits
      1. LCK_M_U Wait Type
      2. LCK_M_S Wait Type
      3. LCK_M_X Wait Type
      4. LCK_M_SCH_S and LCK_M_SCH_M Wait Types
      5. Intent LCK_M_I* Wait Types
      6. Range Locks LCK_M_R* Wait Types
    8. Summary
      1. Troubleshooting Checklist
  10. 9. 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 Page Contention
      2. Running Out of Space
    4. tempdb Configuration
    5. Summary
      1. Troubleshooting Checklist
  11. 10. Latches
    1. Introduction to Latches
    2. Page Latches
      1. Addressing Hotspots: The 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
  12. 11. Transaction Log
    1. Transaction Log Internals
      1. Data Modifications and Transaction Logging
      2. Explicit and Auto-Committed 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. Accelerated Database Recovery
    5. Transaction Log Throughput
    6. Summary
      1. Troubleshooting Checklist
  13. 12. 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 Readable Secondaries
    5. Parallel Redo
    6. Troubleshooting Failover Events
      1. Availability Groups and Windows Server Failover Cluster
      2. Troubleshooting Failovers
      3. When a Failover Does Not Occur
    7. Summary
      1. Troubleshooting Checklist
  14. 13. Other Notable Wait Types
    1. ASYNC_NETWORK_IO Waits
    2. THREADPOOL Waits
    3. Backup-Related Waits
      1. Improving Backup Performance
      2. BUFFERCOUNT and MAXTRANSFERSIZE Options
      3. Partial Database Backups
    4. HTBUILD and Other HT* Waits
    5. Preemptive Waits
      1. PREEMPTIVE_OS_WRITEFILEGATHER Wait Type
      2. PREEMPTIVE_OS_WRITEFILE Wait Type
      3. Authentication-Related Wait Types
      4. OLEDB Waits
    6. Wait Types: Wrapping Up
    7. Summary
      1. Troubleshooting Checklist
  15. 14. Database Schema and Index Analysis
    1. Database Schema Analysis
      1. Heap Tables
      2. Indexes with the uniqueidentifier Data Type
      3. Wide and Nonunique Clustered Indexes
      4. Untrusted Foreign Keys
      5. Nonindexed Foreign Keys
      6. Redundant Indexes
      7. High Identity Values
    2. Index Analysis
      1. The sys.dm_db_index_usage_stats View
      2. The sys.dm_db_index_operational_stats View
      3. Holistic View: sp_Index_Analysis
    3. Summary
      1. Troubleshooting Checklist
  16. 15. SQL Server in Virtualized Environments
    1. To Virtualize or Not to Virtualize, That Is the Question
    2. Configuring SQL Server in Virtualized Environments
      1. Capacity Planning
      2. CPU Configuration
      3. Memory
      4. Storage
      5. Network
    3. Virtual Disk Management
    4. Backup Strategy and Tools
    5. Troubleshooting in Virtual Environments
      1. Insufficient CPU Bandwidth
      2. Memory Pressure
      3. Disk Subsystem Performance
    6. Summary
      1. Troubleshooting Checklist
  17. 16. SQL Server in the Cloud
    1. Cloud Platforms: A 30,000-Foot View
      1. Platform Reliability
      2. Throttling
      3. Topology
    2. Connectivity Considerations and Transient Error Handling
      1. Accessing the Database Instance
      2. Transient Errors
    3. SQL Server in Cloud VMs
      1. I/O Setup and Performance
      2. High Availability Setup
      3. Cross-Region Latency
    4. Managed Microsoft Azure SQL Services
      1. Services Architecture and Design Considerations
      2. Troubleshooting Approaches
    5. Amazon SQL Server RDS
      1. CloudWatch
      2. Performance Insights
    6. Google Cloud SQL
    7. Summary
      1. Troubleshooting Checklist
  18. A. Wait Types
    1. ASYNC_IO_COMPLETION
    2. ASYNC_NETWORK_IO
    3. BACKUPBUFFER
    4. BACKUPIO
    5. BTREE_INSERT_FLOW_CONTROL
    6. CXCONSUMER
    7. CXPACKET
    8. DIRTY_PAGE_TABLE_LOCK
    9. DPT_ENTRY_LOCK
    10. EXCHANGE
    11. HADR_GROUP_COMMIT
    12. HADR_SYNC_COMMIT
    13. HTBUILD
    14. HTDELETE, HTMEMO, HTREINIT, and HTREPARTITION
    15. IO_COMPLETION
    16. LATCH_*
    17. LCK_M_*
    18. LCK_M_I*
    19. LCK_M_R*
    20. LCK_M_S
    21. LCK_M_SCH_M
    22. LCK_M_SCH_S
    23. LCK_M_U
    24. LCK_M_X
    25. LOGBUFFER
    26. OLEDB
    27. PAGEIOLATCH*
    28. PAGELATCH
    29. PARALLEL_REDO_FLOW_CONTROL
    30. PARALLEL_REDO_TRAN_TURN
    31. PREEMPTIVE_OS_ACCEPTSECURITYCONTEXT
    32. PREEMPTIVE_OS_AUTH*
    33. PREEMPTIVE_OS_LOOKUPACCOUNTSID
    34. PREEMPTIVE_OS_WRITEFILE
    35. PREEMPTIVE_OS_WRITEFILEGATHER
    36. QDS*
    37. RESOURCE_SEMAPHORE
    38. RESOURCE_SEMAPHORE_QUERY_COMPILE
    39. THREADPOOL
    40. WRITE_COMPLETION
    41. WRITELOG
  19. Index
  20. About the Author

Product information

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