| title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | ||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Performance Center |
Find the information that you need about performance in the SQL Server Database Engine and Azure SQL Database. |
MikeRayMSFT |
mikeray |
randolphwest |
06/29/2023 |
sql |
performance |
conceptual |
|
|
[!INCLUDE SQL Server Azure SQL Database]
This page provides links to help you locate the information that you need about performance in the [!INCLUDE ssDEnoversion] and [!INCLUDE ssazure-sqldb].
Legend
:::image type="content" source="media/performance-center-for-sql-server-database-engine-and-azure-sql-database/security-center-legend.png" alt-text="Screenshot of the legend that explains the feature availability icons.":::
[!INCLUDE ssNoVersion] provides the ability to affect database engine performance through a number of configuration options at the [!INCLUDE ssDEnoversion] level. With [!INCLUDE ssazure-sqldb], Microsoft performs most, but not all, of these optimizations for you.
| Options | Description |
|---|---|
| Disk configuration options | :::image type="icon" source="media/security-center-sqlserver.png"::: Disk striping and RAID |
| Data and log file configuration options | :::image type="icon" source="media/security-center-sqlserver.png"::: Place Data and Log Files on Separate Drives :::image type="icon" source="media/security-center-sqlserver.png"::: View or Change the Default Locations for Data and Log Files (SQL Server Management Studio) |
tempdb configuration options** |
:::image type="icon" source="media/security-center-sqlserver.png"::: Performance Improvements in TempDB :::image type="icon" source="media/security-center-sqlserver.png"::: Database Engine Configuration - TempDB :::image type="icon" source="media/security-center-sqlserver.png"::: Using SSDs in Azure VMs to store SQL Server TempDB and Buffer Pool Extensions :::image type="icon" source="media/security-center-sqlserver.png"::: Disk and performance best practices for temporary disk for SQL Server in Azure Virtual Machines |
| (server configuration option)s | Processor configuration options :::image type="icon" source="media/security-center-sqlserver.png"::: affinity mask (server configuration option) :::image type="icon" source="media/security-center-sqlserver.png"::: affinity Input-Output mask (server configuration option) :::image type="icon" source="media/security-center-sqlserver.png"::: affinity64 mask (server configuration option) :::image type="icon" source="media/security-center-sqlserver.png"::: affinity64 Input-Output mask (server configuration option) :::image type="icon" source="media/security-center-sqlserver.png"::: Configure the max worker threads (server configuration option) Memory configuration options :::image type="icon" source="media/security-center-sqlserver.png"::: Server Memory (server configuration option)s Index configuration options :::image type="icon" source="media/security-center-sqlserver.png"::: Configure the fill factor (server configuration option) Query configuration options :::image type="icon" source="media/security-center-sqlserver.png"::: Configure the min memory per query (server configuration option) :::image type="icon" source="media/security-center-sqlserver.png"::: Configure the query governor cost limit (server configuration option) :::image type="icon" source="media/security-center-sqlserver.png"::: Configure the max degree of parallelism (server configuration option) :::image type="icon" source="media/security-center-sqlserver.png"::: Configure the cost threshold for parallelism (server configuration option) :::image type="icon" source="media/security-center-sqlserver.png"::: optimize for ad hoc workloads (server configuration option) Backup configuration options :::image type="icon" source="media/security-center-sqlserver.png"::: View or Configure the backup compression default (server configuration option) |
| Database configuration optimization options | :::image type="icon" source="media/security-center-sqlserver.png"::: Data Compression :::image type="icon" source="media/security-center-both.png"::: View or Change the Compatibility Level of a Database :::image type="icon" source="media/security-center-both.png"::: ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL) |
| Table configuration optimization | :::image type="icon" source="media/security-center-sqlserver.png"::: Partitioned Tables and Indexes |
| Database Engine Performance in an Azure Virtual Machine | :::image type="icon" source="media/security-center-sqlserver.png"::: Quick check list :::image type="icon" source="media/security-center-sqlserver.png"::: Virtual machine size and storage account considerations :::image type="icon" source="media/security-center-sqlserver.png"::: Disks and performance considerations :::image type="icon" source="media/security-center-sqlserver.png"::: Collect baseline: Performance best practices :::image type="icon" source="media/security-center-sqlserver.png"::: Feature specific performance considerations |
| Performance best practices and configuration guidelines for SQL Server on Linux | :::image type="icon" source="media/security-center-sqlserver.png"::: SQL Server configuration :::image type="icon" source="media/security-center-sqlserver.png"::: Linux OS Configuration |
Important
Additional considerations are available in:
- 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