Hi @j.keulartz,
Thank you for getting back to me and for sharing the results.
Upon reviewing the information, the fragmentation of the SQL DB indexes does appear high and would likely see a noticeable benefit from running defragmentation and hopefully fix the timeout issue.
Below is a script you can run against your Octopus SQL DB in order to rebuild the indexes. I recommend running this at a time where users won’t be impacted as rebuilding indexes will affect SQL DB performance while it is running:
DECLARE @indexName NVARCHAR(MAX), @schemaName NVARCHAR(MAX), @tableName NVARCHAR(MAX), @current SMALLINT, @avgFragmentationPercentage FLOAT, @start DATETIMEOFFSET, @globalStart DATETIMEOFFSET
SET @globalStart = SYSDATETIMEOFFSET()
PRINT 'Performing maintenance on SQL indexes'
DECLARE @indexFragmentation TABLE (
[TableName] sysname,
[SchemaName] sysname,
[IndexName] sysname,
[AvgFragmentationPercentage] FLOAT
)
PRINT 'Querying index fragmentation'
INSERT INTO @indexFragmentation ([TableName], [SchemaName], [IndexName], [AvgFragmentationPercentage])
SELECT
tbl.name as [TableName],
SCHEMA_NAME (tbl.schema_id) as [SchemaName],
idx.Name as [IndexName],
pst.avg_fragmentation_in_percent as [AvgFragmentationPercentage]
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , 'SAMPLED') as pst
INNER JOIN sys.tables as tbl ON pst.object_id = tbl.object_id
INNER JOIN sys.indexes idx ON pst.object_id = idx.object_id AND pst.index_id = idx.index_id
WHERE pst.index_id != 0
AND pst.alloc_unit_type_desc IN ( N'IN_ROW_DATA', N'ROW_OVERFLOW_DATA');
SET @current = 1
DECLARE indexName_cursor CURSOR
FOR
SELECT [IndexName]
FROM @indexFragmentation
WHERE [AvgFragmentationPercentage] > 10 AND [AvgFragmentationPercentage] <= 30
ORDER BY AvgFragmentationPercentage DESC;
OPEN indexName_cursor;
PRINT 'Reorganizing ' + LTRIM(STR(@@CURSOR_ROWS)) + ' fragmented indexes'
FETCH NEXT FROM indexName_cursor INTO @indexName;
WHILE @@fetch_status = 0
BEGIN
SELECT
@schemaName = [SchemaName],
@tableName = [TableName],
@avgFragmentationPercentage = [AvgFragmentationPercentage]
FROM @indexFragmentation
WHERE [IndexName] = @indexName
PRINT 'Reorganizing index ' + @indexName + ' (' + LTRIM(STR(@avgFragmentationPercentage)) + '%) on table ' + @schemaName + '.' + @tableName + ' (' + LTRIM(STR(@current)) + '/' + LTRIM(STR(@@CURSOR_ROWS)) + ')';
SET @start = SYSDATETIMEOFFSET()
EXEC('ALTER INDEX [' + @indexName + '] ON ['+ @schemaName +'].[' + @tableName + '] REORGANIZE;');
PRINT 'Reorganizing index ' + @indexName + ' took ' + CONVERT(varchar(40), DATEDIFF(second, @start, SYSDATETIMEOFFSET())) + ' seconds';
SET @current = @current + 1
FETCH NEXT FROM indexName_cursor INTO @indexName;
END;
CLOSE indexName_cursor;
DEALLOCATE indexName_cursor;
SET @current = 1
DECLARE indexName_cursor CURSOR
FOR
SELECT [IndexName]
FROM @indexFragmentation
WHERE [AvgFragmentationPercentage] > 30
ORDER BY AvgFragmentationPercentage DESC;
OPEN indexName_cursor;
PRINT 'Rebuilding ' + LTRIM(STR(@@CURSOR_ROWS)) + ' heavily fragmented indexes'
FETCH NEXT FROM indexName_cursor INTO @indexName;
WHILE @@fetch_status = 0
BEGIN
SELECT
@schemaName = [SchemaName],
@tableName = [TableName],
@avgFragmentationPercentage = [AvgFragmentationPercentage]
FROM @indexFragmentation
WHERE [IndexName] = @indexName
PRINT 'Rebuilding index ' + @indexName + ' (' + LTRIM(STR(@avgFragmentationPercentage)) + '%) on table ' + @schemaName + '.' + @tableName + ' (' + LTRIM(STR(@current)) + '/' + LTRIM(STR(@@CURSOR_ROWS)) + ')';
SET @start = SYSDATETIMEOFFSET()
EXEC('ALTER INDEX [' + @indexName + '] ON ['+ @schemaName +'].[' + @tableName + '] REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF);');
PRINT 'Rebuilding index ' + @indexName + ' took ' + CONVERT(varchar(40), DATEDIFF(second, @start, SYSDATETIMEOFFSET())) + ' seconds';
SET @current = @current + 1
FETCH NEXT FROM indexName_cursor INTO @indexName;
END;
CLOSE indexName_cursor;
DEALLOCATE indexName_cursor;
PRINT 'Finished performing maintenance on SQL indexes. The whole process took ' + CONVERT(varchar(40), DATEDIFF(second, @globalStart, SYSDATETIMEOFFSET())) + ' seconds'
Let me know if you have any questions prior to running the script. Once the script has run, please confirm if you are able to access the Runbook snapshots for the Runbook in question without timing out.
Best Regards,
Donny
