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

Jeudi, janvier 28, 2010
By David Baffaleuf in SQL Server (dbaffaleuf@capdata-osmozium.com) [71 article(s)]

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 :




Cliquer pour partager cet article sur Viadeo
Cliquer sur "CAPTURER" pour sauvegarder cet article dans Evernote Clip to Evernote

Tags:

2 Responses to “Retrouver les tables dont les stats ne sont plus compilées en automatique”

  1. Thierry GASCARD

    Salut David,

    Bon article.
    Donc tu passes en statistiques manuelles sur SQL Server. Pourquoi avec les statistiques tu utilises le terme recompilation ?.

    Merci.

    Thierry.

    #99
  2. Hello,

    Effectivement ce n’est pas de la recompilation de code, comme des procédures stockées ou des triggers. On peut dire recalcul si tu veuxw mais le terme utilisé est aussi recompilation. Pour chaque table, il existe un seuil au delà duquel les stats vont être recompilées: modification importante de la répartition des valeurs dans les colonnes d’un index, par exemple. On parle de compilation parce que la conséquence est double. Des nouvelles stats vont être générées, et les traitements vont être recompilés avec ces nouvelles stats.

    A+ David.

    #106

Leave a Reply