SQL Server Advanced Troubleshooting and Performance Tuning: Best Practices and Techniques

✍ Scribed by Dmitri Korotkevitch

• Publisher: O'Reilly Media
• Year: 2022
• Tongue: English
• Leaves: 500
• Edition: 1
• 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

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

✦ Subjects

SQL Server; Troubleshooting; Performance Tuning; Query Tuning; CPU Load; Locking; Concurrency; Latches; Transaction Log