Years ago, when I was frustrated with my SQL Server’s performance, the SAN admin kept saying it was a SQL Server problem. When the SAN admin quit, I took his job – and discovered the ugly truth. In the years since, I’ve done a lot of projects with SQL Server and VMware on SAN storage, and I know how to find out right away if it’s really a SAN problem. Learn how to use disk testing tools in this session:
Along the way, we discuss:
- My Blitz: SQL Server Takeover Script – this T-SQL script helps you rapidly assess the health of your servers. I’ve added new backup throughput sections to show your backup throughput over time.
- Glenn Berry’s DMV Queries – uses SQL Server wait stats to prove if the server’s waiting on storage. Also check out Glenn’s book on SQL Server hardware.
- Microsoft SQLCAT disk latency recommendations – Top OLTP Issues and OLTP Best Practices. Yes, these these posts are old, and if your SAN guy complains about that, respond with, “Oh, are our disks not supposed to be as fast as they were in 2006?”
- CrystalDiskMark – the easy button of storage testing. It doesn’t mimic SQL Server at all, but that’s okay, because remember that we’re proving it’s a SAN problem.
- SQLIO – nowhere near as easy as CrystalDiskMark, but gives you much more fine-grained control. I still don’t really try to mimic SQL Server IO patterns exactly, but just use this to get a sanity check after the quick results from CrystalDiskMark.
- Tom’s Hardware Disk Performance Charts – to get quick ballpark numbers to compare your storage throughput. Yes, these are only single hard drives, but disturbingly, you’ll often find that poorly configured storage isn’t all that much faster than a single enterprise hard drive.
- Wes Brown’s Fundamentals of SQL Server IO – great educational series that goes into detail with lots of links.
- My SAN Best Practices – a collection of my posts about SQL Server on shared storage.