book

SQL Server Advanced Troubleshooting and Performance Tuning

by Dmitri Korotkevitch

May 2022

Intermediate to advanced

497 pages

13h 4m

English

O'Reilly Media, Inc.

Book available

Read now

Unlock full access

Who This Book Is ForOverview of the ChaptersConventions Used in This BookUsing Code ExamplesO’Reilly Online LearningHow to Contact UsHow to Contact the AuthorAcknowledgments
Hardware and Operating System ConsiderationsCPUMemoryDisk SubsystemNetworkOperating Systems and ApplicationsVirtualization and CloudsConfiguring Your SQL ServerSQL Server Version and Patching LevelInstant File Initializationtempdb ConfigurationTrace FlagsServer OptionsConfiguring Your DatabasesDatabase SettingsTransaction Log SettingsData Files and FilegroupsAnalyzing the SQL Server Error LogConsolidating Instances and DatabasesObserver EffectSummaryTroubleshooting Checklist
SQL Server: High-Level ArchitectureSQLOS and the Execution ModelWait StatisticsExecution Model–Related Dynamic Management Viewssys.dm_os_wait_statssys.dm_exec_session_wait_statssys.dm_os_waiting_taskssys.dm_exec_requestssys.dm_os_schedulersResource Governor OverviewSummaryTroubleshooting Checklist
Anatomy of the SQL Server I/O SubsystemScheduling and I/OData ReadsData WritesThe Storage Subsystem: A Holistic Viewsys.dm_io_virtual_file_stats viewPerformance Counters and OS MetricsVirtualization, HBA, and Storage LayersCheckpoint TuningI/O WaitsASYNC_IO_COMPLETION WaitsIO_COMPLETION WaitsWRITELOG WaitsWRITE_COMPLETION WaitsPAGEIOLATCH WaitsSummaryTroubleshooting Checklist
The Impact of Inefficient QueriesPlan Cache–Based Execution StatisticsExtended Events and SQL TracesQuery StoreQuery Store SSMS ReportsWorking with Query Store DMVsThird-Party ToolsSummaryTroubleshooting Checklist
Data Storage and Access PatternsRow-Based Storage TablesB-Tree IndexesComposite IndexesNonclustered IndexesIndex FragmentationStatistics and Cardinality EstimationStatistics MaintenanceCardinality Estimation ModelsAnalyzing Your Execution PlanRow Mode and Batch Mode ExecutionLive Query Statistics and Execution Statistics ProfilingCommon Issues and InefficienciesInefficient CodeInefficient Index SeekIncorrect Join TypeExcessive Key LookupsIndexing the DataSummaryTroubleshooting Checklist
Nonoptimized Queries and T-SQL CodeInefficient T-SQL CodeScripts for Troubleshooting High CPU LoadNonoptimized Query Patterns to Watch ForQuery Compilation and Plan CachingParameter-Sensitive PlansParameter-Value IndependenceCompilation and ParameterizationAuto-ParameterizationSimple ParameterizationForced ParameterizationParallelismSummaryTroubleshooting Checklist
SQL Server Memory Usage and ConfigurationConfiguring SQL Server MemoryHow Much Memory Is Enough?Memory AllocationsMemory ClerksThe DBCC MEMORYSTATUS CommandQuery Execution and Memory GrantsOptimizing Memory-Intensive QueriesMemory Grant FeedbackControlling Memory Grant SizeIn-Memory OLTP Memory Usage and TroubleshootingSummaryTroubleshooting Checklist
Lock Types and Locking BehaviorMajor Lock TypesLock CompatibilityTransaction Isolation Levels and Locking BehaviorBlocking IssuesTroubleshooting Real-Time BlockingWorking with Blocked Process ReportsEvent Notifications and Blocking Monitoring FrameworkDeadlocksTroubleshooting DeadlocksLocking and IndexesOptimistic Isolation LevelsREAD COMMITTED SNAPSHOT Isolation LevelSNAPSHOT Isolation LevelSchema LocksLock EscalationLock Escalation TroubleshootingLocking-Related WaitsLCK_M_U Wait TypeLCK_M_S Wait TypeLCK_M_X Wait TypeLCK_M_SCH_S and LCK_M_SCH_M Wait TypesIntent LCK_M_I* Wait TypesRange Locks LCK_M_R* Wait TypesSummaryTroubleshooting Checklist
Temporary Objects: Usage and Best PracticesTemporary Tables and Table VariablesTemporary Object CachingTable-Valued ParametersRegular Tables in tempdb and Transaction LoggingInternal tempdb ConsumersVersion StoreSpillsCommon tempdb IssuesSystem Page ContentionRunning Out of Spacetempdb ConfigurationSummaryTroubleshooting Checklist

Introduction to LatchesPage LatchesAddressing Hotspots: The OPTIMIZE_FOR_SEQUENTIAL_KEY Index OptionAddressing Hotspots: Hash PartitioningAddressing Hotspots: In-Memory OLTPOther Latch TypesSummaryTroubleshooting Checklist
Transaction Log InternalsData Modifications and Transaction LoggingExplicit and Auto-Committed Transactions and Log OverheadDelayed DurabilityIn-Memory OLTP Transaction LoggingVirtual Log FilesTransaction Log ConfigurationLog Truncation IssuesLOG_BACKUP Log Reuse WaitACTIVE_TRANSACTION Log Reuse WaitAVAILABILITY_REPLICA Log Reuse WaitDATABASE_MIRRORING Log Reuse WaitREPLICATION Log Reuse WaitACTIVE_BACKUP_OR_RESTORE Log Reuse WaitOther Mitigation StrategiesAccelerated Database RecoveryTransaction Log ThroughputSummaryTroubleshooting Checklist
AlwaysOn Availability Groups OverviewAvailability Group QueuesSynchronous Replication and the Danger of the HADR_SYNC_COMMIT WaitAvailability Group Extended EventsAsynchronous Replication and Readable SecondariesThe Impact of Readable SecondariesParallel RedoTroubleshooting Failover EventsAvailability Groups and Windows Server Failover ClusterTroubleshooting FailoversWhen a Failover Does Not OccurSummaryTroubleshooting Checklist
ASYNC_NETWORK_IO WaitsTHREADPOOL WaitsBackup-Related WaitsImproving Backup PerformanceBUFFERCOUNT and MAXTRANSFERSIZE OptionsPartial Database BackupsHTBUILD and Other HT* WaitsPreemptive WaitsPREEMPTIVE_OS_WRITEFILEGATHER Wait TypePREEMPTIVE_OS_WRITEFILE Wait TypeAuthentication-Related Wait TypesOLEDB WaitsWait Types: Wrapping UpSummaryTroubleshooting Checklist
Database Schema AnalysisHeap TablesIndexes with the uniqueidentifier Data TypeWide and Nonunique Clustered IndexesUntrusted Foreign KeysNonindexed Foreign KeysRedundant IndexesHigh Identity ValuesIndex AnalysisThe sys.dm_db_index_usage_stats ViewThe sys.dm_db_index_operational_stats ViewHolistic View: sp_Index_AnalysisSummaryTroubleshooting Checklist
To Virtualize or Not to Virtualize, That Is the QuestionConfiguring SQL Server in Virtualized EnvironmentsCapacity PlanningCPU ConfigurationMemoryStorageNetworkVirtual Disk ManagementBackup Strategy and ToolsTroubleshooting in Virtual EnvironmentsInsufficient CPU BandwidthMemory PressureDisk Subsystem PerformanceSummaryTroubleshooting Checklist
Cloud Platforms: A 30,000-Foot ViewPlatform ReliabilityThrottlingTopologyConnectivity Considerations and Transient Error HandlingAccessing the Database InstanceTransient ErrorsSQL Server in Cloud VMsI/O Setup and PerformanceHigh Availability SetupCross-Region LatencyManaged Microsoft Azure SQL ServicesServices Architecture and Design ConsiderationsTroubleshooting ApproachesAmazon SQL Server RDSCloudWatchPerformance InsightsGoogle Cloud SQLSummaryTroubleshooting Checklist
ASYNC_IO_COMPLETIONASYNC_NETWORK_IOBACKUPBUFFERBACKUPIOBTREE_INSERT_FLOW_CONTROLCXCONSUMERCXPACKETDIRTY_PAGE_TABLE_LOCKDPT_ENTRY_LOCKEXCHANGEHADR_GROUP_COMMITHADR_SYNC_COMMITHTBUILDHTDELETE, HTMEMO, HTREINIT, and HTREPARTITIONIO_COMPLETIONLATCH_*LCK_M_*LCK_M_I*LCK_M_R*LCK_M_SLCK_M_SCH_MLCK_M_SCH_SLCK_M_ULCK_M_XLOGBUFFEROLEDBPAGEIOLATCH*PAGELATCHPARALLEL_REDO_FLOW_CONTROLPARALLEL_REDO_TRAN_TURNPREEMPTIVE_OS_ACCEPTSECURITYCONTEXTPREEMPTIVE_OS_AUTH*PREEMPTIVE_OS_LOOKUPACCOUNTSIDPREEMPTIVE_OS_WRITEFILEPREEMPTIVE_OS_WRITEFILEGATHERQDS*RESOURCE_SEMAPHORERESOURCE_SEMAPHORE_QUERY_COMPILETHREADPOOLWRITE_COMPLETIONWRITELOG

Overview

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

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Start your free trial

SQL Server Query Tuning and Optimization

Publisher Resources

ISBN: 9781098101916Errata Page Supplemental Content

SQL Server Advanced Troubleshooting and Performance Tuning

by Dmitri Korotkevitch

Overview

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

You might also like

SQL Server Query Tuning and Optimization

SQL Server 2022 Query Performance Tuning: Troubleshoot and Optimize Query Performance

T-SQL Querying

SQL Server 2022 Administration Inside Out

Publisher Resources

Overview

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,and much more.

You might also like

SQL Server Query Tuning and Optimization

SQL Server 2022 Query Performance Tuning: Troubleshoot and Optimize Query Performance

T-SQL Querying

SQL Server 2022 Administration Inside Out

Publisher Resources

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.