Maintenance on the Vault Index Database

[lbauer]

Database Health

You should regularly check all of the Vault databases for any internal errors. The DBCC CHECKDB command will run through a thorough set of checks to make sure the database is in operating condition. This can be done within any SQL Server Query tool by running the Check DB command. See SQL Server's help system for more information for DBCC CHECKDB - http://msdn.microsoft.com/en-us/library/ms176064.aspx


Optimizing the Vault Index Database

When a repository index is first built, statistics used by SQL Server may not be available due to the large size of insertions made to the sgvaultindex database. In order to optimize the database for use, you will need to manually update the statistics within the 'sgvaultindex' database for that given repository. Note, as of Vault 6, this is a manual process as you can schedule maintenance routines to fit into your own schedule in order leave you in control of your SQL Server resources.

There are two methods for updating a SQL Server database statistics.

Updating the Statistics - SQL Server keeps internal meta-information about the table to help determine a plan of execution for queries issued by the Vault Server. You can force SQL Server to update these statistics using the UPDATE STATISTICS T-SQL command -
http://msdn.microsoft.com/en-us/library/ms187348.aspx

Rebuilding and Defragging indices - Sometimes when rows are deleted from tables, an index used to help retrieve data within a database table may become severely fragmented. Statistics can updated as a side effect of DBCC DBREINDEX.

DBCC DBREINDEX - http://msdn.microsoft.com/en-us/library/ms181671.aspx

Finally note, when the Vault database is stored on conventional I/O subsystems ( non-RAID / non-striped / non-SAN ), SQL Server can sometimes run into problems related to physical disk fragmentation. You can determine whether disk fragmentation exists using system tools provided Windows or from third parties to analyze these drives. In these environments, you should correct disk fragmentation before running any database index defragmentation.

For some sections of this KB article, you may need to know a given repository's internal ID. To retrieve this data, you will need to run this query using a tool like SQL Server Management Studio where the Vault Server's database is hosted:

Code: Select all

SELECT repid, name FROM sgvault.dbo.tblrepositories

Using the internal repository's id to determine the repository which will be updated, the SQL Script below will update statistics for a single repository's data tables:

Code: Select all

USE sgvaultindex
GO

DECLARE @repid [int],
       @s [nvarchar](256),
       @name [nvarchar](128)

SET @repid = <repid from previous query>

DECLARE c CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE N'rep[_]' + CAST(@repid AS nvarchar(32)) + N'%' ORDER BY TABLE_NAME
OPEN c

FETCH FROM c INTO @name
WHILE (@@FETCH_STATUS = 0)
BEGIN
   -- print the name of the table
   PRINT N'Table Name: ' + @name

   -- statement to update table's statistics
   SET @s = N'UPDATE STATISTICS ' + @name
   EXEC sp_executesql @stmt = @s

   -- next table
   FETCH NEXT FROM c INTO @name
END

CLOSE c
DEALLOCATE c

Using the internal repository's id to determine the repository which will be updated, the SQL Script below will rebuild database indices for a single repository's data tables:

Code: Select all

USE sgvaultindex
GO

DECLARE @repid [int],
       @s [nvarchar](256),
       @name [nvarchar](128)

SET @repid = <repid from previous query>

DECLARE c CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE N'rep[_]' + CAST(@repid AS nvarchar(32)) + N'%' ORDER BY TABLE_NAME
OPEN c

FETCH FROM c INTO @name
WHILE (@@FETCH_STATUS = 0)
BEGIN
   -- print the name of the table
   PRINT N'Table Name: ' + @name

   -- this statement will completely rebuild all indices on the table
   SET @s = N'DBCC DBREINDEX(''sgvaultindex.dbo.' + @name + ''', '''', 0)' 
   EXEC sp_executesql @stmt = @s

   -- next table
   FETCH NEXT FROM c INTO @name
END

CLOSE c
DEALLOCATE c

On the chance you wish to update ALL statistics or rebuild indices for ALL tables within the sgvaultindex database, as a courtesy to our customers the following scripts have been provided.

The following SQL Script will update statistics on all tables in the sgvaultindex database.

Code: Select all

USE sgvaultindex
GO

DECLARE @s [nvarchar](256),
       @name [nvarchar](128)

DECLARE c CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME
OPEN c

FETCH FROM c INTO @name
WHILE (@@FETCH_STATUS = 0)
BEGIN
   -- print the name of the table
   PRINT N'Table Name: ' + @name

   -- statement to update table's statistics
   SET @s = N'UPDATE STATISTICS ' + @name
   EXEC sp_executesql @stmt = @s

   -- next table
   FETCH NEXT FROM c INTO @name
END

CLOSE c
DEALLOCATE c

The following SQL Script will rebuild database indices on all tables in the sgvaultindex database.

Code: Select all

USE sgvaultindex
GO

DECLARE @name [nvarchar](128),
   @s [nvarchar](384)

DECLARE c CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME
OPEN c

FETCH FROM c INTO @name
WHILE (@@FETCH_STATUS = 0)
BEGIN
   -- print the name of the table
   PRINT N'Table Name: ' + @name

   -- this statement will completely rebuild all indices on the table
   SET @s = N'DBCC DBREINDEX(''sgvaultindex.dbo.' + @name + ''', '''', 0)' 
   EXEC sp_executesql @stmt = @s

   -- next table
   FETCH NEXT FROM c INTO @name
END

CLOSE c
DEALLOCATE c