SQL Server Advanced Troubleshooting and Performance Tuning - Helion

ebook

Autor: Dmitri Korotkevitch
ISBN: 9781098101886
stron: 500, Format: ebook
Data wydania: 2022-05-13
Księgarnia: Helion

Cena książki: 211,65 zł (poprzednio: 246,10 zł)
Oszczędzasz: 14% (-34,45 zł)

Osoby, które kupiły tę książkę, wybierały także »

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

Osoby które kupowały "SQL Server Advanced Troubleshooting and Performance Tuning", wybierały także:

Windows Media Center. Domowe centrum rozrywki 66,67 zł, (8,00 zł -88%)
Ruby on Rails. Ćwiczenia 18,75 zł, (3,00 zł -84%)
DevOps w praktyce. Kurs video. Jenkins, Ansible, Terraform i Docker 190,00 zł, (39,90 zł -79%)
Przywództwo w świecie VUCA. Jak być skutecznym liderem w niepewnym środowisku 58,64 zł, (12,90 zł -78%)
Scrum. O zwinnym zarządzaniu projektami. Wydanie II rozszerzone 58,64 zł, (12,90 zł -78%)

Spis treści

SQL Server Advanced Troubleshooting and Performance Tuning eBook -- spis treści

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