SQL Server Advanced Troubleshooting and Performance Tuning

Spis treści książki

• Preface
  • Who This Book Is For
  • Overview of the Chapters
  • Conventions Used in This Book
  • Using Code Examples
  • OReilly Online Learning
  • How to Contact Us
    • How to Contact the Author
  • Acknowledgments
• 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
      • Optimize for Ad-hoc Workloads
      • Max Server Memory
      • Affinity mask
      • Parallelism
      • Configuration settings
  • 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
• 2. SQL Server Execution Model and Wait Statistics
  • SQL Server: High-Level Architecture
  • SQLOS and the Execution Model
  • Wait Statistics
  • Execution ModelRelated 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
• 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
• 4. Inefficient Queries
  • The Impact of Inefficient Queries
  • Plan CacheBased Execution Statistics
  • Extended Events and SQL Traces
  • Query Store
    • Query Store SSMS Reports
      • Regressed Queries
      • Top Resource Consuming Queries
      • Overall Resource Consumption
      • Queries With High Variation
      • Queries With Forced Plans
      • Query Wait Statistics
      • Tracked Queries
    • Working with Query Store DMVs
  • Third-Party Tools
  • Summary
    • Troubleshooting Checklist
• 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
      • Non-SARGable predicates
      • User-defined functions
      • Temporary tables and table variables
      • Stored procedures and ORM frameworks
    • Inefficient Index Seek
    • Incorrect Join Type
      • Loop join
      • Merge join
      • Hash join
      • Comparing join types
    • Excessive Key Lookups
  • Indexing the Data
  • Summary
    • Troubleshooting Checklist
• 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
      • The worst offenders
      • Death by a thousand cuts
  • 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. Memory Issues
  • SQL Server Memory Usage and Configuration
    • Configuring SQL Server Memory
    • How Much Memory Is Enough?
  • Memory Allocations
    • Memory Clerks
      • MEMORYCLERK_SQLBUFFERPOOL
      • CACHESTORE_OBJCP, CACHESTORE_SQLCP, and CACHESTORE_PHDR
      • OBJECTSTORE_LOCK_MANAGER
      • MEMORYCLERK_SQLQERESERVATIONS
      • USERSTORE_TOKENPERM
      • MEMORYCLERK_SQLCONNECTIONPOOL
      • MEMORYCLERK_SQLCLR, MEMORYCLERK_SQLCLRASSEMBLY, and MEMORYCLERK_SQLEXTENSIBILITY
      • MEMORYCLERK_XTP
      • Wrapping up
    • 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
• 8. Locking, Blocking, and Concurrency
  • Lock Types and Locking Behavior
    • Major Lock Types
      • Exclusive (X) locks
      • Shared (S) locks
      • Intent Shared (IS), Intent Update (IU), and Intent Exclusive (IX) locks
      • Update (U) locks
    • 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
• 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
• 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
• 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
• 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
• 13. Other Notable Wait Types
  • ASYNC_NETWORK_IO Waits
  • THREADPOOL Waits
  • Backup-Related Waits
    • Improving Backup Performance
      • Backup compression
      • Striped backups
    • 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
• 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
      • Unused indexes
      • Indexes with high maintenance costs
      • Inefficient reads
      • Inefficient clustered indexes and heaps
      • Wrapping up
    • The sys.dm_db_index_operational_stats View
      • Data modification statistics
      • Data access statistics
      • Locking information
      • Latching information
      • I/O information
      • Wrapping up
    • Holistic View: sp_Index_Analysis
  • Summary
    • Troubleshooting Checklist
• 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
      • Analyzing CPU metrics in Hyper-V
      • Analyzing CPU metrics in VMware ESXi
    • Memory Pressure
    • Disk Subsystem Performance
  • Summary
    • Troubleshooting Checklist
• 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
• 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

pokaż cały spis treści

ukryj recenzje