TL;DR
As part of my PASS Summit 2023 community session, I created a hybrid of two of my favorite sys.dm_io_virtual_file_stats scripts. It captures data from the DMV at two different intervals, to help you diagnose SQL Server I/O related performance data.
The Bigger Picture
A few weeks ago, I had the honor and pleasure of presenting at PASS Summit 2023. My session, Is Storage the Root Cause of Your Performance Woes… or Not? was part of a Learning Pathway called Solving Real World SQL Server Problems featuring sessions by myself, Deborah Melkin (b), and Jeff Iannucci (b).
The inspiration for my particular session was to share knowledge about storage related performance headaches, to Andy-3-years-before, before I joined Pure Storage. I just celebrated my 2 year anniversary with Pure Storage, and to say that I’ve learned a tremendous amount about storage in these two years is an understatement. So this presentation was trying to address some of the more interesting performance & storage related nuances, beyond just looking at perf counters and DMV queries.
Scripts That I Love Using
That being said, in the course of the presentation, I did share two of my favorite existing scripts for working with sys.dm_io_virtual_file_stats.
The first script has been my go-to for the majority of my career, written by Paul Randal of SQLskills. Paul has a standalone version which queries the DMV but also has one that queries the DMV twice over a period of time, to obtain a more focused delta of your I/O and workload behavior.
The second script was written by my friend and teammate Anthony Nocentino. I really like Anthony’s script, because he calculates some additional data points that Paul’s script does not.
So, I did what any good DBA would do with two useful scripts – I merged them together! (all done with prior permission from both Paul and Anthony.) And that’s why I’m writing this blog today – to highlight that it’s out there and for you to try it out if you want.
If you weren’t able to attend PASS Summit 2023, no worries – my slidedeck is also in the same github repository. But a slidedeck only tells part of a story, so I am looking out for opportunities to present this session virtually in the coming months!
Thanks for reading and let me know if you have any feedback the hybrid delta DMV the script.
Every Byte Counts 