TechEd2014 : SQL on Azure VMs

My notes from the TechEd session on SQL inside of Azure VMs


This stuff is confusing. No standard storage terminology being used.

Someone asked if the disks in the BLOBS where Thick or Thin. Answer: We don’t use those terms.



Performance Guidance for SQL Server in Windows Azure Virtual Machines


Azure VMs tie to Azure Storage – need to understand how Azure Storage works

Azure service is a shared service, multi-tenet service


Azure BLOB storage

 VHDs stored in BLOB

3 copies automatic – persistent copies

Shared service – noisy neighbors can be an issue

No direct performance correlation between disk requests made by VM and the underlying physical disk

No performance SLA’s

500TB / BLOB

Total requests rate 20,000 /sec

Geo-redundant storage = 10gb/sec Ingress, 20 gb/sec Egress

Local redundant = 20gb/sec Ingress 30 gb/sec Egress


Two tiers of service for VMs

VM Basic tier = A0-A4 tiers

VM Standard tier = A5-A9 tiers


Disk types

C:- 127GB – persistent

D: Temp drive – 20GB-605GB depend on tier – may be wiped on reboot – local disk, low latency

Data Disks – up to 1TB each (thin)


Where and when you provision your VM matters – the service can move your VM around

Maintenance operations have an effect on performance – notified from Microsoft when they do maintenance


C:- D:are local disks versus Azure disks stored in a BLOB

– need to test to determine scenarios for performance of where to put what


VM Disk Cache

“Host Cache Preference” when creating a disk

Two tier cache:

Tier 1 – Most recently accessed data stored in RAM of physical virtualization host. This cache is sharedby all VMs on that host

Tier 2 – Less recently accessed data stored on the local disk of the physical host (disk based cache). Ther is cache space reserved to each VM OS disk and Data disk based VM size.


  • Reduce transaction against Azure Storage
  • Reduce disk IO latency when IO rate is low
  • Impr0ve boot time


Network Bandwidth and Delay


I/O go through separate stack from Guest VM network


“Cloud Service” is a management, config, security, networking and service model boundary

Affinity group : Compute and storage to be placed close to each other

Going through load balancers adds latency


IO paths on Azure VM


Azure diagram


Disk recommendations:

Use Data disks (not local OS disk) for DBs larger than 10GB

Cache settings:

Use “None” for high OLTP – random I/O

Use “Read” if workload is sensitive to latency

Use “Read/Write” for sporadic disk access – not a write back cache

Static data, seldom accessed

Cache setting can be changed via PowerShell


VM instance size and limits

Place user DB & logs on different data disks

If on OS drive:

Avoid OS disk stripping, use SQL striping (ie. file group)

Warm up disk before measuring performance

After initialize the file by writing some bit patterns sequentially using SQLIO

Consider enabling SQL data compression (row, page) for I/O bound workloads

Reduces I/O

CPU usage can increase

Enable instant file initialization

Don’t use auto-grow, pre-allocate instead

Use 64KB DAU


Placement of data files

Option 1: DB across multiple files with a file group

Option 2: Create an “OS volume” on top of multiple disk

Option 3: Multiple striped volumes each with one or more data disks (based on specific I/O performance requirements)(Host level RAID 0)



Place on OS or Data disks

Don’t use D: temp drive – performance issues can occur


Disk warm up

Disk Load balancers – cold disks get less performance, hot disks get better performance

Idol disks (cold) for 20 mins will reduce your performance. Azure remove disk load balancers from cold disks.

Manually Warm up disks for 10 mins – will get you better performance

Use a Script to warm up


VM Dashboard

Monitor tab for storage account

Storage analytics logging

Minimal or Verbose

Aggregated data place into hidden place in BLOB “&log” – accessible via PowerShell


Moving to the cloud doesn’t solve app design issues

Performance baseline needed prior to moving to Azure in order to understand performance



  1. Backup directly to BLOB storage via URL – within the same data center, if another datacenter will be charged egress
  2. Backup to regular disk


Repurposing of data -??

How to populate test/dev?