Area | Optimizations |
---|---|
VM size | - DS3_v2 or higher for SQL Enterprise edition. - DS2_v2 or higher for SQL Standard and Web editions. |
Storage | - Use premium SSDs. Standard storage is only recommended for dev/test. - Keep the storage account and SQL Server VM in the same region. * Disable Azure geo-redundant storage (geo-replication) on the storage account. |
Disks | - Use a minimum of 2 P30 disks (1 for log files and 1 for data files including TempDB). For workloads requiring ~50,000 IOPS, consider using an Ultra SSD. - Avoid using operating system or temporary disks for database storage or logging. - Enable read caching on the disk(s) hosting the data files and TempDB data files. - Do not enable caching on disk(s) hosting the log file. Important: Stop the SQL Server service when changing the cache settings for an Azure VM disk. - Stripe multiple Azure data disks to get increased IO throughput. - Format with documented allocation sizes. - Place TempDB on the local SSD D: drive for mission critical SQL Server workloads (after choosing correct VM size). More info in the blog Using SSDs to store TempDB. |
I/O | - Enable database page compression. - Enable instant file initialization for data files. - Limit autogrowth of the database. - Disable autoshrink of the database. - Move all databases to data disks, including system databases. - Move SQL Server error log and trace file directories to data disks. - Setup default backup and database file locations. - Enable locked pages. - Apply SQL Server performance fixes. |
Feature-specific | - Back up directly to blob storage. |