SQL Server Database Engine 和 Azure SQL Database 的效能中心Performance Center for SQL Server Database Engine and Azure SQL Database

適用於: 是SQL Server 是Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

本頁提供的連結有助於您尋找所需之 SQL Server Database EngineSQL Server Database EngineAzure SQL DatabaseAzure SQL Database中的效能相關資訊。This page provides links to help you locate the information that you need about performance in the SQL Server Database EngineSQL Server Database Engine and Azure SQL DatabaseAzure SQL Database.

圖例Legend

security-center-legendsecurity-center-legend

效能的組態選項Configuration Options for Performance

SQL ServerSQL Server 可讓您透過許多組態選項來影響 SQL Server Database EngineSQL Server Database Engine 層級的資料庫引擎效能。provides the ability to affect database engine performance through a number of configuration options at the SQL Server Database EngineSQL Server Database Engine level. 使用 Azure SQL DatabaseAzure SQL Database,Microsoft 會為您執行其中的大部分 (但非全部) 最大化。With Azure SQL DatabaseAzure SQL Database, Microsoft performs most, but not all, of these optimizations for you.

磁碟組態選項Disk configuration options security-center-sqlserver 磁碟分割和 RAIDsecurity-center-sqlserver Disk striping and RAID
資料和記錄檔組態選項Data and log file configuration options security-center-sqlserver 將資料檔和記錄檔放在不同的磁碟機上security-center-sqlserver Place Data and Log Files on Separate Drives
security-center-sqlserver 檢視或變更資料及記錄檔的預設位置 (SQL Server Management Studio)security-center-sqlserver View or Change the Default Locations for Data and Log Files (SQL Server Management Studio)
TempDB 組態選項TempDB configuration options security-center-sqlserver TempDB 中的效能改善security-center-sqlserver Performance Improvements in TempDB
security-center-sqlserver 資料庫引擎設定 - TempDBsecurity-center-sqlserver Database Engine Configuration - TempDB
security-center-sqlserver 在 Azure VM 中使用 SSD 來儲存 SQL Server TempDB 和緩衝集區延伸模組security-center-sqlserver Using SSDs in Azure VMs to store SQL Server TempDB and Buffer Pool Extensions
security-center-sqlserver Azure 虛擬機器中 SQL Server 暫存磁碟的磁碟和效能最佳做法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 親和性遮罩伺服器組態選項security-center-sqlserver affinity mask Server Configuration Option
security-center-sqlserver 親和性輸入 - 輸出遮罩伺服器組態選項security-center-sqlserver affinity Input-Output mask Server Configuration Option
security-center-sqlserver affinity64 遮罩伺服器組態選項security-center-sqlserver affinity64 mask Server Configuration Option
security-center-sqlserver affinity64 輸入 - 輸出遮罩伺服器組態選項security-center-sqlserver affinity64 Input-Output mask Server Configuration Option
security-center-sqlserver 設定最大背景工作執行緒伺服器組態選項security-center-sqlserver Configure the max worker threads Server Configuration Option

記憶體組態選項Memory configuration options

security-center-sqlserver 伺服器記憶體伺服器組態選項security-center-sqlserver Server Memory Server Configuration Options

索引組態選項Index configuration options

security-center-sqlserver 設定填滿因數伺服器組態選項security-center-sqlserver Configure the fill factor Server Configuration Option

查詢組態選項Query configuration options

security-center-sqlserver 設定每個查詢的最小記憶體伺服器組態選項security-center-sqlserver Configure the min memory per query Server Configuration Option
security-center-sqlserver 設定查詢管理員成本限制伺服器組態選項security-center-sqlserver Configure the query governor cost limit Server Configuration Option
security-center-sqlserver 設定平行處理原則的最大程度伺服器組態選項security-center-sqlserver Configure the max degree of parallelism Server Configuration Option
security-center-sqlserver 設定平行處理原則的成本閾值伺服器組態選項security-center-sqlserver Configure the cost threshold for parallelism Server Configuration Option
security-center-sqlserver 將臨機操作工作負載最佳化伺服器組態選項security-center-sqlserver optimize for ad hoc workloads Server Configuration Option

備份組態選項Backup configuration options

security-center-sqlserver 檢視或設定備份壓縮預設伺服器組態選項security-center-sqlserver View or Configure the backup compression default Server Configuration Option
資料庫組態最佳化選項Database configuration optimization options security-center-sqlserver 資料壓縮security-center-sqlserver Data Compression
security-center-both 檢視或變更資料庫的相容性層級security-center-both View or Change the Compatibility Level of a Database
security-center-both ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)security-center-both ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
資料表組態最佳化Table configuration optimization security-center-sqlserver 資料分割資料表與索引security-center-sqlserver Partitioned Tables and Indexes
Azure 虛擬機器中的 Database Engine 效能Database Engine Performance in an Azure Virtual Machine security-center-sqlserver 快速檢查清單security-center-sqlserver Quick check list
security-center-sqlserver 虛擬機器大小和儲存體帳戶考量security-center-sqlserver Virtual machine size and storage account considerations
security-center-sqlserver 磁碟和效能考量security-center-sqlserver Disks and performance considerations
security-center-sqlserver I/O 效能考量security-center-sqlserver I/O Performance Considerations
security-center-sqlserver 功能特定效能考量security-center-sqlserver Feature specific performance considerations
Linux 上 SQL Server 的效能最佳做法和設定方針Performance best practices and configuration guidelines for SQL Server on Linux security-center-sqlserver SQL Server 組態security-center-sqlserver SQL Server configuration
security-center-sqlserver Linux OS 組態security-center-sqlserver Linux OS Configuration

查詢效能選項Query Performance Options

security-center-both 索引security-center-both Indexes 重新組織與重建索引Reorganize and Rebuild Indexes
指定索引的填滿因素Specify Fill Factor for an Index
設定平行索引作業Configure Parallel Index Operations
索引的 SORT_IN_TEMPDB 選項SORT_IN_TEMPDB Option For Indexes
改善全文檢索索引的效能Improve the Performance of Full-Text Indexes
設定每筆查詢的最小記憶體數伺服器組態選項Configure the min memory per query Server Configuration Option
設定 index create memory 伺服器組態選項Configure the index create memory Server Configuration Option
security-center-both 資料分割資料表與索引security-center-both Partitioned Tables and Indexes 資料分割的優點Benefits of Partitioning
security-center-both 聯結security-center-both Joins 聯結基本概念Join Fundamentals
巢狀迴圈聯結Nested Loops join
合併聯結Merge join
雜湊聯結Hash join
security-center-both 子查詢security-center-both Subqueries 子查詢基本概念Subquery Fundamentals
相互關聯的子查詢Correlated subqueries
子查詢類型Subquery types
security-center-both 預存程序security-center-both Stored Procedures CREATE PROCEDURE (Transact-SQL)CREATE PROCEDURE (Transact-SQL)
security-center-both 使用者定義函式security-center-both User-Defined Functions CREATE FUNCTION (Transact-SQL)CREATE FUNCTION (Transact-SQL)
建立使用者定義函式 (資料庫引擎)Create User-defined Functions (Database Engine)
security-center-both 平行處理原則最佳化security-center-both Parallelism optimization 設定 max worker threads 伺服器組態選項Configure the max worker threads Server Configuration Option
ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
security-center-both 查詢最佳化工具最佳化security-center-both Query optimizer optimization ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
USE HINT 查詢提示USE HINT query hint
security-center-both 統計資料security-center-both Statistics 何時更新統計資料When to Update Statistics
更新統計資料Update Statistics
security-center-both 記憶體內部 OLTP (記憶體內部最佳化)security-center-both In-Memory OLTP (In-Memory Optimization) 記憶體最佳化資料表Memory-Optimized Tables
原生編譯的預存程序Natively Compiled Stored Procedures
從原生編譯預存程序建立及存取 TempDB 中的資料表Creating and Accessing Tables in TempDB from Natively Compiled Stored Procedures
針對記憶體最佳化雜湊索引常見效能問題進行疑難排解Troubleshooting Common Performance Problems with Memory-Optimized Hash Indexes
示範:記憶體內部 OLTP 的效能改善Demonstration: Performance Improvement of In-Memory OLTP
security-center-both 智慧型查詢處理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 效能指引 Azure SQL Database performance guidance for single databases
使用彈性集區最佳化 Azure SQL Database 效能 Optimizing Azure SQL Database Performance using Elastic Pools
Azure 查詢效能深入解析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 交易鎖定與資料列版本設定指南SQL Server Transaction Locking and Row Versioning Guide
SQL Server 交易記錄架構與管理指南SQL Server Transaction Log Architecture and Management Guide
執行緒和工作架構指南Thread and Task Architecture Guide