SQL Server Index Nightmare

The other day I discovered one of the MSSQL tables I’m using heavily at the moment had 214 indices! There were about 20 with sensible names and the rest were all named similar to _dta_index_SA_OrderHeader_5_499519046__K1_2_6_9_13. I mentioned this issue to John Waters in the office today which elicited a nice laugh until I sent him this screenshot:

While I’m not exactly new to MSSQL I’m no expert though fortunately Falafel has a few experts on staff and John’s one of them. He dug into the problem answering the what, where and how to deal them, feel free to click through if that’s what you need.

Had I not blogged back in February asking about MSSQL tools this problem would likely have gone undetected for a lot longer. Microsoft’s Management Studio doesn’t show these so called Hypothetical Indices so you have no way of knowing your table/DB is being impacted by them. Ironically, they’re created by the Index Tuning Wizard. Anyway, one commenter to my post mentioned EMS Database Management Solutions (a mouthful notwithstanding whatever EMS stands for) SQL Manager 2008 for which there is a freeware download. SQL Manager’s treeview provides a wealth of information including counts for things like indices which allowed me to easily stumble upon the problem.

All in all, there were 500+ of these indices hanging around but thanks to SQL Manager no longer!