Missing Indexes in MS SQL or Optimization in no Time

When executing a query, the SQL Server optimizer tries to find the best query plan based on existing indexes and available latest statistics for a reasonable time, of course, if this plan is not already stored in the server cache. If no, the query is executed according to this plan, and the plan is stored in the server cache. If the plan has already been built for this query, the query is executed according to the existing plan.

We are interested in the following issue:

During compilation of a query plan, when sorting possible indexes, if the server does not find the best index, the missing index is marked in the query plan, and the server keeps statistics on such indexes: how many times the server would use this index and how much this query would cost.

In this article, we are going to analyze these missing indexes – how to deal with them.

Let’s consider this on a particular example. Create a couple of tables in our database on a local and test server:

Code

The structure is simple and consists of two tables. The first table is called orders with such fields as an identifier, date of sale and seller. The second one is order details, where some goods are specified with price and quantity.

Look at a simple query and its plan:

We can see a green hint about the missing index on the graphic display of the query plan. If you right-click it and select “Missing Index Details ..”, there will be the text of the suggested index. The only thing to do is to remove the comments in the text and give a name to the index. The script is ready to be executed.

We will not build the index we received from the hint provided by SSMS. Instead, we will see whether this index will be recommended by dynamic views linked to missing indexes. The views are as follows:

As we can see, there are some statistics on missing indexes in the first view:

  1. How many times would a search be performed if the suggested index existed?
  2. How many times would a scan be performed if the suggested index existed?
  3. Latest date and time we used the index
  4. The current real cost of the query plan without the suggested index.

The second view is the index body:

  1. Database
  2. Object/table
  3. Sorted columns
  4. Columns added to increase index coverage

The third view is the combination of the first and second views.

Accordingly, it is not difficult to get a script that would generate a script to create missing indexes from these dynamic views. The script is as follows:

Code

For index efficiency, the missing indexes are output. The perfect solution is when this result set returns nothing. In our example, the result set will return at least one index:

When there is no time and you do not feel like dealing with the client bugs, I executed the query, copied the first column and executed it on the server. After this, everything worked well.

I recommend treating the information on these indexes consciously. For example, if the system recommends the following indexes:

And these indexes are used for the search, it is quite obvious that it is more logical to replace these indexes with one that will cover all three suggested:
Thus, we make a review of the missing indexes before deploying them to the production server. Although…. Again, for example, I deployed the lost indexes to the TFS server, thus, increasing the overall performance. It took minimum time to perform this optimization. However, when changing from TFS 2015 to TFS 2017, I faced the issue that there was no update due to these new indexes. Nevertheless, they can easily be found by the mask
Alexey Kurenkov

Alexey Kurenkov

Alexey is a certified Microsoft expert who has been working with SQL Server for over 15 years. He is engaged in developing and administering databases.
Alexey Kurenkov
530 views