Just today, I was reviewing the indexing of a database used by a third-party application. As part of my review, I ran a script against it that is used to identify duplicate indexes. The script I used was written Paul Nielsen, the author of the SQL Server Bible; and Itzik Ben-Gan. The script is available here.
After running Paul’s script against the database to find duplicate indexes, I discovered that almost every table had a duplicate index. As I delved into this a little closer, I discovered what the designer of this database had done.
First, it looked like he created primary keys using non-clustered indexes on an identity column for most of the tables in his database. Second, he then added a clustered index on the same identity column for each of these same tables. By doing so, he ended up having two identical indexes on the same identity column, one clustered and one non-clustered. Instead, the designer should have created his primary keys using clustered indexes instead of using non-clustered indexes. This would give him the same end effect, but requiring only a single index, not two. Obviously, by having duplicate indexes on most of his tables, he was causing SQL Server a lot of additional work to maintain extra indexes that were not needed.
If you have never checked your databases for duplicate indexes before, you should do so, as you might find duplicate indexes that can be removed, helping to boost the performance of your existing databases.
| Share this post : |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
