[SQL Server] Index Réorganisation et Compilation
CREATE PROCEDURE dbo.[IndexRebuild] AS
DECLARE @TableName NVARCHAR(500);
DECLARE @SQLIndex NVARCHAR(max);
DECLARE @RowCount INT;
DECLARE @Counter INT;
DECLARE @IndexAnalysis TABLE
(AnalysisID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
TableName NVARCHAR(500),
SQLText NVARCHAR(max),
IndexDepth INT,
AvgFragmentationInPercent FLOAT,
FragmentCount BIGINT,
AvgFragmentSizeInPages FLOAT,
PageCount BIGINT)
BEGIN
INSERT INTO @IndexAnalysis
SELECT [objects].name,
'ALTER INDEX [' + [indexes].name + '] ON [' + [schemas].name + '].[' + [objects].name +'] ' +
(CASE
WHEN ([dm_db_index_physical_stats].avg_fragmentation_in_percent >= 20 AND [dm_db_index_physical_stats].avg_fragmentation_in_percent < 40) THEN 'REORGANIZE'
WHEN [dm_db_index_physical_stats].avg_fragmentation_in_percent > = 40 THEN 'REBUILD' END) AS zSQL,
[dm_db_index_physical_stats].index_depth,
[dm_db_index_physical_stats].avg_fragmentation_in_percent,
[dm_db_index_physical_stats].fragment_count,
[dm_db_index_physical_stats].avg_fragment_size_in_pages,
[dm_db_index_physical_stats].page_count
FROM [sys].[dm_db_index_physical_stats](DB_ID(),NULL,NULL,NULL,'LIMITED' ) AS [dm_db_index_physical_stats]
INNER JOIN [sys].[objects] AS [objects] ON ([dm_db_index_physical_stats].[object_id] = [objects].[object_id])
INNER JOIN [sys].[schemas] AS [schemas] ON ([objects].[schema_id] = [schemas].[schema_id])
INNER JOIN [sys].[indexes] AS [indexes] ON ([dm_db_index_physical_stats].[object_id] = [indexes].[object_id] AND [dm_db_index_physical_stats].index_id = [indexes].index_id )
WHERE index_type_desc <> 'HEAP'
AND [dm_db_index_physical_stats].avg_fragmentation_in_percent > 20
END
SELECT @RowCount = COUNT(AnalysisID) FROM @IndexAnalysis
SET @Counter = 1
WHILE @Counter <= @RowCount
BEGIN
SELECT @SQLIndex = SQLText FROM @IndexAnalysis WHERE AnalysisID = @Counter
EXECUTE sp_executesql@SQLIndex
SET @Counter = @Counter + 1
END
GO