Retrouver les tables dont les stats ne sont plus compilées en automatique

Quand on tombe sur un problème de performance de requête, il faut entre autres choses vérifier si les statistiques sur les tables ont bien été recompilées récemment. En effet, ce n’est pas parce que les options ‘auto create statistics‘ et ‘auto update statistics‘ sont activées pour une base que ses tables et indexes ont toujours des statistiques à jour.

On va créer une base de test pour démontrer ce point:

create database test_statistics

On va vérifier si les options de création et de mise à jour auto sont bien activées

select databasepropertyex('test_statistics','IsAutoCreateStatistics')
select databasepropertyex('test_statistics','IsAutoUpdateStatistics')

1
1

On créé un peu de volumétrie et on ajoute les indexes clusterisés

use test_statistics
create table STATS1(a numeric identity, b varchar(400))
create table STATS2(a numeric identity, b varchar(400))
insert into STATS1 values (replicate('a',400))
go 1000
insert into STATS2 values (replicate('a',400))
go 1000
alter table STATS1 add constraint PK_STATS1 primary key (a)
alter table STATS2 add constraint PK_STATS2 primary key (a)

On fixe ensuite la date de dernière mise à jour des stats pour les deux tables avec STATS_DATE()

select object_name(object_id), stats_date(object_id,stats_id) FROM sys.stats S
inner join sys.tables T on T.object_id = S.object_id

STATS1    2010-01-28 17:41:45.617
STATS2    2010-01-28 17:41:45.963

A partir de là, on va forcer le calcul des stats en NORECOMPUTE sur STATS1 seulement et revérifier les dates de stats. NORECOMPUTE aura pour effet de dire à SQL Server de ne plus compiler en auto sur cette table.

update statistics dbo.STATS1 PK_STATS1 WITH NORECOMPUTE
 Command(s) completed successfully.

select object_name(S.object_id), stats_date(S.object_id,S.stats_id) FROM sys.stats S
inner join sys.tables T on T.object_id = S.object_id
STATS1    2010-01-28 17:42:14.350 
STATS2    2010-01-28 17:41:45.963

La date pour STATS1 a bien été modifiée. OK
On va générer quelques perturbations identiques aux niveau des deux tables comme ajouter une colonne, supprimer des lignes, etc de manière à déclencher statman, l’utilitaire de recompilation auto des stats… On le verra passer dans une session Profiler:

Si  on revérifie maintenant les dates de mise à jour:

select object_name(S.object_id), stats_date(S.object_id,S.stats_id) FROM sys.stats S
inner join sys.tables T on T.object_id = S.object_id
STATS1    2010-01-28 17:42:14.350
STATS2    2010-01-28 17:47:59.630

Cette fois seule STATS2 a été modifiée. Il faudra donc que je prévoie de mettre à jour manuellement STATS1 à intervalles réguliers, 1 fois par jour la nuit par exemple.

Conclusion pour retrouver sur une base les tables qui sont passées en norecompute au niveau des stats:

select T.name 'tablename', S.name 'statsname', S.no_recompute
from sys.tables T
INNER JOIN sys.stats S on S.object_id = T.object_id
WHERE S.no_recompute = 1

tablename             statsname                        no_recompute
-------------------  -------------------------------  ------------------------------
STATS1                PK_STATS1                       1

A+ [David B.]

Continuez votre lecture sur le blog :

• Oracle et SQL Server: Les Statistiques (Benjamin VESAN) [OracleSQL Server]
• Nouveautés MySQL 8.0 : Les Histogrammes (Capdata team) [MySQL]
• Production SQL Server : Les Statistiques (Benjamin VESAN) [SQL Server]
• Repeupler un index FullText (Benjamin VESAN) [SQL Server]
• Insert et Update en une seule fois avec MERGE (Benjamin VESAN) [SQL Server]