Improving Your Insight SharePoint and Teamcenter SQL Server Performance By 460% with Index Defragmentation

Courtesy of the SQL Fool an awesome script to defragment the SQL Server indexes.   This is a maintenance script and can be useful for improving your overall SQL Server performance issues.

SQL utility and maintenance scripts can be applicable for both Insight and TCX installations running SQL Server.

So does index defragmentation matter?  From Microsoft SQL Server 2000 Index Defragmentation Best Practices: 

"The workload performance increase realized in the small-scale environment ranged from 60 percent at the low level of fragmentation to more than 460 percent at the highest level of fragmentation. The workload performance increased realized for the large-scale environment ranged from 13 percent at the low fragmentation level to 40 percent at the medium fragmentation level."

So how to use this defragmentation script?


In SQL Server Management Studio open a New Query:

Download or copy and paste the entire script from SQL Fool into the SQL Query pane:

Execute the script to create the tables, store the function etc. used by this script:

Review the comments in the script to determine all of the parameters you can run the script with:

Open another SQL Query window.  Insert the parameters you wish to run the script with.  For this example, use the following parameters to defragment all databases, with the maximum verboseness including debug messages, print results, etc.:

EXECUTE dbo.dba_indexDefrag_sp
              @executeSQL           = 1
            , @printCommands        = 1
            , @debugMode            = 1
            , @printFragmentation   = 1
            , @forceRescan          = 1
            , @maxDopRestriction    = 1
            , @minPageCount         = 8
            , @maxPageCount         = NULL
            , @minFragmentation     = 1
            , @rebuildThreshold     = 30
            , @defragDelay          = '00:00:05'
            , @defragOrderColumn    = 'page_count'
            , @defragSortOrder      = 'DESC'
            , @excludeMaxPartition  = 1
            , @timeLimit            = NULL;

Sit back and wait for the processing to finish.  Depending on the parameters used to run the script you can check the Messages tab for progress:

Once complete, again depending on the parameters used review the results.  On my test Insight and Teamcenter  servers there was considerable fragmentation on the indexes with several at 90%, 95% and even 99% fragmented.  My test servers see nowhere close to the volume, data and subsequent defragmentation that your production Insight or Teamcenter servers experience.

Initial Insight SQL Server fragmentation results:

Initial Teamcenter SQL Server fragmentation results:

Running the script a second time we can clearly see that the fragmentation on the indexes has vastly improved.

Subsequent Insight SQL Server fragmentation results:

Subsequent Teamcenter SQL Server fragmentation results:

I would hope that it goes without saying but just to be sure, run this fragmentation script during off hours when the database is idle.

On a production server you would want to install this script as a periodic maintenance task.

As always, YMMV.