Production SQL Server: Réorganisation des objets

Lundi, avril 2, 2012
By Benjamin VESAN in SQL Server (bvesan@capdata-osmozium.com) [20 article(s)]

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

GO

Continuez votre lecture sur le blog :




Cliquer pour partager cet article sur Viadeo
Cliquer sur "CAPTURER" pour sauvegarder cet article dans Evernote Clip to Evernote

Tags: ,

Leave a Reply