Cover image for SQL Server Advanced Troubleshooting and Performance Tuning

SQL Server Advanced Troubleshooting and Performance Tuning

Release Date: 2022/04/01
ISBN: 9781098101916
Topic:
0%
11
Chapters
0-1
Hours read
0k
Total Words
    Start Reading Now    
       Add to Wishlist       
View table of contents

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

  1. 1. SQL Server Setup and Configuration
    1. Hardware and Operating System Considerations
    2. CPU
    3. Memory
    4. Disk Subsystem
    5. Network
    6. Operating Systems and Applications
    7. Virtualization and Clouds
    8. Configuring Your SQL Server
    9. SQL Server Version and Patching Level
    10. Instant File Initialization
    11. Tempdb Configuration
    12. Trace Flags
    13. Server Options
    14. Configuring Your Databases
    15. Database Settings
    16. Transaction Log Settings
    17. Data Files and Filegroups
    18. Analyzing SQL Server Error Log
    19. Consolidating Instances and Databases
    20. Observer Effect
    21. Summary
    22. Troubleshooting Checklist
  2. 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 Data Management Views
    5. sys.dm_os_wait_stats
    6. sys.dm_exec_session_wait_stats
    7. sys.dm_os_waiting_tasks
    8. sys.dm_exec_requests
    9. sys.dm_os_schedulers
    10. Resource Governor Overview
    11. Summary
    12. Troubleshooting Checklist
  3. 3. Troubleshooting Disk Subsystem Issues
    1. Anatomy of the SQL Server I/O Subsystem
    2. Scheduling and I/O
    3. Data Reads
    4. Data Writes
    5. The Storage Subsystem: A Holistic View
    6. sys.dm_io_virtual_file_stats view
    7. Performance Counters and OS Metrics
    8. Virtualization, HBA, and Storage Layers
    9. Checkpoint Tuning
    10. I/O Waits
    11. ASYNC_IO_COMPLETION waits
    12. IO_COMPLETION waits
    13. WRITELOG waits
    14. WRITE_COMPLETION waits
    15. PAGEIOLATCH waits
    16. Summary
    17. Troubleshooting Checklist
  4. 4. Detecting Inefficient Queries
    1. The Impact of Inefficient Queries
    2. Plan-Cache-Based Execution Statistics
    3. SQL Traces and Extended Events
    4. Query Store
    5. Query Store SSMS Reports
    6. Working with Query Store DMVs
    7. Third-Party Tools
    8. Summary
    9. Troubleshooting Checklist
  5. 5. Intro to Query Tuning
    1. Data Storage and Access Patterns
    2. Row-Based Storage Tables
    3. B-Tree Indexes
    4. Composite Indexes
    5. Nonclustered Indexes
    6. Index Fragmentation
    7. Statistics and Cardinality Estimation
    8. Statistics Maintenance
    9. Cardinality Estimation Models
    10. Analyzing Your Execution Plan
    11. Row Mode and Batch Mode Execution
    12. Live Query Statistics and Execution Statistics Profiling
    13. Common Issues and Inefficiencies
    14. Inefficient Code
    15. Inefficient Index Seek
    16. Incorrect Join Type
    17. Excessive Key Lookups
    18. Indexing the Data
    19. Summary
    20. Troubleshooting Checklist
  6. 6. High CPU Load
    1. Non-Optimized Queries and T-SQL Code
    2. Inefficient T-SQL Code
    3. Scripts for Troubleshooting High CPU Load
    4. Non-Optimized Query Patterns to Watch For
    5. Query Compilation and Plan Caching
    6. Parameter-Sensitive Plans
    7. Parameter-Value Independence
    8. Compilation and Parameterization
    9. Auto-Parameterization
    10. Simple parameterization
    11. Forced Parameterization
    12. Parallelism
    13. Summary
    14. Troubleshooting Checklist
  7. 7. Transaction Log
    1. Transaction Log Internals
    2. Data Modifications and Transaction Logging
    3. Explicit and Autocommitted Transactions and Log Overhead
    4. Delayed Durability
    5. In-Memory OLTP Transaction Logging
    6. VIRTUAL LOG FILES
    7. Transaction Log Configuration
    8. Log Truncation Issues
    9. LOG_BACKUP Log Reuse Wait
    10. ACTIVE_TRANSACTION Log Reuse Wait
    11. AVAILABILITY_REPLICA Log Reuse Wait
    12. DATABASE_MIRRORING Log Reuse Wait
    13. REPLICATION Log Reuse Wait
    14. ACTIVE_BACKUP_OR_RESTORE Log Reuse Wait
    15. Other Mitigation Strategies
    16. Transaction Log Throughout
    17. Summary
    18. Troubleshooting Checklist
13.58.197.26