performance-center-for-sql-server-database-engine-and-azure-sql-database.md

title	Performance Center
description	Find the information that you need about performance in the SQL Server Database Engine and Azure SQL Database.
ms.custom	seo-dt-2019
ms.date	12/11/2018
ms.prod	sql
ms.reviewer
ms.technology	performance
ms.topic	conceptual
f1_keywords	Performance (SQL Server) Performance (SQL Database)
helpviewer_keywords	SQL Server, performance performance (SQL Server) database performance (SQL Server) SQL Database (Performance) performance (SQL Database) database performance (SQL Database)
ms.assetid	301204b2-140d-4495-98ed-021a9b5025f5
author	WilliamDAssafMSFT
ms.author	wiassaf

Performance Center for SQL Server Database Engine and Azure SQL Database

[!INCLUDE SQL Server Azure SQL Database] This page provides links to help you locate the information that you need about performance in the [!INCLUDEssDEnoversion] and [!INCLUDEssSDSFull].

Legend

Configuration Options for Performance

[!INCLUDEssNoVersion] provides the ability to affect database engine performance through a number of configuration options at the [!INCLUDEssDEnoversion] level. With [!INCLUDEssSDSFull], Microsoft performs most, but not all, of these optimizations for you.

Options	Description
Disk configuration options	:::image type="icon" source="../../relational-databases/performance/media/security-center-sqlserver.png"::: Disk striping and RAID
Data and log file configuration options	:::image type="icon" source="../../relational-databases/performance/media/security-center-sqlserver.png"::: Place Data and Log Files on Separate Drives :::image type="icon" source="../../relational-databases/performance/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="../../relational-databases/performance/media/security-center-sqlserver.png"::: Performance Improvements in TempDB :::image type="icon" source="../../relational-databases/performance/media/security-center-sqlserver.png"::: Database Engine Configuration - TempDB :::image type="icon" source="../../relational-databases/performance/media/security-center-sqlserver.png"::: Using SSDs in Azure VMs to store SQL Server TempDB and Buffer Pool Extensions :::image type="icon" source="../../relational-databases/performance/media/security-center-sqlserver.png"::: Disk and performance best practices for temporary disk for SQL Server in Azure Virtual Machines
Server Configuration Options	Processor configuration options :::image type="icon" source="../../relational-databases/performance/media/security-center-sqlserver.png"::: affinity mask Server Configuration Option :::image type="icon" source="../../relational-databases/performance/media/security-center-sqlserver.png"::: affinity Input-Output mask Server Configuration Option :::image type="icon" source="../../relational-databases/performance/media/security-center-sqlserver.png"::: affinity64 mask Server Configuration Option :::image type="icon" source="../../relational-databases/performance/media/security-center-sqlserver.png"::: affinity64 Input-Output mask Server Configuration Option :::image type="icon" source="../../relational-databases/performance/media/security-center-sqlserver.png"::: Configure the max worker threads Server Configuration Option Memory configuration options :::image type="icon" source="../../relational-databases/performance/media/security-center-sqlserver.png"::: Server Memory Server Configuration Options Index configuration options :::image type="icon" source="../../relational-databases/performance/media/security-center-sqlserver.png"::: Configure the fill factor Server Configuration Option Query configuration options :::image type="icon" source="../../relational-databases/performance/media/security-center-sqlserver.png"::: Configure the min memory per query Server Configuration Option :::image type="icon" source="../../relational-databases/performance/media/security-center-sqlserver.png"::: Configure the query governor cost limit Server Configuration Option :::image type="icon" source="../../relational-databases/performance/media/security-center-sqlserver.png"::: Configure the max degree of parallelism Server Configuration Option :::image type="icon" source="../../relational-databases/performance/media/security-center-sqlserver.png"::: Configure the cost threshold for parallelism Server Configuration Option :::image type="icon" source="../../relational-databases/performance/media/security-center-sqlserver.png"::: optimize for ad hoc workloads Server Configuration Option Backup configuration options :::image type="icon" source="../../relational-databases/performance/media/security-center-sqlserver.png"::: View or Configure the backup compression default Server Configuration Option
Database configuration optimization options	:::image type="icon" source="../../relational-databases/performance/media/security-center-sqlserver.png"::: Data Compression :::image type="icon" source="../../relational-databases/performance/media/security-center-both.png"::: View or Change the Compatibility Level of a Database :::image type="icon" source="../../relational-databases/performance/media/security-center-both.png"::: ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
Table configuration optimization	:::image type="icon" source="../../relational-databases/performance/media/security-center-sqlserver.png"::: Partitioned Tables and Indexes
Database Engine Performance in an Azure Virtual Machine	:::image type="icon" source="../../relational-databases/performance/media/security-center-sqlserver.png"::: Quick check list :::image type="icon" source="../../relational-databases/performance/media/security-center-sqlserver.png"::: Virtual machine size and storage account considerations :::image type="icon" source="../../relational-databases/performance/media/security-center-sqlserver.png"::: Disks and performance considerations :::image type="icon" source="../../relational-databases/performance/media/security-center-sqlserver.png"::: I/O Performance Considerations :::image type="icon" source="../../relational-databases/performance/media/security-center-sqlserver.png"::: Feature specific performance considerations
Performance best practices and configuration guidelines for SQL Server on Linux	:::image type="icon" source="../../relational-databases/performance/media/security-center-sqlserver.png"::: SQL Server configuration :::image type="icon" source="../../relational-databases/performance/media/security-center-sqlserver.png"::: 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
:::image type="icon" source="../../relational-databases/performance/media/security-center-both.png"::: 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
:::image type="icon" source="../../relational-databases/performance/media/security-center-both.png"::: Partitioned Tables and Indexes	Benefits of Partitioning
:::image type="icon" source="../../relational-databases/performance/media/security-center-both.png"::: Joins	Join Fundamentals Nested Loops join Merge join Hash join
:::image type="icon" source="../../relational-databases/performance/media/security-center-both.png"::: Subqueries	Subquery Fundamentals Correlated subqueries Subquery types
:::image type="icon" source="../../relational-databases/performance/media/security-center-both.png"::: Stored Procedures	CREATE PROCEDURE (Transact-SQL)
:::image type="icon" source="../../relational-databases/performance/media/security-center-both.png"::: User-Defined Functions	CREATE FUNCTION (Transact-SQL) Create User-defined Functions (Database Engine)
:::image type="icon" source="../../relational-databases/performance/media/security-center-both.png"::: Parallelism optimization	Configure the max worker threads Server Configuration Option ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
:::image type="icon" source="../../relational-databases/performance/media/security-center-both.png"::: Query optimizer optimization	ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL) USE HINT query hint
:::image type="icon" source="../../relational-databases/performance/media/security-center-both.png"::: Statistics	When to Update Statistics Update Statistics
:::image type="icon" source="../../relational-databases/performance/media/security-center-both.png"::: 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
:::image type="icon" source="../../relational-databases/performance/media/security-center-both.png"::: 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