Voice of the DBA

Tag: BrentOzar

  • Mastering Index Tuning–Day 3

    This is a short series of posts on the courses I took with Brent Ozar. I actually completed the courses in the past, but I wrote notes and wanted to revisit the way things went.

    This post looks at the Mastering Index Tuning class. Other  posts are:

    Day 3

    As with Day 2, we begin with reviewing the labs from yesterday. These were harder labs, and Brent spent time looking at how he solved the labs, referencing parts of solutions some people had. This took awhile, with a break in the middle.

    As usual, we can ask questions and discuss the solutions in Slack, which Brent keeps an eye on.

    We start the lectures with artisanal food, which Brent does enjoy. Hand crafted items from the chef, which felt funny since my car killed something and left an organ of some sort in the bathroom.

    The analogy is that there are artisanal indexes, like those on computed columns, indexed views, and filtered indexes. These are items that can help in specific situations, but in general we don’t want to use them.

    I like that Brent brings in the experience they’ve had with clients, noting that some of these features don’t work well.

    The afternoon lab is fix some really bad reporting procedures with indexes (regular or artisanal) or changing code. I know I can’t always change code in databases, but this gives us a chance to try things. I ended up changing some code, but not much. The lab review after lunch was interesting, as Brent had a strange result with the last proc. Looking forward to seeing his debugging of this later.

    The lecture after lunch moves to the end of D.E.A.T.H, heaps. I hate heaps, so this was interesting. Brent agrees with me, you really need a CI on the table. Maybe there are some reasons to not use one in a situation, but most of you need to just add a key.

    The last part of the afternoon looks at the impact of CIs and then constraints and FKs. The CI part is interesting. I see lots of people talking about how to decide on this. I tend to lean towards Brent’s view, which he’s presented on and it’s in the class. Take the class if you want to learn (I don’t want to republish here).

    For FKs/constraints, the module had lots of discussion. People think about FKs in interesting ways. I’ll have to re-watch this as I got busy in the middle with other stuff and missed some lecture.

    The final lab is a big one. Use all the skills from the three days of the class. Restore the db, run a setup that messes up indexes, then fix things. It was a challenge, and I burned about 12 minutes deduping and eliminating indexes, then about 20 coming up with more to add. The creation took quite some time, so I never really got around to tuning, and since this is only part of my day, I had to stop. I do have some real work to do.

    The final lab solution goes up the day after, and what Brent came up with was interesting. I liked watching the videos later to see how he approached the issues and solved them. I like that there wasn’t “one” solution, and he talks about how we might solve the lab that would be different than production.

    That’s important, and it’s something that I appreciated in this class. I know better, but it’s always good to be reminded that the class is a game, a model of what could happen, but in the real world, these are just tools that might help, but could hurt. Judgment is still needed.

    The Aftermath

    One thing I like about this class, which I’ve missed in some live classes, is that I can re watch sections of the class later. The class page has a list of all the lectures and labs, with each containing a video. Some might be from my class, some from previous ones. Since this is delivered and recorded in a modular fashion, Brent can update sections over time.

    I went back to watch the first Artisanal index module, as I was distracted that morning by something at work. That was a nice benefit.

    The Final Word

    This was a great class. I haven’t been to a real class across multiple days in awhile, and I think the format of some lecture, a lab (with interactivity), and then a review of the lab, was great.

    The lectures were interesting, and I learned a few things. The labs were challenging, designed to force you to work within constraints to tune something. Indexing is often a place where you can make changes and rapidly affect your system. The effects could be good or bad, so you need to be sure you are proceeding in a methodical fashion and also capturing metrics on the changes.

    If you’re interested in the class, you can visit the Mastering Index Tuning page to learn more and purchase the class.

  • Mastering Index Tuning–Day 2

    This is a short series of posts on the courses I took with Brent Ozar. I actually completed the courses in the past, but I wrote notes and wanted to revisit the way things went.

    This post looks at the Mastering Index Tuning class. Other  posts are:

    Day 2

    The day starts by looking at homework from last lab. How does Brent do this? One nice thing is that Brent limits the time here for himself. He solves the indexing lab, but stops before some people would. He explains this as he tackles indexing like this. Make some changes, but set a time limit. Then see deploy them and evaluate again after some time.

    We get to watch how he’d solve the lab, and I popped open my VM to check what I’d done. After all, it had been like 16 hours. I saved each lab work in a file on my VM, which was good. I could reference the order in which I’d done things and since I’d save the before/after stats, I could compare with Brent.

    The rest of the day was similar to day 1, with a lecture, a short lab, more lecture, and a long lab over lunch. During this day, we looked at blocking, which is one of those areas where many people have issues. Brent has built a lab that creates blocking, so we can see it happening in our instances.

    While there are different ways you can clear blocking, the challenge here is to use indexes to get rid of blocking. This isn’t the best way to clear blocking, but it is an option, and again, the challenge here is to focus on indexes.

    This was a better day for me, getting into the swing and rhythm of the class as well as starting to feel challenged. I focused more on the labs here, which I wish I’d done a bit more of this on the first day.

    The lab at the end of the day was more complex, though I didn’t have time to get it all done. I got a first pass that seemed to solve some of the issues, and I had to stop since other work was calling. Still, a good day.

    If you’re interested in the class, you can visit the Mastering Index Tuning page to learn more and purchase the class.

  • Mastering Index Tuning–Day 1

    This is a short series of posts on the courses I took with Brent Ozar. I actually completed the courses in the past, but I wrote notes and wanted to revisit the way things went. I started with the Mastering Index Tuning class.

    I took the class a few weeks ago, but I scheduled this out for when I returned from vacation.

    A Late Start

    The classes started at 9am Eastern time. That’s 7am for me, which is a touch early. I’m usually awake, but not always at my desk. In this case, I was hustling a bit to get through a morning routine, including a shower since I had a meeting during the day.

    I managed to get to my desk and start the GoToWebinar and found a welcome slide up that noted the class will start at 5 minutes after. That’s cool since it meant I could go start coffee without missing anything.

    This also gave me a chance to switch to speakers for the audio. I’m at home, and have my own office, so I was glad to not wear a headset all day. We were on mute, but I muted myself with a hard button, just to be sure there weren’t any disturbances from my end.

    Setup

    I’d gotten the email the night before with a lab VM and credentials. After getting coffee going (priorities are important), I came back, logged in and fired up SSMS, leaving things alone.

    I also popped open the webinar and Slack. That’s 3 things going on at once. Fortunately, I have three monitors, so this worked out well. I put the VM in front of me, since it’s always easier to concentrate straight ahead. The left monitor got Brent and the GoToWebinar. That meant a full screen for slides and a small area for the webcam Brent runs. I find it easier to follow along if I can see someone. I then put Slack up on the right screen, set to the channel for the classes.

    I think that taking this on a laptop would be tougher, but doable. This is especially true if you are watching the recorded items. There isn’t a rapid movement of slides, so you could focus on Slack and listen, or watch and come back to the VM when appropriate. I followed along a few times with Brent, so I was glad for the multiple monitors.

    The Day 1 Flow

    The class opens with introductions of Brent and a few people mentioned from Slack. He does orient us to remember to open Slack for questions and discussion. One nice thing here is there isn’t any time spent chatting with each other, or a limited amount in Slack.

    We introduce the VM and how it’s configured, along with some explanation. It might feel unnecessary, but I expect that most people have questions or would have done one thing differently. Across the class, we each might think about one different thing, so it’s good. I like that Brent also warns us what not to touch, since it’s easy to blow things up. We also start up a workload with SQLQueryStress before a break.

    The first thing is a lecture about how to use indexes and some demos. After some basics, we get into how we get information from the DMVs and elsewhere, and Brent uses various sp_BlitzXX scripts. I think that’s good as he’s put time into these scripts to make them helpful. They’re not necessarily better than those you might build yourself, or get from other speakers, but they are useful.

    We have a first mini lab, which we work on and then Brent reviews what he’d do. Then a little more lecture before a lunch lab. In this one, we have to analyze and post our proposed changes in Slack. Brent then acts as a manager to approve changes.

    I like this, and it’s a good way to sanity check yourself without just doing something. The lab is a 2 hour lab, but it’s designed as a lunch break as well. Good for some, but it’s 9:30ish for me. That’s early. Still, I do the lab and then spend time on other work.

    After lunch, we review the lab with Brent, watch what he does. That’s good, and it helps people learn, without picking on their solution. The afternoon passes with more lecture and then a final lab that we can tackle then or do in the morning. Since my time is offset and the class starts at 7, I decide to just do it then. Again, propose changes and get them approved.

    One nice thing is that Brent is watching Slack while presenting, so he can respond to comments or questions inline with what he’s teaching. This also makes for some fun distractions with comments and questions, and things aren’t scripted. Brent will pop something open in a browser to teach.

    A long day, made longer since I still need to do some SQLServerCentral stuff, but it was a good day.

    If you’re interested in the class, you can visit the Mastering Index Tuning page to learn more and purchase the class.