Update: A read-only Notification Server report (SQL Index Fragmentation.xml) is now available (see attachments on the right side of this article). The report will indicate if significant fragmentation exists. It does not require SQL knowledge to execute. Note: Be aware that running this report is very intense on SQL and should only be run off hours and not during any Notification Server maintenance windows.

For long term remediation, please utilize the SQL Server maintenance wizard to create a weekly maintenance task to perform an index rebuild. 

Sample report screenshot


 

SQL query for Index Defrag
The script provided below is primarily intended for a quick one-time fix. As indexes will re-fragment over time, a regularly scheduled maintenance task is the best solution.

Before running this script, back up your database (just in case); however, this script should not damage anything. It should only remove empty spaces in the database. Read the article about DBCC SHOWCONTIG (http://msdn2.microsoft.com/en-us/library/aa258803(SQL.80).aspx), and you will find more information about this script. The process will take few minutes, since the database is large, but if you want to run the defrag with less percentage of increase, change SELECT @maxfrag = 30.0 to 10.0 or something like that.
 

/* /   Perform a 'USE <database name>' to select the database in which to run the script. */ -- Declare variables SET NOCOUNT ON DECLARE @tablename varchar (128) DECLARE @execstr   varchar (255) DECLARE @objectid  int DECLARE @indexid   int DECLARE @frag        numeric(8,3) DECLARE @maxfrag  numeric(8,3)   -- Decide on the maximum fragmentation to allow -- Below is the percentage the index has to be fragmented -- Suggest to start at 30.0 (30%) SELECT @maxfrag = 30.0   -- Declare cursor DECLARE tables CURSOR FOR    SELECT TABLE_NAME    FROM INFORMATION_SCHEMA.TABLES    WHERE TABLE_TYPE = 'BASE TABLE' -- Do not scan common table indexes         AND TABLE_NAME NOT LIKE 'xdl%'         AND TABLE_NAME NOT LIKE 'Prf_%'   -- Create the table CREATE TABLE #fraglist (    ObjectName varchar (255),    ObjectId int,    IndexName varchar (255),    IndexId int,    Lvl int,    CountPages int,    CountRows int,    MinRecSize int,    MaxRecSize int,    AvgRecSize int,    ForRecCount int,    Extents int,    ExtentSwitches int,    AvgFreeBytes int,    AvgPageDensity int,    ScanDensity numeric(8,3),    BestCount int,    ActualCount int,    LogicalFrag numeric(8,3),    ExtentFrag numeric(8,3) )   -- Open the cursor OPEN tables   -- Loop through all the tables in the database FETCH NEXT FROM tables INTO @tablename   WHILE (@@FETCH_STATUS = 0) BEGIN -- Do the showcontig of all indexes of the table    INSERT INTO #fraglist    EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')    FETCH NEXT FROM tables INTO @tablename END   -- Close and deallocate the cursor CLOSE tables DEALLOCATE tables   -- Declare cursor for list of indexes to be defragged DECLARE indexes CURSOR FOR    SELECT ObjectName, ObjectId, IndexId, LogicalFrag    FROM #fraglist    WHERE LogicalFrag >= @maxfrag         AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0   -- Open the cursor OPEN indexes   -- loop through the indexes FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @frag   WHILE (@@FETCH_STATUS = 0) BEGIN    PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',       ' + RTRIM(@indexid) + ') - fragmentation currently '        + RTRIM(CONVERT(varchar(15),@frag)) + '%'    SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ', ' + RTRIM(@indexid) + ')'    EXEC (@execstr)      FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @frag END   -- Close and deallocate the cursor CLOSE indexes DEALLOCATE indexes   -- Delete the temporary table DROP TABLE #fraglist GO