SQL Server Advanced Troubleshooting and Performance Tuning: Best Practices and Techniques
- Length: 500 pages
- Edition: 1
- Language: English
- Publisher: O'Reilly Media
- Publication Date: 2022-05-17
- ISBN-10: 1098101928
- ISBN-13: 9781098101923
- Sales Rank: #1799828 (See Top 100 Books)
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
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
Donate to keep this site alive
How to download source code?
1. Go to: https://www.oreilly.com/
2. Search the book title: SQL Server Advanced Troubleshooting and Performance Tuning: Best Practices and Techniques
, sometime you may not get the results, please search the main title
3. Click the book title in the search results
3. Publisher resources
section, click Download Example Code
.
1. Disable the AdBlock plugin. Otherwise, you may not get any links.
2. Solve the CAPTCHA.
3. Click download link.
4. Lead to download server to download.