While running SQL Server in Azure Virtual Machines, it is recommended that you continue using the same database performance tuning options that are applicable to SQL Server in on-premises server environment. However, the performance of a relational database in a public cloud depends on many factors such as the size of a virtual machine, and the configuration of the data disks.
This post is focused on getting the best performance for SQL Server on Azure VMs. If your workload is less demanding, you might not require every optimization listed below. Consider your performance needs and workload patterns as you evaluate these recommendations.
The following is a quick check list for optimal performance of SQL Server on Azure Virtual Machines:
| Area | Optimizations |
| VM size | DS3 or higher for SQL Enterprise edition.
DS2 or higher for SQL Standard and Web editions. |
| Storage | Use Premium Storage. 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; 1 for data files and TempDB).
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. 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. |
| I/O | Enable database page compression.
Enable instant file initialization for data files. Limit or disable autogrow on the database. Disable autoshrink on 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. |
For more information on how and why to make these optimizations, please review the details and guidance provided in this article.
[…] While running SQL Server in Azure Virtual Machines, it is recommended that you continue using Read more Share Δημοσίευση στην κατηγορία: Microsoft […]