Recherche de statistiques en double & DBCC SHOW_STATISTICS

[elsuket]

Bonjour,

Pour trouver la liste des statistiques en double dans une base de données, j'ai écrit la requête suivante :

Code :

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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
;WITH
	CTE_STAT_COLUMNS AS
	(
		SELECT		T.name AS table_name
				, S.name AS stat_name
				, C.name AS column_name
				, SC.stats_column_id
				, CASE
					WHEN I.name IS NULL THEN 'S'
					ELSE 'I'
				END AS stat_type
		FROM		sys.stats AS S
		INNER JOIN	sys.stats_columns AS SC
					ON S.object_id = SC.object_id
					AND S.stats_id = SC.stats_id
		INNER JOIN	sys.COLUMNS AS C
					ON SC.object_id = C.object_id
					AND SC.column_id = C.column_id
		INNER JOIN	sys.TABLES AS T
					ON C.object_id = T.object_id
		LEFT JOIN	sys.indexes AS I
					ON S.name = I.name
	)
	, CTE_STAT_COLUMN_LIST AS
	(
			SELECT	table_name
				, stat_name
				, CAST(column_name AS varchar(max)) AS stat_column_list
				, stats_column_id
				, stat_type
			FROM	CTE_STAT_COLUMNS
			WHERE	stats_column_id = 1
		UNION ALL
			SELECT		SCL.table_name
					, SCL.stat_name
					, CAST(stat_column_list + ',' + SC.column_name AS varchar(max)) AS stat_column_list
					, SCL.stats_column_id + 1
					, SC.stat_type
			FROM		CTE_STAT_COLUMNS AS SC
			INNER JOIN	CTE_STAT_COLUMN_LIST AS SCL
						ON SC.table_name = SCL.table_name
						AND SC.stat_name = SCL.stat_name
						AND SC.stats_column_id = SCL.stats_column_id + 1
	)	
	, CTE_STAT_COLUMN_COUNT AS
	(
		SELECT	table_name
			, stat_name
			, MAX(stats_column_id) AS max_stats_column_id
		FROM	CTE_STAT_COLUMN_LIST
		GROUP	BY table_name, stat_name
	)	
SELECT		S.table_name
		, S.stat_name
		, I.stat_name AS index_name
		, S.stat_column_list
		, S.stats_column_id
FROM		CTE_STAT_COLUMN_LIST AS S
INNER JOIN	CTE_STAT_COLUMN_LIST AS I
			ON S.table_name = I.table_name
			AND S.stat_column_list = I.stat_column_list
			AND S.stat_type = 'S'
			AND I.stat_type = 'I'
INNER JOIN	CTE_STAT_COLUMN_COUNT AS SCCS
			ON S.table_name = SCCS.table_name
			AND S.stat_name = SCCS.stat_name
			AND S.stats_column_id = SCCS.max_stats_column_id
INNER JOIN	CTE_STAT_COLUMN_COUNT AS SCCI
			ON I.table_name = SCCI.table_name
			AND I.stat_name = SCCI.stat_name
			AND I.stats_column_id = SCCI.max_stats_column_id

Qui me retourne bien les doublons, mais lorsque je regarde le résultat de DBCC SHOW_STATISTICS à l'aide de la requête suivante :

Code :

1
2
DBCC SHOW_STATISTICS (maTable, _WA_Sys_uneStatDeColonneDe_maTable) WITH HISTOGRAM
DBCC SHOW_STATISTICS (maTable, uneStatDUnIndexDe_maTable) WITH HISTOGRAM

J'obtiens pour RANGE_HI_KEY des valeurs différentes (plus exactement de types de données différents) dans les deux cas, lorsque les doublons trouvés sont sur des clés de plus d'une colonne.

J'ai donc écrit la requête suivante :

Code :

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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
;WITH
	CTE_STAT_COLUMNS AS
	(
		SELECT		T.name AS table_name
				, S.name AS stat_name
				, C.name AS column_name
				, SC.stats_column_id
		FROM		sys.stats AS S
		INNER JOIN	sys.stats_columns AS SC
					ON S.object_id = SC.object_id
					AND S.stats_id = SC.stats_id
		INNER JOIN	sys.COLUMNS AS C
					ON SC.object_id = C.object_id
					AND SC.column_id = C.column_id
		INNER JOIN	sys.TABLES AS T
					ON C.object_id = T.object_id
		LEFT JOIN	sys.indexes AS I
					ON S.name = I.name
		WHERE		I.name IS NULL
	)
	, CTE_STAT_COLUMN_LIST AS
	(
			SELECT	table_name
				, stat_name
				, CAST(column_name AS varchar(max)) AS stat_column_list
				, stats_column_id
			FROM	CTE_STAT_COLUMNS
			WHERE	stats_column_id = 1
		UNION ALL
			SELECT		SCL.table_name
					, SCL.stat_name
					, CAST(stat_column_list + ',' + SC.column_name AS varchar(max)) AS stat_column_list
					, SCL.stats_column_id + 1
			FROM		CTE_STAT_COLUMNS AS SC
			INNER JOIN	CTE_STAT_COLUMN_LIST AS SCL
						ON SC.table_name = SCL.table_name
						AND SC.stat_name = SCL.stat_name
						AND SC.stats_column_id = SCL.stats_column_id + 1
	)	
	, CTE_STAT_COLUMN_COUNT AS
	(
		SELECT	table_name
			, stat_name
			, MAX(stats_column_id) AS max_stats_column_id
		FROM	CTE_STAT_COLUMN_LIST
		GROUP	BY table_name, stat_name
	),
	CTE_INDEX_COLUMNS AS
	(
		SELECT		T.name AS table_name
				, I.name AS index_name
				, C.name AS column_name
				, IC.index_column_id
		FROM		sys.indexes AS I
		INNER JOIN	sys.index_columns AS IC
					ON IC.object_id = I.object_id
					AND IC.index_id = I.index_id
		INNER JOIN	sys.COLUMNS AS C
					ON IC.object_id = C.object_id
					AND IC.column_id = C.column_id
		INNER JOIN	sys.TABLES AS T
					ON I.object_id = T.object_id
	)
	, CTE_INDEX_COLUMN_LIST AS
	(
			SELECT	table_name
				, index_name
				, CAST(column_name AS varchar(max)) AS index_column_list
				, index_column_id
			FROM	CTE_INDEX_COLUMNS
			WHERE	index_column_id = 1
		UNION ALL
			SELECT		ICL.table_name
					, ICL.index_name
					, CAST(index_column_list + ',' + IC.column_name AS varchar(max)) AS index_column_list
					, ICL.index_column_id + 1
			FROM		CTE_INDEX_COLUMNS AS IC
			INNER JOIN	CTE_INDEX_COLUMN_LIST AS ICL
						ON IC.table_name = ICL.table_name
						AND IC.index_name = ICL.index_name
						AND IC.index_column_id = ICL.index_column_id + 1
	)
	, CTE_INDEX_COLUMN_COUNT AS
	(
		SELECT	table_name
			, index_name
			, MAX(index_column_id) AS max_index_column_id
		FROM	CTE_INDEX_COLUMN_LIST
		GROUP	BY table_name, index_name
	)
SELECT		SCL.table_name
		, SCL.stat_name
		, ICL.index_name
		, SCL.stat_column_list
		, SCL.stats_column_id
FROM		CTE_STAT_COLUMN_LIST AS SCL
INNER JOIN	CTE_STAT_COLUMN_COUNT AS SCC
			ON SCL.table_name = SCC.table_name
			AND SCL.stat_name = SCC.stat_name
			AND SCL.stats_column_id = SCC.max_stats_column_id
INNER JOIN	CTE_INDEX_COLUMN_LIST AS ICL
			ON SCL.table_name = ICL.table_name
			AND SCL.stat_column_list = ICL.index_column_list
INNER JOIN	CTE_INDEX_COLUMN_COUNT AS ICC
			ON ICL.table_name = ICC.table_name
			AND ICL.index_name = ICC.index_name
			AND ICL.index_column_id = ICC.max_index_column_id

Mais j'observe la même chose.
A quoi cela peut-il être dû ?

@++

[mikedavem]

J'ai pas encore tes scripts j'avoue .. mais a tu fais un dbcc updateusage avant ?

++

[elsuket]

Je l'ai lancé sans conviction sur une BD de 3TB, je pense que ça va prendre quelques heures ...
Sans conviction parce qu'ici nous cherchons les doublons entre les statistiques d'une même table ...
Se peut-il donc que la définition des statistiques retournées par DBCC SHOW_STATISTICS en termes d'ordre des colonnes soit faussée ?
Si tel est le cas, pour quelle raison ?

J'avais compris que DBCC UPDATEUSAGE corrige les données que retourne la procédure stockée sp_spaceused.
En fait, je n'ai jamais noté de différence entre le nombre de lignes qu'on obtient en exécutant un COUNT(*) sur une table et ce que je peux obtenir à l'aide de la seconde requête que j'ai publiée ici.

Je comprends que le nombre de lignes ne soit pas mis à jour en temps réel, car cela demanderait un travail considérable sur les tables supportant les transactions d'une base de données OLTP.

En revanche sur une opération telle qu'un BULK INSERT, que l'on exécute :

- sp_spaceused pour la table qui subit cette opération
- la requête de mon billet pour la même table
- un COUNT(*) sur la table

On obtient strictement la même valeur.

@++

[mikedavem]

Tu pourrais nous donner un exemple de valeurs que tu trouves ?

Que cherches tu a faire avec cette requete qui te cherche les statistiques en double ?

PS : A y reflechir effectivement DBC UPDATEUSAGE ne devrait pas regler ton probleme

++

[elsuket]

En fait j'ai remarqué que le moteur de base de données ne supprime pas une statistique de colonne(s) qui a la même définition qu'un index sur la même table (même colonnes dans la clé, et même ordre des colonnes).

Cela n'est pas très gênant et ne donnera certainement pas un gain en performances énorme, mais je trouve qu'il est dommage d'avoir deux fois la même statistique sur la même table ...

@++

[SQLpro]

En fait le contenu des statistiques peut être différent car il n'est pas callculé au même instant. De plus si tu traîne ces stats depuis une ancienne version, genre 2000, la méthode de calcul des stats à été modifiée....

A +