195
6.4 Statistiques
modifications de données. Comme la création, elle peut se déclencher automatiquement ou manuellement.
Automatiquement avec l’option de base de données AUTO UPDATE STATISTICS,
qu’il est vivement recommandé de laisser à sa valeur par défaut ON.
SELECT DATABASEPROPERTYEX('IsAutoUpdateStatistics')
-- pour consulter la valeur actuelle
ALTER DATABASE AdventureWorks SET AUTO_UPDATE_STATISTICS [ON|OFF]
-- pour modifier la valeur
En SQL Server 2000, la mise à jour automatique des statistiques était déclenchée
par un nombre de modifications de lignes de la table. Chaque fois qu’un INSERT,
DELETE ou une modification de colonne indexée était réalisé, la valeur de la colonne
rowmodctr de la table sysindexes était incrémentée. Pour que la mise à jour des statistiques soit décidée, il fallait que la valeur de rowmodctr soit au moins 500.
Vous trouverez une explication plus détaillée de ce mécanisme dans le document
de la base de connaissances Microsoft 195565 (http://support.microsoft.com/kb/
195565).
Avec SQL Server 2005, ce seuil (threshold) est géré plus finement. La colonne
rowmodctr est toujours maintenue mais on ne peut plus considérer sa valeur comme
exacte. En effet, SQL Server maintient des compteurs de modifications pour chaque
colonne, dans un enregistrement nommé colmodctr présent pour chaque colonne de
statistique. Cependant cette colonne n’est pas visible. Vous pouvez toujours vous
baser sur la valeur de rowmodctr pour déterminer si vous pouvez mettre à jour les statistiques sur une colonne, ou si la mise à jour automatique va se déclencher bientôt,
car les développeurs ont fait en sorte que sa valeur soit « raisonnablement » proche
de ce qu’elle donnait dans les versions suivantes.
Vous pouvez activer ou désactiver la mise à jour automatique des statistiques sur
un index, une table ou des statistiques, en utilisant la procédure stockée
sp_autostats :
sp_autostats [@tblname =] 'table_name'
[, [@flagc =] 'stats_flag']
[, [@indname =] 'index_name']
Le paramètre @flagc peut être indiqué à ON pour activer, OFF pour désactiver, NULL
pour afficher l’état actuel.
sp_updatestats met à jour les statistiques de toutes les tables de la base courante,
mais seulement celles qui ont dépassé le seuil d’obsolescence déterminé par rowmodctr (contrairement à SQL Server 2000 qui mettait à jour toutes les statistiques).
Cela vous permet de lancer une mise à jour des statistiques de façon contrôlée,
durant des périodes creuses, afin d’éviter d’éventuels problèmes de performances dus
à la mise à jour automatique, dont nous allons parler dans la section suivante.
6.4 Statistiques
modifications de données. Comme la création, elle peut se déclencher automatiquement ou manuellement.
Automatiquement avec l’option de base de données AUTO UPDATE STATISTICS,
qu’il est vivement recommandé de laisser à sa valeur par défaut ON.
SELECT DATABASEPROPERTYEX('IsAutoUpdateStatistics')
-- pour consulter la valeur actuelle
ALTER DATABASE AdventureWorks SET AUTO_UPDATE_STATISTICS [ON|OFF]
-- pour modifier la valeur
En SQL Server 2000, la mise à jour automatique des statistiques était déclenchée
par un nombre de modifications de lignes de la table. Chaque fois qu’un INSERT,
DELETE ou une modification de colonne indexée était réalisé, la valeur de la colonne
rowmodctr de la table sysindexes était incrémentée. Pour que la mise à jour des statistiques soit décidée, il fallait que la valeur de rowmodctr soit au moins 500.
Vous trouverez une explication plus détaillée de ce mécanisme dans le document
de la base de connaissances Microsoft 195565 (http://support.microsoft.com/kb/
195565).
Avec SQL Server 2005, ce seuil (threshold) est géré plus finement. La colonne
rowmodctr est toujours maintenue mais on ne peut plus considérer sa valeur comme
exacte. En effet, SQL Server maintient des compteurs de modifications pour chaque
colonne, dans un enregistrement nommé colmodctr présent pour chaque colonne de
statistique. Cependant cette colonne n’est pas visible. Vous pouvez toujours vous
baser sur la valeur de rowmodctr pour déterminer si vous pouvez mettre à jour les statistiques sur une colonne, ou si la mise à jour automatique va se déclencher bientôt,
car les développeurs ont fait en sorte que sa valeur soit « raisonnablement » proche
de ce qu’elle donnait dans les versions suivantes.
Vous pouvez activer ou désactiver la mise à jour automatique des statistiques sur
un index, une table ou des statistiques, en utilisant la procédure stockée
sp_autostats :
sp_autostats [@tblname =] 'table_name'
[, [@flagc =] 'stats_flag']
[, [@indname =] 'index_name']
Le paramètre @flagc peut être indiqué à ON pour activer, OFF pour désactiver, NULL
pour afficher l’état actuel.
sp_updatestats met à jour les statistiques de toutes les tables de la base courante,
mais seulement celles qui ont dépassé le seuil d’obsolescence déterminé par rowmodctr (contrairement à SQL Server 2000 qui mettait à jour toutes les statistiques).
Cela vous permet de lancer une mise à jour des statistiques de façon contrôlée,
durant des périodes creuses, afin d’éviter d’éventuels problèmes de performances dus
à la mise à jour automatique, dont nous allons parler dans la section suivante.