Discussion :

[Mothership]

Bonjour à tous,

SQL2005 entreprise 9.0.3282

Je rencontre un problème lors d'un plan de maintenance effectuant une reconstruction de l'ensemble de nos indexs.

Ci dessous la commande TSQL (générée depuis l'assistant graphique):

ALTER INDEX [HLW046L2] ON [reflex].[HLW046P] REBUILD WITH ( FILLFACTOR = 80, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON )

note : La table HLW046P est une table de travail très fortement utilisée et très importante (<20 000 000.lignes * x.colonnes).
note ² : nos requêtes utilisent le mode curseur.

1ère question : lorsque les indexs de cette table sont reconstruits, une analyse une heure plus tard m'indique que les indexs sont toujours fragmentés (jusqu'à 90%) ce qui m'inquiète fortement.
J'ai lu dans un des tutoriels (très bien fait) de Frédéric Brouard que sans statistique à jour, les index sont déclarés obsolètes et non utilisés par les requêtes. Ici il ne s'agit que de l'analyse depuis la sys.dm_db_index_physical_stats qui j'espère vérifie les données de fragmentation à chaque sollicitation.
Suis je dans le faux, faut il faire la mise à jour des statistiques immédiatement après la reconstruction des index pour que sys.dm_db_index_physical_stats soit utilisable convenablement et ne retourne pas de données erronées ?

2nde question: malgré l'option ONLINE=ON, lorsque l'index est reconstruit, le schéma de la table change et provoque une erreur sur le curseur en cours (ci dessous le message d'erreur généré depuis notre applicatif). J'ai lu dans un autre tutoriel de Frédéric Brouard que le mode online permettait de garder en ligne l'ancien index. J'imagine qu'au moment de la bascule, online ou pas, on change d'index actif provoquant l'erreur ci dessous. Suis je dans le vrai ?

E--
2008/08/27 14:26:33 =>
[HFIF16][SQL Server.RFXCOOPQAS.reflex] Api Code [16943], Dbm Code [16943], SQLSTATE [42000], Cursor [HFIF16INT_ENTREE]
[Microsoft][SQL Native Client][SQL Server]Impossible d'achever l'opération de curseur parce que le schéma de la table a changé après la déclaration du curseur.
SELECT ITNINI, ITNSIE, ITCAPP, ITCINE, ITCRIE, ITDSIE FROM "REFLEX".HLW046P WHERE (ITNINI = ? AND ITCAPP = ? AND ITCINE = ?) ORDER BY ITNINI ASC, ITCAPP ASC, ITCINE ASC, ITNSIE ASC
4(3371)A('HL')A('16')

Si quelqu'un pouvait m'éclairer sur ces deux points ce serait zuper great

[SQLpro]

1) le rebuild remet à jour les statistiques. Par contre le defrag ne le fait pas.

2) très probable. Mais l'avez-vous au départ créé avec l'option ONLINE ?

conseil :
Utilisez plustôt un fillfactor de 92% et le PAD_INDEX. Au beson, auditez le nombre de split de page via perfmon.

Il est aussi très probable que la fragmentation vienne de l'usage de varchar dans les colonnes indexées comme dans la table. Cette dernière possède-t-elle un index cluster ? SI oui, quel sont les types de données de cet index IDENTITY compris ???

A +

[Mothership]

1) Merci pour cette réponse, je sens que notre plan de maintenance va être allégé.

2) Je ne sais pas si l'option online=off par le passé (depuis l'installation de la version 2005 SQL). Je prends la main peu à peu sur la partie SQL, sans réelle connaissance hormis une formation trop plan plan, hors de ma juridiction et sans apporter les bases TSQL donc je surnage un petit peu Notre éditeur invoque la phrase : "demandez à Microsoft pourquoi l'index ..." alors qu'ils semblent avoir un minimum d'expertise SQL.
Bref, ci dessous le code utilisé pour la création de la table, je ne sais pas si ça répond à ta question.

CREATE TABLE [reflex].[HLW046P](
[ITNINI] [numeric](4, 0) NOT NULL DEFAULT (0.0),
[ITNSIE] [numeric](7, 0) NOT NULL DEFAULT (0.0),
[ITCAPP] [varchar](2) NOT NULL DEFAULT (''),
[ITCINE] [varchar](2) NOT NULL DEFAULT (''),
[ITCRIE] [varchar](3) NOT NULL DEFAULT (''),
[ITDSIE] [varchar](256) NOT NULL DEFAULT (''),
PRIMARY KEY CLUSTERED
(
[ITNINI] ASC,
[ITNSIE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

[SQLpro]

Votre fragmentation excessive est très probablement due à l'usage systématique des VARCHAR qui est une imbécilité.
De multiples VARCHAR combiné à une clef primaire CLUSTER est un véritable gouffre à fragmentation s'il y a beaucoup d'UPDATE/INSERT/DELETE sur les tables.

Il est d'ailleurs parfaitement stupide d'utiliser du VARCHAR pour des petits littéraux, car le VARCHAR incorpore dans le stockage la longueur de la donnée stockée, donc augmente le coût su stockage. C'est hautement crétin pour du VARCHAR(2) par exemple !

Comme vous avez une version d'éditeur, la seule chose à faire est de vous retourner contre lui en lui demandant de modifier les varchar en CHAR le plus souvent possible, et notamment pour les colonnes :

• clef (primaire, unique, étrangères)
• indexées
• de longueur inférieures à 8
• fréquemment recherchées

(ou)

Voici un script que je joue en formation dans le cours d'optimisation que je donne à Orsys (SQO) afin de montrer ce phénomène :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
USE master;
GO
 
IF EXISTS(SELECT *
          FROM   master.sys.databases
          WHERE  name = 'DB_SQO')
   DROP DATABASE DB_SQO;
GO
 
CREATE DATABASE DB_SQO;
GO
 
USE DB_SQO;
GO
 
CREATE TABLE dbo.T_PROSPECT_PSP
(PSP_ID          INT NOT NULL PRIMARY KEY IDENTITY,
 PSP_NOM         VARCHAR(32),
 PSP_PRENOM      VARCHAR(25));
GO
 
DECLARE @I INT;
SET @I = 1;
WHILE @I <= 10000
BEGIN
   INSERT INTO dbo.T_PROSPECT_PSP VALUES ('DUPONT', 'Paul');
   SET @I = @I + 1;
END;
GO
 
EXEC sp_spaceused 'dbo.T_PROSPECT_PSP';
GO
 
/********************************************************************************************************
name             rows        reserved           data               index_size         unused             
---------------- ----------- ------------------ ------------------ ------------------ ------------------ 
T_PROSPECT_PSP   10000       328 KB             288 KB             16 KB              24 KB
********************************************************************************************************/
 
DBCC SHOWCONTIG ('dbo.T_PROSPECT_PSP');
GO
 
SELECT *
FROM   sys.dm_db_index_physical_stats 
           (DB_ID('DB_SQO'), OBJECT_ID('dbo.T_PROSPECT_PSP'), null, null, 'DETAILED') 
WHERE  index_level = 0;
GO
-- remarque : moins détaillé !!! (nécessite requête plus complexe...)
 
 
/********************************************************************************************************
DBCC SHOWCONTIG analyse la table 'T_PROSPECT_PSP'...
Table : 'T_PROSPECT_PSP' (2073058421) ; index ID : 1, base de données ID : 12
Analyse du niveau TABLE effectuée.
- Pages analysées................................: 36
- Extensions analysées..............................: 10
- Commutateurs d'extension..............................: 10
- Moyenne des pages par extension........................: 3.6
- Densité d'analyse [meilleure valeur:valeur réelle].......: 45.45% [5:11]
- Fragmentation d'analyse logique..................: 19.44%
- Fragmentation d'analyse d'extension...................: 30.00%
- Moyenne d'octets libres par page.....................: 40.4
- Densité de page moyenne (complète).....................: 99.50%
********************************************************************************************************/
 
SELECT CURRENT_TIMESTAMP AS DEBUT;
GO
 
-- mise à jour de 10% des lignes de la table, ajout de 9 caractères aux noms
UPDATE dbo.T_PROSPECT_PSP
SET    PSP_NOM = 'DUPONT-DUMOULIN'
WHERE  PSP_ID % 10 = 0;
GO
 
-- mise à jour de 10% des mêmes lignes de la table, ajout de 5 caractères aux prénoms
UPDATE dbo.T_PROSPECT_PSP
SET    PSP_PRENOM = 'Jean-Paul'
WHERE  PSP_ID % 10 = 0;
GO
 
SELECT CURRENT_TIMESTAMP AS FIN;
GO
 
-- éxecution : 250 ms (temps écoulé)
 
 
-- 10% des lignes ont une taille qui est passé de 10 à 24 caractères
-- soit une augmentation de 140% pour les lignes concernées
-- soit une augmentation de 14% pour la table...
 
EXEC sp_spaceused 'dbo.T_PROSPECT_PSP'
GO
 
/********************************************************************************************************
name             rows        reserved           data               index_size         unused             
---------------- ----------- ------------------ ------------------ ------------------ ------------------ 
T_PROSPECT_PSP   10000       584 KB             568 KB             16 KB              0 KB
********************************************************************************************************/
 
-- la différence est de 97% d'augmentation de volume et non 14% !!!
 
DBCC SHOWCONTIG ('dbo.T_PROSPECT_PSP');
GO
 
/********************************************************************************************************
DBCC SHOWCONTIG analyse la table 'T_PROSPECT_PSP'...
Table : 'T_PROSPECT_PSP' (1977058079); index ID = 1, base de données ID = 17
Analyse du niveau TABLE effectuée.
- Pages analysées................................: 71
- extensions analysées...........................: 14
- extensions commutées..........................: 70
- Moy des pages par extension...............: 5.1
- Densité d'analyse [meilleure valeur du compte réel].......: 12.68% [9:71]
- Fragmentation d'analyse logique..: 98.59%
- Fragmentation d'analyse d'extension..: 21.43%
- Moy octets libres par page.....................: 3814.3
- Densité de page moy (pleine)...........: 52.87%
********************************************************************************************************/
 
-- le nombre de "sauts" d'une extension à l'autre est passé de 5 à 70, soit une aumentation de 1400 %...
 
/********************************************************************************************************
VERSION CHAR
********************************************************************************************************/
 
CREATE TABLE dbo.T_PROSPECT2_PSP
(PSP_ID          INT NOT NULL PRIMARY KEY IDENTITY,
 PSP_NOM         CHAR(32),
 PSP_PRENOM      CHAR(25));
GO
 
DECLARE @I INT;
SET @I = 1;
WHILE @I <= 10000
BEGIN
   INSERT INTO dbo.T_PROSPECT2_PSP VALUES ('DUPONT', 'Paul');
   SET @I = @I + 1;
END;
GO
 
EXEC sp_spaceused 'dbo.T_PROSPECT2_PSP';
GO
 
/********************************************************************************************************
name              rows        reserved           data               index_size         unused             
----------------- ----------- ------------------ ------------------ ------------------ ------------------ 
T_PROSPECT2_PSP   10000        712 KB             696 KB             16 KB              0 KB
********************************************************************************************************/
 
DBCC SHOWCONTIG ('dbo.T_PROSPECT2_PSP');
GO
 
/********************************************************************************************************
DBCC SHOWCONTIG analyse la table 'T_PROSPECT2_PSP'...
Table : 'T_PROSPECT2_PSP' (2025058250); index ID = 1, base de données ID = 17
Analyse du niveau TABLE effectuée.
- Pages analysées................................: 87
- extensions analysées...........................: 16
- extensions commutées..........................: 16
- Moy des pages par extension...............: 5.4
- Densité d'analyse [meilleure valeur du compte réel].......: 64.71% [11:12]
- Fragmentation d'analyse logique..: 8.05%
- Fragmentation d'analyse d'extension..: 31.25%
- Moy octets libres par page.....................: 50.0
- Densité de page moy (pleine)...........: 99.38%
********************************************************************************************************/
 
SELECT CURRENT_TIMESTAMP AS DEBUT;
GO
 
-- les mêmes mise à jour
UPDATE dbo.T_PROSPECT2_PSP
SET    PSP_NOM = 'DUPONT-DUMOULIN'
WHERE  PSP_ID % 10 = 0;
GO
 
UPDATE dbo.T_PROSPECT2_PSP
SET    PSP_PRENOM = 'Jean-Paul'
WHERE  PSP_ID % 10 = 0;
GO
 
SELECT CURRENT_TIMESTAMP AS FIN;
GO
 
-- temps d'exécution : 93 ms soit 2,7 fois plus rapide...
 
EXEC sp_spaceused 'dbo.T_PROSPECT2_PSP';
GO
 
/********************************************************************************************************
name              rows        reserved           data               index_size         unused             
----------------- ----------- ------------------ ------------------ ------------------ ------------------ 
T_PROSPECT2_PSP   9940        712 KB             696 KB             16 KB              0 KB
********************************************************************************************************/
 
-- aucune influence
 
DBCC SHOWCONTIG ('dbo.T_PROSPECT2_PSP');
GO
 
/********************************************************************************************************
DBCC SHOWCONTIG analyse la table 'T_PROSPECT2_PSP'...
Table : 'T_PROSPECT2_PSP' (2025058250); index ID = 1, base de données ID = 17
Analyse du niveau TABLE effectuée.
- Pages analysées................................: 87
- extensions analysées...........................: 12
- extensions commutées..........................: 11
- Moy des pages par extension...............: 7.3
- Densité d'analyse [meilleure valeur du compte réel].......: 91.67% [11:12]
- Fragmentation d'analyse logique..: 0.00%
- Fragmentation d'analyse d'extension..: 16.67%
- Moy octets libres par page.....................: 50.0
- Densité de page moy (pleine)...........: 99.38%
********************************************************************************************************/
 
-- aucune influence
 
/********************************************************************************************************
  CONCLUSION
 
  Table en VARCHAR ...  
  Les données de cette table montrent :
  - un volume calculé de : 
      10 caractères *  9 000 lignes =  90 000 octets
    + 24 caractères *    999 lignes =  23 976 octets
    + entier 4 o    * 10 000 lignes =  40 000 octets
      TOTAL                         = 153 976 octets 
                                    = 151 Ko
  - pour un volume final de           568 Ko
 
  Table en CHAR ...  
  Les données de cette table montrent :
  - un volume calculé de : 
      57 caractères * 10 000 lignes = 570 000 octets
    + entier 4 o    * 1 0000 lignes =  40 000 octets
      TOTAL                         = 610 000 octets 
                                    = 596 Ko
  - pour un volume final de           696 Ko
 
  De plus : l'effort d'insertion est moindre...
 
********************************************************************************************************/

A +

[Mothership]

Merci pour toutes ces informations, cela apporte un peu d'eau à notre moulin.
Concernant les plans de maintenance et plus particulièrement la gestion des indexs et des statistiques, avez vous un tutoriel expliquant les bonnes méthodes pour la gestion d'une base de données assez conséquentes.
Je vais créer un autre poste, je termine celui ci.

Merci encore

ciao

[Mothership]

Juste pour information, ci dessous les infos sur la table :

EXEC sp_spaceused 'reflex.HLW046P';
name rows reserved data index_size unused
HLW046P 17720208 3143112KB 2015624KB 1110448KB 17040 KB

DBCC SHOWCONTIG analyse la table 'HLW046P'...
Table*: 'HLW046P' (658934265)*; index ID*: 1, base de données ID*: 6
Analyse du niveau TABLE effectuée.
- Pages analysées................................: 251953
- Extensions analysées..............................: 31694
- Commutateurs d'extension..............................: 77390
- Moyenne des pages par extension........................: 7.9
- Densité d'analyse [meilleure valeur:valeur réelle].......: 40.70% [31495:77391]
- Fragmentation d'analyse logique..................: 5.48%
- Fragmentation d'analyse d'extension...................: 57.76%
- Moyenne d'octets libres par page.....................: 1048.8
- Densité de page moyenne (complète).....................: 87.04%
Exécution de DBCC terminée.


Après avoir regarder d'autres tables, l'éditeur a créé systématiquement ces tables avec des colonnes varchar, exemple sur une autre table qui nous pose problème lors de nos maintenance plans :

CREATE TABLE [reflex].[HLODPEP](
[OECACT] [varchar](3) NOT NULL DEFAULT (''),
[OECDPO] [varchar](3) NOT NULL DEFAULT (''),
[OECDDO] [varchar](13) NOT NULL DEFAULT (''),
[OERODP] [varchar](20) NOT NULL DEFAULT (''),
[OECTPR] [varchar](3) NOT NULL DEFAULT (''),
[OECMOP] [varchar](3) NOT NULL DEFAULT (''),
[OECDES] [varchar](13) NOT NULL DEFAULT (''),
[OECDPD] [varchar](3) NOT NULL DEFAULT (''),
[OERODD] [varchar](20) NOT NULL DEFAULT (''),
[OECTDL] [varchar](3) NOT NULL DEFAULT (''),
[OECDEI] [varchar](13) NOT NULL DEFAULT (''),
[OERODI] [varchar](20) NOT NULL DEFAULT (''),
[OECDLA] [varchar](3) NOT NULL DEFAULT (''),
[OECCPL] [varchar](10) NOT NULL DEFAULT (''),
[OECQTP] [varchar](3) NOT NULL DEFAULT (''),
[OECFPR] [varchar](3) NOT NULL DEFAULT (''),
[OECRGC] [varchar](10) NOT NULL DEFAULT (''),
[OECRGE] [varchar](13) NOT NULL DEFAULT (''),
[OECPGP] [varchar](10) NOT NULL DEFAULT (''),
[OECUGP] [varchar](10) NOT NULL DEFAULT (''),
[OETPTG] [varchar](1) NOT NULL DEFAULT (''),
[OETLIG] [varchar](1) NOT NULL DEFAULT (''),
[OETDEI] [varchar](1) NOT NULL DEFAULT (''),
[OETSVO] [varchar](1) NOT NULL DEFAULT (''),
[OETBEC] [varchar](1) NOT NULL DEFAULT (''),
[OETBLE] [varchar](1) NOT NULL DEFAULT (''),
[OETRVF] [varchar](1) NOT NULL DEFAULT (''),
[OETRVI] [varchar](1) NOT NULL DEFAULT (''),
[OETENC] [varchar](1) NOT NULL DEFAULT (''),
[OETPRG] [varchar](1) NOT NULL DEFAULT (''),
[OETODV] [varchar](1) NOT NULL DEFAULT (''),
PRIMARY KEY CLUSTERED
(
...

J'imagine que ce genre de table fait votre régal lors d'un audit ou pour une phase d'optimisation.