SQL Server – Automate Index Maintenance in Mirroring environment

I had a scenario where in I had to automate index maintenance in a mirroring environment. Database mirroring requires a database to be in full recovery mode and thus index rebuild results in huge logs being generated and this directly affects the mirroring throughput. The general guidelines to solve this problem is

– to switch to asynchronous mirroring mode,

– not to use online index rebuild operations and

– to use reorganize instead of rebuild to lessen the logs being generated.

Following on to these guidelines I wrote a power shell script to automate the index maintenance process. So, to start with, first thing is to iterate through databases and indexes. This is shown in below code.

#Load SMO
$v=[System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO')
# connect to specified instance.
$server = new-object ('Microsoft.SqlServer.Management.Smo.Server') $inst
# get the database enumerator
$dbs = $s.Databases
# get database id 
$dbid = [string]$db.ID
 iterate through databases
foreach ($db in $dbs) {
  # filter out user and accessible databases
  if ($db.IsAccessible -eq $True -and $db.IsSystemObject -ne $True -and $db.Readonly -eq $False) {
   # get table enumerator 
    $tbs = $db.Tables
# get table id
$tbid =$tb.ID
# iterate through tables in a database.    
foreach ($tb in $tbs) {
     # get index enumerator          
      $ixs = $tb.Indexes

The above code uses SMO to find out indexes to be maintained. At first, it connects to the specified server. It then enumerates through databases and tables to get the indexes. As we already know that it’s not a good practice to defrag all indexes. So, let’s filter out the index to defrag based on well known MS guidelines. These can be later adjusted as per environment.

# iterate through indexes
    foreach ($ix in $ixs) {
          # filter out xml indexes if not required.
if ($ix.IsXmlIndex -eq $False) {
          # get index name and id
          $ixname = $ix.Name
          $ixid = [string]$ix.ID
          # Get the Fragmentation and page count information
          $qry ="
                    select avg_fragmentation_in_percent, page_count
                    from sys.dm_db_index_physical_stats($dbid, $tbid, $ixid, NULL, 'Limited')" 
            $con = New-Object -TypeName System.Data.SqlClient.SqlConnection
            $con.ConnectionString = "SERVER=$inst;Integrated Security = True"
            $Cmd = New-Object -TypeName system.data.sqlclient.sqlcommand
            $cmd.Connection = $con
            $Cmd.CommandText = $qry
            $dr = $Cmd.ExecuteReader()
    # iterate through data reader to get avg_fragmentation_by_percent and pagecount
                $avgfrgmntvalue = $dr[0]
                $pagecount = $dr[1]
       # filter out the index to be reorganized 
        if ($avgfrgmntvalue -gt 30 -and $pagecount -gt 1000) {

The above code enumerates through indexes and fetches index fragmentation percentage and page count using sys.dm_db_index_physical_stats dmv. This information can also be fetched using $ix.EnumFragmentation(). I found it slow so I used DMV instead.  The code then filters out the indexes to be reorganized based on fragmentation percent and page count value. The next thing is to reorganize the indexes and keep track of mirroring unsent queue. I will only reorganize index because rebuild will generate huge log files which will degrade mirroring throughput.

# reorganize filtered indexes
             # get unsent log details from sp_dbmmonitorresults
            #$unsentlog = fn_getmirroingunsentlog($dbname)
             while($unsentlog -gt $UnsentQthld)
         # wait for unsent log to clear up
             Start-Sleep  60
             # referesh the value of unsent log
             $unsentlog = fn_getmirroingunsentlog($dbname)
       # updates the index statistics           

The above code reorganizes an index and then checks the mirroring unsent log queue. The next index is reorganized only when the mirroring unsent log queue is less than the value of $UnsentQthld parameter. The function fn_getmirroingunsentlog returns the unsent log queue value. The code for the same is shown below.

function fn_getmirroingunsentlog { 
    param ([string]$db)
      $qry = " msdb.dbo.sp_dbmmonitorresults $db"
            $con = New-Object -TypeName System.Data.SqlClient.SqlConnection
            $con.ConnectionString = "SERVER=$inst;Integrated Security = True"
            $Cmd = New-Object -TypeName system.data.sqlclient.sqlcommand
            $cmd.Connection = $con
            $Cmd.CommandText = $qry
            $dr = $Cmd.ExecuteReader()
                $unsentlogq= $dr[5]
         return $unsentlogq

I have used sp_dbmmonitorresults procedure to get the value of unsent log queue.  This can also be fetched through performance monitor counter SQL Server:Database Mirroring\ Log sent Queue.I also added implemented logging to a text file to be used as a report. The code for logging is shown below.

function WriteToLog { 
    param ([string]$msg) 
   # specify log file to log to.
    $LogFile  = ‘d:\Logs\DefragIndex.log’ 
    $date = [string](Get-Date -format MMddyyyy-hh:mm:ss)
   # write the message to log file along with the current date.   
 Write-Output $date":$msg" | Out-File $LogFile -append     
# function usage.
WriteToLog "Index Maintenance Completed for Instance $s in $executiontime"

The last step is to trap and log exceptions for debugging purpose. The code for same is shown below.

# trap and log exceptions.
Trap {
    $exception = $_.Exception
    while( $ exception.InnerException ) {
    $ exception = $ exception.InnerException
    WriteToLog "Error  $ exception.Message"
  # end script execution

To wrap this thing up, I created a function fn_defragindexes($inst,$deffragmentpercent,$defpagecount, $UnsentQthld) which can be called as

fn_defragindexes “ServerName\InstanceName”  30 1000 0 to reorganize indexes for all databases with fragmentation percent greater than 30 , page count greater than or equal to 1000 and the mirroring unsent log queue threshold 0.

The complete code can be downloaded from https://www.dropbox.com/s/3o8qsyd6b3a2tx4/IndexReorgInMirroringEnvironment.ps1.

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *