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.

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.

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.

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.

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.

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

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.



Ahmad Osama

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

Follow me on TwitterFollow me on FaceBook