Statistiques d'index

Mis à jour : 12 décembre 2006

SQL Server 2005 permet de créer des informations statistiques concernant la distribution des valeurs dans une colonne. L'optimiseur de requête utilise ces informations statistiques pour déterminer le plan de requête optimal en estimant le coût d'utilisation d'un index pour évaluer la requête.

Lors de la création des statistiques, le moteur de base de données trie les valeurs des colonnes sur lesquelles sont générées les statistiques et crée un histogramme basé sur 200 de ces valeurs au maximum, séparées par des intervalles. L'histogramme spécifie le nombre de lignes qui correspondent exactement à la valeur de chaque intervalle, le nombre de lignes qui appartiennent à un intervalle et un calcul de la densité des valeurs ou de l'incidence des valeurs dupliquées dans un intervalle.

SQL Server 2005 introduit des informations supplémentaires recueillies par les statistiques créées sur les colonnes char, varchar, varchar(max), nchar, nvarchar, nvarchar(max), text et ntext. Ces informations, nommées résumé de chaîne, aident l'optimiseur de requête à estimer la sélectivité des prédicats de requête sur les motifs de chaîne. Les résumés de chaîne conduisent à une estimation plus précise des tailles des jeux de résultats et souvent à de meilleurs plans de requête lorsque des conditions LIKE sont présentes dans une requête. Il s'agit notamment des conditions telles que WHERE ProductName LIKE '%Bike' et WHERE Name LIKE '[CS]heryl'.

Remarque :

Les informations des résumés de chaîne ne sont pas conservées si le résumé d'un exemple de colonne est d'une taille supérieure à ce que le moteur de base de données peut conserver. Par exemple, le résumé de chaîne ne sera pas conservé sur les statistiques créées à l'aide de WITH FULLSCAN sur une colonne unique varchar(80) contenant 80 caractères par chaîne, pratiquement dépourvue de similarité entre les chaînes, sur une table contenant 85 000 lignes. Pour déterminer si un résumé de chaîne est conservé pour un objet de statistiques particulier, utilisez DBCC SHOW_STATISTICS (Transact-SQL).

Mode de fonctionnement des statistiques automatiques

Lors de la création d'un index, l'optimiseur de requête stocke automatiquement les informations statistiques concernant les colonnes indexées. D'autre part, si l'option de base de données AUTO_CREATE_STATISTICS est activée (ON), ce qui est le cas par défaut, le moteur de base de données crée automatiquement des statistiques pour les colonnes sans index utilisées dans un prédicat.

Si les données d'une colonne changent, les statistiques d'index et de colonne risquent de devenir obsolètes et donc d'amener l'optimiseur de requête à prendre des décisions peu judicieuses sur la manière de traiter une requête. Par exemple, si vous créez une table avec une colonne indexée et 1 000 lignes de données, toutes comportant des valeurs uniques dans la colonne indexée, l'optimiseur de requête considère que la colonne indexée constitue une « bonne » solution pour recueillir les données pour une requête. Si vous mettez à jour les données d'une colonne en créant de nombreuses valeurs dupliquées, la colonne n'est plus un candidat idéal à l'utilisation dans une requête. Cependant, l'optimiseur de requête la considère toujours comme un candidat approprié d'après les statistiques de distribution obsolètes de l'index, qui reposent sur les données d'avant la mise à jour.

Remarque :

Les statistiques obsolètes ou manquantes sont indiquées comme des avertissements (nom de la table en rouge) lorsque le plan d'exécution d'une requête est affiché sous forme graphique à l'aide de SQL Server Management Studio. Pour plus d'informations, consultez Affichage de plans d'exécution graphique (SQL Server Management Studio). D'autre part, le Générateur de profils SQL Server permet de surveiller la classe d'événements Missing Column Statistics et d'être tenu informé lorsque des statistiques manquent. Pour plus d'informations, consultez Catégorie d'événements Erreurs et avertissements (moteur de base de données).

Lorsque l'option de base de données AUTO_UPDATE_STATISTICS est activée (ON), ce qui est le cas par défaut, l'optimiseur de requête met automatiquement à jour ces informations statistiques à intervalles réguliers, à mesure que les données des tables changent. Une mise à jour des statistiques est lancée à chaque fois que les statistiques utilisées dans un plan d'exécution de requête échouent à un test de statistiques actuelles. L'échantillonnage s'effectue de façon aléatoire parmi les pages de données et provient de la table ou du plus petit index non-cluster sur les colonnes nécessaires aux statistiques. Après la lecture d'une page de données à partir du disque, toutes les lignes de la page sont utilisées pour mettre à jour les informations statistiques. Les informations statistiques sont presque toujours mises à jour lorsque 20 pour cent environ des lignes de données ont changé. Toutefois, l'optimiseur de requête vérifie qu'un nombre minimal de lignes est échantillonné. Les tables de taille inférieure à 8 mégaoctets (Mo) sont toujours analysées complètement pour recueillir les statistiques.

L'échantillonnage des données, par rapport à leur analyse complète, réduit le coût de la mise à jour statistique automatique. Dans certaines circonstances, l'échantillonnage statistique ne permet pas de caractériser les données d'une table avec précision. Vous pouvez contrôler la quantité de données échantillonnées au cours des mises à jour manuelles des statistiques, table par table, en utilisant les clauses SAMPLE et FULLSCAN de l'instruction UPDATE STATISTICS. La clause FULLSCAN spécifie que toutes les données de la table doivent être analysées pour élaborer des statistiques, tandis que la clause SAMPLE permet de spécifier le pourcentage ou le nombre de lignes à échantillonner.

Mises à jour asynchrones de statistiques

Une requête qui lance une mise à jour de statistiques obsolètes doit attendre que ces dernières soient mises à jour avant de compiler et de retourner un jeu de résultats. Cette opération peut provoquer des temps de réponse de requête imprévisibles, ainsi que l'échec des applications dont les délais d'expiration sont stricts.

Dans SQL Server 2005, l'option de base de données AUTO_UPDATE_STATISTICS_ASYNC permet la mise à jour asynchrone des statistiques. Lorsque cette option est définie sur ON (activée), les requêtes n'attendent pas que les statistiques soient mises à jour avant de les compiler. À la place, les statistiques obsolètes sont mises en file d'attente pour être mises à jour par un thread de travail dans un processus en arrière-plan. La requête et les éventuelles autres requêtes simultanées compilent immédiatement en utilisant les statistiques obsolètes existantes. Comme il n'y a pas de délai pour les statistiques mises à jour, les temps de réponse de requête sont prévisibles ; toutefois, les statistiques obsolètes peuvent amener l'optimiseur de requête à choisir un plan de requête moins efficace. Les requêtes qui démarrent alors que les statistiques mises à jour sont prêtes utiliseront ces statistiques. Ceci peut provoquer la recompilation des plans mis en cache qui dépendent de la version de statistiques précédente. La mise à jour asynchrone des statistiques ne peut pas avoir lieu si des instructions DDL (Data Definition Language), telles que CREATE, ALTER et DROP, apparaissent dans la même transaction utilisateur explicite.

L'option AUTO_UPDATE_STATISTICS_ASYNC est définie au niveau de la base de données et détermine la méthode de mise à jour de toutes les statistiques de la base de données. Elle s'applique uniquement aux statistiques en cours de mise à jour et ne peut pas servir à créer des statistiques de manière asynchrone. La définition de cette option sur ON (activée) n'a pas d'effet si l'option AUTO_UPDATE_STATISTICS n'est pas également définie sur ON. Par défaut, l'option AUTO_UPDATE_STATISTICS_ASYNC est définie sur OFF. Pour plus d'informations sur la définition de cette option, consultez ALTER DATABASE (Transact-SQL).

Avant d'affecter la valeur SINGLE_USER à une base de données, vérifiez que l'option AUTO_UPDATE_STATISTICS_ASYNC a la valeur OFF. Si la valeur est ON, le thread d'arrière-plan utilisé pour mettre à jour les statistiques se connecte à la base de données et vous ne pourrez pas accéder à celle-ci en mode mono-utilisateur. Si l'option a la valeur ON, effectuez les tâches suivantes :

1. Affectez la valeur OFF à AUTO_UPDATE_STATISTICS_ASYNC.
2. Recherchez les travaux des statistiques asynchrones actifs en interrogeant la vue de gestion dynamique sys.dm_exec_background_job_queue.
3. S'il y a des travaux actifs, laissez ces travaux s'achever ou terminez-les manuellement à l'aide de KILL STATS JOB.

Méthodes préconisées

Vous devez envisager de définir l'option AUTO_UPDATE_STATISTICS_ASYNC sur ON lorsque les caractéristiques suivantes concernent votre application :

• L'application a connu des expirations de délai de demandes clientes causées par une ou plusieurs requêtes en attente de statistiques mises à jour.
• L'application exige des temps de réponse de requête prévisibles, même au détriment des requêtes exécutées de temps en temps dont les plans de requêtes sont moins efficaces en raison de statistiques obsolètes.

Affichage des propriétés de mise à jour asynchrone des statistiques

Pour afficher l'état activé (ON) ou désactivé (OFF) de l'option AUTO_UPDATE_STATISTICS_ASYNC, sélectionnez la colonne is_auto_update_stats_async_on dans la vue du catalogue sys.databases. Pour plus d'informations, consultez sys.databases (Transact-SQL).

Pour déterminer si les statistiques sont mises en file d'attente pour la mise à jour ou sont en cours de mise à jour, utilisez la vue de gestion dynamique sys.dm_exec_background_job_queue. Pour les statistiques, la colonne object_id1 affiche l'ID de table ou de vue et la colonne object_id2 l'ID de statistiques. Utilisez la vue de gestion dynamique sys.dm_exec_background_job_queue_stats pour afficher les statistiques consolidées de toutes les files d'attente de travail en arrière-plan, telles que le nombre de demandes de travail en attente d'exécution, le nombre de demandes ayant échoué et le temps d'exécution moyen des demandes envoyées précédemment.

Désactivation des statistiques automatiques

Vous pouvez désactiver la génération de statistiques automatiques pour une colonne ou un index particulier en utilisant les méthodes suivantes :

• la procédure stockée système sp_autostats ;
• la clause STATISTICS_NORECOMPUTE de l'instruction CREATE INDEX ;
• la clause NORECOMPUTE de l'instruction UPDATE STATISTICS ;
• la clause NORECOMPUTE de l'instruction CREATE STATISTICS ;
• l'instruction ALTER DATABASE, en désactivant (OFF) les options de base de données AUTO_CREATE_STATISTICS et AUTO_UPDATE_STATISTICS. Pour plus d'informations, consultez Définition des options de base de données.

Si vous indiquez au moteur de base de données de ne pas mettre à jour automatiquement les statistiques, il vous faudra le faire manuellement.

Remarque :
L'instruction UPDATE STATISTICS réactive la mise à jour automatique des statistiques, sauf si la clause NORECOMPUTE est spécifiée.

Création et mise à jour manuelles des statistiques

Des statistiques peuvent également être créées en une seule instruction sur toutes les colonnes appropriées de toutes les tables utilisateur de la base de données actuelle à l'aide de la procédure stockée système sp_createstats. Les statistiques peuvent être créées sur des colonnes de table ou de vue spécifiques à l'aide de l'instruction CREATE STATISTICS et mises à jour à l'aide de l'instruction UPDATE STATISTICS. Le nombre maximal de statistiques qui peuvent être créées sur une table ou une vue indépendamment d'un index est de 2 000. Toutes les colonnes ou combinaisons de colonnes pouvant prétendre à une clé d'index peuvent être soumises à des statistiques, aux exceptions suivantes près :

• Les colonnes de type objet volumineux, à l'exception de xml, peuvent être spécifiées. Les types varchar(max), nvarchar(max), varbinary(max), image, text et ntext peuvent être spécifiés.
• La taille maximale autorisée des valeurs des colonnes combinées peut dépasser la limite de 900 octets imposée à la valeur de la clé d'index.

Vous pouvez supprimer les statistiques générées pour une colonne si celles-ci ne vous intéressent plus.

La création manuelle de statistiques vous permet de créer des statistiques contenant plusieurs densités de colonnes. Il s'agit du nombre moyen de doublons pour la combinaison de colonnes. Par exemple, une requête comprend la clause WHERE a = 7 and b = 9.

La création manuelle de statistiques sur l'ensemble des deux colonnes (a, b) peut permettre au moteur de base de données de faire une meilleure estimation de la requête parce que les statistiques comprennent également le nombre moyen de valeurs distinctes dans l'ensemble des colonnes a et b.

Pour créer des statistiques sur une colonne

CREATE STATISTICS (Transact-SQL)

Pour créer des statistiques sur toutes les colonnes appropriées de l'ensemble des tables utilisateur

sp_createstats (Transact-SQL)

Pour mettre à jour manuellement les statistiques

UPDATE STATISTICS (Transact-SQL)

Pour afficher les statistiques d'une table

DBCC SHOW_STATISTICS (Transact-SQL)

Pour supprimer les statistiques d'une colonne

DROP STATISTICS (Transact-SQL)

Utilisation des statistiques après la mise à niveau d'une base de données vers SQL Server 2005

Lors de la mise à niveau d'une base de données vers SQL Server 2005 à partir d'une version antérieure de SQL Server, toutes les statistiques de la version précédente sont considérées obsolètes. Par conséquent, à la première utilisation, les statistiques pouvant prétendre à une mise à jour avec l'option de base de données AUTO_UPDATE_STATISTICS sont mises à jour à l'aide du taux d'échantillonnage par défaut. Cette fonction présente des avantages importants et n'impose en général aucune mesure particulière. Toutefois, en de rares occasions, elle peut produire des statistiques moins précises si les statistiques ont été calculées manuellement dans une version antérieure de SQL Server à l'aide de FULLSCAN ou d'un autre taux d'échantillonnage élevé, ou si la table échantillonnée est d'une taille supérieure à 8 Mo et que la distribution des données n'est pas aléatoire. En effet, une réduction du taux d'échantillonnage peut toujours se produire pour les statistiques FULLSCAN des tables de taille supérieure à 8 Mo lorsque l'option AUTO_UPDATE_STATISTICS est exécutée. La mise à jour initiale des statistiques peut simplement se produire à une heure antérieure après la mise à niveau vers la nouvelle version de SQL Server.

La mise à niveau des statistiques vers le format SQL Server 2005 présente comme avantages que les statistiques SQL Server 2005 pour un taux d'échantillonnage spécifique sont d'une meilleure qualité générale que celles de SQL Server 2000 et des versions antérieures. En outre, SQL Server 2005 crée des statistiques spéciales de résumé de chaîne pour les colonnes de caractères comme décrit précédemment. Pour plus d'informations sur les statistiques dans SQL Server 2005, consultez le site Web de Microsoft.

Méthodes préconisées

Dans la plupart des cas, il n'est pas nécessaire de prendre des mesures particulières concernant les statistiques après la mise à niveau d'une base de données. Toutefois, si votre base de données est volumineuse et exige des performances élevées, il est recommandé d'exécuter sp_updatestats (Transact-SQL) avec l'option RESAMPLE après la mise à niveau. Cette opération conserve les taux d'échantillonnage précédents et met à jour toutes les statistiques au format le plus récent. Sachez que les statistiques créées pendant la création de l'index le sont à l'aide du taux d'échantillonnage FULLSCAN. Ces statistiques, comme d'autres statistiques FULLSCAN, utilisent le taux d'échantillonnage par défaut lorsqu'elles sont mises à jour, en raison de l'option AUTO_UPDATE_STATISTICS. Si vous préférez ne pas mettre à jour toutes les statistiques en exécutant sp_updatestats, envisagez l'utilisation de UPDATE STATISTICS pour mettre à jour les statistiques de manière sélective sur les index et d'autres statistiques FULLSCAN avec le taux d'échantillonnage FULLSCAN après la mise à niveau de la base de données.

Voir aussi

Concepts

Optimisation des index

Autres ressources

CREATE INDEX (Transact-SQL)
Analyse de requêtes
sp_autostats (Transact-SQL)

Aide et Informations

Assistance sur SQL Server 2005

Historique des modifications