Downloaded 12 times
Uploaded bySolarWinds
How to configure SQL Server for SSDs and VMs
The document discusses the impact of solid-state drives (SSDs) and virtualization on SQL Server performance, emphasizing the speed benefits of SSDs compared to traditional hard drives. It highlights best practices for managing SSD environments, optimizing storage policies, and understanding IO bottlenecks, particularly with SQL Server's tempdb. Additionally, it stresses the importance of protecting data and utilizing array-based compression for improved performance.
How to configure SQL Server for SSDs and VMs
- 1. VMs, SSDs andSQL Server—Oh, My!
- 2. Joey D’Antoni » Joeyhas over 15 years of experience with a wide variety of data platforms, in both Fortune 50 companies as well as smaller organizations » Principal Consultant, Denny Cherry and Associates » He is a frequent speaker on database administration, big data, and career management » He is the co-president of the Philadelphia SQL Server User’s Group » He wants you to make sure you can restore your data Joeydantoni.com
- 3. AGENDA How SSDs Change the Game Beinga DBA in a Virtual Environment Managing Your SSD Environment Storage Policy for Databases Maximize Your Benefits 3 V M s , S S Ds And S Q L S e r v e r, O h M y © 2015 SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED.
- 4. SPINNING DISK DRIVES HardDrives can only spin at 15,000 RPM. Hard Drive Performance has improved approximately 10x (1988-2008) Have grown many fold in capacity During the performance improvement CPU speed increase 10000x
- 5. WHY IS STORAGETHE BOTTLENECK?
- 6. MEMORY VERSUS HARDDRIVES
- 7. SOLID STATE DRIVES 7©2015 SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED.
- 8. WHY ARE SSDsSO MUCH FASTER? 8 THE LAW S O F P HY S I CS © 2015 SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED.
- 9. SAN TERMS Disk Array—Thephysical array behind your SAN (a bunch of hard drives) LUN—Logical Unit Number, but in practical terms, what your SAN admin will call a disk that he presents to your server Fibre Channel—Cable connection to SAN IOPs—I/O Operations Per Second—the way your SAN admin measures performance
- 10. HOW DOES THISWORK IN A SAN? Add Spindles to Add IO Performance Performance of ONE PCI-E SSD card • 235,000 8k Random IOPS 20µs latency You’d Need 3500 15k RPM Spinning Disks in RAID 10 to achieve that level of performance 10© 2015 SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED.
- 11. SO HOW DOI BENEFIT THE MOST? Treat SSDs as limited resource • Use Data Compression where possible Where does SQL Server bottleneck on IO? • TempDB—SQL Server uses TempDB for EVERYTHING • Hot Datafiles—what is your busiest database? 11© 2015 SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED.
- 12. ALL FLASH ARRAYS 12©2015 SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED. Not just pure speed Software benefits Write leveling (randomization) Compression Caching Oh, and Pure Speed Peugeot 905, 24 Hours of LeMans 1993, 391 kph/242 mph, Mulsanne Straight
- 13. MODERN STORAGE ARRAYARCHITECTURE Stripes all data across all devices in array (768 kb – 1 MB) Minimizes impact of any hot spotting Increases overall performance of array Makes data file placement far less important than in the past 13© 2015 SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED.
- 14. OLD ARCHITECTURE 14© 2015SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED. Data Files TempDB Transaction Logs
- 15. MODERN ARCHITECTURE 15© 2015SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED. All data is striped across all devices in the array
- 16. HYBRID ARRAYS Mix Performanceand Price • Common config • SSD Tier • Fast Spinning Tier • Dense Spinning Tier Rely on striping for performance gains Your storage admin can’t tell you where data is 16© 2015 SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED.
- 17. DATA FILE LAYOUT Itdepends If using all flash configuration: • Consider Hot Spots • Most arrays are smart enough to distribute workload • If on smart array, there’s no real reason to layout data files If using hybrid array—see next slide Always protect your data 17© 2015 SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED.
- 18. HYBRID ARRAY POLICY SSDSSD SSD 15K Fibre Channel 15k Fibre Channel 15k Fibre Channel 10K SATA 10K SATA 10K SATA Log Data Temp DB Temp DB (2)
- 19. HYBRID ARRAYS FILELAYOUT •TempDB Performance •TempDB Normal •Transaction Logs •Data Files Performance •Data Files Normal Separate Volumes by SAN Policy 19 |
- 20. ALL FLASH LAYOUT Itdoesn’t matter! Software eliminates sequential write performance issues Data is striped across entire array for protection 20 |
- 21. ARRAY BASED COMPRESSION 21 | WHAT DO E S THI S M E AN FO R DATABAS E S ?
- 22. ARRAY BASED COMPRESSION Needsmore research Early consensus from two storage vendors is positive Keep using Data Compression Keep using Columnstore Not just IO benefits, but memory improvements 22 |
- 23. What is theImpact of Virtual Machine Performance? 23 |
- 24. DATASTORES It can beeasy to overwhelm storage if not enough storage devices are presented Datastores will correspond to SAN policy groups VMs Love Fast IO
- 25. DRIVERS AND STORAGECONFIG Avoid Lazy Zeroing on Drive Volumes • Thick Provision Eager Zeroed Group VMDK files of each storage type (TempDB, Logs, Data) in storage pools Use ParaVirtual iSCSI drivers for optimal performance 25 |
- 26. VIRTUALIZING SQL SERVER Reservememory for production workloads Also reserve memory in Hypervisor for Prod Servers Configure storage and data files based on SAN policy Baseline IO performance
- 27. VIRTUALIZING SQL SERVER Thinkcarefully about using lock pages in memory* Enable optimize for ad-hoc workloads DON’T OVERALLOCATE CPUs
- 28. DPA WITH VMOPTION » See correlations between SQL Server performance and VM resources, the physical host, storage and the network » Identify root cause of issues impacting end user response time 28 |
- 29. SUMMARY Understand impact ofstorage to your SQL Server Solid State Drives are FAST!!!! Gain understanding of the type of array that you are working on Design your SQL Server architecture to take advantage of that Understand your virtualization environment of how this all works together 29 |
- 30. QUESTIONS? © 2015 SOLARWINDSWORLDWIDE, LLC. ALL RIGHTS RESERVED. 30
- 31. FREE TRIAL • TryDatabase Performance Analyzer with VM Option FREE for 14 days • Improve performance • Quickly identify root cause of issues that impact end-user response time • See historical trends over days, months, and years • Understand impact of VMware® performance • Agentless architecture, installs in minutes 31 RE S O LV E P E RFO RM ANCE I S S UE S Q UI CKLY © 2015 SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED. www.solarwinds.com/dpa-download/
- 32. THANK YOU © 2015SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED. The SOLARWINDS and SOLARWINDS & Design marks are the exclusive property of SolarWinds Worldwide, LLC, are registered with the U.S. Patent and Trademark Office, and may be registered or pending registration in other countries. All other SolarWinds trademarks, service marks, and logos may be common law marks, registered or pending registration in the United States or in other countries. All other trademarks mentioned herein are used for identification purposes only and may be or are trademarks or registered trademarks of their respective companies. 32