Dans la documentation How to Create Maintenance Plans in SQL to Keep Your Database Running Smoothly (ivanti.com)
on va trouver les query pour la fragmentation et le rebuild des index
SQL Fragmentation Query
--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_count
FROM 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 >= 20
ORDER BY avg_fragmentation_in_percent desc
Rebuild fragmented indexes
-- Rebuild fragmented indexes
DECLARE @TableName VARCHAR(255);
DECLARE @sql NVARCHAR(500);
DECLARE @fillfactor INT;
SET @fillfactor = 70;
DECLARE [TableCursor] CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id]) + '.' + [name] AS [TableName]
FROM [sys].[tables];
OPEN [TableCursor];
FETCH NEXT FROM [TableCursor]
INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
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