70
Chapitre 4. Optimisation des objets et de la structure de la base de données
Pour désactiver cette option, il suffit de fixer la taille à 0, ou 'OFF' (attention,
cela force la conversion de tous les BLOB existants, ce qui peut prendre un certain
temps selon la cardinalité de la table).
Cette opération n’étant utile que pour les colonnes de type (N)TEXT et IMAGE,
nous ne nous y attarderons pas. Utilisez les nouveaux types. Ceux-ci gèrent automatiquement l’inclusion dans la ligne lorsque le contenu peut tenir dans la page (à concurrence de 8 000 octets), en agissant alors comme un VARCHAR. S’il ne peut tenir
dans la page, il est déplacé dans une structure LOB, et agit donc comme un objet
large. Le terme « MAX » permet d’assurer l’évolution vers des tailles de LOB supérieures dans les versions futures de SQL Server sans modifier la définition des tables. Ce
fonctionnement représente le meilleur des deux mondes.
Une autre option de table gère le comportement des valeurs larges ((N)VARCHAR(MAX), VARBINARY(MAX) et XML) :
EXEC sp_tableoption nomtable 'large value types out of row',
['ON'|'OFF'];
• ON (1) – Le contenu est toujours stocké dans une structure LOB, la page ne
contient que le pointeur à 16 octets.
• OFF (0) – Le contenu est stocké dans la page s’il peut y tenir.
Il s’agit donc du raisonnement inverse de 'text in row'. ON veut dire ici que le
LOB est toujours extérieur à la page. La valeur par défaut est OFF. Vous pouvez inspecter les valeurs des deux options pour vos tables, avec la vue système sys.tables :
SELECT SCHEMA_NAME(schema_id) + '.' + Name as tbl,
text_in_row_limit,
large_value_types_out_of_row
FROM sys.tables
ORDER BY tbl;
Il peut être intéressant de fixer 'large value types out of row' à ON, au cas par
cas. Si une table comporte une colonne de valeur large (un XML par exemple) qui
stocke des structures qui ne sont que très peu retournées dans les requêtes d’extraction, activer cette option permet de conserver des pages plus compactes, et donc
d’optimiser la plupart des lectures qui ne demandent pas le LOB.
Lorsque l’option 'large value types out of row' est changée, dans un sens ou
dans l’autre, les lignes existantes ne sont pas modifiées. Elles le seront au fur et à
mesure des UPDATE.
Dépassement de ligne
Dans le chapitre concernant la structure du fichier de données, nous avons dit
qu’une ligne ne pouvait dépasser 8 060 octets, soit l’espace disponible dans une page
de données de 8 Ko. Nous venons de voir que le stockage des LOB permet de
s’affranchir de cette limite. Ce stockage comporte toutefois des inconvénients : la
structure en B-Tree de gestion des LOB est un peu lourde lorsqu’il s’agit de gérer des
valeurs qui vont jusqu’à 8 Ko, mais qui ne peuvent tenir dans la page à cause de la
Chapitre 4. Optimisation des objets et de la structure de la base de données
Pour désactiver cette option, il suffit de fixer la taille à 0, ou 'OFF' (attention,
cela force la conversion de tous les BLOB existants, ce qui peut prendre un certain
temps selon la cardinalité de la table).
Cette opération n’étant utile que pour les colonnes de type (N)TEXT et IMAGE,
nous ne nous y attarderons pas. Utilisez les nouveaux types. Ceux-ci gèrent automatiquement l’inclusion dans la ligne lorsque le contenu peut tenir dans la page (à concurrence de 8 000 octets), en agissant alors comme un VARCHAR. S’il ne peut tenir
dans la page, il est déplacé dans une structure LOB, et agit donc comme un objet
large. Le terme « MAX » permet d’assurer l’évolution vers des tailles de LOB supérieures dans les versions futures de SQL Server sans modifier la définition des tables. Ce
fonctionnement représente le meilleur des deux mondes.
Une autre option de table gère le comportement des valeurs larges ((N)VARCHAR(MAX), VARBINARY(MAX) et XML) :
EXEC sp_tableoption nomtable 'large value types out of row',
['ON'|'OFF'];
• ON (1) – Le contenu est toujours stocké dans une structure LOB, la page ne
contient que le pointeur à 16 octets.
• OFF (0) – Le contenu est stocké dans la page s’il peut y tenir.
Il s’agit donc du raisonnement inverse de 'text in row'. ON veut dire ici que le
LOB est toujours extérieur à la page. La valeur par défaut est OFF. Vous pouvez inspecter les valeurs des deux options pour vos tables, avec la vue système sys.tables :
SELECT SCHEMA_NAME(schema_id) + '.' + Name as tbl,
text_in_row_limit,
large_value_types_out_of_row
FROM sys.tables
ORDER BY tbl;
Il peut être intéressant de fixer 'large value types out of row' à ON, au cas par
cas. Si une table comporte une colonne de valeur large (un XML par exemple) qui
stocke des structures qui ne sont que très peu retournées dans les requêtes d’extraction, activer cette option permet de conserver des pages plus compactes, et donc
d’optimiser la plupart des lectures qui ne demandent pas le LOB.
Lorsque l’option 'large value types out of row' est changée, dans un sens ou
dans l’autre, les lignes existantes ne sont pas modifiées. Elles le seront au fur et à
mesure des UPDATE.
Dépassement de ligne
Dans le chapitre concernant la structure du fichier de données, nous avons dit
qu’une ligne ne pouvait dépasser 8 060 octets, soit l’espace disponible dans une page
de données de 8 Ko. Nous venons de voir que le stockage des LOB permet de
s’affranchir de cette limite. Ce stockage comporte toutefois des inconvénients : la
structure en B-Tree de gestion des LOB est un peu lourde lorsqu’il s’agit de gérer des
valeurs qui vont jusqu’à 8 Ko, mais qui ne peuvent tenir dans la page à cause de la