Home Page Icon
Home Page
Table of Contents for
SQL Server Advanced Troubleshooting and Performance Tuning
Close
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
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. 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
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Next
Next Chapter
SQL Server Advanced Troubleshooting and Performance Tuning
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset