DMV Script for Diagnosing Storage Performance

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.

#NewStarNovember – A Look Back at the Impact of Speaking

New Stars of Data has had a tremendous impact on our technical community, helping to grow new speakers. But the task of finding, encouraging, and mentoring new speakers is always ongoing. To that end, wanted to share some thoughts for #NewStarNovember.

Let’s Do the Time Warp Again!

Because I got my start speaking through the encouragement of others, growing new speakers became a passion of mine as well. In late 2016, I put my money where my mouth is, with this T-SQL Tuesday: Growing New Speakers blog. I challenged prospective new speakers to blog about their ideas and existing speakers to share their experiences and wisdom. And to really drive home how serious I was, I openly offered mentorship to any new speaker who wanted it.

Then and Now

The round-up to that call was tremendous. If nothing else, I would encourage you to revisit that roundup and look over the names of the people who were categorized as New or Novice speakers. And consider where many of them are today!

I don’t know about you, but I think that’s absolutely amazing. We speakers often say things like “speaking opens doors” and other sayings like that. I’d strongly argue that this list of people, looking at them then and now, is solid proof that speaking and presenting pays off exponentially.

Call to Action

Are you thinking about getting into speaking? Or are you relatively new to speaking but want to keep growing that skill? I would like to repeat my offer from 2016 and offer mentorship once again.

If you’ve never presented before and take me up on this challenge I will take the time to work with you.

• If you want help developing your first presentation out, I will help you.
• If you are wary of putting your first PowerPoint together, I will help you.
• If you need ideas on how to write demo scripts, I will help you.

I will do whatever I can to help you begin this journey. But it’s up to you to take that first step. Trust me (and others) when we all say that it’s absolutely worth it!

It’s 2017 – Where Have You Been Andy?

Hello everyone and Happy 2017!

Yes, it’s Feb 27th – is it too late to still say that? 🙂

I admit that I’ve been extremely quiet in this blog over these past few months. But that’s because I’ve been extremely busy elsewhere! Doing what you may ask? Here’s a brief recap!

* December – Presented at UP (Yooper) PASS User Group: I presented virtually as their debut speaker!
* January – Presented at SQL Saturday Nashville: a great SQL Saturday to kick off 2017!
* February – Presented at SQL Saturday Cleveland: another fantastic event that I was thrilled to be a part of!
* February – Idera Twitter #SQLChat: Idera invited me to host this monthly Twitter event. Was a fast & furious flurry of fun Twitter chatter (say that 3 times fast).
* February – Saturday Night SQL Virtual Chapter: This VC approached me back at PASS Summit and I had a blast presenting. Hope to return again!

So I’d think that that’s a decent amount of community activity, right? But it doesn’t stop there!

SQL Saturday Chicago

In less than two weeks, SQL Saturday Chicago will be upon us. I’ve taken on a new role, running pre-cons this year, in addition to helping keep the lights on with the rest of Team SQL Saturday Chicago.

One thing that makes SQL Saturday Chicago special is that it is the 600th SQL Saturday! In addition to that, ours is the first SQL Saturday to feature the new PASS branding! In fact, our first piece of attendee swag showed up earlier today, which is what prompted me to finally sit down and write this blog post.

Oh, you want to see it?

We’re pretty jazzed! Our event hit the waitlist TWO MONTHS IN ADVANCE! Can you believe it? We couldn’t! No pressure here, right? 🙂

SQLBits

Yes, you read that header right. I’ll be flying to the U.K. to attend AND present at SQLBits – the largest SQL Server conference in Europe!

Speaking at PASS Summit was the first pinnacle of my speaking career, which I attained last year. My next goal was to speak internationally. I just never expected this opportunity to happen so quickly, but the dates for SQLBits 2017 were favorable, so I submitted and was accepted to present not just one, but TWO sessions! Afterwards, I will be spending the week following SQLBits in London with my wife. Never having been before, we are both looking forward to exploring the magnificant city!

On The Horizon

Beyond this, I have a few SQL Saturday’s on my radar that I’m eyeballing. PASS Summit 2017 session submissions will be forthcoming as well. And I still have a new session that I will be developing for 2017, focused around T-SQL tips & tricks, so keep a lookout for that!
Here’s to a productive 2017 of learning, sharing, and SQL Server fun!

T-SQL Tuesday #84: Growing New Speakers Round-Up

Welcome to this month’s T-SQL Tuesday Round-Up! A few weeks ago, I sent out a call for bloggers and must say that I’m utterly blown away by the response. A whopping FORTY bloggers responded last week with contributions for Growing New Speakers!  Four – zero!  You people are all amazing!!!

I’ve decided to split the list of contributors into three groups.

• New Speakers: Those who have never presented before!
• Novice Speakers: Those who have presented just once, just a few times, or perhaps long ago in a galaxy far, far away. Generally if you’ve only spoken a handful of times, been speaking for less than a year, and/or self-identified as a new-ish speaker, you were grouped here.
• Experienced Speakers: Those who are seasoned speakers.

NEW SPEAKERS

1. Andrew Pruski-First Foray Into Presenting: @dbafromthecold
   Shares story of first presentation
2. Angela Tidwell: @angelatidwell
   Writes about things learned at PASS Summit about presenting
3. Arun Sirpal-SQL Server – Reconfigure: @blobeater1
   Explores RECONFIGURE in a technical blog for a first presentation
4. Chris Voss: @ceedubvee
   Writes about upcoming first presentations and muses how to progress further
5. Constantine Kokkinos-Exploring DBA Tools or: How I Learned to Stop Worrying and Love PowerShell: @mobileck
   Blogs about DBATools & Powershell for first presentation
6. Deb Melkin-If I Were a Speaker…: @dgmelkin
   Explores three ideas for a first presentation
7. Devon Leann Ramirez: @restinbeachface
   Writes an introduction for first presentation
8. Jo Douglass: @jodouglass
   Writes about 1st presentation topic idea: surrogate keys as a DW/ETL anti-pattern
9. Michelle Haarhues-On the Doorsteps: @mhaarhues
   Explores Women In Technology and first presentation about women presenters
10. Robert Preseau: @robertpreseau
    Writes about overcoming mental obstacles to speaking
11. Scott Millard-Taking the next step – becoming a speaker: @leftseatsql
    Accepts the challenge to speak
12. Tywan Terrell: @tyawnterrell
    Writes about T-SQL fundamentals as first presentation topic

NOVICE SPEAKERS

1. Anthony Nocentino-Public Speaking – The First Time: @nocentino
   Shares an updated recap blog from first presentation
2. Bjoern Peters-Become a Speaker at a PASS Event or other Meetups – share your knowledge: @sql_aus_hh
   Writes about elements that make for a good presentation
3. Chris Lumnah: @lumnah
   Reflects on lessons learned after first presentation
4. Chris Sommer: @cjsommer
   Shares things to think about when writing a new presentation
5. Kenneth Fisher-Zip to Speaker: @sqlstudent144
   Recaps prior blogs about speaking & offers encouragement
6. Kevin Hill-Speaking & Presenting: @kevin3nf
   Explores DBA Basics for Non-DBAs as a “first” presentation idea
7. Matt Gordon: @sqlatspeed
   Shares two key lessons learned
8. Mike Kane: @tcp1433
   Explores target audience, topic, and format
9. Monica Rathbun-Helping New Speakers: @sqlespresso
   Writes how to get started
10. Wylie Blanchard-Use Video as a Tool to Enhance Speaking Skill and Create Content: @wylieblanchard1
    Discusses value & benefits of videotaping one’s self

EXPERIENCED SPEAKERS

1. Alexander Arvidsson: @arcticdba
   Writes about body language
2. Andy Yun-Building Your Slidedeck: @sqlbek
   Shares slidedeck building tips
3. Brent Ozar-The Three Parts of Every Presentation: @brento
   Explores types of presentations
4. Derek Hammer-Building an Hour of Content: @sqlhammer
   Shares advice in building your first hour long presentation
5. Doug Lane-Conquer Your Fear of Presenting with a Gift-Giver’s Mentality: @thedouglane
   Presents a video blog about the Gift Giver’s Mentality
6. Erin Stellato: @erinstellato
   Recaps 4 prior blogs about speaking & addresses topic of comfort
7. Jes Borland-Dealing with Failure: @grrl_geek
   Discusses how to handle failure on the fly.
8. John Deardurff-Speaking about SQL: @john_deardurff
   Shares tips about learning itself
9. Kathi Kellenberger-New Speakers: @auntkathi
   Writes about how to field questions
10. Lori Edwards-So You Want to Present: @loriedwards
    Builds on prior blog post about speaking, sharing advice learned since
11. Michael Swart-I’m Trying To Kick My “Undo Button” Habit: @mjswart
    Writes about speaking at PASS Summit for the first time
12. Mike Fal-Getting Ready for your Presentation: @mike_fal
    Shares how to prepare the day of your presentation.
13. Mike Lawell-Speak in Public? What? Me?: @sqldiver
    Shares journey about starting speaking.
14. Riley Major-No One Wants to Eat You: @rileymajor
    Shares his personal story of speaking, followed by a huge list of tips, tricks, and resources
15. Rob Farley-How I Prepare for a Presentation: @rob_farley
    Writes about deeply exploring your topic
16. Rob Sewell-Speaking? You? Go on.: @sqldbawithbeard
    Shares advice about getting into speaking
17. Steff Locke-A note to (potential) new speakers: It’s ok not to be perfect!: @stefflocke
    Reinforces that perfection is not necessary.
18. Steve Jones: @way0utwest
    Encourages readers to speak.

WHAT COMES NEXT?

As I pledged in the first blog post, I will now be reaching out to all New Speakers, to offer personalized feedback.  A number of Experienced Speakers also volunteered to help, so I will be working out those logistics.  Additionally, if any of the Novice Speakers would like the same kind of help, please contact me – I will add you to the list too!

There are numerous opportunities to present: internally to coworkers, Toastmasters, User Groups, SQL Saturdays, & Virtual Chapters.  And all of them are always looking for new speakers!  When will you speak next?

T-SQL Tuesday #84: Building Your Slidedeck

Welcome to another edition of T-SQL Tuesday. I am this month’s host, and the topic is Growing New Speakers. My contribution to this month’s T-SQL Tuesday was inspired via a tweet by Jonathan Cox (b|t).

When I gave my very first presentation, I had next to no experience with PowerPoint. In fact, I found it a bit intimidating. Where to start? How should I make it look? Do I have to pick a color scheme?

Not to worry! When you first open PowerPoint, there are numerous basic templates already loaded! My advice is to select a template that is simple. Don’t select one that has a background with very cluttered graphics. Visual noise is distracting and is the last thing you want to worry about.

For your first presentation slidedeck, there’s no need to get fancy. Keep things simple like the templates below.

Readability is very important. What may look fantastic on your 1920×1200 laptop screen that’s 2 feet from you, will look drastically different when it’s been downsized by a projector to 1024×768 and is projected to an entire room.

Font size is the first key factor in Readability. Karen Lopez (b|t) tweeted an excellent tip, that I used when I was working on my PASS Summit slidedeck.

I readjusted a handful of my slides to pass this test (thanks Karen!).

Color contrast is important. Different rooms have different lighting conditions, so be sure to use a simple, high-contrast color combination so everyone in your room can read your slides. A lightly colored or white background with dark or black text works best. Stick with the basics – they work.

When it comes to amount of content, don’t write huge walls of text on slides. Put only your main talking points. Your speaking will fill in the corresponding details.  And if you do have a lot of slide content, don’t hesitate to split it into two slides.

Images and clip art. Some like art, I generally shy away from it – it’s all personal preference. If you do use an image from somewhere, you must give credit. Generally a footnote is acceptable. I prefer to rely on Creative Commons 0 images. Makes life hassle free. Check out Kendra Little’s (b|t) blog post all about Easy Free-Use Images.

I hope this collection of tips helps you on your way to building your first slidedeck!  And if you still find yourself a little stumped, Google/Bing is your friend!  There are many out there, who have written numerous articles & blogs on how to build effective slidedecks.  Leverage their collective knowledge.

Happy Powerpointing!

October of Awesome!

I can’t believe that we’re halfway through October! This month has simply flown by. But the best is yet to come!!!

This Tuesday, I will finally be presenting at my own Chicago Suburban SQL Server User Group! I will be debuting my new and improved Why Your Datatype Choices Matter presentation.

Then a week from today, I am honored to be back at SQL Saturday Oregon. There, I will be presenting this year’s flagship presentation: Performance Pitfalls of Code Reuse. I’ve had a lot of fun presenting this session over the course of this year, and it’s been a fantastic way to also showcase my sp_helpExpandView tool.

Finally, a week and a half from now, I will be making my debut at PASS Summit 2016, presenting Why Your Datatype Choices Matter! This improved session is a 200 level session, with some splashes of 400 level content thrown in! There’s something for everyone! I’m blessed to be in the very first speaking slot, Wednesday morning at 10:15AM in Room 2AB! Come see me after the introductory keynote!

I’m really looking forward to seeing friends, new and old, at PASS Summit. And if we’ve never met, do come find me and say hello!

Summer of Presenting!

24 HOURS OF PASS
It’s been a couple of months since I was honored with an invitation to speak at PASS Summit 2016. I’m still overwhelmed, amazed, and grateful, when I reflect on my career journey since my first PASS Summit in 2013. Speaking at the PASS Summit is an opportunity that I never imagined that I would receive.

Fast forward to a few weeks ago, when I received an e-mail from PASS, inviting me to present at 24 Hours of PASS: Preview Edition!! This special event highlights select speakers from the upcoming PASS Summit.  It’s one thing to be picked just to speak at PASS Summit, but is a double-honor to be asked to present at 24 Hours of PASS!

Because I’ve presented my Summit Session: Why Your Datatype Choices Matter, at 24 Hours of PASS: Community Edition in 2015, it was suggested that I present another session to highlight myself as a speaker.  I decided to offer my new Performance Pitfalls of Code Reuse session, which I debuted earlier this year.  If you work with developers who favor an Object-Oriented approach to developing T-SQL, then this session is for you!
USER GROUPS & SQL SATURDAYS
Since the beginning of the year, I’ve spoken at 6 SQL Saturdays and 2 User Group meetings!  At SQL Saturday Iowa, I debuted my 3rd session, Performance Pitfalls of Code Reuse, with great success! I’ve presented it 3 times thus far and have gotten rave reviews every time! I’m just thrilled that attendees are getting a lot of out of it and enjoying it!

But the fun isn’t over!  In addition to 24 Hours of PASS, I have 3 more User Groups (and hopefully 1 more SQL Saturday) lined up!  I’ll be presenting at the Chicago Downtown User Group in early September, followed by MADPASS in late September.  I’ll be presenting my Performance Pitfalls of Code Reuse session at both.  In mid-October, I’ll be presenting my new and improved Why Your Datatype Choices Matter session, at my Chicago Suburban User Group.  That’ll be a week before I present it at PASS Summit.

 

Things are really exciting for me right now, and I couldn’t be happier to be sharing the things I’ve learned with all of you.  Hope to catch you somewhere soon!

What a Difference a Year Makes!

A year ago, last June, is the last time I wrote a “soft” blog update. Since then, a tremendous amount has changed.

TL;DR

OMG I’VE BEEN SELECTED TO SPEAK AT PASS SUMMIT 2016!!!!!

 

And how I’ve felt since getting the acceptance e-mail last night.

 

LONG VERSION

CAREER CHANGES

Since last June, I’ve changed employers. But more importantly I shifted my career path. I’d spent the better part of 10 years in Database Development roles, and felt it was time for a change. So I took an opportunity as an Operational DBA in a company with an extremely large environment. Now I’m working at a Managed Services firm, with a large team of other DBAs, on a variety of clients with some very large infrastructures.

CHI-SUB USER GROUP

The Chicago Suburban SQL Server User Group has continued to be a success. We’ve just hit our 2 year anniversary! We average 20-25 attendees a month, many of whom are regulars. And occasionally, depending on topic, we get more and/or even see new faces, which I always love!

SPEAKING – SQL SATURDAY

I have also spent a lot of time speaking, over the past year. I’ve had the honor of being selected for a number of different regional SQL Saturdays. I was also flattered that SQL Saturday Portland accepted me. Portland was a big deal because that SQL Saturday preceeds PASS Summit, and often attracts many high-end speakers. I like to think of it as PASS Summit pre-partying! 🙂

SPEAKING – NEW SESSIONS

At the beginning of 2015, I developed a new SQL Session: Uncovering Duplicate, Redundant, & Missing Indexes. Like my first session, it has been growing, changing, and maturing, with each subsequent presentation. I’ve been very happy with it, as it addresses a niche of Indexing that one does not commonly see offered in sessions.

I decided to continue my “1 new session a year” rhythm, and spent a good chunk of Q1 & Q2 developing my new session “Performance Pitfalls of Code Reuse.” I wanted to create a session to highlight my sp_helpExpandView (https://sqlbek.wordpress.com/tag/sp_helpexpandview/?order=ASC) community tool, while diving deeper into the technical “why’s” that drove me to write the tool. I presented it for the first time a few weeks ago at SQL Saturday Iowa and it was a huge success!

SPEAKING – PASS SUMMIT 2016

In 2015, I submitted to speak at the PASS Summit for the very first time.  I only had one session in my speaking portfolio.  I figured given the # of speakers & abstracts that get submitted, that I didn’t have much of a chance.  And I was not accepted, but I was okay with that.  I got some interesting feedback and I went on.

For 2016, at the time of Call for Speakers, I had 2 sessions in my portfolio and was developing my 3rd.  So I submitted all three.  I also took lessons learned from my prior year’s submission, refined my abstracts, and provided far greater details in my submission application.  And well, I’d like to think that paid off in spades!!!

I attended my very first PASS Summit in 2013.  At the time, I never thought I’d get into speaking.  But now 3 years later, I’m not only a speaker, but am absolutely flabbergasted, humbled, and blown away that I am been selected to speak!

http://www.sqlpass.org/summit/2016/Sessions/Details.aspx?sid=47480

Yes, that’s me… that’s really ME!  EEP!

I will have more thoughts to share later, but I’m still absolutely floored by this honor.  I want to thank everyone on the Selection Committee for all of their hard work!  Congratulations to my fellow speakers, and to those who were declined, I’ve been there too – chin up and carry on, you guys are awesome too!

 

Post 24 Hours of PASS & Other Speaking Engagements

24 Hours of PASS

Last week, I had the thrill and honor of presenting for 24 Hours of PASS. I presented a shortened version of my successful Every Byte Counts: Why Your Datatype Choices Matter session and had a blast.

While I’ve presented this session in person a number of times, I faced some new challenges this time. First, I was presenting a new, shortened version (the original has grown to 70 minutes of content!). The second challenge was presenting online – a much bigger challenge than one might think. I had no crowd to feed off of. Additionally, I had to consciously remember to make eye contact with my webcam, instead of looking at my slides. Finally, I had picked up a head cold the week prior. 30 minutes before my start, my nose decided to start running, so I had to excuse myself a few times to tend to that. A little embarrassing & irritating, but I suppose it’s no different than presenting in person. After all, the show must go on!

Despite the challenges, it was an amazing experience. I was honored to have been given the opportunity and want to thank everyone who worked hard to put another successful 24 Hours of PASS together.

Here is Matt Penny’s (@salisbury_matt) Sketchnote from my session.

PASS Summit

On another note, I also received notice last week that I was not accepted to speak at PASS Summit. I wasn’t upset about this. After all, almost 900 abstracts were submitted, of which only a 15% were accepted? While I know that my Every Byte Counts session is useful information, and COULD be Summit quality, it is also a niche topic that given the volume of other submissions, would probably lose out. And it did, but I’m okay with that. I will not be dissuaded from speaking or submitting, and am still proud to be part of this community!

What Next?

So what’s next? Last week, I was also accepted to speak at SQL Saturday Indianapolis! I’m thrilled to be making my return to Indianapolis & joining a great group of presenters. Additionally, I will be presenting remotely to the Las Vegas User Group. This will be the first time I’ve done a remote User Group session, but I’m hoping that this will be the first of many! And finally, I just submitted for SQL Saturday Minnesota!

Keep on Talking!

I’m happy to share that my SQL Presentation spree for 2015 is going strong! I wanted to highlight when/where I’ll be presenting again, in the next month.

SQL Saturday Iowa

After several years of conflicts, I’m thrilled to finally be attending SQL Saturday Iowa! I will be debuting a revised version of my Every Byte Counts presentation. The original version of the session had grown to 70 minutes of content and I needed to compress it to fit 60 minute speaking slots.

FoxPASS

My good friend Jes Borland (b|t) runs FoxPASS and I’ll be making the trek up there to present! This will be the second time I am presenting my other session: Cleaning House: The Indexing Edition. I had a lot of fun with it at MadPASS, so am looking forward to giving it again.

You can watch online too! Lync info is available on the FoxPASS website.

24 Hours of PASS

I am without words to describe how I feel about 24 Hours of PASS. I’ve been watching these semi-annual webinar events for years now, and now I get to present in one! I’m thrilled to be joining a fantastic line up of speakers. I will be presenting my Every Byte Counts session

Three presentations over the course of the next month – wow, that’s a lot! Nevertheless, I’m jazzed to share what I’ve learned. Hope to see you all soon!