SQL Server 2008 & Solid State Drives

1. SQL Server 2008 & Solid State Drives

2. SQL Server 2008 & Solid State Drives • Why SSD ? • Weight • Space • Power consumption • DC event horizon • Durability • Less equipment to manage • Less downtime • IOPS – SPEED !

3. SQL Server 2008 & Solid State Drives • Myths • They don’t last long

4. 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

5. SQL Server 2008 & Solid State Drives • Durability • Step at 4.5 years of daily backups • Write load balancing effectively turned off

6. SQL Server 2008 & Solid State Drives • Myths • They don’t last long X • They don’t retain data when you remove power

7. 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

8. 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

9. 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

10. 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

11. 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

12. 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

13. SQL Server 2008 & Solid State Drives Demo 1 : HDSpeed

14. 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

15. 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

16. 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

17. 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

18. 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

19. SQL Server 2008 & Solid State Drives Installation Hardware - five minutes out of the box Drivers - five minutes

20. SQL Server 2008 & Solid State Drives Give me a proper database demo !

21. SQL Server 2008 & Solid State Drives • Demo 2 : Querying • SELECTs • INSERTs • UPDATEs

22. 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.

23. SQL Server 2008 & Solid State Drives • Migration • Backup, copy and restore • Detach, copy and re-attach • Mirror, break, bring online and re-point DNS

24. SQL Server 2008 & Solid State Drives • Pros and Cons • Cost • Capacity • Durability • Random IO Speed • Power usage • Heat dissipation • Weight • Size

25. SQL Server 2008 & Solid State Drives Pros and Cons Cost – or is it ? How much does this cost ? :-

26. SQL Server 2008 & Solid State Drives Pros and Cons Cost – or is it? How much does this cost ? :-

27. SQL Server 2008 & Solid State Drives MD1000 disk array : £2,069 +VAT

28. SQL Server 2008 & Solid State Drives MD1000 disk array : £2,069 +VAT With disks : 15 x 15kRPM 300Gb £5,839+vat

29. 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

30. 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

31. 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

32. 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

33. SQL Server 2008 & Solid State Drives • Cost comparison • MD1000 : 4.2Tb 2,800 iops • £1.50/Gb , £2.21/iops • ioDrive : 0.6Tb 150,000 iops • £13.30/Gb , £0.05/iops • 15% capacity, 53x faster • 10x cost for capacity • 1/45th cost for speed

34. SQL Server 2008 & Solid State Drives • Database Maintenance • DBCC SHOWCONTIG • 600Gb database • Heavily indexed

35. SQL Server 2008 & Solid State Drives

36. SQL Server 2008 & Solid State Drives

37. SQL Server 2008 & Solid State Drives Results HDD vs SSD : 600Gb database 190Gb backup file copy – 5m vs < 1m 5x

38. 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

39. 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

40. 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

41. SQL Server 2008 & Solid State Drives • Conclusions • Very fast – especially random I/O

42. SQL Server 2008 & Solid State Drives • Conclusions • Very fast – especially random I/O • Easy to implement operationally

43. SQL Server 2008 & Solid State Drives • Conclusions • Very fast – especially random I/O • Easy to implement operationally • Long operational life even at 100% capacity

44. 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

45. 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

46. 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

47. SQL Server 2008 & Solid State Drives

48. SQL Server 2008 & Solid State Drives

49. SQL Server 2008 & Solid State Drives