Transcript SQL Server 2008 & Solid State Drives Jon Reade SQL Server Consultant SQL Server 2008 MCITP, MCTS Co-founder SQLServerClub.com, SSC Bristol @jonreade [email protected] linkedin.com/in/readejon.

SQL Server 2008
& Solid State Drives
Jon Reade
SQL Server Consultant
SQL Server 2008 MCITP, MCTS
Co-founder SQLServerClub.com, SSC Bristol
@jonreade
[email protected]
linkedin.com/in/readejon
SQL Server 2008
& Solid State Drives
► Why








SSD ?
Weight
Space
Power consumption
DC event horizon
Durability
Less equipment to manage
Less downtime
IOPS – SPEED !
SQL Server 2008
& Solid State Drives
► Myths
 They don’t last long
SQL Server 2008
& Solid State Drives
► 20Gb
backup
► ioDrive filled to 97% full before backup
► Repeatedly written into same space
► 2,000 x = over 5 years daily backups
► Slow down after 4.5 years
► BUT – no wear load balancing
► Still longer than a typical hard disk
Backup time, seconds
SQL Server 2008
& Solid State Drives
► Durability
5 years - Simulated Backups
100
90
80
70
60
50
40
30
20
10
0
1
68 135 202 269 336 403 470 537 604 671 738 805 872 939 1006 1073 1140 1207 1274 1341 1408 1475 1542 1609 1676 1743 1810 1877 1944 2011
Number of backups
► Step
at 4.5 years of daily backups
► Write load balancing effectively turned off
SQL Server 2008
& Solid State Drives
► Myths
 They don’t last long X
 They don’t retain data when you remove power
SQL Server 2008
& Solid State Drives
► Myths
 They don’t last long X
 They don’t retain data when you remove power X
 They’re not very quick at write operations
SQL Server 2008
& Solid State Drives
► Myths




They don’t last long X
They don’t retain data when you remove power X
They’re not very quick at write operations X
They’re difficult to configure
SQL Server 2008
& Solid State Drives
► Myths





They don’t last long X
They don’t retain data when you remove power X
They’re not very quick at write operations X
They’re difficult to configure X
You need special hardware
SQL Server 2008
& Solid State Drives
► Myths






They don’t last long X
They don’t retain data when you remove power X
They’re not very quick at write operations X
They’re difficult to configure X
You need special hardware X
They run hot
SQL Server 2008
& Solid State Drives
► Myths







They don’t last long X
They don’t retain data when you remove power X
They’re not very quick at write operations X
They’re difficult to configure X
You need special hardware X
They run hot X
They’re expensive
SQL Server 2008
& Solid State Drives
► Myths







They don’t last long X
They don’t retain data when you remove power X
They’re not very quick at write operations X
They’re difficult to configure X
You need special hardware X
They run hot X
They’re expensive X
SQL Server 2008
& Solid State Drives
► Demo
1 : HDSpeed
SQL Server 2008
& Solid State Drives
► Demo
1 : HDSpeed – SSD vs HDD
► 64Kb block size (extent) :
 SSD : 597 MBytes/sec
 HDD : 104 Mbytes/sec avg
► 512Kb
block size :
 SSD: 730 Mbytes/sec avg
 HDD : 105 Mbytes/sec avg
SQL Server 2008
& Solid State Drives
► How
are FusionIO drives different to normal
SSDs and HDDs?
► Bypass the traditional storage controller
► Takes the SATA bus out of the equation
SQL Server 2008
& Solid State Drives
► How
are FusionIO drives different to normal
SSDs and HDDs?
► Bypass the traditional storage controller
► Takes the SATA bus out of the equation
► SATA III – 6Gbits (0.6Gbyte) per second
SQL Server 2008
& Solid State Drives
► How
are FusionIO drives different to normal
SSDs and HDDs?
► Bypass the traditional storage controller
► Takes the SATA bus out of the equation
► SATA III – 6Gbits (0.6Gbyte) per second
► PCI Express x4 – 1GByte per second
► 1.6x faster – for sequential operations
SQL Server 2008
& Solid State Drives
► DMA
access to memory
► CPU not involved
► Less latency. Completely re-architected
storage - no hardware bottleneck
► Fundamental problem at the end of the
chain – HDD is really bad at random i/o
SQL Server 2008
& Solid State Drives
► Installation
► Hardware
- five minutes out of the box
► Drivers - five minutes
SQL Server 2008
& Solid State Drives
Give me a proper database demo !
SQL Server 2008
& Solid State Drives
► Demo
2 : Querying
 SELECTs
 INSERTs
 UPDATEs
SQL Server 2008
& Solid State Drives
► Things
to be aware of
 Asymmetric read/write characteristics
 Periodic consistency checks
 Denali CTP1 can take different times to execute
the same task, with the same load.
SQL Server 2008
& Solid State Drives
► Migration
 Backup, copy and restore
 Detach, copy and re-attach
 Mirror, break, bring online and re-point DNS
SQL Server 2008
& Solid State Drives
► Pros








and Cons
Cost
Capacity
Durability
Random IO Speed
Power usage
Heat dissipation
Weight
Size
SQL Server 2008
& Solid State Drives
► Pros
and Cons
► Cost – or is it ?
► How much does this cost ? :-
SQL Server 2008
& Solid State Drives
► Pros
and Cons
► Cost – or is it?
► How much does this cost ? :-
SQL Server 2008
& Solid State Drives
► MD1000
disk array : £2,069 +VAT
SQL Server 2008
& Solid State Drives
► MD1000
disk array : £2,069 +VAT
► With disks : 15 x 15kRPM 300Gb
£5,839+vat
SQL Server 2008
& Solid State Drives
► MD1000
disk array : £2,069 +VAT
► With disks : 15 x 15kRPM 300Gb
£5,839+vat
► With controller card : £6,189 + VAT
SQL Server 2008
& Solid State Drives
► MD1000
disk array : £2,069 +VAT
► With disks : 15 x 15kRPM 300Gb
£5,839+vat
► With controller card : £6,189 + VAT
► FusionIO ioDrive : £8,000 + VAT
SQL Server 2008
& Solid State Drives
► MD1000
disk array : £2,069 +VAT
► With disks : 15 x 15kRPM 300Gb
£5,839+vat
► With controller card : £6,189 + VAT
► FusionIO ioDrive : £8,000 + VAT
► MD1000 : 4.2Tb
2,800 iops
SQL Server 2008
& Solid State Drives
► MD1000
disk array : £2,069 +VAT
► With disks : 15 x 15kRPM 300Gb
£5,839+vat
► With controller card : £6,189 + VAT
► FusionIO ioDrive : £8,000 + VAT
► MD1000 : 4.2Tb
2,800 iops
► ioDrive : 0.6Tb 150,000 iops
SQL Server 2008
& Solid State Drives
► Cost
comparison
 MD1000 : 4.2Tb
►£1.50/Gb
2,800 iops
, £2.21/iops
 ioDrive : 0.6Tb 150,000 iops
►£13.30/Gb
► 15%
, £0.05/iops
capacity, 53x faster
► 10x cost for capacity
► 1/45th cost for speed
SQL Server 2008
& Solid State Drives
► Database
Maintenance
 DBCC SHOWCONTIG
 600Gb database
 Heavily indexed
SQL Server 2008
& Solid State Drives
SQL Server 2008
& Solid State Drives
SQL Server 2008
& Solid State Drives
► Results
HDD vs SSD : 600Gb database
► 190Gb backup file copy – 5m vs < 1m
5x
SQL Server 2008
& Solid State Drives
► Results
HDD vs SSD : 600Gb database
► 190Gb backup file copy – 5m vs < 1m 5x
► sp_updatestats – 23m12s vs 3m35
6x
SQL Server 2008
& Solid State Drives
► Results
HDD vs SSD : 600Gb database
► 190Gb backup file copy – 5m vs < 1m 5x
► sp_updatestats – 23m12s vs 3m35
6x
► dbcc shrinkfile – 3h02m51s vs 17m36 10x
SQL Server 2008
& Solid State Drives
► Results
HDD vs SSD : 600Gb database
► 190Gb backup file copy – 5m vs < 1m 5x
► sp_updatestats – 23m12s vs 3m35
6x
► dbcc shrinkfile – 3h02m51s vs 17m36 10x
► dbcc showcontig – 2h16m vs 4m48s 28x
SQL Server 2008
& Solid State Drives
► Conclusions
 Very fast – especially random I/O
SQL Server 2008
& Solid State Drives
► Conclusions
 Very fast – especially random I/O
 Easy to implement operationally
SQL Server 2008
& Solid State Drives
► Conclusions
 Very fast – especially random I/O
 Easy to implement operationally
 Long operational life even at 100% capacity
SQL Server 2008
& Solid State Drives
► Conclusions




Very fast – especially random I/O
Easy to implement operationally
Long operational life even at 100% capacity
Reduce query time, reduced contention
SQL Server 2008
& Solid State Drives
► Conclusions





Very fast – especially random I/O
Easy to implement operationally
Long operational life even at 100% capacity
Reduce query time, reduced contention
Shorten database maintenance windows
SQL Server 2008
& Solid State Drives
► Conclusions







Very fast – especially random I/O
Easy to implement operationally
Long operational life even at 100% capacity
Reduce query time, reduced contention
Shorten database maintenance windows
Few problems with Denali CTP1
Worthy of consideration for storage upgrades &
storage/SAN replacement
SQL Server 2008
& Solid State Drives
Interesting links
http://www.fusionio.com/products/iodrive/
http://www.designmind.com/blog/?p=1325
http://www.youtube.com/watch?v=96dWOEa4Djs
http://www.dvnation.com/Fusion-IO-IODrive-SSD-Solid-StateDisk-Drive-Review.html
SQL Server 2008
& Solid State Drives
Very special thanks to Mat Young and
FusionIO for the loan of the ioDrive cards
Big thank you to all of our SQLBits sponsors
SQL Server 2008
& Solid State Drives
Jon Reade
SQL Server Consultant
SQL Server 2008 MCITP, MCTS
Co-founder SQLServerClub.com, SSC Bristol
@jonreade
[email protected]
linkedin.com/in/readejon