Recommended Steps to Manually Rebuild SQL Indexes for Enterprise Vault and Accelerator Databases

Overview

SQL Server maintains Indexes to reflect updates to the Tables. All Indexes contain pointers to the previous and next pages within the Index; this forms a list of all index/data pages. Ideally, the physical ordering of the pages in the data file should match the logical ordering. Fragmentation exists when Indexes have pages in which the logical ordering does not match the physical ordering inside the data file. As data is inserted, updated and deleted over time fragmentation levels on the Indexes will increase. When the physical ordering does not match the logical ordering, disk throughput will become less efficient as the drives must continuously scan non-contiguous sectors to gather the Index pages instead of scanning forward in one direction.

Rebuilding the Indexes will reorganize the pointers in the correct order. After Rebuilding the Indexes, overall disk throughput can significantly increase as the physical ordering more closely matches the logical ordering of the data. This in turn allows SQL Queries and Stored Procedures to process more efficiently.

Note: Fragmentation affects disk I/O performance but has no effect on performance of queries whose data pages reside in the SQL Server data cache.

How to Rebuild Indexes

It is recommended that full Database backups are completed and all EV, CA, and DA Services are Stopped before running any Index Rebuilds. If Services are not Stopped, active processes will lock Table pages causing the Index Rebuild to skip those pages, resulting in the Tables remaining fragmented.

Method 1

Run SQL Maintenance per the "How to Automatically Backup and Perform Recommended Maintenance for Enterprise Vault (EV), Compliance Accelerator (CA), and Discovery Accelerator (DA) SQL databases" article referenced below.

Method 2

Replace DB_NAME with the Database Name and run the following SQL Query:


USE DB_NAME --Change DB_NAME with the correct Database Name
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
   object_id AS objectid,
   index_id AS indexid,
   partition_number AS partitionnum,
   avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 5.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
   BEGIN;
       FETCH NEXT
          FROM partitions
          INTO @objectid, @indexid, @partitionnum, @frag;
       IF @@FETCH_STATUS < 0 BREAK;
       SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
       FROM sys.objects AS o
       JOIN sys.schemas as s ON s.schema_id = o.schema_id
       WHERE o.object_id = @objectid;
       SELECT @indexname = QUOTENAME(name)
       FROM sys.indexes
       WHERE  object_id = @objectid AND index_id = @indexid;
       SELECT @partitioncount = count (*)
       FROM sys.partitions
       WHERE object_id = @objectid AND index_id = @indexid;

-- 10 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
       IF @frag < 10.0
           SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
       IF @frag >= 10.0
           SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
       IF @partitioncount > 1
           SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
       EXEC (@command);
       PRINT N'Executed: ' + @command;
   END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO