[SQL Server] Statistiques : SQL Server et la mise-à-jour automatique de statistiques

Ce billet aborde comment SQL Server gère les statistiques et surtout comment il les met automatiquement à jour. Il permet également de comprendre à quel point le fait de disposer des statistiques à jour est très important dans le cadre de l’optimisation de requêtes.

Vous pouvez jeter un coup d’œil ici pour comprendre comment les statistiques peuvent être lues au sein d’un plan d’exécution (sous-section Statistiques dans la section Eléments d’optimisation).

Notions de statistiques

Les statistiques sont des métadonnées qui permettent au moteur SQL d’être capable d’estimer combien d’enregistrements va retourner une requête comportant une clause de recherche sur cette colonne de sorte qu’il soit en mesure de pouvoir adopter la meilleure stratégie possible concernant le traitement de ladite requête.

Les statistiques sont stockées au sein des tables systèmes de la base de données au sein de laquelle elles ont été créées.

Les statistiques peuvent être créées notamment lors de la création d’un index (en admettant que l’option Auto Create Statistics au niveau base de données soit activée, ce qui est le cas par défaut) ou via T-SQL en utilisant CREATE STATISTICS.

L’affichage d’informations relatives à des statistiques sur une colonne via T-SQL est possible :

Comment SQL Server gère les statistiques ? Comment les met-il à jour automatiquement ?

    Fonctionnement et gestion des mises-à-jour automatiques de statistiques

Lorsque des lignes de données sont ajoutées, modifiées ou supprimées au sein d’une table, SQL Server effectue la même chose sur les lignes correspondantes stockées au sein de tous les indexes concernés. En théorie, quand l’option auto-update stats de la table cible est activée, SQL Server met automatiquement à jour les statistiques des indexes concernés de façon à ce que ses informations (nombre de lignes, tailles, etc…) représentent correctement la réalité. Dans la pratique, la musique en est tout autre.

Chaque fois qu’une modification est faite sur une ligne de données, SQL Server utilise la colonne rcmodified pour tracer ladite modification. Cette colonne est située au sein d’une table système appelée :

  • sys.rowsetcolumns avant SQL Server 2008.
  • sys.sysrscols (un mix entre sys.rowsetcolumns et sys.hobtcolumns) à partir de SQL Server 2008.

Cependant, Microsoft indique que des seuils sont utilisés par SQL Server pour effectuer ou non une mise-à-jour automatique des statistiques. Concrètement, il n’y a(ura) pas de mise-à-jour automatique d’une statistique spécifique si :

  • Lors de la dernière mise-à-jour (ou création) de ladite statistique, la table MaTable a plus de 500 lignes, et la valeur de colmodctr de la colonne sur laquelle a été créée la statistique cible a été changée avec une valeur supérieure à 20% du nombre de lignes + 500, après les modifications effectuées dans
    la table.
  • La volumétrie de la table
  • Lors de la dernière mise-à-jour (ou création) d’une statistique, la table a 500 lignes ou moins, et la valeur de colmodctr de la colonne sur laquelle a été créée la statistique cible a été changée avec une valeur supérieure à 500.
  • La volumétrie de la table reste inférieure à 1 lignes.
Les compteurs colmodctr (Column Modification Counters) ne sont pas visibles de l’utilisateur. Ces compteurs sont seulement disponibles au sein de l’architecture interne de SQL Server pour le processeur de requêtes.

Le fait que SQL Server ne fasse pas une mise-à-jour automatique instantanée des statistiques peut avoir un impact non-négligeable sur les performances d’une requêtes, dès lors que l’optimiseur est amené à utiliser un plan d’exécution qui tient compte de statistiques dont la version date d’avant la mise-à-jour automatique par SQL Server.

Quand SQL Server lance la mise-à-jour automatique des statistiques, si la table cible a une volumétrie supérieure à 8 Mo, le taux d’échantillonnage par défaut sera utilisé. Le taux d’échantillonnage (sample) correspond à la quantité approximative de lignes dans une table à utiliser par l’optimiseur de requêtes au cours de la mise-à-jour de statistiques. Par conséquent, dans la mesure où une partie d’une table est scannée, il est possible que les informations pertinentes d’une ou plusieurs colonnes concernées ne soient pas capturées, ce qui peut, par exemple, donner des statistiques qui ne fournissent pas avec précision la distribution des données au sein des colonnes.

De plus, la détermination de l’échantillon de lignes de données à scanner n’est pas faite de façon aléatoire[1]. Cela signifie que dès que, lors d’une mise-à-jour automatique de statistiques, SQL Server scanne une première fois des pages (et donc toutes les lignes de données de chaque page traitée) d’une table (ou d’un index), il sera amené à les réutiliser, ce qui peut conduire à des informations statistiques ignorées durant la mise-à-jour automatique et/ou une mise-à-jour des informations statistiques déjà à jour.

C’est donc pour cette raison qu’il est de bon ton d’utiliser un plan de maintenance contenant un job de mise-à-jour de toutes les statistiques des tables (via FULLSCAN ou SAMPLE 100 PERCENT)
au sein d’un plan de maintenance exécutant des tâches d’optimisation (quasi-)quotidiennes (i.e., défragmentation d’indexes, etc…).

Les modes de mise-à-jour automatique de statistiques

Pour finir, SQL Server met automatiquement à jour les statistiques selon 2 modes :

  • Synchrone (par défaut) à L’optimiseur de requêtes vérifie si les statistiques sont à jour, puis les met éventuellement à jour lorsqu’elles sont utilisées par des requêtes qu’il est en train de traiter. La vérification se fait avant la compilation de la requête cible.
  • Asynchrone à Si l’optimiseur tombe sur des statistiques pas à jour, il continue quand même à compiler et exécuter des requêtes avec lesdites statistiques. Cela lui permet, d’une certaine façon, de gagner du temps lors du pré-traitement d’une requête.

Pour connaître les options de synchronisation des mises-à-jour automatique de chaque base de données d’une instance SQL Server, vous pouvez exécuter la requête suivante :

SELECT name AS "Name",
   is_auto_create_stats_on AS "Auto Create Stats",
   is_auto_update_stats_on AS "Auto Update Stats",
   is_auto_update_stats_async_on AS "Asynchronous Update"
FROM sys.databases
GO

Vous pouvez filtrer les bases de données en spécifiant le nom de la (ou les) base(s) souhaitée(s) dans une clause WHERE.

Pour « jouer » avec ces 2 modes, il suffit d’ajuster la valeur de l’option AUTO_UPDATE_STATISTICS (par défaut, à OFF) :

-- Activation préalable de AUTO_UPDATE_STATISTICS si ce n'est déjà fait
ALTER DATABASE MaBase SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE MaBase SET AUTO_UPDATE_STATISTICS_ASYNC ON
GO
sp_helpdb MaBase
GO
La mise-à-jour automatique…

  • … synchrone des statistiques est plus recommandée si :
    • Des opérations impactant significativement les informations relatives à la distribution de données (bulk-insert, troncature,…) sont utilisées, ce qui peut altérer l’efficacité d’un (des) plan(s) d’exécution, et donc rallonger les temps de réponse des requêtes en cas de statistiques obsolètes.
  • … asynchrone des statistiques peut être envisagée si :
    • Un certain nombre de requêtes sont régulièrement utilisées pour travailler sur des tables qui changent peu fréquemment ou utilisent pratiquement toujours le(s) même(s) plan(s) d’exécution en cache sans que cela ne pose problème.
    • Des requêtes sont fréquemment ralenties par la longueur d’une mise-à-jour automatique des statistiques par l’optimiseur. Cela peut arriver lorsqu’un gros volume d’informations statistiques doit être mis à jour, créant ainsi de longs délais d’attente pour les requêtes, et donc des blocages.

En règle générale, il est de bon ton de laisser le mode synchrone activé, et de ne le changer que si cela est vraiment nécessaire, après tests.

Tests de validation de la mise-à-jour automatique des statistiques

Etapes préliminaires

Les éléments théoriques ayant déjà été abordés plus haut, nous allons ici effectuer quelques démonstrations de l’utilité de la mise-à-jour automatique des statistiques.

  • Avant de commencer, activation, si ce n’est déjà le cas, l’option AUTO_UPDATE_STATISTICS de la base de données (dans notre cas, MaBase) où seront effectués les tests :
IF (SELECT COUNT(*) FROM sys.databases WHERE name ='MaBase'AND is_auto_create_stats_on = 0) = 0
BEGIN
 ALTER DATABASE MaBase SET AUTO_UPDATE_STATISTICS ON
END
GO

On s’assure également que l’option d’auto-création de statistiques est activée pour la base MaBase.

  • Création d’une table MaTable et insertions de 5000 enregistrements :
CREATE TABLE MaTable(MaTable_ID INT IDENTITY(1,1) NOT NULL, MaTable_Nom NVARCHAR(20) NULL, MaTable_Desc NVARCHAR(128) NULL)
GO
INSERT INTO MaTable(MaTable_Nom,MaTable_Desc)
 VALUES('Nom','Description')
GO 5000
  • Création des indexes (et implicitement des statistiques) :
CREATE CLUSTERED INDEX IXC_MaTable_ID
   ON MaTable(MaTable_ID)
GO
CREATE NONCLUSTERED INDEX IX_MaTable_Nom
   ON MaTable(MaTable_Nom)
GO

Notez que si, lors de la création de la table, vous créez une clé primaire, SQL Server créera automatiquement un index en cluster sur la colonne concernée, et ses statistiques associées. Notre exemple se veut volontairement simpliste.

  • Consultation de la date de dernière mise-à-jour des statistiques (avec DBCC SHOW STATISTICS, STATS_DATE ou, à partir de SQL Server 2012 SP1, la DMV sys.dm_db_stats_properties) :
DBCC SHOW_STATISTICS (MaTable,IXC_MaTable_ID)
GO
DBCC SHOW_STATISTICS (MaTable,IX_MaTable_Nom)
GO

Voici un extrait des informations qui nous intéressent :



Invalidation des statistiques

Pour faire suite à la dernière étape préliminaire, insérons 10 000 enregistrements :

  • Si nous relançons DBCC SHOW STATISTICS sur les indexes concernés (ou autre méthode similaire), nous obtenons le même résultat qu’avant :



SQL Server n’a pas mis-à-jour les statistiques automatiquement, malgré l’ajout en masse d’enregistrements. Comment expliquer cela ? Pour rebondir sur ce qui a déjà été dit plus haut, dans ce billet, des seuils sont utilisés par SQL Server pour effectuer ou non une mise-à-jour automatique des statistiques. Dans le contexte de notre exemple, sachant que lors de la création des statistiques, la table MaTable avait plus de 500 lignes, il n’y a pas eu de mise-à-jour automatique des statistiques car la valeur de colmodctr de la colonne de la statistique cible a été changée avec une valeur supérieure à 20% du nombre de lignes + 500, après les modifications effectuées dans MaTable.

Il est facile de déduire la valeur changée en effectuant le calcul suivant :

(5000 * 0.20) + 500 = 1500

En dépassant les 1500 modifications (du fait de nos 10 000 insertions en masse), la mise-à-jour automatique des statistiques n’a donc pas été faite par SQL Server.

Mise-à-jour automatique des statistiques

Comme évoqué plus haut dans ce billet, c’est l’optimiseur de requêtes qui lance la mise-à-jour des statistiques avant la phase de compilation d’une requête. Si un plan d’exécution n’existe pas en cache (nécessitant alors une compilation de la requête), et que les statistiques à utiliser ne sont pas à jour, SQL Server mettra automatiquement à jour les statistiques, juste avant la mise-en-cache du plan d’exécution concerné. En revanche, s’il y a déjà un plan d’exécution en cache, il mettra à jour ses statistiques avant réutilisation.

Lançons une requête de mise-à-jour d’une ligne de données sur MaTable :

UPDATE MaTable
SET MaTable_Nom='Kikoolol'
WHERE MaTable_ID=1245
GO

Vérifions les informations de mise-à-jour des statistiques :



On peut noter que les statistiques de l’index IXC_MaTable_ID (de la colonne MaTable_ID) ont été les seules mises à jour. Cela laisse supposer que l’optimiseur de requêtes n’a utilisé que l’index IXC_MaTable_ID (et ses statistiques) pour le traitement de la requête. On peut vérifier cela en jetant un coup d’œil au plan d’exécution de la requête qui confirme nos suppositions :


Limites de la mise-à-jour automatique des statistiques

Le précédent test signifie-t-il pour autant que la mise-à-jour automatique des statistiques est 100% pertinente à chaque exécution d’une requête par l’optimiseur ? Pas forcément, notamment à cause du sampling, SQL Server étant amené à utiliser un taux d’échantillonnage par défaut pour traiter une portion de lignes de données, si la table « pèse » plus de 8 Mo, comme évoqué plus haut dans ce billet.

Pour valider ces assertions, vérifions la volumétrie de MaTable  (< 8 Mo):

sp_spaceused 'MaTable'
GO


Insérons, par exemple, 200 000 lignes supplémentaires :

INSERT INTO MaTable(MaTable_Nom,MaTable_Desc)
 VALUES('Nom','Description')
GO 200000

Voici ce que donnent les informations de volumétrie de MaTable (> 8 Mo) :


Maintenant, effectuons une mise-à-jour

UPDATE MaTable
SET MaTable_Nom='Kikoolol'
WHERE MaTable_ID=120202
GO

Et enfin, lancement la vérification des mises-à-jour des statistiques pour IXC_MaTable_ID :


On peut noter que 133225 lignes sont « samplées » sur 215000… D’où l’importance d’avoir un plan de maintenance disposant d’un job de mise-à-jour de statistiques en mode FULLSCAN ou SAMPLE 100 PERCENT pouvant être exécuté en soirée ou dans la nuit.

Pour aller plus loin…

Pour consulter d’autres billets relatifs aux statistiques, gardez notamment un oeil iciici et .


Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s