I thought I’d share a script that I came up with today. If you’re using MSSQL, you can use it to automate index rebuilds on the currently active Historian partition.
/*--------------------------------------------------------------------------------------
Description: Rebuilds indexes for active partition on Ignition Historian.
Date: September 17, 2014
Written By: Cody Warren
Company: Tamaki Control Ltd.
--------------------------------------------------------------------------------------*/
DECLARE @Index VARCHAR(MAX); -- Index Name
DECLARE @Table VARCHAR(MAX); -- Table Name
DECLARE @SQL VARCHAR(MAX); -- Dynamic SQL Query
DECLARE @PartitionCursor CURSOR; -- Cursor for list of indexes
-- Create list of indexes for currently active partition table
SET @PartitionCursor = CURSOR FOR
SELECT
'[' + s.[name] + '].' + '[' + t.[name] + ']' as [table],
'[' + i.[name] + ']' as [index]
FROM [sys].[indexes] i
INNER JOIN [sys].[tables] t ON
i.[object_id] = t.[object_id]
AND
i.[name] IS NOT NULL
INNER JOIN [sys].[schemas] s ON
t.[schema_id] = s.[schema_id]
INNER JOIN
(
SELECT TOP 1
[pname]
FROM [dbo].[sqlth_partitions]
ORDER BY [start_time] DESC
) p ON
t.[name] = p.[pname]
OPEN @PartitionCursor;
FETCH NEXT FROM @PartitionCursor INTO @Table, @Index;
SET @SQL = '
USE [Historian]
';
-- Loop through indexes in currently used partition and create rebuild SQL statement
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = @SQL + '
ALTER INDEX ' + @index + ' ON ' + @Table + ' REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
'
FETCH NEXT FROM @PartitionCursor INTO @Table, @Index;
END
--SELECT @SQL; -- Debug Only
EXECUTE(@SQL); -- Execute dynamic SQL