Auto Create Statistics : Création automatique des statistiques d’index

Lors des formations administration SQL Server ou développement SQL Server, je mets l’accent sur l’importance des index dans une base de données. Je ne vais pas revenir dessus dans cet article.

Lors de ces formations, et lors d’interventions chez des clients, j’essaie de faire passer le message comme quoi les statistiques d’index sont aussi importantes que l’index en lui même … Voire plus, puisque ces statistiques vont permettre à l’optimiseur de requête de SQL Server de déterminer un plan d’exécution. L’optimiseur va devoir faire des choix sur l’utilisation ou non des index, le type de parcours d’index (seek ou scan), etc …

Pour choisir le “meilleur” plan d’exécution  (hum hum, tâche difficile, je vous renvoie à l’intervention du Dr DeWitt lors des keynotes du 3eme du du PASS 2010 !!!), l’optimiseur va prendre en compte le nombre de lignes que va renvoyer l’index, le cout des IO, etc…

Et encore faut-il que ces informations soient correctes !

C’est pourquoi, dans une très grande majorité des cas, il est vivement conseillé de conserver le paramètre Auto Update Statistics à Vrai sur vos bases de données.

Cette option permet à SQL Server de “garder à jour” les statistiques d’index. Pour des tables de faible volumétrie (<= 500 enregistrements) il faut au minimum 500 mouvements de données. Que ce soit 500 lignes différentes ou bien 500 updates sur la même ligne … Pour des tables plus importantes, il faut que 20% du nombre d’enregistrements + 500 aient été modifiés pour que les statistiques soient mises à jour.

Voici quelques requêtes SQL qui vont nous permettre de mettre en évidence ce comportement.

Nous allons créer une base de test avec 1 seule table (10 000 lignes pour simplifier le calcul des 20%   ), une clé primaire et un index non cluster.

Use master;
GO

CREATE DATABASE [TestStatistics]
GO

USE [TestStatistics]
GO

CREATE TABLE [dbo].[Person](
	[BusinessEntityID] [int] NOT NULL,
	[FirstName]        [nvarchar](100) NOT NULL,
	[LastName]         [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_Person_BusinessEntityID] PRIMARY KEY CLUSTERED
(
	[BusinessEntityID] ASC
)
) ON [PRIMARY]
GO

INSERT INTO [dbo].[Person]
(
	[BusinessEntityID],
	[FirstName],
	[LastName]
)
SELECT TOP 10000
	[BusinessEntityID],
	[FirstName],
	[LastName]
FROM AdventureWorks2008.person.Person;
go

CREATE NONCLUSTERED INDEX [IX_FirstName]
ON [dbo].[Person] ( [FirstName] ASC )
ON [PRIMARY]
GO

Afin de visualiser la date de mise à jour des statistiques, nous utiliserons la requête suivante :

SELECT   object_name(object_id) AS [Table Name]
       , name AS [Index Name]
       , stats_date(object_id, stats_id) AS [Last Updated]
FROM sys.stats
WHERE name = 'IX_FirstName'

 

Recherchons une information :

On peut consulter les informations sur l’utilisation d’un index au travers de la requête suivante :

SELECT DB_NAME(database_id) as Database_name,OBJECT_NAME(object_id) as Index_name,index_id
       ,user_lookups,user_scans,user_seeks,user_updates
       ,last_user_lookup,last_user_scan,last_user_seek,last_user_update
FROM sys.dm_db_index_usage_stats
WHERE object_id = OBJECT_ID('dbo.person')

1 seul user_seek sur l’index d’indice 2 (l’indice 1 étant l’index cluster), ce que confirme le plan d’exécution :

Mettons 20% des données à jour :

SET ROWCOUNT 2000

UPDATE dbo.Person
SET FirstName = 'Christophe'

Les statistiques n’ont pas évolué :

Et quelque chose me chagrine : est-ce que ma requête est correctement traitée par SQL Server ??? Est-ce que le plan d’exécution est correct ?

Le plan d’exécution réel montre clairement que l’optimiseur pensait retrouver plus ou moins 10 enregistrements qui correspondaient ) FirstName = ‘Christophe’ alors que le nombre d’enregistrements effectivement renvoyés est bien 2000 (mes lignes mises à jour).

Cette requête a engendré plus de 4000 lectures logiques :

Alors que 66 lectures suffisent avec un bête table scan (ou cluster index scan) :

Le fait que les statistiques soient incorrectes a trompé l’optimiseur qui est resté sur le plan d’exécution avec Nested Loops, passant à côté d’une belle économie d’IO en changeant le plan d’exécution pour utiliser un cluster Index Scan.

Passons maintenant la limite des 20% + 500 enregistrements (510 pour avoir un peu de marge ….)

SET ROWCOUNT 510

UPDATE dbo.Person
SET FirstName = 'Christophe'

Voyons si les statistiques sont à jour :

Et surprise, les statistiques ne sont pas à jour !!!!

En fait, SQL Server ne va effectivement mettre à jour ces statistiques que lorsque l’optimiseur de requête va en avoir besoin, soit à la prochaine exécution de la requête Select :

Le plan d’exécution a bien basculé sur un cluster index scan

car les statistiques que l’optimiseur a utilisées pour bâtir le plan d’exécution sont a présent correctes :

Notez que ma requête ne renvoie que 2000 lignes, car mon second update a porté sur des lignes déjà mises à jour, mettant en lumière le fait que SQL Server ne se préoccupe que nu nombre de mouvements, peu importe qu’ils soient effectués sur des enregistrements identiques ou non.

Cette valeur de 20% + 500 enregistrements commence à être réellement problématique sur de très grosses tables (pensez à une table avec 500 000 000 d’enregistrements !). D’où l’importance de réindexer une table, ce qui a pour effet de défragmenter (suppressions d’enregistrements, pointeurs avant …) mais aussi de mettre à jour les statistiques (forcément). Mais la ré indexation coute “cher” en temps et en ressources, ce que l’activité de certaines bases ne peut permettre. Il faut donc ajouter aux tâches de maintenance pour la mise à jour des statistiques (lisez aussi l’article de David).

Pour les curieux qui voudraient savoir d’où vient l’estimation de 10.6667 enregistrements renvoyés après le premier update de 2000 lignes, il suffit d’aller voir l’histogramme, dans SSMS, click droit sur les statistiques ou bien DBCC SHOWSTATISTICS :

Avant :

et après mise à jour des statistiques :

Si vous rencontrez des plans d’exécutions qui vous paraissent incorrect, ayez le réflexe d’aller voir la date de mise à jour des statistiques…

Si vous utilisez les DMV/DMF sys.dm_db_index_…. notez bien que le fait de mettre hors ligne une base, de la détacher, de la “fermer” (option auto_close à ON) va réinitialiser ces statistiques.

D’ici peu je publierais un article concernant les statistiques crées automatiquement par SQL Server …