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.
<#
.SYNOPSIS
Script can be used to compare indexes on tables on different servers/databases.
.DESCRIPTION
When executed the script will check passed in databases on different (or the same) server and output a SQLCMD script which
can be executed to add missing indexes.
.PARAMETER <paramName>
-Server1 The source SQL Instance that is used as the master
-Server2 The destination SQL Instance that will be the target for additional indexes
-Database1 The source database
-Database2 The destination database (leave blank if the source and destination databases are the same)
-OutputScriptLocation Path and filename for the .sql file that will be created for any indexes that need to be added
-Verbose Output some logging information
.EXAMPLE
./Compare-Indexes -Server1 localhost -Server2 remotehostinstance1 -Database MyDB -OutputScriptLocation C:TempNewIndexes.sql -Verbose
#>
param
(
[string]$Server1 = "$(Read-Host 'SQL Instance 1 [e.g. (local)]')",
[string]$Server2 = "$(Read-Host 'SQL Instance 2 [e.g. (local)]')",
[string]$Database1 = "$(Read-Host 'Database Name')",
[string]$Database2,
[string]$OutputScriptLocation = "$(Read-Host 'Output File Name')",
[switch]$Verbose
)
if (!$Database2) { $Database2 = $Database1}
$MissingIndexCount = 0
try
{
#Initialize the output file
$FileInitiation = @"
:ON ERROR exit
:setvar SQLInstance `"$Server2`"
:setvar Database `"$Database2`"
:CONNECT `$(SQLInstance)
USE `$(Database);
"@
$FileInitiation | Out-File $OutputScriptLocation
$File = "True"
}
catch { Write-Host "Output folder does not exist...aborting script."
$File = "False"
}
if ($File -eq "True")
{
if ($Server1 -ilike "**") { $BaseServer1 = "SQLSERVER:SQL$Server1Databases$Database1Tables" }
else { $BaseServer1 = "SQLSERVER:SQL$Server1defaultDatabases$Database1Tables" }
if ($Server2 -ilike "**") { $BaseServer2 = "SQLSERVER:SQL$Server2Databases$Database2Tables" }
else { $BaseServer2 = "SQLSERVER:SQL$Server2defaultDatabases$Database2Tables" }
Write-Output "Please wait...comparing $Server1.$Database1 to $Server2.$Database2"
$Server1List = dir $BaseServer1 | select DisplayName
$Server2List = dir $BaseServer2 | select DisplayName
#For information purposes show a list of tables missing on server2 if verbose enabled
if ($Verbose)
{
$MissingTables = Compare-Object $Server1List $Server2List -Property DisplayName | Where-Object {$_.SideIndicator -eq '<=' } | Select DisplayName
foreach ($MsTbl in $MissingTables)
{ $MissingTable = $MsTbl.DisplayName
Write-Output "Missing table - $MissingTable"
}
}
#The compare uses -IncludeEqual as we only want to compare indexes on tables that exist on both servers
$TableList = Compare-Object $Server1List $Server2List -Property DisplayName -IncludeEqual | Where-Object {$_.SideIndicator -eq '==' } | Select DisplayName
if ($TableList)
{
foreach ($Table in $TableList)
{
$MissingIndex = "" #Initialize the variable so we can null it
Clear-Variable MissingIndex #Null the variable as we'll be checking for a value in here later
$TableToCheck = $Table.DisplayName
$Table1Index = dir $BaseServer1$TableToCheckIndexes | Select Name # | Where-Object {$_.DisplayName -eq "$TableToCheck"}).indexes | Select Name
$Table2Index = dir $BaseServer2$TableToCheckIndexes | Select Name # | Where-Object {$_.DisplayName -eq "$TableToCheck"}).indexes | Select Name
if ($Verbose) {Write-Host "Checking $TableToCheck"}
$MissingIndex = Compare-Object $Table1Index $Table2Index -Property Name -IncludeEqual | Where-Object {$_.SideIndicator -eq '<='} | Select Name
if ($MissingIndex)
{
$MissingIndexCount += 1
$Index = $MissingIndex.Name
Write-Output "Missing Index - $Index on $TableToCheck"
dir $BaseServer1$TableToCheckIndexes | where-object {$_.Name -eq "$Index"} |
% {$_.Script() | out-file $OutputScriptLocation -append; "; `r`nRAISERROR(`'Index $TableToCheck.$Index created...`',0,1) WITH NOWAIT; `r`n`r`n " |
out-file $OutputScriptLocation -Append;}
}
}
if ($MissingIndexCount -gt 0) {Write-Output "File $OutputScriptLocation created. Open this file in SSMS and execute in SQLCMD mode"}
else {Write-Output "No missing indexes found!"}
}
else { Write-Output "No matching tables found."}
}
SirSQL.net 