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.
Raw storage performance testing scripts: Discusses the usage of SQLIO. Snapshot wait stats script: Demonstrates how to automate the calculation of waits during a time interval. Requests executing on the system script: Provides a view of requests executing on SQL Server sorted by total elapsed time, includes the corresponding plan for each statement. Top query statements and plan by total CPU time script: Provides information from Query Stats on the top overall resource consumers. Snapshot spinlock stats script: Uses a temporary table to provide the delta of spinlock information since the last execution of this script. Snapshot I/O stats script: Uses a temporary table to provide the delta of I/O information since the last execution of this script. Raw storage performance testing scripts You can run the following scripts in Azure Virtual Machine to generate some of the most common I/O patterns that SQL Server utilizes and measure related performance results on different storage configurations. The following SQLIO test scripts demonstrate testing random 8K reads/writes (a typical OLTP I/O pattern), sequential writes for log files, and large sequential reads and writes for table scans and OLAP workloads. The script uses the following options: The -k option to specify the I/O operation type (read or The -s option to specify the test duration in seconds The –f option to specify the type of I/O access (sequential or random) The –o option to specify the number of outstanding requests The –b option to specify the size of the I/O request in bytesblock size The –LS option to capture the disk latency option The –F option to specify the name of the file which contain the test files to run SQLIO against Copy and save the following script in a file called exectests.bat. ::Test random 8K sqlio -kW -s300 -frandom -o32 -b8 -LS -Fparam.txt sqlio -kR -s300 -frandom -o32 -b8 -LS -Fparam.txt ::Test random 32K reads/writes (for example, SQL Server Analysis Services I/O pattern) -kW -s300 -frandom -o32 -b32 -LS -Fparamnew.txt