Suite à une discussion sur le forum je me permets une billet sur comment déplacer les données et index d’une base de données de façon équitable dans plusieurs fichiers d’un même groupe de fichiers. Cette opération peut s’avérer utile dans bien des cas. Un exemple simple est l’ajout d’un axe physique disque sur lequel on veut ajouter un fichier de données et répartir équitablement celles-ci pour bénéficier par la suite de l’algorithme de réparation de SQL Server.

La question est comment réaliser cette opération ?

La première solution à laquelle on peut penser en premier est d’ajouter un ou plusieurs fichiers dans groupe de fichiers et de reconstruire les index. On peut supposer qu’à ce moment le moteur va répartir les données de manière équitable sur tous les fichiers du groupe. Vérifions le …

Pour cela nous prendrons une base de données avec 2 groupes de fichiers :

• Le groupe PRIMARY avec un seul fichiers de données et qui accueillera les tables
• Le groupe INDEX avec un seul fichiers de données et qui accueillera les index non cluster

CREATE DATABASE [TEST_MOVE_DATA] ON  PRIMARY
( NAME = N’TEST_MOVE_DATA’,
  FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TEST_MOVE_DATA.mdf’ ,
  SIZE = 1048576KB ,
  FILEGROWTH = 1024KB ),
FILEGROUP [INDEX]
( NAME = N’TEST_MOVE_DATA_index’,
  FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TEST_MOVE_DATA_index.ndf’ ,
  SIZE = 1048576KB ,
  FILEGROWTH = 1024KB )
LOG ON
( NAME = N’TEST_MOVE_DATA_log’,
  FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TEST_MOVE_DATA_log.ldf’ ,
  SIZE = 20160KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [TEST_MOVE_DATA] SET RECOVERY SIMPLE;
GO

Nous créerons ensuite une table ayant les caractéristiques suivantes …

USE TEST_MOVE_DATA
GO

CREATE TABLE test
(
col1 INT IDENTITY(1,1) PRIMARY KEY,
col2 CHAR(200),
col3 VARCHAR(200)
);

… avec le jeu de données suivant

INSERT test (col2, col3) VALUES (REPLICATE(‘T’, 200), REPLICATE(‘X’, 200));
GO 2000000

… et un index cluster qui résidera sur le groupe de fichiers [INDEX]

CREATE NONCLUSTERED INDEX IDX_TEST
ON test
(
col2, col3
) ON [INDEX];

 Commençons par ajouter un deuxième fichier au groupe de fichiers [PRIMARY] ayant la même taille que le fichier existant :

ALTER DATABASE TEST_MOVE_DATA
ADD FILE ( NAME = N’TEST_MOVE_DATA_2′,
           FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TEST_MOVE_DATA_2.ndf’ ,
           SIZE = 1048576KB ,
           MAXSIZE = UNLIMITED)
TO FILEGROUP [PRIMARY];

 Le script suivant permet de voir l’espace occupé par les données et l’espace libre restant pour chaque fichier de données de la base concernée :

SELECT
    mf.file_id,
    mf.type_desc,
    mf.name AS [filename],
    fg.name AS groupename,
    mf.physical_name,
    mf.size * 8 AS size_KB,
    FILEPROPERTY(mf.name, ‘spaceused’) * 8 AS free_KB,
    (mf.size – FILEPROPERTY(mf.name, ‘spaceused’)) * 8  AS used_KB
FROM sys.master_files AS mf
INNER JOIN sys.filegroups AS fg
ON mf.data_space_id = fg.data_space_id
WHERE mf.database_id = DB_ID(‘TEST_MOVE_DATA’)
AND mf.type_desc = ‘ROWS’
ORDER BY fg.name, mf.name;

Le résultat est le suivant :

Le nouveau fichier TEST_MOVE_DATA_2 n’est pour le moment pas utilisé. Reconstruisons maintenant l’index cluster.

EXEC sp_helpindex ‘test';
GO
– Index name : PK__test__357D0D3E7F60ED59

ALTER INDEX PK__test__357D0D3E7F60ED59
ON test REBUILD;

Vérifions à nouveau l’espace utilisé dans les deux fichiers du groupe PRIMARY :

Le premier fichier du groupe PRIMARY possède 33% des données alors que le deuxième en possède 77%. La reconstruction d’index ne permet pas dans ce premier cas de répartir les données uniformément dans les 2 fichiers. Il faut savoir que le premier fichier du groupe PRIMARY contient les données des tables systèmes et celles-ci ne peuvent pas être déplacés. En ajoutant deux autres fichiers au groupe PRIMARY et en reconstruisant l’index on peut remarquer que les données sont répartis uniformément sur ces derniers.

 

Essayons la même chose avec le groupe INDEX et reconstruisons l’index non cluster :

 

Les données sont relativement bien réparties sur l’ensemble des fichiers

Une deuxième solution qui a été également évoquée consiste à ajouter deux fichiers supplémentaires dans un même groupe et de vider les données du premier fichier en espérant que celles-ci soient répartis équitablement dans ces derniers. Seulement il y a deux inconvénients majeurs dans cette méthode : si le groupe de fichiers concerné est le groupe PRIMARY une erreur sera générée si l’on essaie de vider le premier fichier. En effet les données des objets systèmes ne peuvent pas être déplacés. De plus une opération de ce type risque d’augmenter la fragmentation des index dans le cas où elle ne concerne pas le premier fichier du groupe PRIMARY .

Nous pouvons le vérifier. Récupérons d’abord l’état de fragmentation de l’index non cluster avant cette opération.

SELECT
    DB_NAME(database_id) AS database_name,
    index_id,
    index_type_desc,
    avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(‘test’), NULL, NULL, DEFAULT);

qui donne le résultat suivant :

Ajoutons ensuite deux fichiers au groupe INDEX :

ALTER DATABASE TEST_MOVE_DATA
ADD FILE ( NAME = N’TEST_MOVE_DATA_index_2′,
           FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TEST_MOVE_DATA_index_2.ndf’ ,
           SIZE = 1048576KB ,
           MAXSIZE = UNLIMITED)
TO FILEGROUP [INDEX]

ALTER DATABASE TEST_MOVE_DATA
ADD FILE ( NAME = N’TEST_MOVE_DATA_index_3′,
           FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TEST_MOVE_DATA_index_3.ndf’ ,
           SIZE = 1048576KB ,
           MAXSIZE = UNLIMITED)
TO FILEGROUP [INDEX]

et vidons le 1er fichier à l’aide de la commande SHRINK et de l’option EMPTYFILE

DBCC SHRINKFILE(TEST_MOVE_DATA_2, EMPTYFILE);

On peut constater que la répartition n’est pas uniforme …

… et que la fragmentation est importante dans le cas présent

Une autre solution consiste à créer un groupe de fichiers à part et de transvaser les données d’un groupe

ALTER DATABASE TEST_MOVE_DATA
ADD FILEGROUP DATA2

ALTER DATABASE TEST_MOVE_DATA
ADD FILE ( NAME = N’TEST_MOVE_DATA2′,
           FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TEST_MOVE_DATA2.ndf’ ,
           SIZE = 1048576KB ,
           MAXSIZE = UNLIMITED)
TO FILEGROUP [DATA2]

ALTER DATABASE TEST_MOVE_DATA
ADD FILE ( NAME = N’TEST_MOVE_DATA3′,
           FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TEST_MOVE_DATA3.ndf’ ,
           SIZE = 1048576KB ,
           MAXSIZE = UNLIMITED)
TO FILEGROUP [DATA2]

ALTER DATABASE TEST_MOVE_DATA
ADD FILEGROUP INDEX2

ALTER DATABASE TEST_MOVE_DATA
ADD FILE ( NAME = N’TEST_MOVE_INDEX2′,
           FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TEST_MOVE_INDEX2.ndf’ ,
           SIZE = 1048576KB ,
           MAXSIZE = UNLIMITED)
TO FILEGROUP [INDEX2]

ALTER DATABASE TEST_MOVE_DATA
ADD FILE ( NAME = N’TEST_MOVE_INDEX3′,
           FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TEST_MOVE_INDEX3.ndf’ ,
           SIZE = 1048576KB ,
           MAXSIZE = UNLIMITED)
TO FILEGROUP [INDEX2]

L’opération consiste à reconstruire les index dans les nouveaux groupes de fichiers. Pour cela il faut utiliser cette fois-ci l’instruction CREATE INDEX et l’option DROP_EXISTING. C’est la seule instruction qui permet de cibler un groupe de fichiers.

CREATE UNIQUE CLUSTERED INDEX PK__test__357D0D3E7F60ED59
ON test
(
col1
)
WITH (DROP_EXISTING = ON)
ON [DATA2];

CREATE NONCLUSTERED INDEX IDX_TEST
ON test
(
col2, col3
)
WITH (DROP_EXISTING = ON)
ON [INDEX2];

Voyons comment les données ont été réparties dans les nouveaux groupes de fichiers :

Enfin il reste la méthode de créer une base de données à côté et d’y transférer les données … sur ce coup je vous laisse tenter l’aventure

Résumons :

• Peu importe la méthode utilisée, pour répartir les données de façon équitable il faut avant tout les créer avec la même taille pour que l’algorithme de répartition du moteur puisse opérer de manière optimale.
• Les données du premier fichier du groupe PRIMARY ne peuvent être que partiellement déplacées du fait de l’existence des objets systèmes
• Le déplacement des données via l’instruction SHRINKFILE est à proscrire dans ce cas car elle ne préserve pas l’état de fragmentation des index d’une part et ne répartit uniformément les données d’autre part

Bon déplacement de données !!

David BARBARIN (Mikedavem)
Elève ingénieur CNAM Lyon