Tag: Indexes

Indexing, SQL

Filtered Indexes on Computed Columns

I was trying to improve the performance of some code recently and decided that a filtered index could really help me out. Upon attempting to add the index I got an error stating that you cannot add filtered indexes to computed columns. This is the case even if the computed column is persisted.

That’s a shame as this would be a really useful thing to have. 

There is actually an open Connect item on this. It’s been open for 4 years now, so I’m not sure that there will ever be any traction on it, but feel free to upvote it yourself.

 

Code to repeat the problem below:

CREATE TABLE #SomeData 

    (

      RowID INT NOT NULL CONSTRAINT PKSomeData PRIMARY KEY

    , Quantity INT NOT NULL

    , Price MONEY NOT NULL

    , TotalCost AS (Quantity * Price) PERSISTED

    );

 

CREATE INDEX IDX_CostGreaterThan10 ON #SomeData (RowID) WHERE TotalCost > 10;

Msg 10609, Level 16, State 1, Line 1
Filtered index ‘IDX_CostGreaterThan10’ cannot be created on table ‘#SomeData’ because the column ‘TotalCost’ in the filter expression is a computed column. Rewrite the filter expression so that it does not include this column.

 

PowerShell, SQL

Comparing Indexes Using PowerShell

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."}

}