For those of you out there who are tasked with improving your sql query performance, one of the best tools you can use is the Execution plan that you can optionally set to come back with the results of your query in Sql Server Management Studio. It provides a host of data about what tables and indices are used by the server to acquire the results for your query.
In the past, the developer would either have to make an educated guess, or use the Index Tuning Wizard to try and determine where indices might be beneficial. But Sql Server 2008 introduced a new feature of the execution plan: suggesting missing indexes. I hadn't heard of this and it was very exciting the first time I noticed it. The only drawback is that it only works against databases running on Sql Server 2008. Even with the new SSMS, this data is not returned in the execution plans on earlier server versions.
There isn't anything fancy you have to do beyond turn on the "Include Execution Plan" as you've always done. If the server feels that your query performance would be improved with an index, that suggestion is included in the execution plan in green, along with the necessary script to create the index, making it easy for you to copy and paste that into a new query window and execute the creation of the index.
