SQL Server Advanced Troubleshooting and Performance Tuning
by
SQL Server Advanced Troubleshooting and Performance Tuning
- 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
- 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. 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
SQL Server Advanced Troubleshooting and Performance Tuning
Best Practices and Techniques
Dmitri Korotkevitch
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.