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:
SQL Manager 2008 Lite
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 $g(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!

One thought on “SQL Server Index Nightmare

  1. A few years ago, I bought the EMS software for the Firebird Database. I have since purchased the Microsoft SQL version (with database comparer – great tool), and I am about to purchase the MySQL version.
    Wonderful software!

Comments are closed.