Knowledge Base Article

Home Page > Knowledge Base > Spira Platform

Home Page > Knowledge Base > Spira Platform > SpiraPlan

Home Page > Knowledge Base > Spira Platform > SpiraTeam

Home Page > Knowledge Base > Spira Platform > SpiraTest

Article Rebuilding Database Indexes

by Mike M. on Sunday, July 10, 2016

This article has a couple SQL commands that can be run to optimize database speed and performance.
Over time some of the SQL Server database indexes may need to be rebuilt to ensure optimum performance.

SpiraTeam v5.0 or Later
From v5.0 onwards, we have added a built-in option within the Administration > Data Tools section to reindex the underlying database:



Just click the button and the system will perform the reindex for you.

SpiraTeam v4.2 or Earlier

To rebuild all the indexes in the SpiraTeam database, you need to run the following database command through SQL Query Analyzer / Management Studio whilst all users are logged off the application. For SQL 2005, 2008, or 2012, run the following SQL command:
SET NOCOUNT ON
GO

DECLARE
@FillFactor TINYINT
SELECT
@FillFactor = 80
DECLARE @StartTime DATETIME
SELECT
@StartTime = GETDATE()

IF object_id('tempdb..#TablesToRebuildIndex') IS NOT NULL
BEGIN
            DROP TABLE
#TablesToRebuildIndex
END

DECLARE
@NumTables VARCHAR(20)
SELECT s.[Name] AS SchemaName
            ,t.[name] AS TableName
            ,SUM(p.rows) AS RowsInTable
INTO #TablesToRebuildIndex
FROM sys.schemas s
LEFT JOIN sys.tables t ON s.schema_id = t.schema_id
LEFT JOIN sys.partitions p ON t.object_id = p.object_id
LEFT JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE p.index_id IN (0,1)
           
AND p.rows IS NOT NULL
            AND
a.type = 1
GROUP BY s.[Name] ,t.[name]
SELECT @NumTables = @@ROWCOUNT

DECLARE RebuildIndex CURSOR FOR
SELECT
ROW_NUMBER() OVER (ORDER BY ttus.RowsInTable)
            ,
ttus.SchemaName
            ,ttus.TableName
            ,ttus.RowsInTable
FROM #TablesToRebuildIndex AS ttus
ORDER BY ttus.RowsInTable

OPEN RebuildIndex

DECLARE @TableNumber VARCHAR(20)
DECLARE @SchemaName NVARCHAR(128)
DECLARE @tableName NVARCHAR(128)
DECLARE @RowsInTable VARCHAR(20)
DECLARE @Statement NVARCHAR(300)
DECLARE @Status NVARCHAR(300)

FETCH NEXT
FROM
RebuildIndex
INTO @TableNumber
            ,@SchemaName
            ,@tablename
            ,@RowsInTable

WHILE (@@FETCH_STATUS = 0)
BEGIN
            SET
@Status = 'Table ' + @TableNumber + ' of ' + @NumTables + ': Rebuilding indexes on ' + @SchemaName + '.' + @tablename + ' (' + @RowsInTable + ' rows)'
           
RAISERROR (@Status,0,1)       WITH NOWAIT

            SET
@Statement = 'ALTER INDEX ALL ON [' + @SchemaName + '].[' + @tablename + '] REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3), @FillFactor) + ' )'
           
EXEC sp_executesql @Statement

            FETCH NEXT
            FROM
RebuildIndex
            INTO @TableNumber
                        ,@SchemaName
                        ,@tablename
                        ,@RowsInTable
END
CLOSE
RebuildIndex
DEALLOCATE RebuildIndex
DROP TABLE #TablesToRebuildIndex
PRINT 'Total Elapsed Time: ' + CONVERT(VARCHAR(100), DATEDIFF(minute, @StartTime, GETDATE())) + ' minutes'
GO

If you are on a version of SQL Server before 2005, run the following SQL. Note, however, that this command will not give update messages, nor will it display any errors if reindexing errors or cannot be completed because tables are in use:

EXEC sp_MSforeachtable 'alter index all on ? rebuild'


You can download the full SQL file from this KB article, or copy and paste the SQL from above.
Modifications of the SQL could damage or cause data loss, execute modified versions of these commands at your own risk!
Attachments
Article Info
Last Updated: 7/10/2016
Article ID: KB10
# Views: 5880
Tags
Powered by KronoDesk v1.1.0.15 | © Copyright Inflectra Corporation 2011-2016 | Licensed to Inflectra Corporation.