| title | SQL Server Guides | ||
|---|---|---|---|
| description | SQL Server internals and architecture guides. | ||
| author | rwestMSFT | ||
| ms.author | randolphwest | ||
| ms.date | 01/19/2026 | ||
| ms.service | sql | ||
| ms.topic | concept-article | ||
| ms.custom |
|
||
| helpviewer_keywords |
|
||
| monikerRange | >=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric-sqldb |
[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics PDW FabricSQLDB]
The following guides are available. They discuss general concepts and apply to all platforms that use the SQL Database Engine, unless stated otherwise in the respective guide.
- Concurrency, locking, and contention
- Storage engine architecture and I/O
- Query execution and optimization
- Memory, threads, and internal scheduling
- High availability, migration, and validation
- Connectivity and authentication
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. |
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. |
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. |
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. |
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. |
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. |