Use SQL Compare by Red Gate to Find a Missing Index

A couple of weeks ago one of our developers sent me an email saying, “I have a query that returns in about 3 seconds in one environment, but it never returns in the other environment.  Can you check for blocking?”

To appease the developer, I checked and there wasn’t any.  He had a few more ideas, but since the environment in question has very relaxed security policies I guessed right off what the problem was.  I asked the developer for a copy of the query in question and told him that I would look into it.  I don’t know how many of you use SQL Compare, from Red Gate, but if you have to maintain database consistency across several environments it is a must have.  Before I started digging too much into execution plans and query optimizations I decided to check for missing indexes.  I could have accomplished this task several ways, but it is really easy to compare two database Schemas using SQL Compare.  Not only will it show you missing indexes, but it will show differences between indexes that exist on both the Source and Target databases.  The following is a screen shot of SQL Compare. 

The compare in this screenshot was on a database that contains over 500 objects and it ran in less than a minute.  From the screenshot you can clearly see that one index and two statistics are missing from the Target database, this can be identified by the yellow highlights and the blue arrows.  Also, the Orange highlight notifies me of differences between any objects that exist on both databases, in my case indexes. For example, in the above screenshot there is an Index named CIX_CalendarDate on both the Source and Target databases.  From the compare I was able to quickly determine that I needed to change that index to a Clustered index on my Target database.  Once that change, along with the other discrepancies that I found using SQL Compare, was made I was able to resolve the developers problem within minutes.  Once again, making me look like a SQL Rock Star.  Thanks Red Gate!

Talk to you soon,

Patrick LeBlanc

SQL Down South