{"id":452,"date":"2010-01-28T18:09:28","date_gmt":"2010-01-28T17:09:28","guid":{"rendered":"http:\/\/blog.capdata.fr\/?p=452"},"modified":"2022-12-01T17:57:24","modified_gmt":"2022-12-01T16:57:24","slug":"sql-server-retrouver-les-tables-dont-les-stats-ne-sont-plus-compilees-en-automatique","status":"publish","type":"post","link":"https:\/\/blog.capdata.fr\/index.php\/sql-server-retrouver-les-tables-dont-les-stats-ne-sont-plus-compilees-en-automatique\/","title":{"rendered":"Retrouver les tables dont les stats ne sont plus compil\u00e9es en automatique"},"content":{"rendered":"\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>

Quand on tombe sur un probl\u00e8me de performance de requ\u00eate, il faut entre autres choses v\u00e9rifier si les statistiques sur les tables ont bien \u00e9t\u00e9 recompil\u00e9es r\u00e9cemment. En effet, ce n’est pas parce que les options ‘auto create statistics<\/em>‘ et ‘auto update statistics<\/em>‘ sont activ\u00e9es pour une base que ses tables et indexes ont toujours des statistiques \u00e0 jour.<\/p>\n

On va cr\u00e9er une base de test pour d\u00e9montrer ce point:<\/p>\n

create database test_statistics<\/span><\/pre>\n
On va v\u00e9rifier si les options de cr\u00e9ation et de mise \u00e0 jour auto sont bien activ\u00e9es<\/p>\n
select databasepropertyex('test_statistics','IsAutoCreateStatistics')\r\nselect databasepropertyex('test_statistics','IsAutoUpdateStatistics')<\/span><\/pre>\n
1\r\n1<\/em><\/span><\/pre>\n
On cr\u00e9\u00e9 un peu de volum\u00e9trie et on ajoute les indexes clusteris\u00e9s<\/p>\n
use test_statistics\r\ncreate table STATS1(a numeric identity, b varchar(400))\r\ncreate table STATS2(a numeric identity, b varchar(400))\r\ninsert into STATS1 values (replicate('a',400))\r\ngo 1000\r\ninsert into STATS2 values (replicate('a',400))\r\ngo 1000\r\nalter table STATS1 add constraint PK_STATS1 primary key (a)\r\nalter table STATS2 add constraint PK_STATS2 primary key (a)<\/span><\/pre>\n
On fixe ensuite la date de derni\u00e8re mise \u00e0 jour des stats pour les deux tables avec STATS_DATE()<\/p>\n
select object_name(object_id), stats_date(object_id,stats_id) FROM sys.stats S\r\ninner<\/span> join sys.tables T on T.object_id = S.object_id\r\n\r\n<\/span>STATS1\u00a0\u00a0 \u00a02010-01-28 17:41:45.617\r\nSTATS2\u00a0\u00a0 \u00a02010-01-28 17:41:45.963<\/em>\r\n<\/span><\/pre>\n
A partir de l\u00e0, on va forcer le calcul des stats en NORECOMPUTE <\/strong>sur STATS1 seulement et rev\u00e9rifier les dates de stats. NORECOMPUTE <\/strong>aura pour effet de dire \u00e0 SQL Server de ne plus compiler en auto sur cette table.<\/p>\n
update statistics dbo.STATS1 PK_STATS1 WITH NORECOMPUTE<\/span>\r\n\u00a0Command(s) completed successfully.<\/span>\r\n<\/span><\/em>\r\nselect object_name(S.object_id), stats_date(S.object_id,S.stats_id) FROM sys.stats S\r\ninner join sys.tables T on T.object_id = S.object_id\r\n<\/span>STATS1\u00a0\u00a0 \u00a02010-01-28<\/span> 17:42:14.350 <\/strong><\/span><\/em><\/span>\r\nSTATS2\u00a0\u00a0 \u00a02010-01-28 17:41:45.963<\/span><\/em>\r\n<\/pre>\n
La date pour STATS1 a bien \u00e9t\u00e9 modifi\u00e9e. OK
\nOn va g\u00e9n\u00e9rer quelques perturbations identiques aux niveau des deux tables comme ajouter une colonne, supprimer des lignes, etc de mani\u00e8re \u00e0 d\u00e9clencher statman<\/strong>, l’utilitaire de recompilation auto des stats… On le verra passer dans une session Profiler:<\/p>\n
\"\"<\/a><\/p>\n
Si\u00a0 on rev\u00e9rifie maintenant les dates de mise \u00e0 jour:<\/p>\n
select object_name(S.object_id), stats_date(S.object_id,S.stats_id) FROM sys.stats S\r\ninner join sys.tables T on T.object_id = S.object_id<\/span>\r\nSTATS1\u00a0\u00a0 \u00a02010-01-28 17:42:14.350\r\n<\/span>STATS2\u00a0\u00a0 \u00a02010-01-28 <\/span>17:47:59.630<\/strong><\/span><\/em><\/span><\/pre>\n
Cette fois seule STATS2 a \u00e9t\u00e9 modifi\u00e9e. Il faudra donc que je pr\u00e9voie de mettre \u00e0 jour manuellement STATS1 \u00e0 intervalles r\u00e9guliers, 1 fois par jour la nuit par exemple.<\/p>\n
Conclusion <\/strong>pour retrouver sur une base les tables qui sont pass\u00e9es en norecompute au niveau des stats:<\/p>\n
select T.name 'tablename', S.name 'statsname', S.no_recompute\r\nfrom sys.tables T\r\nINNER JOIN sys.stats S on S.object_id = T.object_id\r\nWHERE S.no_recompute = 1<\/span>\r\n\r\ntablename\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 statsname                        no_recompute\r\n-------------------  -------------------------------  ------------------------------\r\nSTATS1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PK_STATS1                       1\r\n<\/span>\r\n<\/span><\/em><\/pre>\n
A+ [David B.]<\/em><\/span><\/p>\n
 <\/span><\/em><\/p>\n