T-SQL Tuesday #120 – Severely neglected index

Reading Time: 2 minutes

Welcome to this month’s T-SQL Tuesday post which is about the time I had to rebuild a severely neglected index.

Wayne Sheffield is hosting this month’s T-SQL Tuesday. It is about a SQL Server situation where you have been left wondering “what were you thinking”.

Ten years

Before I go any further, I should point out this is the 120th T-SQL Tuesday invitation, which means T-SQL Tuesday has been happening for ten years now.

Severely neglected index

Anyway, back to the topic at hand. In truth, I had to think about what to post this month. Because I had a few examples from over the years.

In truth, if I had discussed some it may have embarrassed some people if they had ever read it.

However, I’ve decided to discuss a large table I once inherited. Of course, I will try and make things as anonymous as possible in this post.

Some time ago I inherited this SQL Server instance which had a large table on there. After further investigation it appeared indexes for a table were heavily fragmented at various index levels.

So, I ask a colleague how this came to be. It was then they told me that they had decided to only defragment (reorganize) the index to save the data file growing.

So, I thought I’d do the sensible thing and ask how long they had been defragmenting the indexes for this very large table. Because I thought must have been a while.

However, I did not realise a while had actually being five years. Which, it turns out was just before they started getting complaints about the performance of this table.

After this discovery I set some maintenance time aside to rebuild the clustered index for this particular table.

In addition, I had to explain in depth as to why I had to do this for this particular table because of a notion somebody had in their mind.

In reality, it’s one of the main reasons I write posts like my older indexing one here.

Of course, these days depending on your hardware and your database size this might not matter to you. However, it is always worth checking the basics when you inherit a new database.

Final word

After being too busy travelling to speak last month, I hope you have enjoyed my T-SQL Tuesday contribution this month.

Now you’ve read this post I highly recommend you read whatever Rob Farley has published this month. Because they’ve contributed to T-SQL Tuesday right from the start.