One of the recommendations I give to my clients is to remove unused indexes from their system when we are working together on the Comprehensive Database Performance Health Check. Today I am going to share our very interesting conversation about sp_updatestats Performance and Disabled Nonclustered Indexes.
Dropping Indexes vs Disabling Indexes
When I explained to my clients that they should remove the unused indexes, they asked me if it would be better to just disable those indexes instead of dropping them. While I personally, do not have major issues with disabling the indexes initially and after a while dropping them, there is one reason, I personally feel that everyone should know about Disabled Nonclustered Indexes.
There are two major advantages of dropping indexes over disabling indexes.
- Reclaiming Space. When you drop indexes, you get the space back to your database files which were occupied by the indexes.
- Faster sp_updatestats. Yes, it is true. sp_updatestats updates statistics on disabled nonclustered indexes and does not update statistics on disabled clustered indexes.
Disabled Nonclustered Indexes
I have not seen much discussion on the internet about the faster sp_updatestats. If you have many disabled nonclustered indexes, I suggest that you do not run sp_updatestats but rather write your own custom solution to identify the indexes which need to be updated and only update those indexes otherwise, you will end up wasting lots of resources of your system.
Here are a few additional resources which you may find important which are related to this blog post.
- SQL SERVER – Missing Index Script – Download
- SQL SERVER – Unused Index Script – Download
- SQL SERVER – Poor Indexing Strategies – 10 Don’ts for Indexes
Additionally, I am also including a video here which discusses how an index can show down SELECT statement along with Insert, Update, and Delete statement. Do let me know what you think about it and if you like what you see please do not forget to subscribe to my YouTube Channel.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
