SQL Server Advanced Troubleshooting and Performance Tuning

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

A. Wait Types

Index
About the Author