[SQL Server] Pourquoi le « shrink » de base de données est à bannir ? Démonstration…

Le but de ce billet est de montrer pourquoi le shrink (réduction physique de la taille d’une base de données, ici) est une opération néfaste pour un index. Pour comprendre comment fonctionne le shrink reférez-vous au billet [SQL Server] Gestion de fichiers de données: contrôle de la croissance d’un fichier de données.

  • Créons une table MaTable1 au sein d’une base de données MaTable :
USE MaBase
GO
–- Création d'une table de test MaTable1
CREATE TABLE MaTable1 (MaTable1_ID INT IDENTITY,MaTable1_Desc CHAR(8000))
GO
  • Insérons 3000 enregistrements au sein de MaTable1 :
USE MaBase
GO
–- Insertion de 3000 enregistrements
INSERT INTO MaTable1 VALUES ('test')
GO 3000
  • Créons une table MaTable2 et créons un index en cluster au sein de la table :
USE MaBase
GO
–- Création d'une table de test MaTable
CREATE TABLE MaTable2(MaTable2_ID INT IDENTITY,MaTable2_Desc CHAR(8000))
GO
CREATE CLUSTERED INDEX IDX_MaTable2_ID ON dbo.MaTable2(MaTable2_ID)
GO
  • Insérons 3000 enregistrements au sein de MaTable2 :
USE MaBase
GO
–- Insertion de 3000 enregistrements
INSERT INTO MaTable2 VALUES ('test')
GO 3000
  • Jetons un coup d’œil à l’état de santé de l’index[1] de MaTable2 :
SELECT avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID('MaBase'),OBJECT_ID('MaTable2'), 1,NULL, 'LIMITED')
GO

Comme on peut le noter ci-dessous, le taux de fragmentation logique (pages désordonnées au sein d’un index) de l’index est proche de 0%. :


  • Supprimons la table MaTable1 afin de libérer de l’espace en début de fichier[2] :
USE MaBase
GO
DROP TABLE MaTable1
GO
  • Effectuons une réduction physique (shrink) du fichier de données MaBase_Data auque appartient la table précédemment créée :
DBCC SHRINKDATABASE(MaBase)
–- Ou plus conventionnellement DBCC SHRINKFILE (MaBase_Data),
–- MaBase_Data étant notre seul MDF
–- sachant que c'est là que se situe notre table MaTable
GO

  • Observons l’état de santé de l’index de la table MaTable2 post-shrink en reprenant la requête du point 5) :


Le résultat ci-dessus est sans appel : le shrink a fortement fragmenté l’index. Et c’est un euphémisme…

La moralité dans l’histoire est la suivante : à moins d’avoir une excuse exceptionnellisime, n’effectuez jamais de shrink de bases de données et encore moins d’auto-shrink pour ne pas impacter les performances de votre base de données.



[1]Si vos tests se font sous SQL Server 2000, vous pouvez utiliser DBCC SHOWCONTIG (i.e., DBCC SHOWCONTIG (MaTable2, 1) WITH FAST;) et surveiller la valeur de Logical Scan Fragmentation.

[2]On rappelle que le shrink est une opération qui déplace les pages de données de la fin du fichier vers le début du fichier tant qu’il y a de la place.

Publicités

2 commentaires sur “[SQL Server] Pourquoi le « shrink » de base de données est à bannir ? Démonstration…

  1. Tony dit :

    Cet article est instructif pour 2 raisons, j’apprend ce qu’est le « shrink » et j’apprend qu’il ne faut pas l’utiliser. Exemple très représentatif, difficile de contester de tels faits. Bref, merci pour l’article.

  2. drop-ship dit :

    Bonjour ! Que de souvenirs….Le bon vieux temps des pur site

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s