Edit

Share via

SQL Server internals and architecture guides

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL database in Microsoft Fabric

The following guides are available. They discuss general concepts and apply to all versions of SQL Server, unless stated otherwise in the respective guide.

Concurrency, locking, and contention

Use these guides to understand how SQL Server manages concurrent access to data and internal structures, and how to diagnose contention-related issues.

Guide Description
Transaction locking and row versioning guide Explains the locking and row versioning mechanisms that SQL Server uses to preserve transaction integrity. Describes how applications can efficiently control transactions.
Deadlocks guide Deep dive on Database Engine deadlocks that competing locks cause. Explains how deadlocks form and how SQL Server detects and breaks them.
Diagnose and resolve latch contention on SQL Server Focuses on identifying and resolving latch contention (notably page latch contention) in high-concurrency SQL Server workloads.
Diagnose and resolve spinlock contention on SQL Server In-depth guide on identifying and resolving spinlock contention in high-concurrency SQL Server workloads.

Storage engine architecture and I/O

Use these guides to understand how SQL Server stores, accesses, and maintains data on disk.

Guide Description
Page and extent architecture guide Describes page and extent structures and how pages and extents are organized within data files.
SQL Server I/O fundamentals Explains why I/O is core to the engine and discusses efficiency articles such as drive caching principles. It also discusses I/O reliability requirements.
SQL Server transaction log architecture and management guide Explains the transaction log's role and provides details on the physical and logical architecture of the log.
Ghost cleanup process guide Describes ghost cleanup as a background process that physically removes rows previously marked for deletion.

Query execution and optimization

Use these guides to understand how SQL Server compiles, optimizes, and executes queries.

Guide Description
Query processing architecture guide Describes how the Database Engine processes queries across storage architectures. It covers optimization and reuse through execution plan caching.
Index architecture and design guide Covers index architecture and fundamentals. It provides best practices for designing effective indexes.

Memory, threads, and internal scheduling

Use these guides to understand how SQL Server manages memory and CPU resources internally.

Guide Description
Memory management architecture guide Describes SQL Server memory architecture and how SQL Server acquires and uses memory. It includes background on OS virtual memory.
Thread and task architecture guide Describes threading and task concepts in the context of OS scheduling. It explains how work is executed through threads and tasks.
Security cache concepts Explains how SQL Server uses a security cache to validate permissions for principals accessing securables.

High availability, migration, and validation

Use these guides when deploying, migrating, or operating SQL Server in production environments.

Guide Description
Always On availability groups troubleshooting and monitoring guide A troubleshooting and monitoring guide that also explicitly serves as a landing page pointing to other published resources for common AG scenarios and tools.
Post-migration validation and optimization guide Frames post-migration as reconciling data accuracy and completeness and uncovering performance issues, then enumerates common post-migration performance scenarios.

Connectivity and authentication

Use this guide to understand how clients authenticate and connect to SQL Server.

Guide Description
Trace the network authentication process to the Database Engine Walks through network traces that capture TCP connection establishment handshakes and authentication sequences between client and server.
  • Last updated on