T-SQL Tuesday #118 – Automatic index management levels

Reading Time: 2 minutes

My T-SQL Tuesday contribution for this month discusses how I want automatic index management levels to be included with SQL Server.

T-SQL Tuesday host

For those of you who aren’t aware, this month’s T-SQL Tuesday topic was chosen by yours truly. Last week I invited everybody to write about a fantasy SQL service they have.

You can find out more about the invitation by clicking on the above picture, which is also a link.

In truth, I could have just talked about the suggestions I have already made. However, since I am the host this month, I am using more imagination.

Before I go any further and to avoid any confusion, I would also like to point out this post is written in British-English.

Automatic index management levels

Of course, aware some of you will be wondering what I mean by auto indexing levels.

Well currently the auto indexing feature only creates certain types of additional indexes. For example, an additional non-clustered index with included columns.

What I would like to finally see is a feature where you can set this at various levels for how aggressive it is. For instance, how it is now at level 1.

Afterwards, if I wanted it to go as far as to test introducing filtered indexes or converting set it to level 2.

In addition, be able to select another level for SQL Server to look to convert traditional rowstore indexes to columnstore indexes. Because of the extra analysis and risk involved this would be level 3.

I think this would save people a lot of time because would save everybody having to identify these indexes that should be converted.

Of course, I realise this would be risky for performance reasons as well. Which is why I suggest it’s an extra level that is set with the risks being understood.

Ideally, I want to see this both on premises and in Azure SQL Databases.

To be honest, I would not be suprised if Microsoft did not already have this in mind. So, I have decided not to add it as a suggestion on the SQL Server site.

Another reason is because there is a level of risk involved if you were to use this. Though, there can be just as much risk involved letting somebody else do it manually.

Of course, if this gives you any ideas feel free to make a suggestion on the official SQL Server suggestion site here.

Final T-SQL Tuesday word

Now I do hope you have enjoyed my T-SQL Tuesday contribution about auto indexing levels.

In addition, I look forward to reading everybody else’s submissions this month and writing the round-up of them all. So, keep a look out for the round-up after today.