SQL Server: Disable/Remove Indexes Across Servers/Databases Using PowerShell & dbatools

This is something I had to do today. In this blog post, I am going to show you how you can disable or remove indexes matching certain name criteria or other condition(s) across multiple servers and databases in various tables. Usually, this is a very painful process if done by hand but with the fantastic work done by the dbatools team, it becomes quite easy.

The idea of the script is to

  • For a given a set of SQL servers
  • Scan each server and all databases within it
  • In each database look at all tables
  • Find indexes that match certain naming criteria or other conditions
  • Export the list and the generated TSQL to disable/remove matching indexes as CSV
  • Disable or remove the indexes physically

In the example below, I am looping through a bunch of servers and all databases in those servers looking for indexes with name starting with “NCIX01”. I know that I created them temporarily and would like to disable them. In your case, the criteria might be different, so alter accordingly.

Also, if you only want to view or export the results without running it, just comment out the line – “Invoke-DbaQuery” at the end.

   Given a index name search pattern and a list of servers, finds matching indexes that can be disabled/dropped
    For the pattern of index name provided, gets the list of all matching indexes.  
    It also generates the TSQL to disable or drop the indexes. 
    Alter it as necessary to rebuild index or whatever else it is that you want to do
Version History
    v1.0   - Jana Sattainathan - Nov.09.2022

[string] $indexFilter = 'NCIX01*'  #  <<<- Change this to '*' to get all indexes or to something meaningful based on your need

#List of SQL servers goes here
[string] $servers = @"

[string] $disableIndexSQLTempl = 'ALTER INDEX [<<INDEX_NAME>>] ON <<SCHEMA.TABLE>> DISABLE'
[string] $dropIndexSQLTempl = 'DROP INDEX IF EXISTS [<<INDEX_NAME>>] ON <<SCHEMA.TABLE>>'

#Convert the above string into a string array that can be looped through
[string[]] $serverList = $servers.ToUpper().Split("`r").Split("`n").Split(',').Split(';') | 
                    foreach { $_.Split(".")[0]} |
                    where {$_} |
                    select -Unique

#Loop through the servers
$results = foreach ($server in $serverList){
    $dbs = Get-DbaDatabase -SqlInstance $server -ExcludeDatabase model,master,tempdb,msdb |
                        select name -ExpandProperty name

    #Loop through the databases on the server
    foreach($db in $dbs)
        Write-Verbose $db

        Get-DbaHelpIndex -SqlInstance $server -Database $db | 
                        Where-Object {$_.Index -like $indexFilter} |
                        Select-Object *, @{Label="DisableIndexSQL";Expression={$disableIndexSQLTempl.Replace('<<INDEX_NAME>>',$_.Index).Replace('<<SCHEMA.TABLE>>',$_.Object)}},

#Review if needed
$results | ogv

#Export the results if needed
$results | Export-Csv -LiteralPath "C:\Temp\DisableOrDropIndexes.$((get-date).ToString("yyyyMMdd_HHmmss")).csv" -NoTypeInformation

#Loop through and run the index disable TSQL if needed
foreach($idx in $results)
    Write-Verbose 'About to run on [$($idx.SqlInstance)].[$($idx.Database)]'
    Write-Verbose $idx.DisableIndexSQL
    Write-Verbose $idx.DropIndexSQL

    #Index will be disabled upon execution
    Invoke-DbaQuery -SqlInstance $idx.SqlInstance -Database $idx.Database -Query $idx.DisableIndexSQL

There is not much more to it other than the fact that you need dbatools module. You can alter it for example to only select indexes that have a certain column name in the index column list.


One thought on “SQL Server: Disable/Remove Indexes Across Servers/Databases Using PowerShell & dbatools

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s