Mastering Index Tuning

(38 customer reviews)

$995.00$2,995.00

You need to speed up a SQL Server app, but you only want to make index changes. You don’t want to buy hardware, change SQL Server, or change the code. Good news – I’ll teach you how in 3 days of learning and fun.

Clear
  • $0.00

Product Description

You need to speed up a SQL Server app, but you only want to make index changes. You don’t want to buy hardware, change SQL Server, or change the code. Good news – I’ll teach you how in 3 days of learning and fun. Join me, Brent Ozar, as I explain how to make your SQL Server apps go faster just by tuning the indexes.

You’ll work along with me in a VM – each student gets their own SQL Server 2019 lab VM in the cloud with the Stack Overflow database and running workloads. If you buy the version without a lab VM, you get instructions on how to set up your own lab SQL Server to follow along.) Each day is structured like this:

  • 9AM-Noon – lectures, where I explain the concepts you’ll be working on
  • Noon-2PM – lab and lunch. It’s up to you whether you work on the lab for an hour first, then go to lunch, or vice versa, or spend more time on the lab. Or have martinis for lunch. Whatever.
  • 2PM-3PM – you watch me work through the lab you just did, explaining my techniques as I go.
  • 3PM-4PM – afternoon lecture
  • 4PM-5PM – lab homework (and you can also work on this later if you like – your VM will stay on overnight.)

This mix of lectures and labs helps reinforce the topics you’re learning and makes sure that you’ll be able to tackle the same kinds of projects in your own environment. (Many students do just that: after hours, they switch into their own environments and use the same diagnostic queries to make immediate improvements in their apps.)

You can choose to skip the lab VM – in the Mastering classes, I alternate between lectures and hands-on lab assignments. If you’re willing to build your own lab VM, you can save a ton of money. Just make sure you’ve got access to a SQL Server 2016 or newer with 4-8 CPU cores, 32-64GB RAM, and at least 600GB of free SSD space. Here’s how to set up your own lab workstation.

Labs & lectures include:

  • How to deduplicate & prune an existing database with too many indexes
  • How to use sp_BlitzIndex to design the right nonclustered indexes for specific queries
  • How to assess a large workload and prioritize which tables/indexes to work on first
  • How to convert badly-performing recommended indexes into blazing-fast hand-tuned indexes
  • How to know when filtered indexes, indexed views, and indexed computed columns will pay off
  • How to measure whether your index changes made things better, or worse
  • (Note that this class focuses on rowstore indexes for all currently supported versions/editions of SQL Server. Graph, full text, XML, spatial, in-memory OLTP, and columnstore indexes will not be covered.)

This is an online class – I (Brent) host it in GoToWebinar, live with me on webcam, taking questions as we go through the material together. Audio can come through either your computer audio, or by dialing into a US phone number – headset recommended either way. For more information, check out the Training Logistics PDF for our online classes.

You can watch again later with Instant Replay – as soon as you buy the class, you can start watching the recordings, and keep watching for a year. Brush up on stuff you missed or revisit your favorite topics. It’s the best of both worlds: live training with Brent, plus reminders. (Or, you can just buy the Instant Replay access – save money, and start watching your recordings immediately, streaming them in your browser for a year.)

Prerequisites – you need to be already thoroughly familiar with my free 90-minute class, How to Think Like the SQL Server Engine, and my 1-day Fundamentals of Index Tuning class. After you enroll, do the full prerequisites including chat setup.

Class dates & times:

Schedule subject to change in the event of natural disasters, food poisoning, exploding demos:

Day 1 – Tuning Nonclustered Indexes

Index Usage DMV Gotchas – SQL Server’s diagnostic management views show you which indexes are being used – or do they? It turns out they have serious gaps in coverage. Once you understand how they’re not quite accurate, you’ll be able to use sp_BlitzIndex much more confidently. You’ll start with SQL Server’s data, but then interpret it by hand.

The D.E.A.T.H. Method – you’re not starting from scratch; you’re dealing with a database that already exists, and you have to make it go faster. You need to analyze the current indexes, remove the ones that aren’t helping, and add the right ones. Brent explains the Dedupe/Eliminate/Add/Tune/Heaps method.

Lab 1: D.E. – you have one hour to deduplicate and eliminate indexes in an over-indexed Stack Overflow database, live, while a workload runs against it.

Adding Indexes – the missing index hints in query plans and DMVs have so many gotchas, and no, it’s not just about asking for too many fields or indexes. For starters, the fields aren’t even in order. Brent explains how to interpret Clippy’s recommendations and do a better job by hand.

Lab 2: Add Indexes – starting from scratch with no nonclustered indexes on StackOverflow, you’ll run a workload against it and analyze which indexes SQL Server recommends. You’ll see why they’re not quite right, implement better indexes by hand, and measure your improvements.

Day 2 – Tuning Indexes for Specific Queries

Tuning Indexes for Queries – Now we’re into the T part of the D.E.A.T.H. method, and it’s going to take more time when we examine queries one at a time rather than using the database-wide index DMVs. You’ll learn how to use sp_BlitzCache to identify the queries that need indexing help, then design the right indexes to help them. Before you just add the indexes, though, you’ll need to examine the other indexes that already exist on the tables and make tough decisions about whether to create more, or merge them together.

Heap Mitigation with Clustered Indexes – How should you structure the physical storage of your tables? You’ll learn as we cover how heaps cause performance problems, see what’s really inside an index, explain the logic behind IDENTITY and GUID fields as clustering keys, and show when to break the best practices.

Lab 3: Tuning – you’ll reinforce what you just learned by tackling a database with a decently tuned set of indexes, but then take it to the next level by hand-crafting indexes for specific queries.

Solving Lock Waits with Indexes – In order to minimize blocking and deadlocks, you need to have enough indexes to make your queries fast, but not so many that queries start to go slow. We’ll explain what the balance looks like, and how to design indexes to support updates and deletes.

Lab 4: Fixing Blocking – alert! You’re running a workload against StackOverflow, but you’ve got horrific blocking and deadlocking on. Fix it fast with by dropping the wrong indexes and adding the right ones.

Day 3 – Major Index Surgery

Filtered Indexes, Indexed Views, and Computed Columns – most of the time, traditional clustered and nonclustered indexes will get the job done. However, there are 3 scenarios where these artisanal index types can save the day. They’re not easy to use, and they come with a lot of gotchas.

Lab 5: Implementing Artisanal Indexes – because these indexes are so much harder to leverage, you won’t get a running workload here. You’ll simply get a list of stored procedures that might benefit from artisanal indexes. It’s up to you to figure out which feature makes sense, implement it, and if necessary, change the queries so that they’ll leverage the indexes.

Foreign Keys and Check Constraints – Defining relationships between tables can help enforce data quality rules when application code fails, but it has some surprising side effects. Performance can go down – or up – based on how you configure these relationships in the database. We’ll also discuss why you shouldn’t rely on cascading updates or deletes.

Fill Factor and Fragmentation – These two data storage issues are widely misunderstood. Should you set fill factor? Odds are, no – but we’ll explain specific scenarios where you should.

Lab 6: The Final Lab – Deep, calming breaths: time to put everything you’ve learned so far to work.

38 reviews for Mastering Index Tuning

  1. Laura Beranek (verified owner)

    This is an excellent course as is the Mastering Query Tuning course. Brent’s teaching style is effective, efficient and entertaining. I learned a lot and have already put things I’ve learned to use in my job. I really like that he answers questions posted in Slack as well. The format is very interactive and has a similar feel to an in person class with other students. I highly recommend this course.

  2. jessedo (verified owner)

    Brent does an awesome job showing how to take a systematic approach to tuning indexes. The labs are challenging, and they reinforce the material perfectly. I can’t wait for the Query Tuning class!

  3. Steve Malcolm (verified owner)

    This was my first experience with a multi-day on-line class. It could not have been better. Having the Q&A in Slack with the other students is brilliant.

  4. Brandon

    Absolutely incredible! Indexing is something we can all work on, and honestly this is the place to brush up. Brent’s class was entertaining and engaging, and with the labs he sets up well worth the money. I couldn’t be more happy with the class, will definitely be looking into Mastering Query Tuning next!

  5. Jay Taylor (verified owner)

    Throrough and entertaining. Brent hits Sql Server indexing’s high points, low points and all points in between. Then he gives you a lab to do the work to let the lecture sink in. During the lab, he’ll help you out, in real time. And later you can watch the session again and do the labs again, on your own. It’s a great way to learn. I wish there were similar courses for C#, Angular and Javascript.

  6. Mohammad Darab (verified owner)

    Brent’s way of teaching is unmatched! This class is an absolute must for SQL Server professionals. Perfect amount of lecture, lab time, and Q&A.

  7. kalotai (verified owner)

    Absolutely loving it. The class, Brent style, everything. I have to admit, I had my reservations before the class, but boy I was wrong about it. Less than 5 minutes in the first lab anyone can see the insane amount of work what was put into the class. This admiration only grows through the 3 days as everything is marching toward the last lab. I am really grateful to Brent and his team to use their extensive real-life knowledge and put this class together. Cannot wait till Query Tuning starts, probably I will start the previous recordings 🙂

  8. katie (verified owner)

    This class was just top notch. Brent is very entertaining and he explains the material in such a clear and concise manner. I learned a ton. Looking forward to the next class

  9. Michael DeMore (verified owner)

    The Master Index Tuning class is great. From the way the material is presented, to working the labs I really enjoyed this class. In fact, I have take 4 of Brents classes and they all are wonderul. I do highly recommend. And since I purchased the season pass, I look forward to the next class I take.

  10. cbrien (verified owner)

    Brent is a great instructor with deep SQL Server knowledge and has a great sense of humor. Class was extremely educational and I highly recommend it. Looking forward to signing up for additional courses in the future.

  11. Joe D’Aquila (verified owner)

    Excellent! Informative and entertaining. There was the right amount of structure and freedom. The lab machines and tools were top notch. I left the session eager to index tune. I am very much looking forward to the rest of the training classes in this series. Thank you Brent!

  12. Sammy (verified owner)

    Great content with excellent delivery, very very helpful course.

  13. andrew.benner (verified owner)

    This class is great. It’s challenging and it isn’t boring.
    The information presented will absolutely make you better at index tuning.
    Labs are structured so that you can choose how much effort you want to expend. You can do just enough to get by or if something piques your curiosity, you can dive in and get both hands dirty, right up to the elbows. Having a VM you can mess with for 3 days 24/7 is invaluable for these side tracks or if you need to work through the labs multiple times.
    This class is an exceptional value and it’s purpose is exactly what it says.

    To get the most out of it:
    Do the prerequisites recommended. You’ll be glad you did. Review them just before the class.
    Work from home and tell work you won’t be available for 3 days. You’re going to need to concentrate.
    Also consider that you just might need to attend this class twice (or more) to catch everything.

    Unlike any other training I have attended, taking this class over again to better grasp the concepts is appealing to me.

  14. Bear Golightly (verified owner)

    Thrills! Chills! Tempdb spills!

    From award-winning director Brent Ozar comes “Dude Where’s My Index?”, a tale of joined tables who, after a night of ETL, find themselves with absolutely no memory of the plan cache, unable to remember whether to seek or scan.

    Leveraging their SARGability, the pair minimize their logical reads and head off to look for the Stack Overflow. You’ll laugh, you’ll cry, you’ll end up with a tattoo that just says “DUDE, SEEK!”

  15. kdavenport (verified owner)

    Excellent delivery from an extremely knowledgeable source! The live sessions are great because of the labs and the ability to see how others approach specific problems. Real life examples with a step-by-step explanation of possible solutions. You will not be disappointed.

  16. Isaac Asher (verified owner)

    Brent is an amazing teacher! The class was very detailed and realistic, and I gained a lot of actionable information from the very first day. I’m so glad to have been a part of this class and I know that I am much improved at my job for having attended. Brent’s lectures are as entertaining as they are educational. I laughed as much as I learned! Brilliant experience and I will definitely be back for more classes in the future.

  17. JV (verified owner)

    Great class delivered by a SQL master! The class is engaging, appropriate, and most of all you learn a lot! For me personally it was learning there’s a lot more to learn.
    My appreciation and thanks to Brent for making this opportunity available.

  18. Douglas Riga (verified owner)

    Another great course in the tuning trilogy! Realistic, challenging labs and entertaining lectures make this course an exceptional value. I definitely feel better equipped to tackle index tuning and have a more in in-depth understanding of how to interpret what SQL Server is telling me.

  19. Bryan Rebok

    Brent knocked it out of the park again with the Mastering Index Tuning course. The single thing most important thing that was explained in depth and reinforced for me in this course was how to manually formulate an ideal index in SQL Server for a specific query and the various ways that SQL Server’s missing index recommendations often fall short of hitting that mark.

    As was the case in his Mastering Query Tuning class, the lectures are great and informative; but, the labs are what truly set the class apart. The labs are set up to very realistically simulate the problems of a true production environment and, like real life, it is challenging to come up with a proper solution in a small timeframe.

    If you want to learn how to become a better query tuner, this class is well worth your time and effort.

  20. Steve Larsen (verified owner)

    This was a great class it really helped out! I now have a better methodology to attack some of the performance issues I need to work on with my customers. I plan on replaying it again to reinforce the information! Thanks Brent!

  21. techsourcing (verified owner)

    Brent Ozar gives some of the best training you can find for SQL Server. The lab examples for this class (and all his classes) are real-life, not contrived like so many other training classes make them. I highly recommend Mastering Index Tuning!

  22. Todd Nelson (verified owner)

    Brent uses his expert knowledge and great presentation skills to build and expand on his “Think Like then Engine” training – taking us to the next level. The methods and techniques demonstrated in this class are well explained and brought home through the excellent course labs.

  23. Becky Harter (verified owner)

    As always Brent exceeded expectations! He has the ability to review the material to make it stick and the demos and labs worked great together. A natural born teacher who has mastered his craft; I highly recommend his training courses; webinars and conference talks. Although I knew enough to get by with index tuning and of course SentyOne plan explorer has index analysis that is great for finding the right index, the material the Brent covered made so much sense and cleared up a lot of the finer points of parsing execution plans that I learned many new tricks. He also takes the time to answer any questions. Take his courses, you won’t be disappointed!!

  24. Marie McAndrews (verified owner)

    Excellent class! I used to be scared of interpreting execution plans, but after this class I feel much more confident. Brent is an AWESOME teacher, and his enthusiasm for SQL Server and performance tuning are inspiring!

  25. Alan O’Neal (verified owner)

    Great class! I learned a ton. Highly recommended.

  26. arun.gopinath (verified owner)

    Fantastic class, I highly recommend taking it.

  27. Gerry Veraa (verified owner)

    Excellent class, the ratio explanation to labs worked really well for me. Expectations were high and got met.

  28. sonal.boraste

    Awesome class. Brent is very nice and interactive presenter. Lab and slides combination teaches the way of tackling a problem by going step by step to the root of the problem.

  29. Ari

    Great class!
    Brent is an awesome teacher. Learned a lot. And Brent keeps you engaged with his excellent delivery.

  30. Tim Cockerham (verified owner)

    Fantastic class. Brent is constantly refining his teaching skills to make complex topics understandable. If you want to learn what an index does, go watch How to Think Like the Engine. If you’re ready for the next step, index tuning, take this class.

  31. ahmed Cassoo

    Great class. Changed my perception on a lot!!

  32. VK

    Excellent Class!

  33. Kevin Kelso (verified owner)

    Death by a thousand indexes? Not after this course! Brent teaches you how to stay lean and mean with a well thought out indexing plan. You can’t afford to miss this one!

  34. Tim Hughes (verified owner)

    Great class! The lecture and lab combo really helps learn the material.

  35. Ashutosh Barick (verified owner)

    One of the best classes I have ever attended. Now, my arsenal has a lot more sharp edged weapons to fight tuning issues. Looking forward to the Query Tuning class. Brent, you rock.

  36. Jim Beattie (verified owner)

    This class was really outstanding. I learned a ton. Brent is not just eerily knowledgeable, he’s constantly engaging.

  37. Michael Neymit (verified owner)

    Excellent class and I learned a lot! Brent is one of the best presenters/teachers out there. Even people who think they know everything will always learn something new.

  38. Jay Falck (verified owner)

    Great job once again Brent. As I feared though, I did get several work interruptions during the course so it’s time to get torrent working so I can work through it again. Looking forward to Query Tuning.

Only logged in customers who have purchased this product may leave a review.

Bert Wagner of SQL with Bert attended our Mastering Index Tuning class, and here’s what he said about the quality of the labs:

You may also like…

Menu
{"cart_token":"","hash":"","cart_data":""}