Statistiques utilisées par l'optimiseur de requête dans Microsoft SQL Server 2005 : Microsoft TechNet SQL Server TechCenter

Article
03/19/2009

Par Auteur : Eric N. Hanson et Collaborateur : Lubor Kollar

Microsoft® SQL Server™ 2005 collecte des informations statistiques sur les index et les données de colonne stockées dans la base de données. Ces statistiques sont utilisées par l'optimiseur de requête SQL Server pour choisir le plan le plus efficace pour extraire ou mettre à jour les données. Ce document décrit les données collectées, à quel emplacement elles sont stockées, ainsi que les commandes permettant de créer, de mettre à jour et de supprimer des statistiques. Par défaut, SQL Server 2005 crée et met à jour les statistiques automatiquement, lorsqu'une telle opération est considérée comme utile. Ce document décrit également la façon dont ces comportements peuvent être modifiés à différents niveaux (colonne, table et base de données).

Sur cette page

Données statistiques dans SQL Server 2005
Récapitulatif des fonctionnalités de statistiques
Nouveautés concernant les statistiques dans SQL Server 2005
Définitions
Statistiques collectées par SQL Server 2005
Création et affichage de statistiques : exemple
Création de statistiques avec SQL Server 2005
Gestion des statistiques dans SQL Server 2005
Statistiques de résumé de chaîne
Statistiques sur les colonnes calculées
Statistiques sur les colonnes CLR de type défini par l'utilisateur
Statistiques et vues indexées
Méthodes recommandées pour la gestion des statistiques
Résumé
Références

Données statistiques dans SQL Server 2005

Microsoft® SQL Server™ 2005 collecte des statistiques sur des colonnes individuelles (statistiques à une seule colonne) ou sur des ensembles de colonnes (statistiques multi-colonne). Les statistiques sont utilisées par l'optimiseur de requête pour évaluer la sélectivité des expressions, et donc la taille des résultats intermédiaires et finaux des requêtes. De bonnes statistiques permettent à l'optimiseur d'évaluer de manière précise le coût de différents plans de requête, et de choisir un plan de qualité élevée. Toutes les informations sur un objet statistique unique sont stockées dans plusieurs colonnes d'une même ligne de la table sysindexes, et dans un objet binaire de statistiques volumineux (statblob) conservé dans une table accessible en interne uniquement. En outre, les informations sur les statistiques peuvent être trouvées dans les nouvelles vues de métadonnées sys.stats et sys.indexes.

Récapitulatif des fonctionnalités de statistiques

SQL Server 2005 offre un certain nombre de fonctionnalités pour la gestion des statistiques. La fonctionnalité la plus importante est la possibilité de créer et de mettre à jour automatiquement les statistiques. Cette fonctionnalité est activée par défaut dans SQL Server 2005 et SQL Server 2000. Environ 98 % des installations SQL Server 2000 conservent cette fonctionnalité activée, ce qui est une bonne pratique. Pour la majeure partie des bases de données et applications, les développeurs et administrateurs peuvent utiliser la création et la mise à jour automatiques des statistiques afin de fournir des statistiques complètes et précises sur leurs données. Cela permet à l'optimiseur de requête SQL Server 2005 de générer de manière cohérente de bons plans de requête, tout en limitant les coûts de développement et d'administration. Si vous avez besoin de davantage de contrôle sur la création et la mise à jour des statistiques afin d'obtenir de bons plans de requête et de gérer la surcharge liée à la collecte des statistiques, vous pouvez utiliser les fonctionnalités de création et de mise à jour manuelles des statistiques.

Une nouvelle fonctionnalité importante pour les environnements haut débit d'application de traitement en ligne des transactions est la possibilité de mettre à jour automatiquement les statistiques, de manière asynchrone. Cela permet d'améliorer de façon prévisible le temps de réponse des requêtes dans ces environnements.

Les fonctionnalités de statistiques de SQL Server 2005 vous permettent d'effectuer les opérations suivantes :

Créer et mettre à jour les statistiques de manière implicite avec la fréquence d'échantillonnage par défaut (Dans les commandes SELECT, INSERT, DELETE et UPDATE, l'utilisation d'une colonne dans une condition de requête telle qu'une clause WHERE ou JOIN entraîne la création ou la mise à jour des statistiques si nécessaire, dès lors que la fonctionnalité de création et de mise à jour automatiques des statistiques est activée.)
Créer et mettre à jour manuellement les statistiques avec n'importe quelle fréquence d'échantillonnage souhaitée, ainsi que supprimer des statistiques (CREATE STATISTICS, UPDATE STATISTICS, DROP STATISTICS, CREATE INDEX, DROP INDEX)
Créer manuellement des statistiques en masse pour toutes les colonnes de toutes les tables d'une base de données (sp_createstats)
Mettre à jour manuellement toutes les statistiques existantes dans la base de données (sp_updatestats)
Répertorier les objets statistiques présents pour une table ou une base de données (sp_helpstats, vues de catalogue sys.stats, sys.stats_columns)
Afficher des informations descriptives sur les objets statistique (DBCC SHOW_STATISTICS)
Activer et désactiver la création et la mise à jour automatiques des statistiques à l'échelle de la base de données ou pour une table ou un objet statistiques spécifique (options ALTER DATABASE AUTO_CREATE_STATISTICS et AUTO_UPDATE_STATISTICS, sp_autostats, et l'option NORECOMPUTE sur CREATE STATISTICS et UPDATE STATISTICS)
Activer et désactiver la mise à jour automatique asynchrone des statistiques (option ALTER DATABASE AUTO_UPDATE_STATISTICS_ASYNC)

En outre, SQL Server Management Studio vous permet d'explorer et de contrôler graphiquement les objets statistiques via la vue Explorateur d'objets. Les statistiques sont répertoriées dans l'Explorateur d'objets dans un dossier sous chaque objet table.

Nouveautés concernant les statistiques dans SQL Server 2005

SQL Server 2005 présente un certain nombre de nouvelles fonctionnalités de statistiques permettant à l'optimiseur de choisir de meilleurs plans de requête pour un éventail plus large de requêtes, ou d'améliorer la gestion des statistiques. Ces fonctionnalités incluent les améliorations suivantes :

Statistiques de résumé de chaîne: Des informations sur la distribution en fréquence des sous-chaînes sont gérées pour les colonnes de type caractère. Cela permet à l'optimiseur de mieux évaluer la sélectivité des conditions qui utilisent l'opérateur LIKE.
Statistiques asynchrones à mise à jour automatique: L'option AUTO_UPDATE_STATISTICS_ASYNC de l'instruction ALTER DATABASE est une nouveauté de SQL Server 2005. Elle est désactivée par défaut. Lorsqu'elle est activée, SQL Server 2005 procède à la mise à jour automatique des statistiques en arrière-plan. La requête ayant entraîné la mise à jour des statistiques n'est pas bloquée. Elle passe ensuite aux statistiques anciennes. Vous bénéficiez ainsi d'un temps de réponse plus prévisible pour certaines charges de travail.
Statistiques de colonne calculées: Les statistiques peuvent être créées et mises à jour manuellement ou automatiquement sur les colonnes calculées (Cette possibilité était partiellement prise en charge par SQL Server 2000, sans être documentée.).
Prise en charge des objets volumineux: Les colonnes de type ntext, text et image, ainsi que les nouveaux types nvarchar(max), varchar(max) et varbinary(max), peuvent être spécifiés en tant que colonnes de statistiques.
Environnement amélioré de chargement des statistiques: L'optimiseur améliore en interne le chargement des statistiques par rapport à SQL Server 2000. Il charge désormais toutes les statistiques dont il a besoin et uniquement celles-là, ce qui permet d'améliorer la qualité et les performances des résultats de requête.
Possibilité accrue de créer automatiquement des statistiques sur les colonnes calculées: Si une requête contient une expression équivalente à l'expression d'une colonne calculée, SQL Server 2005 peut créer automatiquement des statistiques sur la colonne calculée si elles sont nécessaires.
Taille minimale des échantillons : Un minimum de 8 mégaoctets de données, ou la taille de la table si celle-ci est plus petite, sont désormais échantillonnés au cours de la collecte des statistiques.
Augmentation de la limite du nombre de statistiques: Le nombre d'objets statistiques de colonne autorisés par table a été porté à 2 000. 249 statistiques d'index supplémentaires peuvent également être présentes, ce qui porte le maximum à 2 249 statistiques par table.
Sortie améliorée de DBCC SHOW_STATISTICS: DBCC SHOW_STATISTICS affiche désormais le nom de l'objet statistique affiché, afin d'éviter toute ambiguité.
La mise à jour automatique des statistiques est désormais basée sur les compteurs de modification de colonne : Dans SQL Server 2000, la mise à jour des statistiques était déterminée par le nombre de modifications apportées aux lignes. À présent, les modifications font l'objet d'un suivi au niveau colonne et la mise à jour automatique des statistiques peut désormais être évitée sur les colonnes qui n'ont pas changé suffisamment pour justifier la mise à jour des statistiques.
Statistiques sur les tables internes: Les statistiques sont totalement prises en charge sur les tables répertoriées dans sys.internal_tables, y compris les index XML et fulltext, les files d'attente service broker, ainsi que les tables de notification de requête.
Sortie d'ensemble de lignes unique pour DBCC SHOW_STATISTICS: DBCC SHOW_STATISTICS prend en charge les options permettant de générer l'en-tête, le vecteur de densité et l'histogramme de manière individuelle, en tant qu'ensembles de lignes uniques. Cela permet une programmation plus simple lors du traitement automatique de la sortie DBCC SHOW_STATISTICS.
Statistiques sur un maximum de 32 colonnes: La limite du nombre de colonnes d'un objet statistiques a été portée de 16 à 32.
Statistiques sur les tables partitionnées : Les statistiques sont totalement prises en charge sur les tables partitionnées, ce qui constitue une nouveauté de SQL Server 2005. Les histogrammes sont gérés table par table (Et non par partition).
Collecte de statistiques en parallèle pour l'analyse complète : Pour les statistiques collectées avec analyse complète, la création d'un objet statistiques unique peut être effectuée en parallèle pour les tables non partitionnées et partitionnées.
Création améliorée de recompilations et statistiques dans le cas de statistiques manquantes: Lorsque la création automatique de statistiques a échoué, lors d'une prochaine exécution d'un plan généré avec des statistiques manquantes, une création automatique est effectuée et le plan est recompilé ; la condition des statistiques manquantes disparaît. Pour plus d'informations, reportez-vous au livre blanc Problèmes de la compilation par lot, de la recompilation et de la mise en cache de plans dans SQL Server 2005, écrit par Marathe.
Amélioration de la logique de recompilation et de la mise à jour des statistiques pour les tables vides : Le passage de 0 à > 0 lignes dans une table entraîne la recompilation des requêtes et la mise à jour des statistiques. Pour plus d'informations, reportez-vous au livre blanc Problèmes de la compilation par lot, de la recompilation et de la mise en cache de plans dans SQL Server 2005, écrit par Marathe.
Affichage plus clair et plus cohérent des histogrammes: DBCC SHOW_STATISTICS a été amélioré, avec la mise à l'échelle systématique des histogrammes avant leur stockage dans les catalogues.
Contraintes de corrélation de date déduites : En activant le paramètre de base de données DATE_CORRELATION_OPTIMIZATION, vous pouvez faire en sorte que SQL Server gère les informations sur la corrélation entre les champs date-heure sur une paire de tables liées par une clé étrangère. Ces informations sont utilisées pour permettre la détermination de prédicats implicites pour certaines requêtes. Les informations ne sont pas utilisées directement pour l'estimation de la sélectivité ou pour le calcul du coût par l'optimiseur ; il ne s'agit donc pas de statistiques au sens strict, mais elles sont étroitement liées aux statistiques dans la mesure où il s'agit d'informations auxiliaires utilisées pour obtenir un meilleur plan de requête.
sp_updatestats : Dans Microsoft SQL Server 2005, sp_updatestats met uniquement à jour les statistiques qui le nécessitent, sur la base des informations rowmodctr dans la vue de compatibilité sys.sysindexes, ce qui évite les mises à jour inutiles de ces éléments inchangés. Pour les bases de données qui s'exécutent avec le niveau de compatibilité 90 ou supérieur, sp_updatestats préserve le paramètre UPDATE STATISTICS automatique pour tout index ou statistique particulier.

Le comportement des statistiques a également fait l'objet de quelques autres changements mineurs. En particulier, la colonne statblob de sys.sysindexes est désormais toujours indiquée comme NULL. La colonne statblob proprement dite est conservée dans une table interne uniquement du catalogue.

Définitions

Les définitions suivantes sont utilisées pour les statistiques SQL Server 2005 :

statblob : Statistics Binary Large Object. Cet objet est stocké dans les catalogues système, dans une vue de catalogue interne appelée sys.sysobjvalues.
Résumé de chaîne : Forme de statistiques qui récapitule la distribution en fréquence des sous-chaînes d'une colonne de type chaîne. Celle-ci est utilisée pour estimer la sélectivité des prédicats LIKE. Elle est stockée dans statblob pour une colonne de type chaîne.
sysindexes : La vue de catalogue sys.sysindexes, qui contient des informations sur les tables et les index.
Prédicat : condition qui prend la valeur vrai ou faux. Les prédicats sont utilisés dans une clause WHERE ou JOIN d'une requête de base de données.
Sélectivité : Fraction des lignes du jeu d'entrée du prédicat qui satisfait au prédicat. Des mesures de sélectivité plus sophistiquées sont également utilisées pour évaluer le nombre de lignes générées par les jointures, DISTINCT et d'autres opérateurs. Par exemple :
```
SQL Server 2005 estimates the selectivity of the predicate "Sales.SalesOrderHeader.OrderID = 43659" in the AdventureWorks database as 1/31465 = 0.00003178.
```
Estimation de cardinalité : estimation de la taille d'un ensemble de résultats. Par exemple, si une table T comporte 100 000 lignes et qu'une requête contient un prédicat de sélection de la forme T.a=10, et qu'un histogramme montre que la sélectivité de T.a=10 est de 10 %, l'estimation de cardinalité de la fraction de lignes de T devant être considérée par la requête est 10 % * 100 000 = 10 000.
LOB : Objet volumineux (valeur de type text, ntext, image, varchar(max), nvarchar(max), varbinary(max)).

Statistiques collectées par SQL Server 2005

SQL Server 2005 gère les informations suivantes au niveau table. Celles-ci ne font pas partie d'un objet statistiques, mais SQL Server 2005 les utilise dans certains cas au cours de l'estimation du coût d'une requête.

Nombre de lignes de la table ou de l'index (colonne rows de sys.sysindexes).
Nombre de pages occupées par la table ou l'index (colonne dpages de sys.sysindexes).

SQL Server 2005 collecte les statistiques suivantes concernant les colonnes des tables, puis les stocke dans un objet statistiques (statblob) :

Heure à laquelle les statistiques ont été collectées.
Nombre de lignes utilisées pour générer l'histogramme et les informations de densité (description ci-dessous).
Longueur moyenne des clés.
Histogramme à une seule colonne, y compris le nombre d'étapes.
Résumé des chaînes, si la colonne contient des données de type caractère. La sortie DBCC SHOW_STATISTICS contient une colonne "String Index" qui présente la valeur YES si un objet statistiques contient un résumé des chaînes.

Un histogramme est un ensemble pouvant comporter jusqu'à 200 valeurs d'une colonne donnée. Toutes les valeurs d'une colonne donnée, ou seulement un échantillon, sont triées ; l'ordre est divisé en un maximum de 199 intervalles, de sorte que les informations statistiquement les plus significatives sont capturées. En général, ces intervalles sont de tailles différentes. Les valeurs suivantes, ou les informations suffisantes pour les déduire, sont stockées avec chaque étape de l'histogramme.

RANGE_HI_KEY	Valeur de clé illustrant la limite supérieure d'une étape d'histogramme.
RANGE_ROWS	Spécifie le nombre de lignes de la plage (inférieur à RANGE_HI_KEY, mais supérieur à la valeur RANGE_HI_KEY précédente).
EQ_ROWS	Indique le nombre de lignes exactement égales à RANGE_HI_KEY.
AVG_RANGE_ROWS	Nombre moyen de lignes par valeur distincte dans la plage.
DISTINCT_RANGE_ROWS	Indique le nombre de valeurs de clé distinctes dans cette plage (sans compter la clé précédente avant RANGE_HI_KEY, et RANGE_HI_KEY proprement dite) ;

Les histogrammes dans SQL Server 2005 sont créés uniquement pour une colonne unique, à savoir la première colonne de l'ensemble de colonnes clé de l'objet statistiques.

SQL Server 2005 crée l'histogramme à partir de l'ensemble trié de valeurs de colonne, en trois étapes :

Initialisation de l'histogramme : Lors de la première étape, une suite de valeurs commençant au début de l'ensemble trié est traitée, et jusqu'à 200 valeurs de RANGE_HI_KEY, EQ_ROWS, RANGE_ROWS et DISTINCT_RANGE_ROWS sont collectées (RANGE_ROWS et DISTINCT_RANGE_ROWS sont toujours à zéro pendant cette étape). La première étape se termine lorsque toutes les entrées ont été traitées ou lorsque 200 valeurs ont été trouvées.
Analyse avec fusion des intervalles : Chaque valeur supplémentaire de la première colonne de la clé de statistiques est traitée lors de la deuxième étape, dans l'ordre ; chaque valeur successive est ajoutée à la dernière plage, ou une nouvelle plage est créée à la fin (cela est possible parce que les valeurs d'entrée sont stockées.). Si une nouvelle plage est créée, une paire de plages voisines existantes est réduite en une plage unique. Cette paire de plages est sélectionnée afin de limiter les pertes d'informations. Le nombre d'étapes après la réduction des plages reste à 200 au cours de cette étape. Cette méthode est basée sur une variation de l'histogramme maxdiff.
Consolidation de l'histogramme : Au cours de la troisième étape, davantage de plages peuvent être réduites si une quantité significative d'informations n'est pas perdue. Par conséquent, même si la colonne compte plus de 200 valeurs uniques, l'histogramme peut comporter moins de 200 étapes.

Si l'histogramme a été créé à partir d'un échantillon, les valeurs de RANGE_ROWS, EQ_ROWS, DISTINCT_RANGE_ROWS et AVG_RANGE_ROWS sont estimées et ne doivent donc pas nécessairement être des entiers.

La densité désigne les informations sur le nombre de doublons d'une colonne donnée ou d'une combinaison de colonnes et est calculée via la formule 1/(nombre de valeurs distinctes). Lorsqu'une colonne est utilisée dans un prédicat d'égalité, le nombre de lignes concernées est estimé avec la densité issue de l'histogramme. Les histogrammes sont également utilisés pour estimer la sélectivité des prédicats de sélection de non-égalité, jointures et autres opérateurs.

Outre l'horodatage qui indique l'heure à laquelle les statistiques ont été collectées, le nombre de lignes de la table, le nombre de lignes échantillonnées pour générer l'histogramme, les informations de densité et la longueur moyenne des clés, ainsi que l'histogramme proprement dit, les informations sur les statistiques par colonne incluent une valeur Toute densité pour chaque ensemble de colonnes constituant un préfixe de l'ensemble de colonnes des statistiques. Ceci est illustré dans le deuxième ensemble de lignes généré par DBCC SHOW_STATISTICS. Toute densité est une estimation de 1/(nombre de valeurs distinctes dans l'ensemble de colonnes du préfixe). La section suivante présente un exemple.

Remarque : La valeur Densité incluse dans la première ligne renvoyée par dbcc show_statistics est la densité de toutes les valeurs échantillonnées autres que les valeurs RANGE_HI_KEY. Les valeurs RANGE_HI_KEY sont généralement les valeurs les plus fréquentes dans la distribution. Par conséquent, la densité affichée fournit des informations pouvant s'avérer utiles concernant la densité des valeurs non fréquentes.

Les statistiques multi-colonnes d'un ensemble de colonnes sont constituées d'un histogramme pour la première colonne de la définition des statistiques, d'une valeur de densité pour la première colonne, et d'une valeur Toute densité pour chaque combinaison de préfixe des colonnes (y compris la première colonne seule). Chaque ensemble de statistiques multi-colonnes (un histogramme et deux valeurs de densité ou plus) est stocké dans un objet statblob avec l'horodatage de la dernière mise à jour des statistiques, le nombre de lignes de l'échantillon utilisé pour générer les informations statistiques, le nombre d'étapes de l'histogramme, ainsi que la longueur moyenne de la clé. Un résumé de chaîne est inclus pour la première colonne uniquement, seulement si elle contient des données de type caractère.

Utilisez sp_helpindex et sp_helpstats pour afficher la liste des statistiques disponibles pour une table donnée. sp_helpindex répertorie tous les index de la table et sp_helpstats répertorie toutes les statistiques sur la table. Chaque index utilise également les informations statistiques pour ses colonnes. Les informations statistiques créées à l'aide de la commande CREATE STATISTICS sont équivalentes aux statistiques créées par une commande CREATE INDEX sur les mêmes colonnes. La seule différence réside dans le fait que la commande CREATE STATISTICS utilise l'échantillonnage par défaut, tandis que la commande CREATE INDEX collecte les statistiques avec analyse complète, dans la mesure où elle doit traiter toutes les lignes de l'index.

Création et affichage de statistiques : exemple

L'exemple suivant illustre la façon dont les statistiques sont créées, à la fois automatiquement et manuellement, et illustre comment répertorier et afficher des informations sur les statistiques. Les résultats sont donnés pour certaines commandes, mais pas toutes ; la sortie générée par SQL Server 2005 est indiquée lorsque cela est utile pour illustrer son comportement. Vous pouvez exécuter cet exemple vous-même afin de voir la sortie complète.

USE tempdb 
GO
-- Clean up objects from any previous runs.
IF object_id(N'Person.Contact','U') IS NOT NULL
DROP TABLE Person.Contact
GO
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'Person')
DROP SCHEMA Person
GO
-- Create a sample schema and table.
CREATE SCHEMA Person
GO
CREATE TABLE Person.Contact(
FirstName nvarchar(60), 
LastName nvarchar(60), 
Phone nvarchar(15), 
Title nvarchar(15)
)
GO
-- Populate the table with a few rows.
INSERT INTO Person.Contact 
   VALUES(N'James',N'Smith',N'425-555-1234',N'Mr')
INSERT INTO Person.Contact 
   VALUES(N'James',N'Andersen',N'425-555-1111',N'Mr')
INSERT INTO Person.Contact 
   VALUES(N'James',N'Andersen',N'425-555-3333',N'Mr')
INSERT INTO Person.Contact 
   VALUES(N'Christine',N'Williams',N'425-555-0000',N'Dr')
INSERT INTO Person.Contact 
   VALUES(N'Susan',N'Zhang',N'425-555-2222',N'Ms')
GO
-- Show that there are no statistics yet on the Person.Contact table.
sp_helpstats N'Person.Contact', 'ALL'
GO
-- Implicitly create statistics on LastName.
SELECT * FROM Person.Contact WHERE LastName = N'Andersen'
GO
-- Show that statistics were automatically created on LastName.
sp_helpstats N'Person.Contact', 'ALL'
GO

Résultats :

statistics_name	statistics_keys
_WA_Sys_00000002_1B29035F	LastName

-- Create an index, which also creates statistics.
CREATE NONCLUSTERED INDEX Phone on Person.Contact(Phone)
GO
-- Show that creating the index created an associated statistics object.
sp_helpstats N'Person.Contact', 'ALL'
GO

Résultats :

statistics_name	statistics_keys
_WA_Sys_00000002_1B29035F	LastName
Phone	Phone

-- Create a multi-column statistics object on first and last name.
CREATE STATISTICS FirstLast ON Person.Contact(FirstName,LastName)
GO
-- Show that there are now three statistics objects on the table.
sp_helpstats N'Person.Contact', 'ALL'
GO

Résultats :

statistics_name	statistics_keys
_WA_Sys_00000002_1B29035F	LastName
FirstLast	FirstName, LastName
Phone	Phone

-- Display the statistics for LastName.
DBCC SHOW_STATISTICS (N'Person.Contact', LastName)
GO

Résultats :

Informations sur l'en-tête des statistiques :

Nom	Mis à jour	Lignes	Lignes échantillonnées	Étapes	Densité	Longueur moyenne des clés	Index de chaîne
_WA_Sys_00000002_1B29035F	Mar 25 2005 11:21AM	5	5	4	0	13.6	OUI

Préfixes définis des colonnes et densités et longueurs associées :

Toute densité	Longueur moyenne	Colonnes
0.25	13.6	LastName

Étapes de l'histogramme :

RANGE_HI_KEY	EQ_ROWS	AVG_RANGE_ROWS
Andersen	2	0
Smith	1	1
Williams	1	1
Zhang	1	1

-- If you take the name of the statistics object displayed by 
-- the command above and subsitute it in as the second argument of 
-- DBCC SHOW_STATISTICS you can form a command like the following one 
--(the exact name of the automatically created statistics object 
-- will typically be different for you).

DBCC SHOW_STATISTICS (N'Person.Contact', _WA_Sys_00000002_2D7CBDC4)

-- Executing the above command illustrates that you can show statistics by 
-- column name or statistics object name.
GO
-- The following displays multi-column statistics. Notice the two
-- different density groups for the second rowset in the output.
DBCC SHOW_STATISTICS (N'Person.Contact', FirstLast)

Résultats (deuxième ensemble de lignes uniquement) :

Préfixes définis des colonnes et densités et longueurs associées :

Toute densité	Longueur moyenne	Colonnes
0.3333333	11.6	FirstName
0.25	25.2	FirstName, LastName

Pour voir un histogramme entièrement rempli pour une table plus volumineuse, exécutez les commandes suivantes :

USE AdventureWorks
-- Clean up objects from previous runs.
IF EXISTS (SELECT * FROM sys.stats 
           WHERE object_id = object_id('Sales.SalesOrderHeader')
           AND name = 'TotalDue')
DROP STATISTICS Sales.SalesOrderHeader.TotalDue
GO
CREATE STATISTICS TotalDue ON Sales.SalesOrderHeader(TotalDue)
GO
DBCC SHOW_STATISTICS(N'Sales.SalesOrderHeader', TotalDue)

Création de statistiques avec SQL Server 2005

Vous pouvez décrire les statistiques dans SQL Server 2005 de différentes façons, comme décrit ci-dessous.

L'optimiseur crée automatiquement des statistiques sur une seule colonne en fonction des besoins, suite à l'optimisation des instructions SELECT, INSERT, UPDATE et DELETE lorsque l'option AUTO_CREATE_STATISTICS est activée, ce qui est le cas par défaut.
Il existe dans SQL Server 2005 deux instructions élémentaires qui génèrent explicitement les informations statistiques décrites ci-dessus : CREATE INDEX génère l'index déclaré en premier lieu et crée également un ensemble de statistiques pour les combinaisons de colonnes qui constituent les clés d'index (mais pas les autres colonnes incluses). CREATE STATISTICS génère uniquement les statistiques pour une colonne ou combinaison de colonnes donnée.
En outre, il existe plusieurs autres moyens de créer des statistiques ou des index. Au final, chacun exécute l'une des deux commandes ci-dessus. Utilisez sp_createstats pour créer des statistiques pour toutes les colonnes éligibles (à l'exception de toutes les colonnes XML) pour toutes les tables utilisateur de la base de données actuelle. Aucun nouvel objet statistique n'est créé pour les colonnes comportant déjà un objet statistiques.
Utilisez dbcc dbreindex pour recréer un ou plusieurs index pour une table dans la base de données spécifiée.
Dans Management Studio, développez le dossier sous un objet Table, cliquez avec le bouton droit de la souris sur le dossier Statistiques, puis choisissez Nouvelles statistiques.
Utilisez Database Tuning Advisor (DTA) pour créer des index.

Voici un exemple de commande CREATE STATISTICS sur la table AdventureWorks.Person.Contact :

CREATE STATISTICS FirstLast2 ON Person.Contact(FirstName,LastName)
WITH SAMPLE 50 PERCENT

En général, les statistiques avec échantillonnage par défaut sont suffisantes pour permettre la génération de bon plans de requête. Cependant, dans certains cas, lorsque les statistiques présentent des échantillons de plus grande taille, l'optimisation des requêtes peut être utile, comme par exemple lorsque les valeurs de l'exemple de colonne donné ne sont pas aléatoires. Les échantillons non aléatoires peuvent se produire si les données sont triées ou organisées en clusters. Le tri ou le clustering peuvent être dus à la création d'index, ou au chargement de données dans une structure en tas (heaps) déjà triée ou organisée en clusters. La taille d'échantillon supérieure la plus couramment utilisée est analyse complète, car elle offre les statistiques les plus précises. Le coût lié à l'utilisation de statistiques avec des tailles d'échantillon supérieures est le temps nécessaire à la création des statistiques.

La commande ci-dessus crée un objet statistiques unique à deux colonnes. Dans ce cas, la commande SAMPLE 50 PERCENT est ignorée et une analyse complète est effectuée, car la table est trop petite. L'échantillonnage est utilisé principalement pour éviter les analyses excessives des données et affecte uniquement les tables et index comportant 1 024 pages (8 Mo) ou plus.

Dans SQL Server 2005, des statistiques sont créées pour tous les index lors de la création de l'index. SQL Server crée automatiquement des statistiques à une seule colonne lors de la compilation de requêtes. Ces statistiques sont créées pour les colonnes pour lesquelles l'optimiseur serait sinon contraint d'estimer la densité ou la distribution appropriée. Cette règle comporte trois exceptions. Il est possible que les statistiques ne soient pas créées automatiquement lorsque (1) la base de données est en lecture seule, (2) il y a trop de compilations en cours et (3) le type de données des colonnes n'est pas pris en charge pour les statistiques automatiques.

La fonction de création automatique des statistiques peut être désactivée au niveau base de données via l'exécution du code suivant :

ALTER DATABASE dbname SET AUTO_CREATE_STATISTICS OFF

De la même façon, la création automatique des statistiques peut être activée au niveau base de données via l'exécution du code suivant :

ALTER DATABASE dbname SET AUTO_CREATE_STATISTICS ON

Il est recommandé de conserver ce paramètre activé (ON). Ne le désactivez que si vous y êtes contraint pour faire face à des problèmes de performance connus, tels que la nécessité de spécifier une fréquence d'échantillonnage par défaut pour certaines tables.

Par défaut, les statistiques sont créées par échantillonnage des données lors de l'exécution de la commande CREATE STATISTICS ou lorsque les statistiques sont créées automatiquement. La commande CREATE INDEX analyse l'ensemble de données complet ; par conséquent, les statistiques d'index sont créées initialement sans échantillonnage (l'équivalent de l'analyse complète). La commande CREATE STATISTICS vous permet de définir la taille de l'échantillon dans la clause WITH, soit en précisant analyse complète, soit en indiquant le pourcentage de données ou le nombre de lignes à analyser. Cette dernière solution est interprétée comme une approximation. Il est également possible d'hériter de la taille d'échantillon précédente lors de l'utilisation de l'option WITH RESAMPLE avec la commande UPDATE STATISTICS. Cela s'avère particulièrement utile lorsqu'il existe des index sur certaines colonnes ou certains ensembles de colonnes (créés à l'origine avec des statistiques d'analyse complète) et uniquement des statistiques sur d'autres colonnes ou ensembles de colonnes (créés à l'origine avec des statistiques SAMPLE). L'utilisation de l'option resample avec UPDATE STATISTICS conserve les statistiques d'analyse complète pour les index et les statistiques d'échantillon pour le reste des colonnes.

Pour les petites tables, un minimum de 8 Mo de données est échantillonné. Si une table est petite à l'origine et que vous échantillonnez avec la fréquence par défaut, puis que vous utilisez l'option resample lors de la mise à jour des statistiques, vous obtenez l'équivalent d'une analyse complète, même si la taille de la table dépasse 8 Mo. Évitez l'utilisation de l'option resample si vous souhaitez utiliser la fréquence d'échantillonnage par défaut lorsque la taille de la table varie.

La fréquence d'échantillonnage resample est calculée en fonction du nombre de lignes échantillonnées et du nombre total de lignes de la table au cours du calcul précédent des statistiques. Étant donné que la fréquence d'échantillonnage réelle peut varier en raison de la nature aléatoire de l'échantillonnage, la fréquence resample n'est qu'une approximation de la fréquence d'échantillonnage précédente pour les échantillons sans analyse complète. Pour un échantillonnage pouvant être répété de manière constante, spécifiez explicitement la même fréquence d'échantillonnage que celle utilisée auparavant avec UPDATE STATISTICS, plutôt que d'utiliser resample.

La commande dbcc show_statistics affiche la taille de l'échantillon sous l'en-tête Lignes échantillonnées. Les statistiques créées automatiquement, ou mises à jour automatiquement (comme décrit dans la section suivante) sont toujours générées à l'aide de l'échantillonnage par défaut. La fréquence d'échantillonnage par défaut est une fonction à croissance lente de la taille de la table, ce qui permet la collecte relativement rapide des statistiques, même pour les tables très volumineuses.

Lorsque les statistiques sont créées et mises à jour, l'optimiseur doit choisir un chemin d'accès pour collecter les statistiques. Un chemin d'accès peut inclure un tas (heap), un index en cluster ou un index sans cluster. Pour les statistiques échantillonnées, l'optimiseur tente d'éviter les chemins d'accès physiquement triés sur la colonne clé des principales statistiques. Cela permet d'obtenir un échantillon plus aléatoire, conduisant à des statistiques plus précises. Pour les chemins d'accès qui ne sont pas triés sur la clé des statistiques (si un tel chemin d'accès existe), le chemin d'accès de plus faible coût est choisi. Il s'agit de l'index le plus limité ou du tas (heap). Pour les statistiques d'analyse complète, l'ordre de tri des chemins d'accès n'est pas significatif pour la précision statistique, de sorte que le chemin d'accès de plus faible coût est choisi.

SQL Server Profiler peut surveiller la création automatique des statistiques. L'événement Statistiques automatiques se trouve dans le groupe des événements de suivi des performances. Sélectionnez également les colonnes IntegerData, Success et ObjectID pour l'événement Statistiques automatiques lors de la définition du suivi. Une fois l'événement AutoStats capturé, la colonne Données entières contient le nombre de statistiques créées ou mises à jour pour une table donnée, l'ID d'objet est l'ID de la table et la colonne TextData (incluse dans la définition par défaut du suivi) contient les noms des colonnes pour lesquelles la création ou la mise à jour des statistiques a été effectuée, avec un préfixe Updated: ou Created: . La colonne Réussite contient une indication de la réussite ou de l'échec de l'opération Statistiques automatiques. En particulier, la colonne Réussite peut prendre trois valeurs différentes :

Nom	Value	Définition
FAILED	0	La création ou la mise à jour automatique des statistiques a échoué pour une raison autre que THROTTLED (voir ci-dessous). Par exemple, la base de données était en lecture seule.
SUCCESS	1	La création ou la mise à jour automatique des statistiques a réussi.
THROTTLED	2	La création ou la mise à jour automatique des statistiques a échoué, car un trop grand nombre d'optimisations était en cours.

Dans certains cas, on peut également observer un événement AutoStats dans lequel aucune statistique n'est créée ou mise à jour. Un tel événement est généré lorsque l'option auto update statistics est désactivée, ou lorsqu'un nombre significatif de modifications ont été apportées à une table référencée dans une requête, et que l'optimiseur a pu supprimer du plan de requête toutes les références à la table en raison de la structure de la requête et de l'existence d'une contrainte de clé étrangère.

La commande DROP STATISTICS est utilisée pour supprimer des statistiques, mais il n'est pas possible de supprimer des statistiques qui sont un sous-produit d'un index. De telles statistiques ne sont supprimées que lors de la suppression de l'index.

Gestion des statistiques dans SQL Server 2005

Après une série d'opérations INSERT, DELETE et/ou UPDATE sur une table, il se peut que les statistiques ne reflètent pas la distribution réelle des données dans une colonne ou un index donné. Si l'optimisateur de requête SQL Server nécessite des statistiques pour une colonne particulière d'une table ayant subi une importante activité de mise à jour depuis la dernière création ou mise à jour des statistiques, SQL Server met automatiquement à jour les statistiques en échantillonnant les valeurs des colonnes (avec l'option auto update statistics). La mise à jour automatique des statistiques est déclenchée par l'optimisation des requêtes ou par l'exécution d'un plan compilé et implique uniquement un sous-ensemble des colonnes référencées dans la requête. Les statistiques sont mises à jour avant la compilation de la requête si l'option AUTO_UPDATE_STATISTCS_ASYNC est désactivée (OFF), et de manière asynchrone si elle est activée (ON).

Lors de la première compilation d'une requête, si l'optimiseur a besoin d'un objet statistiques particulier et que cet objet existe, il est mis à jour s'il est périmé. Lorsqu'une requête est exécutée et que son plan se trouve dans le cache, les statistiques dont dépend le plan sont vérifiées afin de déterminer si elles sont périmées. Si tel est le cas, le plan est supprimé du cache et les statistiques sont mises à jour au cours de la recompilation de la requête. Le plan est également supprimé du cache si certaines des statistiques dont il dépend on changé.

SQL Server 2005 détermine si les statistiques doivent être mises à jour en fonction des modifications apportées aux compteurs de modification de colonnes (colmodctrs).

Un objet statistiques est considéré comme périmé dans les cas suivants :

Si les statistiques sont définies sur une table standard, il est périmé si :
1. La taille de la table est passée de 0 à >0 lignes.
2. Le nombre de lignes de la table était inférieur ou égal à 500 lors de la collecte des statistiques, et la valeur colmodctr de la colonne principale des statistiques a changé de plus de 500 depuis cet instant.
3. La table comportait plus de 500 lignes lors de la collecte des statistiques, et la valeur colmodctr de la colonne principale de l'objet statistiques a changé de plus de 500 + 20 % du nombre de lignes de la table lors de la collecte des statistiques.
Si l'objet statistiques est défini sur une table temporaire, il est périmé dans les cas indiqués ci-dessus, à ceci près qu'il existe un seuil supplémentire de recalcul à 6 lignes, avec un test sinon identique au test 2 de la liste précédente.

Les variables de table ne comportent aucune statistique.

La fonctionnalité de mise à jour automatique des statistiques décrite ci-dessus peut être désactivée à différents niveaux.

Au niveau base de données, désactivez la mise à jour automatique des statistiques via la commande suivante :
```
ALTER DATABASE dbname SET AUTO_UPDATE_STATISTICS OFF
```
Au niveau table, désactivez la mise à jour automatique des statistiques à l'aide de l'option NORECOMPUTE de la commande UPDATE STATISTICS ou de la commande CREATE STATISTICS.
Utilisez sp_autostats pour afficher et modifier le paramètre de mise à jour automatique des statistiques pour une table, un index ou un objet statistiques.

La réactivation de la mise à jour automatique des statistiques peut être effectuée de la même façon à l'aide des commandes ALTER DATABASE, UPDATE STATISTICS ou sp_autostats.

SQL Server 2005 gère le paramètre de mise à jour automatique des statistiques par base de données, par table et par index ou au niveau objet statistiques. S'il est possible de changer ce paramètre pour toutes les statistiques d'une table à l'aide de la commande sp_autostats, cette modification est implémentée par la modification individuelle du paramètre pour chaque objet statistiques et index d'une table donnée. Aucune métadonnée n'enregistre explicitement le fait que la mise à jour automatique des statistiques est activée ou désactivée pour la table entière.

Le tableau suivant illustre l'effet combiné de différents paramètres de base de données, de table et d'index.

Paramètre de base de données	Paramètre d'index ou d'objet statistiques	Mise à jour automatique des statistiques en vigueur pour l'objet
ON	ON	ON
ON	OFF	OFF
OFF	ON	OFF
OFF	OFF	OFF

Il n'est pas possible de remplacer le paramètre de base de données OFF pour la mise à jour automatique des statistiques en le remplaçant par ON au niveau objet statistiques.

La mise à jour automatique des statistiques est toujours effectuée par échantillonnage de l'index ou de la table avec la fréquence d'échantillonnage par défaut. Pour définir explicitement la fréquence d'échantillonnage, exécutez la commande CREATE ou UPDATE STATISTICS.

La mise à jour des statistiques est couverte par le même événement SQL Profiler que la création des statistiques.

Statistiques de résumé de chaîne

SQL Server 2005 inclut une technologie brevetée pour l'estimation de la sélectivité des conditions LIKE. Il crée un résumé statistique de la distribution en fréquence des sous-chaînes pour les colonnes de type caractère (un résumé de chaîne). Ce résumé inclut des colonnes de type text, ntext, char, varchar et nvarchar. Avec le résumé des chaînes, SQL Server peut estimer de manière précise la sélectivité des conditions LIKE lorsque le motif peut comporter un nombre quelconque de caractères génériques, dans n'importe quelle combinaison. Par exemple, SQL Server peut estimer la sélectivité des prédicats de la façon suivante :

Column LIKE 'string%'
Column LIKE '%string'
Column LIKE '%string%'
Column LIKE 'string'
Column LIKE 'str_ing'
Column LIKE 'str[abc]ing'
Column LIKE '%abc%xy'

S'il existe un caractère d'échappement spécifié par l'utilisateur dans un motif LIKE (c'est-à-dire si le motif est de la forme LIKE motif ESCAPE caractère_échappement), SQL Server 2005 déduit la sélectivité.

Il s'agit d'une amélioration par rapport à SQL Server 2000, avec l'utilisation d'une hypothèse pour la sélectivité lorsqu'un caractère générique autre qu'un caractère % de fin est utilisé dans le motif LIKE, la précision de l'estimation étant limitée dans ce cas.

Le champ Index de chaîne du premier ensemble de lignes renvoyé par DBCC SHOW_STATISTICS inclut la valeur YES si l'objet statistiques inclut également un résumé de chaîne. Le contenu du résumé de chaîne n'est pas illustré. Le résumé de chaîne inclut des informations supplémentaires au-delà de ce qui est indiqué dans l'histogramme.

Pour les chaînes dont la longueur est supérieure à 80 caractères, les 40 premiers et derniers caractères sont extraits de la chaîne et sont concaténés avant la prise en considération de la chaîne dans la création du résumé des chaînes. Ainsi, des estimations précises de la fréquence ne sont pas disponibles pour les sous-chaînes qui apparaissent uniquement dans la partie ignorée d'une chaîne.

Statistiques sur les colonnes calculées

SQL Server 2005 prend en charge la création, la mise à jour et l'utilisation de statistiques sur des colonnes calculées, y compris lorsqu'une requête ne contient pas la colonne calculée par nom, mais plutôt l'expression de la colonne calculée. SQL Server 2000 peut automatiquement créer, mettre à jour et utiliser des statistiques sur des colonnes calculées uniquement si la colonne est explicitement nommée dans la requête.

Vous pouvez observer la création automatique de statistiques de colonnes calculées pour la colonne Sales.SalesOrderHeader.TotalDue de la table de base de données AdventureWorks si vous exécutez le script Transact-SQL suivant dans SQL Server 2005 :

USE AdventureWorks
GO
-- Remove all statistics for Sales.SalesOrderHeader
DECLARE c CURSOR FOR
SELECT name FROM sys.stats
WHERE object_id = object_id('Sales.SalesOrderHeader')
AND auto_created <> 0 AND user_created <> 0

DECLARE @name NVARCHAR(255)

OPEN c

FETCH next FROM c INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('drop statistics Sales.SalesOrderHeader.' + @name)
FETCH NEXT FROM c INTO @name
END

CLOSE c
DEALLOCATE c

-- Query Sales.SalesOrderHeader with an expression equivalent
-- to the TotalDue computed column,
-- ((isnull(([SubTotal]+[TaxAmt])+[Freight],(0))).
SELECT *
FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderID
AND (isnull(([SubTotal]+[TaxAmt])+[Freight],(0))) > 200000.00
ORDER BY TotalDue DESC

-- List the created statistics. Observe that statistics 
-- are created for TotalDue even though it is not explicitly
-- referenced in the query.
sp_helpstats 'Sales.SalesOrderHeader'

SQL Server 2005 ne prend pas en charge les statistiques sur les colonnes calculées non persistantes incluant une expression CLR (Common Language Runtime), comme par exemple l'invocation d'une fonction CLR définie par l'utilisateur. Pour créer des statistiques sur une colonne calculée CLR, la colonne doit être marquée comme PERSISTED.

Statistiques sur les colonnes CLR de type défini par l'utilisateur

SQL Server 2005 prend en charge la création, la mise à jour et l'utilisation de statistiques sur les colonnes CLR de type défini par l'utilisateur si le type prend en charge le tri binaire. Les statistiques ne sont pas prises en charge sur les types définis par l'utilisateur ne prenant pas en charge le tri binaire. Un type utilise le tri binaire si son indicateur IsByteOrdered est défini sur vrai dans l'attribut SqlUserDefinedType spécifié dans le cadre de la définition du type. Si un type prend en charge le tri binaire, cela signifie que l'ordre de tri binaire standard est un ordre correct d'un point de vue sémantique pour le type.

Statistiques et vues indexées

En général, les statistiques ne sont pas requises sur les vues indexées. Cela tient au fait que la substitution des vues indexées dans le plan de requête est considérée uniquement après que toutes les statistiques des tables et index sous-jacents ont été associées au plan de requête. Il existe toutefois une exception : les statistiques seront utilisées si la vue est directement référencée dans les clauses FROM à l'aide du conseil NOEXPAND. Notez qu'une erreur sera générée et que le plan ne sera pas créé si le conseil NOEXPAND est utilisé sur une vue ne contenant pas également un index.

En raison de leur utilisation limitée, les statistiques sur les vues indexées ne sont pas créées à l'aide de sp_createstats ou mises à jour à l'aide de sp_updatestats. Les fonctionnalités auto update et auto createstatistics fonctionnent pour les vues indexées. Cependant, comme nous l'avons vu précédemment, ces statistiques sont requises par l'optimiseur et ne sont donc créées que si la vue indexée est utilisée avec le conseil NOEXPAND dans la requête et si l'option de création/mise à jour automatique des statistiques est activée. Vous pouvez également exécuter manuellement l'instruction CREATE STATISTICS sur les colonnes de la vue indexée, ou utiliser UPDATE STATISTICS pour mettre à jour les statistiques d'index ou de colonne sur les vues indexées.

Méthodes recommandées pour la gestion des statistiques

L'objectif des statistiques dans SQL Server est de permettre à l'optimiseur d'obtenir de bonnes estimations de cardinalité afin de trouver de bons plans de requête, tout en maintenant à un niveau raisonnable la surcharge ou le retard associé à la collecte des statistiques. Nous indiquons ci-dessous les méthodes recommandées pour la gestion des statistiques dans SQL Server, les plus importantes en premier.

Utilisez la création automatique et la mise à jour automatique des statistiques

Pour une grande majorité des installations SQL Server, la méthode la plus importante consiste à utiliser la création automatique et la mise à jour automatique des statistiques à l'échelle de la base de données. La création et la mise à jour automatiques des statistiques sont activées par défaut. Si vous remarquez de mauvais plans et que vous suspectez que des statistiques sont manquantes ou périmées, vérifiez que la création et la mise à jour automatiques des statistiques sont activées.

Si nécessaire, utilisez de manière sélective les statistiques FULLSCAN

Si vous utilisez la création et la mise à jour automatiques des statistiques et que vous obtenez un mauvais plan de requête parce que les statistiques ne sont pas précises ou récentes, procédez de la façon suivante :

Conservez la création et la mise à jour automatiques des statistiques, et
uniquement pour les statistiques qui ne sont pas suffisamment précises ou actuelles, utilisez
CREATE STATISTICS … WITH FULLSCAN, NORECOMPUTE
et une tâche qui exécute périodiquement la commande
UPDATE STATISTICS … WITH FULLSCAN, NORECOMPUTE.

La fréquence à laquelle vous devez mettre à jour les statistiques dépend de l'application et peut nécessiter des tests. S'agissant de la fréquence des mises à jour fullscan, si la table en question présente une fréquence élevée de mise à jour, il est judicieux d'exécuter chaque nuit une mise à jour des statistiques fullscan. Si la table présente une fréquence de mise à jour limitée, exécutez la mise à jour fullscan des statistiques une fois par semaine.

Évitez l'utilisation de variables locales dans les requêtes.

Si vous utilisez une variable locale dans un prédicat de requête plutôt qu'un paramètre ou une valeur littérale, l'optimiseur génère une estimation de qualité limitée ou devine la sélectivité du prédicat. Utilisez des paramètres ou des valeurs littérales dans la requête plutôt que des variables locales ; l'optimiseur pourra généralement sélectionner un meilleur plan de requête. Par exemple, observez cette requête qui utilise une variable locale :

declare @StartOrderDate datetime
set @StartOrderDate = '20040731'
select * from Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderId
AND h.OrderDate >= @StartOrderDate

Le nombre de lignes de Sales.SalesOrderHeader que l'optimiseur évalue est déterminé par rapport à la condition h.OrderDate >= @StartOrderDate is 9439.5, qui représente exactement 30 % de la taille de la table. Vous pouvez utiliser le showplan graphique de la requête et cliquer avec le bouton droit de la souris sur le nœud du plan pour Sales.SalesOrderHeader afin d'afficher cette estimation de cardinalité. Dans une version bêta de SQL Server 2005, utilisée lors de la préparation de ce document, le plan choisi utilise une jointure de fusion (les observations qui suivent sont basées sur cette même version de SQL Server 2005 ; vos résultats peuvent être différents, en fonction de votre version de SQL Server, de la mémoire disponible, etc.). Examinez à présent cette requête équivalente qui n'utilise pas de variable locale :

SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderId
AND h.OrderDate >= '20040731'

La cardinalité de l'ensemble de résultats du prédicat "h.OrderDate >= '20040731'" est estimée à 40 dans le showplan graphique de la requête (cliquez avec le bouton droit de la souris sur l'opérateur filtre), pour une sélectivité de 0,13 %. Le plan choisi pour cette requête utilise une jointure de boucle imbriquée plutôt qu'une jointure de fusion, en raison de la meilleure estimation.

Même lorsque des variables locales sont utilisées dans une requête, une estimation meilleure est utilisée dans le cas de prédicats d'égalité. La sélectivité pour les conditions de la forme "@ variable_locale = nom_colonne" est estimée à l'aide de la fréquence moyenne à partir de l'histogramme pour nom_colonne. Par exemple, si la colonne nom_colonne contient toutes les valeurs uniques, une estimation de sélectivité de 1/(nombre de valeurs uniques d'une colonne) est utilisée, ce qui donne un résultat précis.

Pour forcer SQL Server à optimiser une requête chaque fois qu'elle est exécutée et utiliser les valeurs de variables locales pour estimer la cardinalité et le coût au cours de l'optimisation de la requête, ajoutez le conseil RECOMPILE à la requête. Par exemple, remplacez la première des deux requêtes de l'exemple précédent par :

SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderId
AND h.OrderDate >= @StartOrderDate
OPTION(RECOMPILE)

Cela permet d'éliminer le problème des estimations incorrectes dues aux variables locales, au prix d'une recompilation de la requête à chaque exécution.

Pour éliminer l'utilisation de variables locales, envisagez (1) la réécriture de la requête en utilisant des valeurs littérales directement dans les requêtes plutôt que de les affecter à des variables, puis d'utiliser les variables dans les requêtes, (2) l'utilisation de sp_executesql ou de curseurs de serveur API avec des paramètres qui remplacent votre utilisation de variables locales, ou (3) l'utilisation d'une procédure stockée avec des paramètres qui remplacent votre utilisation de variables locales.

Dans le cas (1), si les valeurs littérales ne changent pas entre des envois successifs de la requête, vous n'avez pas besoin d'entreprendre d'autres actions. Cependant, si vous utilisez des instructions SQL générées de manière dynamique et que les valeurs littérales changent à chaque envoi, la surcharge liée à la compilation peut être élevée, en particulier pour les petites requêtes exécutées fréquemment. Pour éviter cette surcharge, continuez de modifier votre application afin d'utiliser des lots paramétrés ou des procédures stockées, plutôt que des instructions SQL dynamiques.

Envisagez de limiter l'utilisation de TVF (fonctions à valeurs de tables) multi-instruction et de variables de tables.

Les TVF (fonctions à valeurs de tables) multi-instruction ne présentent pas de statistiques. L'optimiseur doit deviner la taille de leurs résultats. De la même façon, les variables de table ne présentent pas de statistiques et l'optimiseur doit procéder à un nouveau tri afin de deviner leur cardinalité. S'il en résulte un mauvais plan, envisagez l'utilisation d'une table standard ou d'une table temporaire en tant qu'espace réservé temporaire pour les résultats de la TVF, ou remplacement de la variable de table. Cela permettra à l'optimiseur de mieux utiliser les estimations de cardinalité.

Les expressions non pliables et les fonctions scalaires intégrées peuvent entraîner des valeurs devinées

SQL Server peut évaluer lors de la compilation certaines expressions contenant uniquement des constantes. Cette opération est appelée pliage de constante. Les expressions pouvant être pliées sont traitées comme des valeurs littérales au cours de l'estimation de sélectivité. Les expressions qui ne peuvent pas être pliées entraînent des estimations de la sélectivité. Examinez par exemple le script Transact-SQL ci-dessous. Ce script alimente une table UserLog avec 200 lignes. La moitié des lignes présente des valeurs UserName uniques et l'autre moitié présente la même valeur UserName, ce qui donne une distribution faussée.

IF object_id('UserLog') IS NOT NULL
DROP TABLE UserLog
GO
CREATE TABLE UserLog (UserName NVARCHAR(255), Action NVARCHAR(1000))
DECLARE @i INT
SET @i = 1
SET nocount ON
WHILE @i <= 100
BEGIN
INSERT UserLog VALUES(suser_sname(), 'login')
INSERT UserLog VALUES(newid(), 'login')
SET @i = @i + 1
END

La fonction intégrée suser_sname() renvoie la combinaison nom_domaine\nom_utilisateur pour l'utilisateur Windows actuel et newid() donne une valeur unique. À présent, nous allons exécuter deux requêtes différentes qui sont équivalentes. La première, illustrée ci-dessous, contient le prédicat UserName = suser_sname(). L'optimiseur doit deviner la cardinalité du résultat et il devine 1,98 (vous pouvez le voir dans l'attribut EstimateRows du showplan XML généré en raison de la directive SET STATISTICS XML ON). Le résultat est inférieur d'un facteur de plus de 50, car la cardinalité réelle est de 100.

GO
SET STATISTICS XML ON
GO
SELECT * FROM UserLog WHERE UserName = suser_sname()
GO
SET STATISTICS XML OFF
GO

La deuxième requête est exécutée en tant que requête paramétrée via sp_executesql.
La valeur suser_sname() est transmise en tant que paramètre plutôt que d'apparaître dans
la requête en tant qu'expression.

SET STATISTICS XML ON
GO
DECLARE @UserName NVARCHAR(255)
SET @UserName = suser_sname()
EXEC sp_executesql N'SELECT * FROM UserLog WHERE UserName = @n', 
N'@n nvarchar(255)', @UserName
GO
SET STATISTICS XML OFF
GO

Cette fois-ci, l'optimiseur utilise une estimation précise de 50 % pour la sélectivité de UserName = @n. Si vous examinez le showplan XML généré, vous verrez que EstimateRows est de 100, ce qui est parfait. Dans une requête plus volumineuse et plus complexe sur un ensemble de données plus important, ce type d'erreur peut entraîner la sélection d'un mauvais plan. Si cela pose problème pour votre application, envisagez l'utilisation d'une technique telle que celle illustrée ci-dessus. Utilisez sp_executesql ou une procédure stockée contenant la requête qui pose problème, puis transmettez en tant que paramètre le résultat précalculé de l'expression non pliable. Cela vous permettra de contourner le problème et d'obtenir de bonnes estimations de cardinalité.

Utilisez des statistiques multi-colonnes lorsque les requêtes présentent des conditions multi-colonnes.

Lorsqu'une requête présente une condition multi-colonne, envisagez l'utilisation de statistiques multi-colonnes si vous suspectez que l'optimiseur ne génère pas le meilleur plan pour la requête. Vous obtenez des statistiques multi-colonnes suite à la création d'un index multi-colonne, de sorte que s'il existe déjà un index multi-colonne prenant en charge la condition multi-colonne, il n'est pas nécessaire de créer explicitement les statistiques. La création automatique de statistiques crée uniquement des statistiques à une seule colonne, jamais de statistiques multi-colonnes. Si vous avez besoin de statistiques multi-colonnes, créez-les manuellement ou créez un index multicolonnes.

Imaginez une requête qui accède à la table AdventureWorks.Person.Contact et qui contient la condition suivante :

FirstName = 'Catherine' AND LastName = 'Abel'

Pour rendre plus précise l'estimation de la sélectivité pour cette requête, créez l'objet statistiques suivant :

CREATE STATISTICS LastFirst ON Person.Contact(LastName,FirstName)

Cet objet statistiques sera utile pour les requêtes contenant des prédicats sur LastName et FirstName, ainsi que sur LastName seulement. En général, la sélectivité d'un prédicat sur n'importe quel préfixe de l'ensemble de colonnes d'un objet statistiques multi-colonnes peut être estimé à l'aide de cet objet statistiques.

Pour qu'un objet statistiques prenne totalement en charge une condition multi-colonne, un préfixe des colonnes de l'objet statistiques multi-colonnes doit contenir les colonnes dans la condition. Par exemple, un objet statistiques multi-colonnes sur les colonnes (a,b,c) ne prend que partiellement en charge la condition a=1 AND c=1 ; l'histogramme est utilisé pour estimer la sélectivité pour a=1, mais les informations de densité pour c ne seront pas utilisées, car b est manquant dans la condition. Les statistiques multi-colonnes sur (a,c) ou (a,c,b) prennent en charge la condition a=1 AND c=1, et les informations de densité peuvent être utilisées pour améliorer l'estimation de la sélectivité.

Soyez attentif à toute situation dans laquelle SQL Server revient à la méthode consistant à deviner la sélectivité

SQL Server devine la sélectivité dans un certain nombre de situations. Souvent, les valeurs devinées sont raisonnables, les tailles des données sont suffisamment petites ou la valeur devinée ne donne pas lieu à un mauvais plan de requête. Cependant, lorsque SQL Server doit deviner la sélectivité d'un prédicat de requête, il arrive que des plans de requête non optimaux soient générés. Si une requête n'offre pas les performances que vous en attendez et que vous suspectez qu'un plan de requête non optimal est sélectionné, examinez la requête et le plan résultant afin de détecter une situation dans laquelle la sélectivité aurait été devinée, et non estimée à partir de statistiques. Dans de nombreux cas, vous pouvez modifier la requête ou une petite partie de votre application afin d'éviter ce scénario. La liste suivante décrit certaines situations dans lesquelles la sélectivité peut être devinée, ainsi que les solutions possibles :

Statistiques manquantes : Déterminez si la création automatique des statistiques est activée ou prenez soin de créer manuellement les statistiques à l'aide de CREATE STATISTICS ou sp_createstats. Déterminez si la base de données est en lecture seule, ce qui empêcherait le fonctionnement de la création automatique des statistiques.
Utilisation de variables locales dans la condition de requête (problème traité précédemment).
Expressions non pliables avec constantes dans la condition de requête (par exemple T.x+1 = 0, suser_sname() = T.UserName). Réécrivez la requête afin de supprimer l'expression, ou évaluez l'expression avant d'exécuter la requête et transmettez le résultat dans la requête en tant que paramètre (et non en tant que variable locale). Dans le cas de T.x+1 = 0, réécrivez l'expression comme suit : T.x = -1. Le résultat est équivalent et permet une estimation précise, plutôt que devinée.
Expressions complexes, telles que "Price + Tax > 100" ou "Price * (1+TaxRate) > 100". Si vous observez des performances médiocres pour vos requêtes dans un tel cas, envisagez de créer une colonne calculée avec l'expression équivalente, puis la création de statistiques ou d'un index sur la colonne calculée. La création automatique de statistiques entraîne également la création de statistiques pour la colonne calculée si elle existe, de sorte que vous n'avez pas besoin de créer manuellement les statistiques de colonne calculée si la création automatique des statistiques est activée.

Évitez la modification des paramètres de procédure stockée dans un SP avant toute utilisation dans une requête.

Pour obtenir les meilleures performances, dans certains cas vous devrez éviter d'affecter une nouvelle valeur à un paramètre d'une procédure stockée dans le corps de la procédure, puis d'utiliser la valeur du paramètre dans une requête. La procédure stockée et toutes les requêtes qu'elle contient sont initialement compilées avec la valeur de paramètre transmise initialement en tant que paramètre à la requête. Ce processus est parfois appelé détection de paramètre. Examinez la procédure stockée suivante, qui détermine les ventes à une certaine date ou après, ou, si NULL est passé en tant qu'argument, les trois derniers mois de ventes :

CREATE PROCEDURE GetRecentSales (@date datetime) WITH RECOMPILE AS
BEGIN
IF @date IS NULL
SET @date = dateadd("mm",-3,(SELECT MAX(OrderDATE) 
FROM Sales.SalesOrderHeader))
SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderID
AND h.OrderDate > @date
END

Ce SP, s'il est appelé avec NULL, entraîne l'optimisation de l'instruction SELECT finale pour @date = NULL. Dans la mesure où aucune ligne ne présente la valeur NULL pour OrderDate, l'estimation de cardinalité pour le résultat de l'application de ce filtre à SalesOrderHeader est très faible (1 ligne). Cependant, lors de l'exécution, la date n'est pas NULL, mais se situe trois mois avant la dernière valeur OrderDate. Le nombre réel de lignes SalesOrderHeader correspondantes est de 5 736. L'optimiseur choisit une jointure de boucle imbriquée pour la requête lorsque la valeur NULL est transmise à GetRecentSales, tandis que le plan optimal contient une fusion de jointure. Vous pouvez voir le plan sélectionné, ainsi que les cardinalités attendues et réelles, à l'aide de ce script :

SET STATISTICS PROFILE ON
GO
EXEC GetRecentSales NULL
GO
SET STATISTICS PROFILE OFF
GO

Notez que l'option WITH RECOMPILE spécifiée dans la procédure stockée GetRecentSales ci-dessus n'élimine pas l'erreur d'estimation de la cardinalité. Un moyen de s'assurer que les requêtes de cet exemple sont optimisées avec les paramètres appropriés permettant l'obtention de bonnes estimations consiste à modifier la procédure stockée de la façon suivante, en la fractionnant en plusieurs parties :

CREATE PROCEDURE GetRecentSales (@date datetime)  AS
BEGIN
IF @date IS NULL
SET @date = dateadd("mm",-3,(SELECT MAX(OrderDATE) 
FROM Sales.SalesOrderHeader))
EXEC GetRecentSalesHelper @date
END

CREATE PROCEDURE GetRecentSalesHelper (@date datetime) WITH RECOMPILE AS
BEGIN
SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderID
AND h.OrderDate > @date -- @date is unchanged from compile time,
-- so a good plan is obtained.
END

Envisagez la collecte plus fréquente de statistiques pour les clés croissantes

Les colonnes de clé croissantes, telles que les colonnes IDENTITY ou date-heure représentant des horodatages réels, peuvent entraîner des statistiques imprécises dans les tables faisant l'objet de fréquentes opérations INSERT, car toutes les nouvelles valeurs se situent en dehors de l'histogramme. Envisagez la mise à jour fréquente des statistiques sur ces colonnes avec un traitement par lot si votre application semble obtenir des plans de requête inadaptés pour les requêtes présentant une condition sur la colonne de clé croissante. La fréquence à laquelle doit être exécuté le traitement batch dépend de votre application. Considérez une exécution quotidienne ou hebdomadaire, ou plus souvent si votre application le nécessite.

Utilisation de la mise à jour asynchrone des statistiques si la mise à jour synchrone provoque un retard indésirable

Si vous disposez d'une base de données volumineuse et d'une charge de travail OLTP, lorsque l'option AUTO_UPDATE_STATISTICS est activée, certaines transactions qui s'exécutent normalement en une fraction de seconde peuvent très occasionnellement prendre plusieurs secondes, voire davantage, car elles entraînent la mise à jour des statistiques. Si vous souhaitez éviter le risque d'un tel retard, activez l'option AUTO_UPDATE_STATISTICS_ASYNC. Pour les charges de travail avec des requêtes dont l'exécution est longue, l'obtention du meilleur plan est plus importante qu'un retard occasionnel lors de la compilation. Dans ces cas, utilisez une mise à jour automatique synchrone plutôt qu'une mise à jour automatique asynchrone des statistiques.

Résumé

SQL Server 2005 offre de nombreuses améliorations en termes de fonctionnalités de gestion des statistiques. En particulier, vous pouvez utiliser la création et la mise à jour automatiques des statistiques afin d'obtenir de bons plans de requête dans la plupart des cas. Lorsque les statistiques automatiques avec la fréquence d'échantillonnage par défaut ne sont pas suffisantes, vous pouvez contrôler explicitement la fréquence d'échantillonnage des statistiques, ainsi que l'heure de création et de mise à jour. Si vous observez un plan de requête qui semble non optimal dans le cadre des statistiques ou de l'estimation des coûts, envisagez l'application des méthodes décrites dans ce document.

Références

[Mar04] Arun Marathe, Problèmes de la compilation par lot, de la recompilation et de la mise en cache des plans dans SQL Server 2005,
https://www.microsoft.com/france/technet/prodtechnol/sql/2005/recomp.mspx, juillet 2004.

Statistiques utilisées par l'optimiseur de requête dans Microsoft SQL Server 2005 : Microsoft TechNet SQL Server TechCenter

Sur cette page

Données statistiques dans SQL Server 2005

Récapitulatif des fonctionnalités de statistiques

Nouveautés concernant les statistiques dans SQL Server 2005

Définitions

Statistiques collectées par SQL Server 2005

Création et affichage de statistiques : exemple

Création de statistiques avec SQL Server 2005

Gestion des statistiques dans SQL Server 2005

Statistiques de résumé de chaîne

Statistiques sur les colonnes calculées

Statistiques sur les colonnes CLR de type défini par l'utilisateur

Statistiques et vues indexées

Méthodes recommandées pour la gestion des statistiques

Utilisez la création automatique et la mise à jour automatique des statistiques

Si nécessaire, utilisez de manière sélective les statistiques FULLSCAN

Évitez l'utilisation de variables locales dans les requêtes.

Envisagez de limiter l'utilisation de TVF (fonctions à valeurs de tables) multi-instruction et de variables de tables.

Les expressions non pliables et les fonctions scalaires intégrées peuvent entraîner des valeurs devinées

Utilisez des statistiques multi-colonnes lorsque les requêtes présentent des conditions multi-colonnes.

Soyez attentif à toute situation dans laquelle SQL Server revient à la méthode consistant à deviner la sélectivité

Évitez la modification des paramètres de procédure stockée dans un SP avant toute utilisation dans une requête.

Envisagez la collecte plus fréquente de statistiques pour les clés croissantes

Utilisation de la mise à jour asynchrone des statistiques si la mise à jour synchrone provoque un retard indésirable

Résumé

Références

Ressources supplémentaires