SQL - 2nd Article in a Series of 3

Posted on: 17/04/14

Following on from my previous post about duplicate indexes existing in SQL Server, this time I am going to shift focus slightly and look at trying to answer the question ‘Do I have any Potentially Bad Indexes in my SQL Server Database?'

What is a ‘bad’ index? That’s a very good question. I tend to think that a 'bad' index is one where the benefit of it being there is outweighed by the cost of creating and maintaining it.   Additionally, you may have indexes that have never been used, again these should potentially be considered to be ‘bad’, but see the caveat below.

The following script will help you to identify where potentially ‘bad’ indexes may exist:-

 

NB. Please do not treat any index identified by the above script to be an automatic candidate for removal, whilst it may be the case that it receives a lot more write activity than read activity, or may not have been used at all since the last restart, it may still be important for a certain part of the system using the database.

I would suggest that you treat the results of this script as a guide only, and have someone that properly understands the system evaluate whether the index is worth keeping, or whether it could be removed/incorporated into another to reduce the write activity.

In my next rticle, I will take a look at a further aspect of SQL - Server Indexing and ask the question ‘Do I have any missing indexes in my SQL Server Database?'

Celerity have a number of consultants who are experts with a wide range of Microsoft products including SQL Server. If you have any concerns about the performance of your system, or are looking for a health check, Celerity would be happy to discuss your requirements in more detail and tailor a solution to fit.  Please do not hesitate to contact us.

***Disclaimer*** The scripts used in this article may have been created by other people and/or gathered from specialist sites the internet, no implication of ownership should be inferred.

Mike Metcalf, Technical Consultant, Celerity Limited

To view this article on Celerity's website click here