When you have data replicated out to multiple machines or are working on migrations it can be a challenge to ensure that the indexes are in sync, thus preventing query performance issues. I found the need to check and sync up indexes myself the other day and I didn’t have any third party software installed to help me do the compare. Yet again PowerShell came to the rescue and I ended up putting together a script that just kind of grew and grew on me, so this morning I decided to stop and put it out for anyone to use.
The script accepts a few parameters:
- Server1 – The source server that you’ll use as your source of index goodness
- Server2 – The destination that you want to compare (this can be the same server)
- Database1 – The source database that contains the tables and indexes
- Database2 – (this is optional) the destination database to check, if this isn’t passed then this defaults to the same value as Database1
- OutputScriptLocation – The path and filename for the output SQL script file which you can run to create the indexes
- Verbose – A switch which will dump lots of information to the screen, which can be useful as it will also tell you if there are missing tables on the destination
I decided not to do the actual index creation within the script as it’s good to be able to check the output and be sure that’s the thing you want. I did however output the script in such a way to make it as easy as possible to run, and so you can just open the script, switch to SQLCMD mode and run it. The script will make the connection to your destination server, to the right database and then create the indexes. It will also output to the screen when an index is created (this is very useful for those times when you are creating a lot of indexes and some of them are large).
I’ve tested it on a few servers and it’s worked out great for me, let me know if you run into any problems running it or encounter strange bugs.
Download Compare-Indexes.ps1 or get the code below.