74
Chapitre 4. Optimisation des objets et de la structure de la base de données
La longueur correspond à nos 1 000 octets, plus quelques octets supplémentaires.
CQFD.
La fonctionnalité de row overflow est bien pratique, mais qu’en est-il des
performances ? Elles sont nécessairement moins bonnes. À chaque lecture de page,
le moteur de stockage doit aller chercher ailleurs d’autres pages pour lire les données
si des colonnes en row overflow sont demandées, ce qui provoque des lectures aléatoires (random IO) supplémentaires. De plus, comme les pages de row overflow contiennent la totalité de la valeur d’une colonne, cela génère souvent de la fragmentation
interne : les pages ne sont pas remplies de façon optimale. En un mot, réservez le row
overflow à des cas limités, pour gérer un éventuel dépassement de page, pour des
colonnes qui sont en général compactes mais peuvent de temps en temps contenir
une chaîne plus longue.
Les statistiques de pages lues, retournées par SET STATISTICS IO ON, et la trace
SQL, affichent différemment les pages lues. La trace SQL montre, dans la colonne
reads, le nombre total de pages lues (In row, Row overflow et LOB Data), y compris les pages d’IAM. Les statistiques IO renvoyées dans la session séparent les pages In row des pages Row overflow et LOB. Voici un exemple de statistiques
retournées pour une table contenant deux pages In row, deux pages de row overflow, et trois pages de LOB (VARCHAR(MAX)), pages IAM comprises :
Table 'bigcontact'. Scan count 1, logical reads 2, physical reads 0, readahead reads 0, lob logical reads 5, lob physical reads 0, lob read-ahead
reads 0.
L’utilisation de SET STATISTICS IO ON permet donc d’obtenir des résultats plus
précis.
FILESTREAM
Le type FILESTREAM est nouveau dans SQL Server 2008. C’est une implémentation
du type de données DATALINK de la norme SQL. SQL Server n’est pas prévu pour
gérer efficacement le stockage et la manipulation d’objets larges, et maintenir des
LOB en grande quantité pose des problèmes de performances. Souvent, pour éviter
ces problèmes, nous stockons les fichiers sur le système de fichiers, et référençons le
chemin dans une colonne VARCHAR. Le problème avec cette approche est qu’il faut en
général ouvrir un partage sur le réseau, maintenir ces fichiers d’une façon ou d’une
autre, et gérer les sauvegardes de ces fichiers en plus des sauvegardes SQL.
En gérant des types FILESTREAM, vous créez un groupe de fichiers particulier pour
les stocker, et ce groupe de fichiers utilise un stockage NTFS, plus adapté, plutôt que
le stockage traditionnel des fichiers de données. La cohérence transactionnelle sur
ces fichiers est aussi assurée.
Pour utiliser FILESTREAM, vous devez d’abord activer la fonctionnalité, à l’aide de
sp_configure :
Chapitre 4. Optimisation des objets et de la structure de la base de données
La longueur correspond à nos 1 000 octets, plus quelques octets supplémentaires.
CQFD.
La fonctionnalité de row overflow est bien pratique, mais qu’en est-il des
performances ? Elles sont nécessairement moins bonnes. À chaque lecture de page,
le moteur de stockage doit aller chercher ailleurs d’autres pages pour lire les données
si des colonnes en row overflow sont demandées, ce qui provoque des lectures aléatoires (random IO) supplémentaires. De plus, comme les pages de row overflow contiennent la totalité de la valeur d’une colonne, cela génère souvent de la fragmentation
interne : les pages ne sont pas remplies de façon optimale. En un mot, réservez le row
overflow à des cas limités, pour gérer un éventuel dépassement de page, pour des
colonnes qui sont en général compactes mais peuvent de temps en temps contenir
une chaîne plus longue.
Les statistiques de pages lues, retournées par SET STATISTICS IO ON, et la trace
SQL, affichent différemment les pages lues. La trace SQL montre, dans la colonne
reads, le nombre total de pages lues (In row, Row overflow et LOB Data), y compris les pages d’IAM. Les statistiques IO renvoyées dans la session séparent les pages In row des pages Row overflow et LOB. Voici un exemple de statistiques
retournées pour une table contenant deux pages In row, deux pages de row overflow, et trois pages de LOB (VARCHAR(MAX)), pages IAM comprises :
Table 'bigcontact'. Scan count 1, logical reads 2, physical reads 0, readahead reads 0, lob logical reads 5, lob physical reads 0, lob read-ahead
reads 0.
L’utilisation de SET STATISTICS IO ON permet donc d’obtenir des résultats plus
précis.
FILESTREAM
Le type FILESTREAM est nouveau dans SQL Server 2008. C’est une implémentation
du type de données DATALINK de la norme SQL. SQL Server n’est pas prévu pour
gérer efficacement le stockage et la manipulation d’objets larges, et maintenir des
LOB en grande quantité pose des problèmes de performances. Souvent, pour éviter
ces problèmes, nous stockons les fichiers sur le système de fichiers, et référençons le
chemin dans une colonne VARCHAR. Le problème avec cette approche est qu’il faut en
général ouvrir un partage sur le réseau, maintenir ces fichiers d’une façon ou d’une
autre, et gérer les sauvegardes de ces fichiers en plus des sauvegardes SQL.
En gérant des types FILESTREAM, vous créez un groupe de fichiers particulier pour
les stocker, et ce groupe de fichiers utilise un stockage NTFS, plus adapté, plutôt que
le stockage traditionnel des fichiers de données. La cohérence transactionnelle sur
ces fichiers est aussi assurée.
Pour utiliser FILESTREAM, vous devez d’abord activer la fonctionnalité, à l’aide de
sp_configure :