1

VMs, SAN, SQL:The Big Threes

Brent OzarQuest Software

Photo Licensed with Creative Commons From http://www.flickr.com/photos/ektogamat/2687444500/

2

I’m Your Host: Brent Ozar• Microsoft Certified

Master• SQL Server MVP• Quest Software• Past Lives: DBA, SAN,

VMware admin• www.BrentOzar.com• Twitter: @BrentO

3

Today’s Big Threes• 3 “Nevers” for VMs• 3 “Always” for SAN• 3 Metrics for Both

Photo Licensed with Creative Commons From http://www.flickr.com/photos/ektogamat/2687444500/

4

Some Definitions

5

Our Host• 2 Sockets

4 Cores Each• 64GB RAM• 1 Terabyte SAN

Photo Licensed with Creative Commons From http://www.flickr.com/photos/brento/1748661584/

6

The Brochure Says…

7

CPU Scheduling

8

CPU Scheduling

9

CPU Scheduling

10

Not So Fast!

11

Never Overallocate vCPUs• Less is More• Every Tick Matters• Minimize CPU Work• Don’t Be Strict

Photo Licensed with Creative Commons From http://www.flickr.com/photos/rsimpson/3832084595/

12

So What About Memory?• Host: 64GB• Guest: 16GB• Other Guests: 32GB

Photo Licensed with Creative Commons From http://www.flickr.com/photos/erikthenorsk/3642116265/

13

So What About Memory?• Host: 64GB• Guest: 16GB• Other Guests: 80GB

Photo Licensed with Creative Commons From http://www.flickr.com/photos/erikthenorsk/3642116265/

14

How Hypervisors Cope• Host page file• Dedupe memory

(page sharing)• Keep guest OS

memory freed up• Learn more in

my bookmarks:http://delicious.com/brento/balloon

Photo Licensed with Creative Commons From http://www.flickr.com/photos/heartlover1717/208534358/

15

Never Use Automatics• Set SQL Server’s

min/max memory• Set VMware’s

reservation size• Use locked pages

carefully

Photo Licensed with Creative Commons From http://www.flickr.com/photos/nataliejohnson/2419154951/

16

Physical Storage Setup

17

In Virtualization…

18

Behind the Curtain

19

Behind the Curtain

20

Think You’re Alone?

21

Never Assume VMs are Alone • Guests move• Guests get created• Competition:

virus scans, scheduled tasks, backups

Photo Licensed with Creative Commons From http://www.flickr.com/photos/32172473@N00/4036700968/

22

Always Know Your Neighbors• Same disks• Same cache• Same controller• Same SAN• Ask:

– Usage pattern?– Scheduled jobs?– Antivirus?

Photo Licensed with Creative Commons From http://www.flickr.com/photos/searchnetmedia/4327761697/

23

Design Storage as a Whole• Backup schedules• Backup methods• Backup targets• Use differentials• Stagger job times• Reconfigure antivirus

24

Typical SAN Components• Drive enclosures• Controllers (& Cache)• Switch networks• Host Bus Adapters• HBA Drivers• Servers

25

Lots of Paths for Data

26

What Does Active/Active Mean?• One path per LUN• Sending vs receiving• Network design• Post-failover stickiness

27

Got High Speed?

28

So You Tested This Too, Right?

Photo Licensed with Creative Commons From http://www.flickr.com/photos/tophost/2247031208/

29

Always Test First with SQLIO• Doesn’t use SQL Server• Doesn’t mimic SQL Server• Doesn’t return SQL metrics• Therefore: SAN guys love it!

30

The Easy Way

31

SQLIO ResultsE:\Program Files (x86)\SQLIO>sqlio -kW -t2 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.dat sqlio v1.5.SGusing system counter for latency timings, -1361967296 counts per second2 threads writing for 120 secs to file M:Testfile.dat

using 64KB random IOsenabling multiple I/Os per thread with 1 outstandingbuffering set to use hardware disk cache (but not file cache)

using current size: 24576 MB for file: M:Testfile.datinitialization doneCUMULATIVE DATA:throughput metrics:IOs/sec: 1539.50MBs/sec: 96.21latency metrics:Min_Latency(ms): 0Avg_Latency(ms): 0Max_Latency(ms): 572histogram:ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+%: 66 32 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

32

Good Numbers, Bad News• iSCSI

– 1GB = 125 MB/sec– 10GB = 1250 MB/sec

• Fibre Channel:– 2GB = 250 MB/sec– 4GB = 500 MB/sec

33

Which Causes An Outage?• New neighbors• RAID restripe• Drive failure• Cable change• Firmware change• Switch upgrade• Controller reboot

Photo Licensed with Creative Commons From http://www.flickr.com/photos/celebdu/10200825/

34

Where Will You See Changes?• Windows event log• SQL Server logs• Profiler traces• Net Send popups• Your dreams at night

35

Always Be Checking

Photo Licensed with Creative Commons From http://www.flickr.com/photos/buro9/298998173/

36

My 3 Favorite Metrics• Physical Disk:

Avg Sec/Read and Write• System:

Processor Queue Length• SQL Server Memory:

Page Life Expectancy

37

It’s Not All Bad News!• Easier scaling• Less firmware outages• No leased hardware cycles• Easier disaster recovery• Easier dinosaur handling

38

Wrapping It Up• Virtualization Nevers:

– Overallocate vCPUs– Use automatics– Assume your VM is alone

• SAN Always’s:– Know your neighbors– Test first with SQLIO– Be checking

39

http://www.BrentOzar.com/go/virtual

40

Our Book• Internals:

– Memory– Locking– Storage

• Troubleshooting:– Perfmon/Profiler– RML Utilities– SQLNexus

41

Our Authors• Christian Bolton

MVP, MCM, MCAFormer Microsoft PFE

• Justin LangfordFormer MS PFE

• Brent OzarMCM, MVP

• James Rowland-JonesMVP, EMC Consulting

• Steven WortMicrosoft Developer

42

Paperbacks & PDF Versions:SQLServerTroubleshooting.com