Cinquième article de la série Une approche pragmatique de la production SQL Server, il s’agit ici de la défragmentation des objets.
Qu’est-ce que la fragmentation ?
Lorsqu’un objet (table ou indexe) subit des modifications, ses pages peuvent se vider partiellement (on parle de fragmentation interne) et certaines pages, voire certaines extensions (groupes de 8 pages contigües) peuvent se vider complètement (il s’agit alors de fragmentation externe).
Quel est l’impact sur les performances ?
Sur disque
Pour lire une donnée sur disque physique, il faut :
Déplacer la tête de lecture du disque. Cette opération est longue, de l’ordre de 5 millisecondes en moyenne
Attendre que le secteur de données à lire passe sous la tête. Cette opération prend en moyenne entre 2 et 6 millisecondes (un demi-tour du disque, à une vitesse de rotation entre 5000 et 15000 tr/min)
Lorsque les secteurs à lire sont contigus (c.-à-d. placés physiquement à la suite les uns des autres), la tête de lecture n’a plus à se déplacer ; lire 100 secteurs contigus prend donc à peine plus de temps que d’en lire un seul (disons 5ms pour la tête+ 4ms pour le demi tour + 1ms pour la lecture des 100 secteurs=10ms contre 100x(6 + 4)=1000ms pour des secteurs non contigus).
Si l’objet est fragmenté, les déplacements de la tête de lecture seront plus fréquents, les temps de lecture seront donc (beaucoup) plus longs.
En mémoire
Lorsqu’un objet n’est pas fragmenté, ses pages sont pleines. L’objet occupe donc moins de place que s’il était fragmenté. Un objet fragmenté occupera donc plus de place en mémoire qu’un objet non fragmenté, au détriment des autres objets.
Pour l’Optimizer
Lorsque SQL Server calcule le coût d’une opération, il distingue une lecture séquentielle (considérée peu coûteuse) et une lecture aléatoire (considérée très coûteuse). Si un objet est fortement fragmenté, les lectures séquentielles deviennent plus coûteuses que ce qu’a estimé SQL Server, le plan d’exécution choisi n’est donc potentiellement plus optimal.
Mesurer la fragmentation
Depuis la version 2005 de SQL Server, le taux de fragmentation est directement consultable via une vue dynamique de gestion.
select object_name(I.object_id) 'Table', I.name 'Indexe', IPS.avg_fragmentation_in_percent 'Taux de Fragmentation' FROM sys.dm_db_index_physical_stats(DB_ID(),DEFAULT, DEFAULT, DEFAULT,DEFAULT) IPS inner join sys.indexes I ON I.object_id = IPS.object_id AND I.index_id = IPS.index_id where IPS.index_level=0 and IPS.index_id !=0 order by 1,IPS.index_id,2
Comment défragmenter ?
Défragmenter un objet consiste à le compacter et à déplacer ses pages de manière à ce qu’elles soient contigües. L’action de défragmenter est la réorganisation, elle est gérée dans SQL Server par deux ordres :
ALTER INDEX REBUILD --et ALTER INDEX REORGANIZE
Lorsqu’une table ne contient pas d’indexe clusterisé, il faut soit l’exporter, la vider puis la réimporter (attention aux contraintes référentielles qui pourraient empêcher ces opérations), soit créer un indexe clusterisé puis le supprimer.
Lorsqu’elle en contient un, la méthode de défragmentation dépendra du taux de fragmentation observé.
(les valeurs ci-dessous sont relativement empiriques)
Taux <5% Pas de défragmentation. La consommation des ressources liée à la défragmentation serait a priori supérieure au gain en performance résultant.
Taux compris entre 5 et 25% ALTER INDEX <nom de l’indexe> ON <nom de la table> REORGANIZE . La défragmentation s’opérera uniquement au niveau des feuilles de l’indexe/la table, mais sera relativement peu consommatrice en ressources.
Taux supérieur à 25% ALTER INDEX <nom de l’indexe> ON <nom de la table> REBUILD.
Cette méthode va reconstruire intégralement l’indexe/la table, opération consommatrice mais aboutissant à une table complètement défragmentée.
A noter que :
- La défragmentation est un processus très consommateur en ressources (disque et mémoire), il est donc à lancer en dehors des fortes périodes d’activité.
- La défragmentation via ALTER INDEX REBUILD va mettre automatiquement à jour les statistiques. Inutile donc d’effectuer à nouveau cette mise à jour dans votre tâche de maintenance de mise à jour des stats (cf article 4).
- En édition Enterprise, il est possible de réduire l’impact de la défragmentation en utilisant l’option ONLINE.
- Le taux de fragmentation des tables de très petite volumétrie ne pourra être réduit à 0 (cf cet article), c’est tout à fait normal !
Voici un exemple de procédure stockée permettant de défragmenter les indexes d’une base :
USE outils_dba
GO
IF OBJECT_ID('defragmente_base') IS NOT NULL
DROP PROCEDURE defragmente_base
GO
CREATE PROCEDURE defragmente_base ( @nom_base sysname,
@online varchar(3)='NON'
)
AS
/***************************************************************************************
B. Vesan, Cap Data Consulting, Février 2012
Cette procedure permet de défragmenter les indexes d'une base.
0<Taux<5 Pas de défragmentation
5<=Taux<25 REORGANIZE
25<=Taux REBUILD
Si la valeur du paramètre @online vaut 'OUI', la reconstruction sera effectuée avec l'
option ONLINE=ON
La procédure retournera 0 en cas de succès, 1 en cas d'échec.
***************************************************************************************/
BEGIN
SET NOCOUNT ON
DECLARE @cmd varchar(4000),@msg varchar(1000), @option varchar(100), @erreur int
CREATE TABLE #table_defrag(commande varchar(4000))
IF DB_ID(@nom_base) IS NULL
BEGIN
SET @msg = 'Echec de l'' exécution de defragmente_base : La base '+ISNULL(@nom_base,'(valeur nulle)')+' n''existe pas'
RAISERROR (@msg, 16, 1) WITH NOWAIT,LOG
return 1
END
IF ( DATABASEPROPERTYEX(@nom_base,'Status ')!='ONLINE' OR EXISTS (select 1 FROM sys.databases WHERE name=@nom_base AND source_database_id IS NOT NULL))
BEGIN
SET @msg = 'La base '+@nom_base+' n''est pas disponible ou il s''agit d''un snapshot'
PRINT @msg
return 0
END
set @option = (CASE UPPER(@online) WHEN 'OUI' THEN ' WITH ( ONLINE = ON )' ELSE '' END)
set @cmd='USE ['+@nom_base+']
select ''ALTER INDEX [''+I.name+''] ON ['+@nom_base+'].[''+S.name+''].[''+T.name+''] ''+
(CASE WHEN IPS.avg_fragmentation_in_percent >= 25 THEN ''REBUILD '+@option+''' ELSE ''REORGANIZE '' END)
FROM sys.dm_db_index_physical_stats(DB_ID(),DEFAULT, DEFAULT, DEFAULT,DEFAULT) IPS
inner join sys.indexes I ON I.object_id = IPS.object_id AND I.index_id = IPS.index_id
inner join sys.tables T ON T.object_id = I.object_id
inner join sys.schemas S ON S.schema_id = T.schema_id
where IPS.index_level=0
and IPS.index_id !=0
and IPS.avg_fragmentation_in_percent >=5
AND alloc_unit_type_desc NOT IN(''LOB_DATA'')
order by object_name(I.object_id),IPS.index_id'
--PRINT @cmd
INSERT INTO #table_defrag
EXECUTE(@cmd)
DECLARE CURSEUR_INDEXES INSENSITIVE CURSOR FOR
select commande from #table_defrag
OPEN CURSEUR_INDEXES
FETCH NEXT FROM CURSEUR_INDEXES INTO @cmd
WHILE(@@FETCH_STATUS=0)
BEGIN
-- PRINT @cmd
EXECUTE(@cmd)
select @erreur=@@ERROR
IF @erreur !=0
BEGIN
SET @msg = 'Echec de l'' exécution de defragmente_base : Erreur '+CAST(@erreur as varchar(5))+' lors de l''appel à la commande "'+@cmd+'"'
RAISERROR (@msg, 16, 1) WITH NOWAIT,LOG
CLOSE CURSEUR_INDEXES
DEALLOCATE CURSEUR_INDEXES
DROP TABLE #table_defrag
return 1
END
FETCH NEXT FROM CURSEUR_INDEXES INTO @cmd
END
CLOSE CURSEUR_INDEXES
DEALLOCATE CURSEUR_INDEXES
DROP TABLE #table_defrag
END
GOContinuez votre lecture sur le blog :
- Production SQL Server : Les Statistiques (Benjamin VESAN) [SQL Server]
- Question bête: Faut-il créer des plans de maintenance avec Reconstruction ET Réorganisation ? (Benjamin VESAN) [SQL Server]
- Fragmentation sur des tables stockées en S-GAM (David Baffaleuf) [SQL Server]
- Oracle et SQL Server: La Fragmentation (Benjamin VESAN) [OracleSQL Server]
- Production SQL Server : Sauvegardes (Benjamin VESAN) [SQL Server]