User Tools

Site Tools


taterrabase:rebuild-indexes

Rebuild indexes

General

SQL Server uses index tables to speed up queries.
These index tables can become fragmented after some time and need to be rebuild periodically. Daily rebuilds are recommended (preferably at night)
Fragmented indexes can result in very slow databases.

Automatic rebuild

This process can be automated with a Windows Task and a batch file:
sqlcmd_rebuild_indexes.zip

Script (for rebuilding manually)

The following script loops all the tables and rebuilds and reorganizes the indexes that are fragmented.

DECLARE @ec BIGINT
DECLARE @schema NVARCHAR(255)
DECLARE @table NVARCHAR(255)
DECLARE @index NVARCHAR(255)
DECLARE @sql NVARCHAR(MAX)

DECLARE indexes CURSOR LOCAL FOR 
  SELECT [schema_name], [table_name], [index_name] FROM 
  (
    SELECT 
      (select TOP 1 SCHEMA_NAME(t.schema_id) FROM sys.tables t WHERE t.object_id = i.object_id) AS [schema_name]
      ,OBJECT_NAME(i.object_id) as [table_name]
      ,i.[name]                 as [index_name]
    FROM sys.indexes i
    WHERE i.[allow_page_locks] = 1 /*and i.[is_unique] = 1*/
    ) as [tbl]
  WHERE 
    [schema_name] NOT LIKE 'sys%'
    AND [table_name] NOT LIKE 'sys%'
    AND [table_name] NOT LIKE 'queue%'
  ORDER BY [schema_name], [table_name], [index_name]

OPEN indexes
FETCH NEXT FROM indexes INTO 
   @schema
  ,@table
  ,@index
WHILE @@FETCH_STATUS = 0
  BEGIN
  
    PRINT 'REBUILD INDEX: ' + @index
  
    SET @sql = 'ALTER INDEX [' + @index + '] ON [' + @schema + '].[' + @table + '] REBUILD' -- WITH (ALLOW_PAGE_LOCKS = ON)
    
    EXEC sp_executesql @sql

    PRINT 'REORGANIZE INDEX: ' + @index
  
    SET @sql = 'ALTER INDEX [' + @index + '] ON [' + @schema + '].[' + @table + '] REORGANIZE'
  
    EXEC sp_executesql @sql
  
    FETCH NEXT FROM indexes INTO 
       @schema
      ,@table
      ,@index
  END
CLOSE indexes;
DEALLOCATE indexes;

PRINT 'REBUILD AND REORGANISATION FINISHED'

Manual rebuild

In TerraBase, click on the menu 'Extra' to manually rebuild the indexes. This can take some time.

taterrabase/rebuild-indexes.txt · Last modified: 2019/02/27 08:20 by dirk