Fragmentation SQL Server. Gestion des Index

Vous avez tout tenté pour améliorer les performances de SQL Serveur? Plus de RAM, plus de CPU, amélioration du code...

Un des derniers points d'amélioration a ne pas négliger est la fragmentation des index.

Pour savoir si votre base de données SQL server est fragmentée, exécutez ce code:

SELECT dbschemas.[name] as 'Schema', 

dbtables.[name] as 'Table', 

dbindexes.[name] as 'Index',

indexstats.avg_fragmentation_in_percent,

indexstats.page_count

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats

INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]

INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]

INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]

AND indexstats.index_id = dbindexes.index_id

WHERE indexstats.database_id = DB_ID()

ORDER BY indexstats.avg_fragmentation_in_percent desc

Cette requête vous affichera les fragmentations par tables et par index.

Des tables souvent utilisées sont présentes dans cette extraction? Considérez à ajouter des index sur la table ou à recontruire l'index existant.

Pour cela, vous pouvez executer le code suivant qui réindexera tous les index des tables:

DECLARE @Database VARCHAR(255)   

DECLARE @Table VARCHAR(255)  

DECLARE @cmd NVARCHAR(500)  

DECLARE @fillfactor INT 

SET @fillfactor = 90 

DECLARE DatabaseCursor CURSOR FOR  

SELECT name FROM master.dbo.sysdatabases   

WHERE name NOT IN ('master','msdb','tempdb','model','distribution')   

ORDER BY 1  

OPEN DatabaseCursor  

FETCH NEXT FROM DatabaseCursor INTO @Database  

WHILE @@FETCH_STATUS = 0  

BEGIN  

   SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + 

  table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES 

  WHERE table_type = ''BASE TABLE'''   

   -- create table cursor  

   EXEC (@cmd)  

   OPEN TableCursor   

   FETCH NEXT FROM TableCursor INTO @Table   

   WHILE @@FETCH_STATUS = 0   

   BEGIN   

       IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)

       BEGIN

           -- SQL 2005 or higher command 

           SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' 

           EXEC (@cmd) 

           PRINT @Table

       END

       ELSE

       BEGIN

          -- SQL 2000 command 

          DBCC DBREINDEX(@Table,' ',@fillfactor)  

       END

       FETCH NEXT FROM TableCursor INTO @Table   

   END   

   CLOSE TableCursor   

   DEALLOCATE TableCursor  

   FETCH NEXT FROM DatabaseCursor INTO @Database  

END  

CLOSE DatabaseCursor   

DEALLOCATE DatabaseCursor