Performance Guidance for SQL Server in Microsoft Azure Virtual Machines
SQL Server Technical Article
Authors: Silvano Coriani, Jasraj Dange, Ewan Fairweather, Xin Jin, Alexei Khalyako, Sanjay Mishra, Selcin Turkarslan
Technical Reviewers: Mark Russinovich, Brad Calder, Andrew Edwards, Suraj Puri, Flavio Muratore, Hanuma Kodavalla, Madhan Arumugam Ramakrishnan, Naveen Prakash, Robert Dorr, Roger Doherty, Steve Howard, Yorihito Tada, Kun Cheng, Chris Clayton, Igor Pagliai, Shep Sheppard, Tim Wieman, Greg Low, Juergen Thomas, Guy Bowerman, Evgeny Krivosheev
Editor: Beth Inghram
Published: June, 2013
Updated: September, 2014
Applies to: SQL Server and Microsoft Azure
Summary: Developers and IT professionals should be fully knowledgeable about how to optimize the performance of SQL Server workloads running in Microsoft Azure Infrastructure Services and in more traditional on-premises environments. This technical article discusses the key factors to consider when evaluating performance and planning a migration to SQL Server in Azure Virtual Machines. It also provides certain best practices and techniques for performance tuning and troubleshooting when using SQL Server in Microsoft Azure Infrastructure Services.
Copyright
This document is provided “as-is”. Information and views expressed in this document, including URL and other Internet Web site references, may change without notice. You bear the risk of using it.
Some examples depicted herein are provided for illustration only and are fictitious. No real association or connection is intended or should be inferred.
This document does not provide you with any legal rights to any intellectual property in any Microsoft product. You may copy and use this document for your internal, reference purposes.
© 2014 Microsoft. All rights reserved.
Introduction 4
Quick check list 4
Azure Infrastructure Services fundamentals 5
Azure VM configuration options 5
Virtual machine size 5
Network bandwidth 5
Disk types and configurations 5
Disk cache settings in Azure virtual machines 7
Planning a virtual machine configuration in Azure for optimal SQL Server performance 8
Different performance characteristics and considerations for major sub-systems between the cloud and on-premises 8
I/O sub-system 8
CPU and memory 9
Network 9
Raw storage performance testing 10
Best practices and recommendations for optimizing SQL Server performance in Azure VMs 11
Virtual machine sizes 11
Azure virtual machine disks and cache settings 11
Operating system disk vs. data disk 11
Temporary disk 11
Data disks performance options and considerations 12
Single data disk configuration 12
Multiple data disk configuration 13
Adding multiple data disks to Azure virtual machine 13
Disk striping options for Azure Virtual Machines 13
Placement of database files 16
TempDB 19
Effects of warm-up on data disks 19
Single vs. multiple storage accounts for data disks attached to a single VM 20
NTFS allocation unit size 20
Data compression for I/O bound workloads 20
Restore performance – instant file initialization 21
Other existing best practices 21
Performance troubleshooting fundamental concepts 22
Traditional factors that govern performance in SQL Server 22
Factors that govern performance for SQL Server in Azure Virtual Machines 23
Performance monitoring methodology for SQL Server in Azure Virtual Machine 25
Appendix 29
Raw storage performance testing scripts 29
Guidance on defining key performance indicators 32
How to use KPIs 33
SQL Server troubleshooting scripts 34
Snapshot wait stats script 34
Requests executing on the system script 36
Top query statements and plan by total CPU time 37
Snapshot spinlock stats 38
Snapshot I/O stats 40
Performance monitor 43
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 Azure 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 SQL Server in Azure Virtual Machines.
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 Azure 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 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.
• Apply SQL Server performance fixes.
• Setup default locations.
• Enable locked pages.
• Backup directly to blob storage.
For more information, please follow the guidelines provided in the following sub sections.
Dostları ilə paylaş: |