In the documentation How to Create Maintenance Plans in SQL to Keep Your Database Running Smoothly (ivanti.com)
You will find the queries for fragmentation and index rebuilds.
SQL Fragmentation Query
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | <pre class="wp-block-syntaxhighlighter-code">--Change the <YOURDATABASE> variable to the name of your EPM database that you are running the query against.USE <YOURDATABASE>SELECT Object_name(object_id, database_id) AS table_name, index_id, partition_number, avg_fragmentation_in_percent, fragment_count, page_countFROM sys.Dm_db_index_physical_stats(NULL, NULL, NULL, NULL, NULL)WHERE index_type_desc = N'CLUSTERED INDEX' AND avg_fragmentation_in_percent > 5 AND page_count >= 20ORDER BY avg_fragmentation_in_percent desc</pre> |
Rebuild Fragmented Indexes
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | -- Rebuild fragmented indexesDECLARE @TableName VARCHAR(255);DECLARE @sql NVARCHAR(500);DECLARE @fillfactor INT;SET @fillfactor = 70;DECLARE [TableCursor] CURSOR FORSELECT OBJECT_SCHEMA_NAME([object_id]) + '.' + [name] AS [TableName]FROM [sys].[tables];OPEN [TableCursor];FETCH NEXT FROM [TableCursor]INTO @TableName;WHILE @@FETCH_STATUS = 0BEGIN SET @sql = N'ALTER INDEX ALL ON ' + @TableName + N' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3), @fillfactor) + N')'; EXEC (@sql); FETCH NEXT FROM [TableCursor] INTO @TableName;END;CLOSE [TableCursor];DEALLOCATE [TableCursor];GO |
