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.

 

Whitepaper:

Performance Guidance for SQL Server in Windows Azure Virtual Machines

http://msdn.microsoft.com/en-us/library/dn248436.aspx

 

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.

Scenarios

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

 

Network Bandwidth and Delay

Bandwidth

I/O go through separate stack from Guest VM network

Latency

“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

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)

 

TempDB

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

 

Backups

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?