Performance Center for SQL Server Database Engine and Azure SQL Database

Applies to: SQL Server Azure 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

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 option)s	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 option)s 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 Collect baseline: Performance best practices Feature specific performance considerations
Performance best practices and configuration guidelines for SQL Server on Linux	SQL Server configuration Linux OS Configuration

Important

Additional considerations are available in:

• Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 with high-performance workloads
• Recommended updates and configuration options for SQL Server 2017 and 2016 with high-performance workloads

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 Create and Access Tables in TempDB from 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