Marsha Pierce How to talk to your Storage

Marsha Pierce How to talk to your Storage Admin!

Who is this Person? • • Marsha Pierce Lives In Nashville, TN Database Architect and Admin for 23 years Database Solutions Architect at Pure Storage Architect at Asurion Architect at HCA SQL Server, Oracle, Postgre. SQL, DB 2, My. SQL, Large Scale OLTP database, DR, Agile Development, Data Warehousing, Data Lakes • • Driven Storage Admins and VM Admins Crazy for Years She has 23 years of experience as a DBA, which means she has broken and fixed a lot of stuff.

Why Do DBA’s and Storage Admins Argue?

Moore’s Law In Short Processing and RAM have been doubling every two years Moore's law refers to an observation made by Intel cofounder Gordon Moore in 1965. He noticed that the number of transistors per square inch on integrated circuits had doubled every year since their invention. Moore's law predicts that this trend will continue into the

Moore’s Law Has Not Applied to Traditional Spinning Disks Unfortunately, performance of traditional spinning disk storage hasn’t increased at the same rate, which has created a performance gap between the CPU and the HDDs traditionally found in SAN and NAS architectures. In order to overcome this performance gap, the traditional approach has been to stitch together mass quantities of hard drives.

In Short Storage Is The Biggest Infrastructure Bottleneck For Most Databases

When Your Storage Admin Tells You That Everything Is Fine Because There Are Millions of IOPS AVAILABLE

Misconceptions DBA’s Have About Storage • SAN Admins are continuously run monitoring the overall performance of the SAN • Monitoring affects the performance of traditional SANS. So unless there is an issue, monitoring is usually turned off • That they do not have storage issues because their SAN admin showed them that they have millions of IOPS available. • OLTP systems need low latency. Latency and IOPS and different measures.

Misconceptions DBA’s Have About Storage (p 2) • That their storage is fast because they have SAN Auto Tiering. • Auto Tiering is not as automatic as it sounds. It can take weeks for something to move up a tier. • My Data is spread across 1000’s of spindles because I have a SAN. • The data is usually spread across something more like 10 or 20. Also Raid is for data protection not for performance.

Symptoms of Storage Performance Issues Erratic performance individual query times Slows down bulk load Longer than Normal or Erratic Backup Times Long Restore Times Random performance issues that disappear Performance Issues That appear consistently at the same time. • Groups of Machines Having Performance Issues At The Same Time • • •

So Are You Monitoring Your Storage DBA’s Actually Have The Ability To Monitor Storage Performance Within SQL Server • sys. dm_io_virtual_file_stats – AN AVERGE PER FILE SINCE LAST RESTART OF SQL

Great Things People Have Done With File Stats • Sp_Blitz – Brent Ozar • https: //sqlperformance. com/2013/10/t-sql-queries/io-latency • https: //www. littlekendra. com/tag/sys-dm_io_virtual_file_stats/

Monitoring Solutions That Do It For You https: //www. idera. com/ http: //www. solarwinds. com/ http: //www. apexsql. com/ http: //www. minionware. net/

DBA Checklist for Optimizing San Performance • Check For Disk Alignment • • • You Need 1024 Offset 64 k Block Size /L or -Use. Large. FRS when formatting (Windows 2012 or Greater) • Turn On Instant File Initialization (i. e. Perform Volume Maintenance Tasks). This is located in local policies and user rights assignment • Drop Unused Indexes • Spread Your Data Out In File Groups And Data Files • Check Your Queue Depth On Your HBA Cards

What Are Your Storage Requirements • What Kind of Performance Do You Need? • • • IOPS Latency Are All IOPS The Same? Block Size Matters Most Arrays do one or the other well. They do not do both well. What Is Your Capacity For Downtime / Maintenance • • • Are you a 24 hour shop? If a component fails what happens? What do code upgrades look like? What happens when you need space? Are you being back charged for Storage? If so you are the Customer

What Are Your Storage Requirements (p 2) • What Features Do You Need? • • • Encryption At Rest • Is it by volume? • Can you encrypt an unencrypted drive? • What does it do to performance? Deduplication Compression Thin Provisioning Clones Storage Point in Time Recovery (often 5 x or higher overhead) Then if you replicate (10 x overhead)

Mean Time To Restore – It’s Critical • Typicall DR plans center around hardware failure. • Typical Recovery/ DR Strategy • • SAN Based Replication from point A to Point B as part of DR DBA’s reply on local backups to disk to fix any production issues should they occur • What about Data Corruption? • How long does it take you to restore from backup?

Mean Time To Restore – It’s Critical • Leverage Point in Time snapshots • • • Databases are becoming larger and larger Large Multi Terabyte databases can take hours to restore. What is your SLA for recovery in the event of an outage. Protect yourself from Data Disasters Snapshots Can Be Used For More Than Recovery! • • • Use Snapshots to refresh your reporting systems and data lakes. Reseed replication with Snapshots Initialize Replica’s in AG’s with VSS snapshots Use them to create templates for on premise cloud solutions Key for Agile Development and Continuous Integration. Quickly Test Bug fixes against actual data set.

Why You Need Snapshots

Recent Changes To Legacy Storage Landscape • Hybrid Systems • • All Flash Arrays • • • Are they a legacy retrofit Were they redesigned to be more effective for flash? Converged Infrastructure • • Legacy Systems retrofitted with a mix of spinning disks and an all flash tier Best of Breed for Storage, Network, and Compute. Pre-Configured and size for workloads Storage, Compute, and Network can scale independently Can have host both physical and virtual Hyper-Converged Infrastructure • • • Multi Compute Nodes with direct attached storage and networking. Software is used to unify nodes. Storage, Network and Compute do not scale independently. Not typically a good fit for high performance computing Great fit for remote locations

Recent Changes To Legacy Storage Landscape • • Public Cloud • Microsoft, AWS, & Google • • Easy to spin up new environments. Can be expensive Private Cloud • • • A converged or hyper converged system Were they redesigned to be more effective for flash? NVME • • • Non Volatile Memory Express Storage, Compute, and Network can scale independently Can have host both physical and virtual

The Future § § § § QLC (Late 2018 thing) NVMe/NVM over fabrics (Already here!) 3 D X-Point (Optane) (Already here!) NV-DIMMs (Already here!) Ro. CE (Already here!) Z-NAND (Late 2018/2019) Even crazier stuff (Re. RAM, MRAM) (Already here!)

Thank You Sponsors Platinum Gold Silver Bronze Swag Venue

Thank-you!