SQL Server Advanced Troubleshooting and Performance Tuning
Published on 13. May 2022
500 pages
978-1-0981-0188-6 (ISBN)
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
More details
Other editions
Additional editions
Dmitri Korotkevitch
SQL Server Advanced Troubleshooting and Performance Tuning
Best Practices and Techniques
Book
05/2022
O'Reilly
€79.50
Available immediately
Content
- Intro
- 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
