Authors: Silvano Coriani, Jasraj Dange, Ewan Fairweather, Xin Jin, Alexei Khalyako, Sanjay Mishra, Selcin Turkarslan Technical Reviewers



Yüklə 268,61 Kb.
səhifə1/8
tarix16.08.2018
ölçüsü268,61 Kb.
#63132
  1   2   3   4   5   6   7   8

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.


Contents


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.



Yüklə 268,61 Kb.

Dostları ilə paylaş:
  1   2   3   4   5   6   7   8




Verilənlər bazası müəlliflik hüququ ilə müdafiə olunur ©genderi.org 2024
rəhbərliyinə müraciət

    Ana səhifə