Performance Center for SQL Server Database Engine and Azure SQL Database

APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

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

security-center-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.

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

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

Memory configuration options

security-center-sqlserver Server Memory Server Configuration Options

Index configuration options

security-center-sqlserver Configure the fill factor Server Configuration Option

Query configuration options

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

Backup configuration options

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

Query Performance Options

security-center-both 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
security-center-both Partitioned Tables and Indexes Benefits of Partitioning
security-center-both Joins Join Fundamentals
Nested Loops join
Merge join
Hash join
security-center-both Subqueries Subquery Fundamentals
Correlated subqueries
Subquery types
security-center-both Stored Procedures CREATE PROCEDURE (Transact-SQL)
security-center-both User-Defined Functions CREATE FUNCTION (Transact-SQL)
Create User-defined Functions (Database Engine)
security-center-both Parallelism optimization Configure the max worker threads Server Configuration Option
ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
security-center-both Query optimizer optimization ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
USE HINT query hint
security-center-both Statistics When to Update Statistics
Update Statistics
security-center-both 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
security-center-both 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
Azure Query Performance Insight
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