SQL Server Performance Troubleshooting with Ozar Unlimited

Some Background

 Back in college the ability to serialize things to a file in C++ escaped me.  I had a straight up disability when it came to file IO.  I got around this disability by using databases because somehow it was easier for me to read and write into various database systems (right?).  This is how I gained rudimentary SQL skills, mostly in the world of Oracle and MySQL.  When I landed a job as a Software Engineer for National RV, I found myself developing small java based data entry apps that backed by MySQL.  Tragically National RV shutdown and I was out in the cold, cruel harsh world…then I found a job shortly thereafter at American Bath Factory [ABF], where I think my original title was “MAS 200 Support.”

At the time, ABF was using an ERP called MAS 200 and a home grown PHP web application called CRS.  We performed ETL between MAS 200 and MS SQL, so that CRS could access the ERP data and allow customer service reps to input RMAs.  Because I knew how to write queries and I had a small grasp on database internals, the “MAS 200 Support” title quickly changed to “Database Administrator” and I became the go to person for maintaining and optimizing the database server.  I read a couple books, crashed a couple servers, and I was getting the hang of things pretty quickly.  Sound familiar?

In 2009 while browsing the internet for stuff, I stumbled upon a man named Brent Ozar and his script sp_Blitz.  Through my interest of the script, I started reading Brent’s other posts.  I felt as if I had hit the mother-load of educational reference material.  This is when I started stalking…er I mean following his blog and SQLServerPedia videos.  The man never knew about me, but he had became a virtual mentor in the ways of SQL Server.  Through his blog, I discovered there was a DBA community, and I started learning real world DBA skills, without having to crash production servers 🙂

Since then my career has skyrocketed.  My ability to assimilate and optimize have proven to be extremely valuable and luckily in demand.  I was truly inspired by Brent Ozar and the community, and because of that inspiration (and the complete lack of a social life), I began a quest of constant improvement.  Years later, I still follow the Ozar Unlimited team through their posts, I attend their webinars on the regular, I’ve had a critical care session with Jes Schultz Borland, and recently I was finally able to meet Brent, Kendra and Jeremiah at the San Diego SQL Server Performance Troubleshooting session.

Day 1

As excited as I was to finally meet the people that have been so influential in my career, I totally showed up late -_- .  Somehow my calendar was set to a 9am start time (I blame ghosts) when in fact the training started at 8am.  I walked in during a break and it looked somewhat chaotic which was great because I thought everyone else was still arriving.  Brent was handing out training material and people were walking around getting refreshments.  I eventually found a seat and mentioned to the guy next to me something about how I thought I was going to be late.  He said, “Don’t worry you only missed one module, “ which made me laugh because I thought he was joking.  I later found out he wasn’t.  Damn, total fail.

The “Fundamentals of SQL Server Internals” module was awesome.  I very often find myself reviewing the Microsoft SQL Server 2008 Internals book because I think the internal functions of database systems are extremely interesting.  Learning the basics, and reviewing the basics frequently is in my opinion a great way to retain and master a a subject.  Maintaining structural integrity at the foundation of your knowledge can make all the difference when you are trying to troubleshoot problems and design solutions.  It was great that the team presented this information in a way that didn’t put us to sleep (read the internals book, see what happens).

  The module “How to Tell When TempDB is a Performance Problem” was a sneak attack.  I really haven’t had a good (or bad) experience with TempDB in the field, so I wasn’t originally interested in this module.  I’ve always followed recommended optimizations such as multiple TempDB files and placing them properly across fast disk subsystems.  During this module we got to learn more about the inner workings of TempDB, and I now have a new appreciation for it.  Knowing that TempDB does…EVERYTHING…makes me take a second look at my own understanding of the component.  This module definitely sparked a new found interest that is going to result in further research.

Another module that really stuck out was “Why NOLOCK isn’t enough: Finding Query Isolation Problems.”  This is where Kendra Little taught us about Read Commit Snapshot Isolation and how, if carefully and implemented, could cure many of our locking problems.  To be completely honest, I haven’t paid much attention to isolation levels, but that all changes now.  For me this module was a great introduction into isolation levels and how to avoid locking.  RCSI is now on my list for things I need to experiment with and implement.

Day 2

I made sure I was on time for Day 2 🙂 and they had breakfast waiting for us in the conference room.  The day started out with Jeremiah Peschka’s architecture madness of doom.  One thing I need to say about Jeremiah is that I always kind of considered him to be like the drummer of the band.  The drummer is completely necessary and critical to the process, but somewhat disregarded by the spotlight.  By Day 2 Jeremiah was proving himself to be a constant source of architecture and theory whoop ass.  The “Rules, Rules, and Rules” module was another seriously badass module where we discussed architecture, and theory behind the engine.  Jeremiah used words like: “Science”, and “Domains,” as he lectured about the Heap data structure, the nested loop join, the merge join, the hash join, and MVCC.  During this module, something dark and sinister was stirring inside (and I’m not talking indigestion), the more I was hearing someone else talk about architecture and theory…the more I wanted to keep hearing about architecture and theory.

“Seek and Destroy: How to Identify Indexing Problems” was great.  Pretty much anytime Kendra Little talks indexes, you better sit down and STFU, cause she knows a thing or two about them.  IMHO one of the more fun optimizations in SQL is the never-ending quest to tune indexes.  Because of how fun this optimization is, learning about indexes can never really be dull.  I’ve been following Kendra’s posts on tuning indexes for a couple years, so I was familiar with her tuning techniques which are very structured, in depth and win.  Her presentation style is very organized and illustrated, and the demos were gold.

Another module that I really enjoyed was “What Queries Are Killing My Server?” One thing that you can’t argue with is that Brent Ozar is extremely good and methodical when it comes to troubleshooting and fixing problems.  Getting into his head and seeing how he finds the killers, and deals with them, is entertaining and enlightening.  This is the first real introduction to extended events that I have had.  For some reason I’ve been avoiding them, although I started watching some PluralSight videos on them, I’ve just been sticking with an old profiler template that I made a few years ago.  After this module, I plan on converting that template into extended events and becoming teh homies with XE.

Day 3

Up to this point, I was learning so much and I was able to gauge my own skill sets realizing that I’m not doing too shabby.  After grabbing breakfast and socializing for a bit, it was time to begin.  I was feeling totally pumped and ready absorb more information when Jeremiah Peschka stepped up to the plate again, and damn, he laid the intellectual smack down on all of us.  He completely wtfpwnd our brains with “Caching,” “Dynamic SQL,” & Windowing Functions.”  I’ve had exposure to these subjects in the field and in text, but any exposure was still not enough to keep my mind from being blown.  The wonders of application caching, the beauty of handling dynamic SQL (the beauty of QUOTENAME() and PARSENAME()), and the pure amazement of Windowing.  By lunchtime I was kind of star struck.  Earlier I had said that I thought Jeremiah was like the drummer of the band, but after these three modules I realized that he is in fact a true Vatican Assassin Warlock.  You don’t fuck with Peschka’s knowledge…Peshka’s knowledge fucks with you!

Every module in Day 3 was amazing, but like I said earlier I only wanted to review the three that impacted me the most.

Other Stuff

It wasn’t just training with the Ozar Unlimited team, it was a complete experience.  They ate lunch with us and were able to somehow digest food while being bombarded with questions.  Two nights we got to accompany them to dinner where they proved to be interesting people on a personal level.  They kept things fun from start to finish, even threw in a contest, where I proudly walked away with a 2014 Justin Bieber calendar.  At one point Brent even showed us how to use Twitter and #sqlhelp.  I met a lot of other very cool DBAs from various parts of the county and had a great time getting to know each of them.

One thing I left with (little jars of jam and Hilton pens aside) was a list of items that I want to research and write about, if anything just to better understand and retain the skills that were taught to me during this training.  Becoming encumbered with schwag was not the only thing that happened during this journey, I also returned with a rather spiritual experience.  For years I’ve been riding the rainbow tail of database server dragons, but never truly understanding why I was drawn to them in the first place.  Did I just understand them?  Did they just understand me?  Was I part dragon myself?  Were my true birth parents database servers?  I’ve pondered these questions for years, and during this training, while I was learning how to be awesome with nunchucks of database administration fury, I realized that the theory and architecture of database systems is what I love.  Pages, Algebrizers, Hashing, Trees, and Maths are all fascinating components of databases.  Not only do they sound cool when you talk about them at a bar, they look great no matter what font you dress them in.

Conclusion

I have to say this was a 10/10 training session.  It was worth more than what we actually paid for it, and the Ozar Unlimited team can melt faces with laser beams of thunder and awesome.  I would recommend this training to anyone wanting to be serious about acquiring skills to advance their career as a DBA, and I would love to attend further training sessions with group.

Good Game Ozar Unlimited…Good Game.