SQL Tuning: How to determine why the Optimizer is picking the wrong index

One of the most common SQL Tuning challenges you will encounter with enterprise applications is a SQL statement where the Optimizer picks the wrong index. As tempting as it is to brute-force the plan you want via an index hint, it’s always better to understand why the Optimizer made the decision and provide the necessary information and access structures to allow the Optimizer to select the plan you want by default.

In the short video below, I explain how the Optimizer costs each of the index accesses available to it and provide you with a simple set of steps to help you identify these types of problems and guidance on how to create indexes so the Optimizer will automatically select them.

This post is part two of a series of blog posts on SQL Tuning. In part one, I shared some simple steps to help you tune a SQL Statement using the wrong Join Type.

SQL Tuning: How to tune a SQL Statement with the wrong Join Type

When it comes to SQL Tuning, I don’t typically recommend folks add one-off hints or look for magical underscore parameters to help improve their query performance.

Instead, I like to share some simple steps to

  1. Accurately characterise the problem
  2. Determine and apply a solution
  3. Measure the effectiveness of that solution

But instead of talking about my approach, I thought it would be more beneficial to show you how I do it. So in the video below, you will see the exact steps I used to determine what was causing a Sales Report to run slowly and the two alternative approaches you can take to resolve similar problems.

%d bloggers like this: