Introduction The goal of this technical article is to provide guidance to developers and IT professionals on how to optimize the performance of Microsoft SQL Server workloads running in an Virtual Machine environment. The article first describes the key concepts in Azure that have a direct impact on the performance of SQL Server workloads. It then introduces the key factors to take into account when evaluating performance and when you plan a migration to Azure platform. Next, it introduces additional considerations for performance troubleshooting in Azure Infrastructure Services. The article uses results from specific test scenarios to provide guidance and best practices for optimizing the performance of SQL Server workloads running in Azure virtual machine (VM). The online documentation for SQL Server in Azure Virtual Machines covers getting started, migration, deployment, high availability, security, connectivity, backup and restore, creating a new virtual machine using one of our pre-built SQL Server platform images in the Image Gallery, and other topics. For more information, see . Quick check list The following is a quick check list that you can follow: Use minimum Standard Tier A2 for SQL Server VMs. • Keep the storage account and SQL Server VM in the same region. • Disable Azure geo-replication on the storage account. • Avoid using operating system or temporary disks for database storage or logging. • Avoid using Azure data disk caching options (caching policy = None). • Stripe multiple data disks to get increased IO throughput. • Format with documented allocation sizes. • Separate data and log file I/O paths to obtain dedicated IOPs for data and log. • Enable database page compression. • Enable instant file initialization for data • 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. • Apply SQL Server performance fixes. •
Introduction The goal of this technical article is to provide guidance to developers and IT professionals on how to optimize the performance of Microsoft SQL Server workloads running in an Virtual Machine environment. The article first describes the key concepts in Azure that have a direct impact on the performance of SQL Server workloads. It then introduces the key factors to take into account when evaluating performance and when you plan a migration to Azure platform. Next, it introduces additional considerations for performance troubleshooting in Azure Infrastructure Services. The article uses results from specific test scenarios to provide guidance and best practices for optimizing the performance of SQL Server workloads running in Azure virtual machine (VM). The online documentation for SQL Server in Azure Virtual Machines covers getting started, migration, deployment, high availability, security, connectivity, backup and restore, creating a new virtual machine using one of our pre-built SQL Server platform images in the Image Gallery, and other topics. For more information, see . Quick check list The following is a quick check list that you can follow: • Use minimum Standard Tier A2 for SQL Server VMs. • Keep the storage account and SQL Server VM in the same region. • Disable Azure geo-replication on the storage account. • Avoid using operating system or temporary disks for database storage or logging. • Avoid using Azure data disk caching options (caching policy = None). • Stripe multiple data disks to get increased IO throughput. • Format with documented allocation sizes. • Separate data and log file I/O paths to obtain dedicated IOPs for data and log. • Enable database page compression. • Enable instant file initialization for data • 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. • Apply SQL Server performance fixes.
Performance%20Guidance%20for%20SQL%20Server%20in%20Windows%20Azure%20Virtual%20Machines.docx