All Blog Posts2020-03-22T23:21:07+01:00
1002, 2019

The limitations of the missing index feature – It reports only include columns for some queries

Tags: , |0 Comments

In the last post you saw that the missing index feature does not consider the order for columns to be used in an index. In this blog I will explain another limitation of the missing index feature - it reports only include columns for some queries. SQL Server supports two kind of row store indexes - the clustered index and then non-clustered index. It is important to understand the difference between these two indexes in order to implement the right index type to improve reading performance.

3112, 2018

The limitations of the missing index feature – It does not specify an order for columns to be used in an index

Tags: , |4 Comments

In the last post you saw that the missing index feature does not consider trivial plans. In this blog I will explain another limitation of the missing index feature - it does not specify an order for columns to be used in an index. SQL Server can only benefit from a row store index if the key columns are in the correct order. The order of the columns of an index determines whether SQL Server chooses an index scan or seek. Scanning an index simply means that the storage engine of SQL Server will read all index pages and filter the desired records afterwards (residual predicate)

1210, 2018

The limitations of the missing index feature – It does not consider trivial plans

Tags: , |0 Comments

In the last post you saw that the missing index feature does not gather statistics for more than 500 missing index groups. This time I am going to reveal another limitation which is not even documented - the missing index feature does not recommend missing indexes for trivial queries. SQL Server has to create an execution plan before executing a query because it has to evaluate the different available physical iterators depending on the cardinality estimation, supportive indexes, available hardware resources and instance settings to find a good enough plan.

610, 2018

The limitations of the missing index feature – It cannot gather statistics for more than 500 missing index groups

Tags: , |0 Comments

Implementing the right indexes will improve the execution time and IO reads for a Select, Update or Delete. Unfortunately sometimes it is not that easy to identify missing indexes without having a deep understanding about Query Execution Plans. That is why SQL Server gives us a hint about missing indexes in an Execution Plan. Sounds beneficial but in this blog post I will proof that it is not always a good idea to rely on these index recommendations. According to Microsoft, there are the several Limitations for Using the Missing Indexes Feature.