This guide describes how to identify and resolve latch contention issues observed when running SQL Server applications on high concurrency systems with certain workloads. As the number of CPU cores on servers continues to increase, the associated increase in concurrency can introduce contention points on data structures that must be accessed in a serial fashion within the database engine. This is especially true for high throughput/high concurrency transaction processing (OLTP) workloads. There are a number of tools, techniques, and ways to approach these challenges as well as practices that can be followed in designing applications which may help to avoid them altogether. This article will discuss a particular type of contention on data structures that use spinlocks to serialize access to these data structures. Link to PDF : Diagnose and resolve latch contention on SQL Server

The page is the fundamental unit of data storage in SQL Server. An extent is a collection of eight physically contiguous pages. Extents help efficiently manage pages. This guide describes the data structures that are used to manage pages and extents in all versions of SQL Server. Understanding the architecture of pages and extents is important for designing and developing databases that perform efficiently. Link to PDF : Pages and Extents Architecture Guide

Post-migration Validation and Optimization Guide Applies to: SQL Server (all supported versions) SQL Server post migration step is very crucial for reconciling any data accuracy and completeness, as well as uncover performance issues with the workload. Common Performance Scenarios Below are some of the common performance scenarios encountered after migrating to SQL Server Platform and how to resolve them. These include scenarios that are specific to SQL Server to SQL Server migration (older versions to newer versions), as well as foreign platform (such as Oracle, DB2, MySQL and Sybase) to SQL Server migration. Query regressions due to change in CE version Applies to: SQL Server to SQL Server migration. When migrating from an older version of SQL Server to SQL Server 2014 (12.x) or newer, and upgrading the database compatibility level to the latest available, a workload may be exposed to the risk of performance regression. This is because starting with SQL Server 2014

Query Processing Architecture Guide Applies to: SQL Server (all supported versions) Azure SQL Database The SQL Server Database Engine processes queries on various data storage architectures such as local tables, partitioned tables, and tables distributed across multiple servers. The following topics cover how SQL Server processes queries and optimizes query reuse through execution plan caching. Execution modes The SQL Server Database Engine can process Transact-SQL statements using two distinct processing modes: Batch mode execution Row mode execution Row mode execution is a query processing method used with traditional RDMBS tables, where data is stored in row format. When a query is executed and accesses data in row store tables, the execution tree operators and child operators read each required row, across all the columns specified in the table schema. From each row that is read, SQL Server then retrieves the columns that are required for the result set, as referenced by a SELECT

SQL Server Index Architecture and Design Guide Applies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Parallel Data Warehouse Poorly designed indexes and a lack of indexes are primary sources of database application bottlenecks. Designing efficient indexes is paramount to achieving good database and application performance. This SQL Server index design guide contains information on index architecture, and best practices to help you design effective indexes to meet the needs of your application. This guide assumes the reader has a general understanding of the index types available in SQL Server. For a general description of index types, see Index Types. This guide covers the following types of indexes: •    Clustered•    Nonclustered•    Unique•    Filtered•    Columnstore•    Hash•    Memory-Optimized NonclusteredFor information about XML indexes, see XML Indexes Overview.For information about Spatial indexes, see Spatial Indexes Overview.For information about Full-text indexes, see Populate Full-Text Indexes. Index Design Basics Think about a regular