Bonjour,
Je suis face à un problème que je n'arrive pas à comprendre.
Voici une requête :
Code sql : 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
243
244
245
246
247
248
249
250 SELECT top 11 TE_ISI_UP.Artikelbez, TE_ISI_UP.Menge, TE_ISI_UP.ID, TE_ISI_UP.ID_FI, TE_ISI_UP.ID_AU, TE_ISI_UP.F7001, TE_ISI_UP.F7024, TE_ISI_AU.AufDat, TE_ISI_AU.ID, TE_ISI_FI.Firma, TE_ISI_FI.Ort, TE_ISI_FI.FTZ, TE_ISI_FI.Ftz1, TE_ISI_FI.Ftz2, TE_ISI_FI.ID, TE_ISI_FI.F5031 FROM TE_ISI_UP LEFT OUTER JOIN TE_ISI_AU ON ( TE_ISI_UP.ID_AU<>0 AND TE_ISI_AU.ID=TE_ISI_UP.ID_AU AND (TE_ISI_AU.LosKZ=0) ) LEFT OUTER JOIN TE_ISI_FI ON ( TE_ISI_UP.ID_FI=TE_ISI_FI.ID AND (TE_ISI_FI.LosKZ=0) ) WHERE TE_ISI_UP.LosKZ=0 AND (TE_ISI_UP.MNO IN(0,8003,8100,9000)) AND ( ( TE_ISI_UP.ID_FI=0 OR ( TE_ISI_UP.ID_FI<>0 AND EXISTS ( SELECT ID FROM TE_ISI_FI WHERE TE_ISI_UP.ID_FI=ID AND ( TE_ISI_FI.LosKZ=0 AND (TE_ISI_FI.MandNr IN(0,8003,8100,9000)) AND ( ( ( ((TE_ISI_FI.Fityp=2 OR TE_ISI_FI.Fityp2=2 OR TE_ISI_FI.Fityp3=2 OR TE_ISI_FI.Fityp4=2 OR TE_ISI_FI.Fityp5=2)) OR (TE_ISI_FI.Fityp2=2) OR (TE_ISI_FI.Fityp3=2) OR (TE_ISI_FI.Fityp4=2) OR (TE_ISI_FI.Fityp5=2) ) ) AND ( EXISTS ( SELECT ID FROM TE_ISI_SB WHERE (TE_ISI_FI.ID=TE_ISI_SB.ID_FI) AND TE_ISI_SB.LosKZ=0 AND ( EXISTS ( SELECT ID FROM TE_ISI_ID WHERE TE_ISI_SB.BearbeiterId<>0 AND TE_ISI_ID.ID=TE_ISI_SB.BearbeiterId AND ( TE_ISI_ID.Oeffentlich=1 OR TE_ISI_ID.MandNr IN(0,8003,8100,9000) ) AND TE_ISI_ID.LosKZ=0 AND ( ( ( TE_ISI_ID.HierarchyCode LIKE '292.2.%' OR TE_ISI_ID.HierarchyCode='-1' ) ) ) ) ) ) ) OR ( ( ( ( TE_ISI_FI.Fityp!=2 AND TE_ISI_FI.Fityp2!=2 AND TE_ISI_FI.Fityp3!=2 AND TE_ISI_FI.Fityp4!=2 AND TE_ISI_FI.Fityp5!=2 ) ) AND ( ( TE_ISI_FI.Fityp!=0 OR TE_ISI_FI.Fityp2!=0 OR TE_ISI_FI.Fityp3!=0 OR TE_ISI_FI.Fityp4!=0 OR TE_ISI_FI.Fityp5!=0 ) ) ) OR ( (TE_ISI_FI.Fityp2!=2) AND (TE_ISI_FI.Fityp2!=0) ) OR ( (TE_ISI_FI.Fityp3!=2) AND (TE_ISI_FI.Fityp3!=0) ) OR ( (TE_ISI_FI.Fityp4!=2) AND (TE_ISI_FI.Fityp4!=0) ) OR ( (TE_ISI_FI.Fityp5!=2) AND (TE_ISI_FI.Fityp5!=0) ) ) ) AND ( ( ( ( ( TE_ISI_FI.ID>=4294967296 AND TE_ISI_FI.ID<8589934592 ) ) AND ( (TE_ISI_FI.ID % CAST(4294967296 AS BIGINT))=1794 ) ) OR (TE_ISI_FI.F6013=1) OR ( ( ( ( TE_ISI_FI.Fityp!=3 AND TE_ISI_FI.Fityp2!=3 AND TE_ISI_FI.Fityp3!=3 AND TE_ISI_FI.Fityp4!=3 AND TE_ISI_FI.Fityp5!=3 ) ) AND ( ( TE_ISI_FI.Fityp!=0 OR TE_ISI_FI.Fityp2!=0 OR TE_ISI_FI.Fityp3!=0 OR TE_ISI_FI.Fityp4!=0 OR TE_ISI_FI.Fityp5!=0 ) ) ) OR ( (TE_ISI_FI.Fityp2!=3) AND (TE_ISI_FI.Fityp2!=0) ) OR ( (TE_ISI_FI.Fityp3!=3) AND (TE_ISI_FI.Fityp3!=0) ) OR ( (TE_ISI_FI.Fityp4!=3) AND (TE_ISI_FI.Fityp4!=0) ) OR ( (TE_ISI_FI.Fityp5!=3) AND (TE_ISI_FI.Fityp5!=0) ) ) ) OR ( ( ( TE_ISI_FI.Fityp=3 OR TE_ISI_FI.Fityp2=3 OR TE_ISI_FI.Fityp3=3 OR TE_ISI_FI.Fityp4=3 OR TE_ISI_FI.Fityp5=3 ) ) OR (TE_ISI_FI.Fityp2=3) OR (TE_ISI_FI.Fityp3=3) OR (TE_ISI_FI.Fityp4=3) OR (TE_ISI_FI.Fityp5=3) ) AND ( EXISTS ( SELECT ID FROM TE_ISI_C027 WHERE TE_ISI_C027.ID_FI=TE_ISI_FI.ID AND (TE_ISI_C027.MNO IN(0,8003,8100,9000)) AND TE_ISI_C027.DEL=0 AND ((TE_ISI_C027.MNO=8003)) ) ) ) ) ) ) ) AND ( EXISTS ( SELECT ID FROM TE_ISI_AU WHERE TE_ISI_UP.ID_AU<>0 AND TE_ISI_AU.ID=TE_ISI_UP.ID_AU AND ( TE_ISI_AU.LosKZ=0 AND (TE_ISI_AU.MNO IN(0,8003,8100,9000)) ) ) ) ) ORDER BY TE_ISI_AU.AufDat DESC
Je vous la montre pour la postérité, malheureusement je n'ai pas la main sur elle, puisqu'elle est générée automatiquement par un logiciel.
Historiquement, cette requête tournait sur un SQL Server 2014 Standard.
Elle était particulièrement lente (environ 40 secondes pour charger les 11 premières lignes, 60 secondes si je la relançait en demandant les 21 premières lignes)
Lorsque je la lançais dans Management Studio, ce dernier me proposait la création d'un index.
J'ai créé l'index en question, et miracle de la vie, la requête est devenue instantanée (moins de 5 secondes).
Ensuite, on a migré le programme de serveur.
J'ai backupé la base telle quelle, puis je l'ai restaurée sur un serveur moindrement dimensionné SQL Server 2016 Developpeur.
Je n'ai rien tuné/optimisé.
La requête lancée sur le nouveau serveur tournait dans le même ordre de temps.
Enfin, j'ai migré la base sur le nouveau serveur de PROD, de ma même manière : restauration du backup de 2014 un nouveua serveur 2016 Standard, dimensionné à l'identique du 2014.
La requête est aussi lente qu'à l'origine, avant création de l'index.
L'index est bien toujours là.
J'ai lancé un script proposé par SQL Pro pour recalculer/reconstruire les index pollués :
Code sql : 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 go SET ANSI_NULLS, QUOTED_IDENTIFIER, NOCOUNT ON; DECLARE @objectid int; DECLARE @indexid int; DECLARE @partitioncount bigint; DECLARE @schemaname nvarchar(130); DECLARE @objectname nvarchar(130); DECLARE @indexname nvarchar(130); DECLARE @partitionnum bigint; DECLARE @partitions bigint; DECLARE @frag float; DECLARE @command nvarchar(4000); -- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function -- and convert object and index IDs to names. SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag INTO #work_to_do FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0 and page_count > 1000 and alloc_unit_type_desc <> 'LOB'; -- Declare the cursor for the list of partitions to be processed. DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do; -- Open the cursor. OPEN partitions; -- Loop through the partitions. WHILE (1=1) BEGIN; FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag; IF @@FETCH_STATUS < 0 BREAK; SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name) FROM sys.objects AS o JOIN sys.schemas as s ON s.schema_id = o.schema_id WHERE o.object_id = @objectid; SELECT @indexname = QUOTENAME(name) FROM sys.indexes WHERE object_id = @objectid AND index_id = @indexid; SELECT @partitioncount = count (*) FROM sys.partitions WHERE object_id = @objectid AND index_id = @indexid; -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding. IF @frag < 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'; IF @frag >= 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD'; IF @partitioncount > 1 SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10)); EXEC (@command); PRINT N'Executed: ' + @command; END; -- Close and deallocate the cursor. CLOSE partitions; DEALLOCATE partitions; -- Drop the temporary table. DROP TABLE #work_to_do; GO
Rien n'y fait. C'est ultra lent.
Dans Management Studio, je n'ai aucune proposition d'index.
J'ai tenté de faire un tuning advisor sur la requête, idem, il ne me propose absolument aucune optimisation.
Comment c'est possible ?
Comment résoudre mon problème ?
Le serveur est dimensionné tout à fait "largement" : 24 Go de mémoire pour une base de 27 Go (dont les 3/4 sont des données "mortes").
Lorsque la requête tourne, 2 coeurs sont utilisés sur les 4.
Je viens de tenter un REBUILD de tous les index de la base, sans aucun effet.
Je suis en train de mettre à jour les statistiques de toutes les tables... je doute que ça change grand chose
-- Edit : Indentation de la requête (mais bon, elle reste toujours aussi imbittable)
-- Edit : La mise à jour des stats change rien, comme prévu
Partager