Mastering Query Tuning

(16 customer reviews)

$995.00$2,995.00

You need to speed up a SQL Server app, and you’re allowed to change the queries and indexes – but not the server hardware or settings. We’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, and you’re allowed to change the queries and indexes – but not the server hardware or settings. 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.

You’ll work along with me in a VM – each student gets their own SQL Server 2017 lab VM in the cloud with the Stack Overflow database and running workloads. 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 we 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.

This is a live online class – I host it in GoToWebinar, live with me on webcam, taking questions in Slack 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 me, 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 – first, 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. When you’re tuning queries, you’ll do a better job if you understand the differences between clustered & nonclustered indexes, on-row and off-row fields, seeks vs scans, and rowstore & columnstore indexes. Therefore, for the absolute best possible experience, we’d recommend taking Mastering Index Tuning before tackling this class.

Class dates & times:

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

Day 1: Why Execution Plans Go Wrong

Cardinality Estimation – when SQL Server’s estimated number of rows per operator are reasonably close to the actual number of rows, you’re likely getting a good plan. It may not be a fast plan, but it’ll accurately reflect the (possibly large) amount of work required. Brent gives you a series of queries with increasingly harder cardinality estimation (CE) challenges so you can understand the mechanics of getting better estimates.

Lab 1: Improving Estimates – you get several queries that are either over-estimating or under-estimating the work they need to do. Figure out what they’re suffering from, and then tweak them in a way that SQL Server does a better job of cardinality estimation.

Identifying and Fixing Parameter Sniffing – everybody thinks they’ve fixed parameter sniffing by throwing in OPTION RECOMPILE or local variables, but that only takes you so far. Learn all the different options to fix it for good, and when each solution makes sense.

Lab 2: Parameter Sniffing – take 3 stored procedures, figure out why parameter sniffing is causing issues for them, and change ’em in the easiest/safest/fastest ways possible.

Day 2: Creative Query Rewrites

Common Table Expressions (CTEs) – when used effectively, these can cut complexity on awkward queries without harming performance. Used incorrectly, they can cause SQL Server to repeatedly rebuild the same data over and over.

Cross and Outer Apply – Anybody can string a few tables together with JOINs and get the query to compile. When you need additional performance tuning, that’s where CROSS APPLY and OUTER APPLY can come in handy. They give you the ability to make one pass on the data without needing additional constructs like CTEs, derived tables, temp tables, or table variables.

Using sp_BlitzCache to Target Your Tuning – let’s be honest: most of our queries suck. Thing is, we don’t have time to fix all of them. Learn to use the advanced parameters of sp_BlitzCache to figure out which queries to tune first, and how to measure the effects of your tuning.

Lab 3: Analyzing a Running Workload – until now, we’ve been handing you specific queries to tune. Now, you get a live workload running against the Stack Overflow database. It’s your job to figure out which queries you’ll need to tune first, and what changes to consider making. Inside the span of an hour, you may not be able to actually fix them – but at least you’ll build a plan of what you need to do next.

Tuning for SELECT * – Sure, you can TELL people not to select all the fields, but it doesn’t always work. Sometimes they can’t change the code, sometimes they really do need all the fields, and sometimes you’re just not sure whether it’s even a problem. Erik lays out different times when SELECT * is better or worse, and how to work around it with neat tricks like pre-filtering with a CTE.

Paging Large Result Sets – So far in class, we’ve mostly focused on keeping your result sets the same: tuning queries without affecting end users. However, sometimes you need to break up data into smaller chunks, or you only want to show specific pages of results for a web site or report. In this module, Erik shows a really cool technique to make your queries go not just a little faster, but amazingly faster when you only need specific rows in the result set.

Lab 4: Changing Results and Parameters – this afternoon, you learned about tuning methods that can change the surface area of the code: what it takes in, and what it returns.

Day 3: Advanced Query Rewrites

Building and Tuning Fast Dynamic SQL – You know the one: that stored procedure with fifty different parameters, all optional, and users want a perfect execution plan no matter what they pass in. It’s a parameter sniffing nightmare, and you need easy solutions. Brent will start with a bad design, then gradually try different ways of making it go faster, finishing up with dynamic SQL. He’ll then share his favorite dynamic SQL design and tuning tips.

User-Defined Functions – Scalar UDFs, multi-statement table-valued functions, and inline functions: only one performs well today. We demo all 3, shows how they hide in execution plans, and use sp_BlitzCache @SortOrder = ‘xpm’ to find their effects. We’ll finish by talking about what SQL Server vNext might bring to the table for a fix.

Lab 5: Advanced Rewrites – The last couple of modules – dynamic SQL and functions – have required some pretty invasive changes to queries. Tuning them is going to be a lot of work. For this lab, we recommend that you pick only one query and focus on fixing it, but we give you a few to choose from.

Lab 6: The Final Lab – Buckle up! You get a live, running workload in StackOverflow, and you have 2 hours to first tune indexes, pick the queries that you can tune fast for the most bang-for-the-buck, tune them as quickly as possible, and then measure your tuning efforts. See how you do versus the rest of the class, too.

16 reviews for Mastering Query Tuning

  1. kalotai (verified owner)

    If Mastering Index Tuning is a must have, Mastering Query Tuning is an absolute must have. I already know I will be in trouble for Mastering Server Tuning in about a month time :). Back to Mastering Query Tuning: it is not only informative, but the knowledge transfer is very entertaining too. And there is a great amount of knowledge in the class. By the end of the third day my head was tried to burst open a bit with the extra information, as it turned out I was not alone with this. I feel about Mastering classes as I feel about electric heated motorcycle gear: until you try it, you cannot even imagine what you missing from life. Once you had it, you do not want to live without it. If you work with SQL and you can only have one training course in your life, you really need to pick the Mastering classes. Go for all 3.

  2. Mohammad Darab (verified owner)

    Another very educational, eye-opening course in the Mastering series! I’m not a developer by any means but after this course I am pretty comfortable tackling slow performing queries. Thank you Brent!

  3. Andy Benner (verified owner)

    After taking the Mastering Index Tuning class, I was expecting to come into Mastering Query Tuning and learn a lot. I was not disappointed. I would argue this class is harder then Index Tuning. There are just more moving parts to understand and take advantage of.
    One of the things I enjoy about this training is watching someone do things that are not necessarily part of the “plan” and then figure out what went wrong. Brent does a good job of it live. He’s not just a “trainer” who is parroting things off an MS SQL Server Training slide deck.
    I really like this training format. Brent does a great job in front of the camera.

    P.S.
    The difference between people that want 10 and 20 minutes breaks made me laugh out loud !

  4. Jeremy Baxendell (verified owner)

    Brent shares his knowledge of the query optimizer’s dark secrets. He explains what most people are tempted to do, why that’s probably a bad idea, and offer some interesting alternatives. This is a very useful training for anyone that is looking to gain mastery of their DBA profession.

  5. Sammy (verified owner)

    The training was a big eye opener for me, revealing logical ways of tuning query as well as the underline technical details.

  6. Michael Hinz (verified owner)

    I’ve followed Brent for years and was recently able to take this class, and it didn’t disappoint. Brent is a great speaker and is able to keep the conversation interesting, and the labs and demos are extremely useful. I’ll easily be able to take most of this and apply it in my job, and that’s what makes it worth the cost. Can’t wait for the next class!

  7. Douglas Riga (verified owner)

    Great course for learning to understand how query design decisions can affect performance. This course really gave me insight in how to change queries to improve performance and to look at external factors that affect query performance as well. The labs were challenging and informative and the entertaining lectures, as always, are packed full of information.

  8. Heather Hahn (verified owner)

    Brent is a truly amazing instructor. I always find myself glued to my monitor during class in awe at how knowledgeable he is and how he can explain things so clearly and make each subject that he teaches so interesting. He always knows how to deliver the material in a fun and unique way that helps to more easily grasp the ideas and concepts. Erik and Brent are a great team and I found myself learning so much from them along with laughing out loud throughout the training which makes these classes not only full of great information but delivered in an exciting and entertaining way. These classes are NEVER dry, dull, or boring. So, if you are looking for that kind of class, don’t take this one! LOL Instead of staring at executions plans for hours until your eyes bleed, Brent actually makes reading and digging into them more amusing and fun. I can never look at an execution plan again and not hear the voices of Clippy or SQL Server and second guessing and questioning everything they would ever recommend for me to do in order to improve it.
    The training sections of the class were fully-loaded with great content and examples that Brent walks you through to teach you the ideas and concepts that you can apply to the lab sessions. Labs were do-able but challenging, in a very good way! to really make you think about how to apply the concepts you just learned and work through real-world examples that you might face on your own.
    I really enjoyed this class along with the Mastering Index Tuning course and I would recommend this to anyone looking to boost their SQL DBA skills and Master Query Tuning.

  9. Payam (verified owner)

    Brent really knows what he teaches genuinely. Although his focus is MS SQL, the mechanics and concepts he is teaching is eye-opener and insightful for other databases as well. I used many of the ideas in the class as a key to finding the relevant stuff for other RDBs as well.

  10. Brian Huff

    Very engaging and informative. The class covers real-world situations, including VM’s with DB’s and stress test data so you can immediately use knowledge in a real-world situation. Brent and Eric are right there to assist as you work through the labs.

    I was able to take the lessons and apply them in my production environment.

    Great experience, hard to come by these days in online-training.

  11. Daryl Smith (verified owner)

    The training is first-rate. The actual work is hands on after watching Brent and Erik walk through similar type issues in StackOverflow base data.
    Immediately after this class I have a list of items I need to address in my environment, including the modification of a multi-statement function to a Inline Table Function. This trick cut the time of one of my ETL’s by almost half.
    Knowledgeable, interesting, focused training. THANKS!

  12. Victoria L. Ree (verified owner)

    Excellent, fun, engaging!

  13. Bryan Rebok

    This class was a thorough reminder of how skilled Brent Ozar and Erik Darling are at query tuning and how much more I have to learn. The structure of the course was unique and wonderful in that the labs were realistic representations (not AdventureWorks) of many of the same problems that we fight in production every day – with not necessarily one “right answer” to the problem. It was also very cool to see the presenters go off-script and adapt their query tuning approach on the fly. This class is training that is well worth the time.

  14. Kevin Kelso (verified owner)

    This class gave me a giant toolkit for improving query performance. I found holes in my current process as well. I look forward to finding bad queries now because I can make them so much better. I highly recommend you take this course if you have queries that need to be tuned. You won’t be disappointed or bored while the Brent Ozar team provides a fun but very intense class.

  15. Todd Nelson (verified owner)

    Mastering Query Tuning is another fantastic training offering from Brent Ozar Unlimited. Brent and Erik did a fantastic (and entertaining) job of presenting the material and associated triage and tuning methods. The interactive discussion in Slack was very beneficial. The well planned course labs allow attendees to experience, triage, and improve on all of the issues present in the demo database. After spending hours with the lab environment I feel confident that I can diagnose and solve (or at least improve) any of the issues in our production environment.

  16. James Smith (verified owner)

    The class is well worth the cost. Many of the queries that we tuned is very close to the same problems my company has in our system. Everything that we went over will be directly used in my office daily. I have already signed up to take this class again.

    Pros:
    Interaction between teacher and student
    Best VM setup for testing
    Example queries to tune
    Everything is recorded for playback
    All scripts provided
    Classroom material

    Cons:
    Wish the class was longer

    Work Cons:
    Leave me alone during training!

Add a review

Your email address will not be published. Required fields are marked *

Bert Wagner of SQL with Bert attended one of our Mastering classes with hands-on labs, and here’s what he said about the quality of the labs and how our online classes are so different:

You may also like…

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