1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
| with
stat as (
select
s.object_id
, s.name as stat_name
, c.column_id
, c.name as column_name
, s.auto_created
from sys.stats s
inner join sys.objects o on o.object_id= s.object_id
inner join sys.stats_columns sc on sc.object_id = s.object_id and sc.stats_id=s.stats_id
inner join sys.columns c on c.column_id=sc.column_id and c.object_id=sc.object_id
where o.is_ms_shipped = 0
and sc.stats_column_id =1 --seule la 1ere colonne de la stat est importante
)
,stat_index as (
select object_id, column_id, column_name, string_agg (stat_name,', ') as Idx_names, count(1) as Nb_index_With_same_fisrt_column
from stat
where auto_created = 0
group by object_id, column_id, column_name
)
, stat_WA as (
select object_id, column_id, stat_name as Auto_stat_names
from stat
where auto_created = 1
)
select
OBJECT_NAME(i.object_id) as table_name
, i.column_name as first_column_name
, i.Nb_index_With_same_fisrt_column
, i.Idx_names
, s.Auto_stat_names
, ' DROP STATISTICS '+ OBJECT_SCHEMA_NAME(i.object_id)+'.' +OBJECT_NAME(i.object_id)+ '.'+ s.Auto_stat_names as SQL_command
from stat_index i
inner join stat_WA s on s.column_id= i.column_id and s.object_id = i.object_id
order by table_name, first_column_name
; |
Statistiques en double - perturbation du plan d'execution
Répondre avec citation
Envoyé par Michel.Priori
Partager