SQL Server stocke les pages des fichier de données (tables, index, blobs…) dans des blocs de 8 pages appelés « extents » (extensions en français). Autant une page ne peut appartenir qu’à un seul objet, autant une extension peut comporter des pages de différents objets. Est-il possible de force la mise en place d’extension ne contenant qu’un seul et même type d’objet ? La réponse est oui, mais n’est pas sans conséquences !

Les fichiers de données des bases MS SQL Server, sont constitués de pages de 8 Ko organisées par blocs contigus de 8 appelées extensions. A l’intérieur d’une page ne figure que les information d’un seul et même objet (ligne de table, ligne d’index ou blob). En revanche et par défaut, une extensions peut contenir des pages abritant différents objets. On appelle cela une extension mixte (mixed extent). Bien entendu au sein d’une même base, il est possible que certaines extension soient uniformes.
Dans certains cas il y aurait intérêt à forcer le moteur de stockage à utiliser uniquement des extensions uniformes. Ces cas sont rares mais intéressants :

• le cas de la VLDB ayant essentiellement de très grosse table;
• le cas de tempdb si l’on a pensé à la répartir sur autant de gros fichier qu’il y a de CPU.

Pour ce faire, il faut utiliser le drapeau de trace 1118.

Voici un petit script qui montre l’effet de la mise en place de ce paramétrage :

CREATE DATABASE DB_TEST_EXTENT; 

GO 

 

USE DB_TEST_EXTENT; 

GO 

 

CREATE TABLE dbo.T_LONGUE_LIGNE_1_LGL 

(LGL_ID    int identity, 

 LGL_DATA  char(8000) default 'blabla'); 

GO 

 

WHILE NOT EXISTS(SELECT *  

                 FROM   dbo.T_LONGUE_LIGNE_1_LGL  

                 WHERE  LGL_ID = 8) 

    INSERT INTO dbo.T_LONGUE_LIGNE_1_LGL  DEFAULT VALUES; 

GO 

 

DBCC TRACEON(1118); 

GO 

 

CREATE TABLE dbo.T_LONGUE_LIGNE_2_LGL 

(LGL_ID    int identity, 

 LGL_DATA  char(8000) default 'blabla'); 

GO 

 

WHILE NOT EXISTS(SELECT *  

                 FROM   dbo.T_LONGUE_LIGNE_2_LGL  

                 WHERE  LGL_ID = 8) 

    INSERT INTO dbo.T_LONGUE_LIGNE_2_LGL  DEFAULT VALUES; 

GO 

 

DBCC EXTENTINFO(DB_TEST_EXTENT, 'dbo.T_LONGUE_LIGNE_1_LGL', -1); 

DBCC EXTENTINFO(DB_TEST_EXTENT, 'dbo.T_LONGUE_LIGNE_2_LGL', -1);

Et le résultat :

file_id     page_id     pg_alloc    ext_size    object_id   index_id    partition_number partition_id         iam_chain_type       pfs_bytes 

----------- ----------- ----------- ----------- ----------- ----------- ---------------- -------------------- -------------------- ------------------ 

1           21          1           1           2105058535  0           1                72057594038779904    In-row data          0x6400000000000000 

1           55          1           1           2105058535  0           1                72057594038779904    In-row data          0x6400000000000000 

1           77          1           1           2105058535  0           1                72057594038779904    In-row data          0x6400000000000000 

1           78          1           1           2105058535  0           1                72057594038779904    In-row data          0x6400000000000000 

1           79          1           1           2105058535  0           1                72057594038779904    In-row data          0x6400000000000000 

1           80          1           1           2105058535  0           1                72057594038779904    In-row data          0x6400000000000000 

1           89          1           1           2105058535  0           1                72057594038779904    In-row data          0x6400000000000000 

1           90          1           1           2105058535  0           1                72057594038779904    In-row data          0x6400000000000000 

 

 

file_id     page_id     pg_alloc    ext_size    object_id   index_id    partition_number partition_id         iam_chain_type       pfs_bytes 

----------- ----------- ----------- ----------- ----------- ----------- ---------------- -------------------- -------------------- ------------------ 

1           176         8           8           2137058649  0           1                72057594038845440    In-row data          0x4444444444444444

Comme on le voit, ces deux tables contiennent les mêmes données. L’une à donnée lieu à la description de 8 pages dans la même extension, l’autre la description globale des 8 pages dans l’extension uniforme.

Attention à bien tester les effets de ce paramétrage, car le remède peut s’avérer dans certaines circonstances, pire que le mal.

Réglage du paramètre

En exécutant :

DBCC TRACEON(1118);
Vous forcez la session en cours à générer des extensions uniforme. Cela peut être intéressant par exemple si vous voulez insérer un lot important de lignes dans une table sans générer d’extensions mixtes. Par exemple lors de la création d’une base de données, pour l’alimentation du référentiel.

DBCC TRACEON(1118, -1);
Vous forcez le moteur pour toutes les sessions à venir à générer des extensions uniforme.

Vous revenez au comportement normal du moteur de stockage.

—
Frédéric BROUARD, Spécialiste modélisation, bases de données, optimisation, langage SQL.
Le site sur le langage SQL et les S.G.B.D. relationnels : http://sqlpro.developpez.com/
Expert SQL Server http://www.sqlspot.com : audit, optimisation, tuning, formation
* * * * * Enseignant au CNAM PACA et à l’ISEN à Toulon * * * * *