[Video] Interpreting Missing Index Recommendations

Indexing, Videos

This week, we’re sharing Instant Replay videos from our training classes. Next up is Pinal Dave talking about missing index requests from the dynamic management views. It doesn’t matter whose script you use – they’re all built atop SQL Server’s missing-index DMVs, and they all have the same limitations. Treat the missing index requests the same way you would treat appetizers in a restaurant – if you order an appetizer and it’s no good, don’t order more dishes.

That’s from Pinal’s Practical Real-World Performance Tuning class. It’s a fast-moving 4-hour class where Pinal races through one demo after another, showing you how the engine works and how to make a difference, quickly.

The reviews are fantastic.

This was a great class and I believe that Pinal did a great job keeping the class interesting and on track. He encouraged student participation and that helped with the learning process. I thought I knew exactly how indexes use was determined but learned a huge gotcha at the end of the class. – Cindy Pieper

There’s dozens of public reviews like this on the product page:

Great training class; fast paced, with great examples. I thought I understood indexing somewhat, but Pinal does an excellent job explaining all aspects of index use and how they work. Looking forward to optimizing our databases and queries. The three rules really help and make it clear how and when to deploy indexing. – Joe Titus

And Pinal’s delivery wins raves:

Very good class. I’ve been working in SQL Server since 6.5 and there were a lot of interesting and useful ideas presented that I did not know. Or that confirmed suspicions I had but did know fully understand the logic behind. Very well presented. Beats the usual dry power point classes by a mile going away. – Jay Bruner

Here’s what you’ll learn:

Module 1: Indexing, Worst Practices, and Solutions (70 minutes)

Indexes are often considered a silver bullet to solve performance problems, but the reality is far from the age-old myth. The right index can help improve performance, but the wrong indexes often play party spoiler and reduce your server’s performance. Identifying which indexes to create and which indexes to delete can be cumbersome (or near impossible) if we do not know how to do workload analysis.

In this module of the workshop, we will see some neat tricks related to how to create useful indexes which last longer. We will also build a preventive auto-healing system which can help you focus on more complicated performance issues.

Module 2: WhichIndex™ – An Interactive Game with WHERE clause and Multi-Column Indexes (70 minutes)

WhichIndex™ is the interactive game that gives a new experience to teach you how to understand how multi-column indexes work with queries with multiple conditions.

This game follows the format of open book exams where every user begins with answers to the questions. Once we understand our solutions, we start our journey to this interactive game. At a different interval of this game, users have to answer the question WhichIndex™ to the quiz master.

In this self-scored game, the winner is the person who gets the maximum answers wrong. If you have not understood the basics of indexes and performance tuning concept, you will solidify that concept while we play this game.

On a side note – cheating is not only allowed but is heavily encouraged as well!

At the end of this game, users will have an understanding of three essential rules related to Indexes, which YOU will never forget.

Module 3: Don’t Change Code – An Interactive Game with Functions, Views, and Indexes (50 minutes)

Any good singer needs the support of the decent sound system and cooperative band to be successful. Similarly for any index to be successful, it requires a supportive environment. It is incredibly critical to learn how indexes selection takes shape when functions are used in the query.

We start with a problem where there is a query and an index. The query is not using the index. The challenge which we have to solve in this game is to make the necessary modification, so our query uses our index. However, there is one condition – Don’t Change Code. We are not allowed to change the query.

While I struggle to solve this puzzle, your responsibility will be to help me out with various ideas and also help me honor the one condition of the game – Don’t Change Code.

The game is indeed a very fast paced but at every twist and turn we will take few moments to understand the secrets of indexes.

Join Pinal on August 2nd for 4 hours of fun for just $297.50 – that’s half off, this week only. Register now.

Previous Post
[Video] PowerShell Functions and Parameters, and 50% Off Training Classes
Next Post
[Video] Installing SQL Server Updates in an Availability Group, and Save 50% on Training

Leave a Reply

Your email address will not be published.

Fill out this field
Fill out this field
Please enter a valid email address.