I see 10x speedup was given by an index. A while back I was helping a customer with some serious performance issues, and I pulled together a few procs that loo

I see 10x speedup was given by an index. A while back I was helping a customer with some serious performance issues, and I pulled together a few procs that look at e.g. indexes, most-locked-objects/whatever over the whole DB. I've seen this below trick referenced many times but the source article seems to be here: http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-usi... It gives an indication of the highest-impact indexes that are missing. Obviously there's room for experts to tweak, but for most it's an excellent quick tool. Don't create every index it suggests, obviously, just use it as a guide to look for hotspots. It's nice because often it'll surprise you with areas you had no idea were an issue. And it takes a few seconds to run.