cancel
Showing results for 
Search instead for 
Did you mean: 

Enterprise Vault SQL Server Fragmentation

elroy_lo
Level 4

Dear All,

  I got the warnning about SQL tables have one or more indexes with a fragmentation level of more than 30%. What should I do? To rebuild index in SQL server? Is there any maintenace task I would need to do on SQL Server?

Thanks

Best regards,

Elroy

1 ACCEPTED SOLUTION

Accepted Solutions

AndrewB
Moderator
Moderator
Partner    VIP    Accredited

in some extreme cases you might have to stop the EV services but it shouldnt be necessary on an ongoing basis. i dont know how you're going about rebuilding the index but if you're just using the builtin wizard, you might also want to look into other options or consult a DBA.

View solution in original post

10 REPLIES 10

AndrewB
Moderator
Moderator
Partner    VIP    Accredited

generally speaking, this article covers standard SQL maintenance procedures.

How to automatically back up and perform recommended maintenance for Enterprise Vault SQL databases

you can leave out the shrink operation.

elroy_lo
Level 4

Dear Sir,

  Can I have more specific instruction for the SQL maintenance? Should I stop all the enterprise vault services and run a SQL maintenance job to rebuild index? Thanks

Best Regards,

Elroy

AndrewB
Moderator
Moderator
Partner    VIP    Accredited

it's not necessary to stop the services but i suppose you could put EV in backup mode. if you dont do that, at least make sure your archiving window doesnt overlap with your maintenance job

elroy_lo
Level 4

Noted. I have tried to rebuild the index. However, the fragmentation is still high after rebuilding index. Would you please advise? Thanks a lot

Best Regards,

Elroy

AndrewB
Moderator
Moderator
Partner    VIP    Accredited

how do you know that the fragmentation is still high?

elroy_lo
Level 4

Dear Sir,

  It is because I still got the high fragmentation warning and also I have browsed to the SQL table and checked a few tables of the extend fragmentation is over 50%

Thanks

Best Regards,

elroy

AndrewB
Moderator
Moderator
Partner    VIP    Accredited

in some extreme cases you might have to stop the EV services but it shouldnt be necessary on an ongoing basis. i dont know how you're going about rebuilding the index but if you're just using the builtin wizard, you might also want to look into other options or consult a DBA.

elroy_lo
Level 4

I have stopped the EV service to run the index rebuild. I have createda maintenance plan in SQL to do reuild index task and update statistics. Would you please provide any clues for this warning? Thanks

Best Regards,

Elroy

AndrewB
Moderator
Moderator
Partner    VIP    Accredited

i've seen DBAs use this one https://ola.hallengren.com/

Bashweshwar
Level 1

Run below query on SQL for the fragmented databse

USE <Vault store group 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