Performance Center for SQL Server Database Engine and Azure SQL Database

Applies to: yesSQL Server (all supported versions) YesAzure SQL Database

This page provides links to help you locate the information that you need about performance in the SQL Server Database Engine and Azure SQL Database.

Legend

Screenshot of the legend that explains the feature availability icons.

Configuration Options for Performance

SQL Server provides the ability to affect database engine performance through a number of configuration options at the SQL Server Database Engine level. With Azure SQL Database, Microsoft performs most, but not all, of these optimizations for you.

Options Description
Disk configuration options Disk striping and RAID
Data and log file configuration options Place Data and Log Files on Separate Drives
View or Change the Default Locations for Data and Log Files (SQL Server Management Studio)
TempDB configuration options Performance Improvements in TempDB
Database Engine Configuration - TempDB
Using SSDs in Azure VMs to store SQL Server TempDB and Buffer Pool Extensions
Disk and performance best practices for temporary disk for SQL Server in Azure Virtual Machines
Server Configuration Options Processor configuration options

affinity mask Server Configuration Option
affinity Input-Output mask Server Configuration Option
affinity64 mask Server Configuration Option
affinity64 Input-Output mask Server Configuration Option
Configure the max worker threads Server Configuration Option

Memory configuration options

Server Memory Server Configuration Options

Index configuration options

Configure the fill factor Server Configuration Option

Query configuration options

Configure the min memory per query Server Configuration Option
Configure the query governor cost limit Server Configuration Option
Configure the max degree of parallelism Server Configuration Option
Configure the cost threshold for parallelism Server Configuration Option
optimize for ad hoc workloads Server Configuration Option

Backup configuration options

View or Configure the backup compression default Server Configuration Option
Database configuration optimization options Data Compression
View or Change the Compatibility Level of a Database
ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
Table configuration optimization Partitioned Tables and Indexes
Database Engine Performance in an Azure Virtual Machine Quick check list
Virtual machine size and storage account considerations
Disks and performance considerations
I/O Performance Considerations
Feature specific performance considerations
Performance best practices and configuration guidelines for SQL Server on Linux SQL Server configuration
Linux OS Configuration

Query Performance Options

Option Description
Indexes Reorganize and Rebuild Indexes
Specify Fill Factor for an Index
Configure Parallel Index Operations
SORT_IN_TEMPDB Option For Indexes
Improve the Performance of Full-Text Indexes
Configure the min memory per query Server Configuration Option
Configure the index create memory Server Configuration Option
Partitioned Tables and Indexes Benefits of Partitioning
Joins Join Fundamentals
Nested Loops join
Merge join
Hash join
Subqueries Subquery Fundamentals
Correlated subqueries
Subquery types
Stored Procedures CREATE PROCEDURE (Transact-SQL)
User-Defined Functions CREATE FUNCTION (Transact-SQL)
Create User-defined Functions (Database Engine)
Parallelism optimization Configure the max worker threads Server Configuration Option
ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
Query optimizer optimization ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
USE HINT query hint
Statistics When to Update Statistics
Update Statistics
In-Memory OLTP (In-Memory Optimization) Memory-Optimized Tables
Natively Compiled Stored Procedures
Creating and Accessing Tables in TempDB from Natively Compiled Stored Procedures
Troubleshooting Common Performance Problems with Memory-Optimized Hash Indexes
Demonstration: Performance Improvement of In-Memory OLTP
Intelligent query processing Intelligent query processing

See Also

Monitor and Tune for Performance
Monitoring Performance By Using the Query Store
Azure SQL Database performance guidance for single databases
Optimizing Azure SQL Database Performance using Elastic Pools
Query Performance Insight for Azure SQL Database
Index Design Guide
Memory Management Architecture Guide
Pages and Extents Architecture Guide
Post-migration Validation and Optimization Guide
Query Processing Architecture Guide
SQL Server Transaction Locking and Row Versioning Guide
SQL Server Transaction Log Architecture and Management Guide
Thread and Task Architecture Guide