Use SQL Compare by Red Gate to Find a Missing Index

, 2009-08-07

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





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.


1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...


1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.


360 reads