Development progress w/e 23/09/2018 (Version 1.2)

 

Index advisor

A first part of the index advisor module – the missing index dashboard is now released and available for download. I have been experimenting with different charts to best show the impact of the missing indexes. Any feedback welcome. Big thanks to ColinDouglas92 for contributing to this release.

SQLWATCH Index advisor missing indexes

You will notice a new SQL Agent job, scheduled to run once a week, on a Saturday at 6am.

I plan to implement analysis of existing indexes to show how are they being used and flag up any unused and underutilized indexes where the write cost outweighs the read benefits

How we know what indexes are missing

SQL Server is kind enough to tell which indexes it *thinks* are missing based on the execution plans. This information is stored in the following Dynamic Management Views.

Note. Missing indexes reported by SQL Server are a guideline of what SQL thinks and are not always correct. Care should be taken before applying recommendations. Please make sure you understand your workload and data, do not apply indexes automatically without the full understanding of the implications and impact. There are sometimes valid reasons to not have an index despite the recommendation.
The numbers are estimated based on cost-based optimisation and they are all “could” and not “will”. SQL does not really know how useful the index would have been unless this index is created and performance measured. From the Microsoft Docs:
  • avg_total_user_cost – Average cost of the user queries that could be reduced by the index in the group.
  • avg_user_impact – Average percentage benefit that user queries could experience if this missing index group was implemented. The value means that the query cost would on average drop by this percentage if this missing index group was implemented.

I created a quadrant chart on the SQLWATCH dashboard that takes into account avereage_total_cost (x), average_user_impact (y) and the number of seeks (size). I am hoping this will give you a better idea of the potential benefit and impact.

How we capture missing indexes

We are also generating the CREATE INDEX statement for all missing indexes to make it easier to apply them. Enterprise Edition can create and rebuild indexes in ONLINE mode whereas Standard Edition cannot. To make use of ONLINE index creation in the Enterprise edition we need to check which Edition we are running on , and conditionally include the ONLINE=ON statement in the creation command

Impact calculation

The impact column visible on the dashboard is a result of the below formula in PowerBI which takes avg cost, impact and number of user seeks into account:

Limitations

As mentioned above, these recommendations are just a guideline based on cost-estimation and are not always correct. They do not distinguish between clustered and non-clustered indexes and do not take storage impact and write penalties into account.

Retention

Indexes are logged under a dedicated snapshot_type_id = 3 thanks to which, we can set a different retention period. By default, missing indexes will be removed after 30 days from the SQLWATCH.

New Formatting

You will also notice the new dashboard has a slightly different layout, with an apparent purple bar on top. This isn’t me being inconsistent and random but rather me trying to find the best color scheme and design for PowerBI dashboards. Eventually I will re-align all dashboards to follow one standard. Again, any feedback welcome.

Download

You can download this releases from GitHub: v.1.2.6841.38712 (2018-09-24)

This site, like most websites, uses cookies. By continuing to use this website, you agree to their use.