optimize for ad hoc workloads

Version imprimable

10/02/2020, 15h23
Bernardos

optimize for ad hoc workloads

[QUOTE]Bonjour,
J'ai vu passer pas mal de discussions sur le sujet sur ce forum et sur d'autres forums... et je n'arrive pas à me faire une conviction forte sur le sujet.

un article sur le sujet
on peut y voir

Citation:

In fact, there is basically no downside to using this option, so you may consider enabling it for every SQL Server installation.

Alors que sur une discussion sur brentozar

on peut lire

Citation:

Jeremiah Peschka
September 4, 2013 4:22 pm

I can’t recommend “optimize for ad hoc workloads” as a blanket recommendation. While this can control the overall growth of the size of the plan cache, it doesn’t control growth of the count of entries in the plan cache. Since search time in a hash table is O(n) (it grows linearly with hash table size), this can become a big problem when you have a lot of ad hoc queries and a high query volume. Basically – you can end up performing enough plan stub lookups to cause performance problems.

Compression is an EE only feature in SQL Server 2008 but is free in Standard starting with 2008R2. That’s why I mentioned “If you’re using SQL Server starting with SQL Server 2008R2…”

et un peu plus loin

Citation:

Jeremiah Peschka
September 10, 2013 8:32 am

Good news! You already heard about it the comment Kendra replied to.

Optimize for Ad Hoc can end up causing huge wait types when you’re looking up plans when you have a sizable plan cache.

Let’s say you have a table and all reads on the table are performed on an indexed BIGINT column. Queries from the application are unparameterized and come in as fast as SQL Server can process them.

In this situation, you can end up with hundreds of thousands (if not more) plan stubs in the execution plan cache. Whenever a new query comes in, SQL Server has to check to see if that plan has ever been seen before. This lookup operation isn’t free, neither is inserting a plan or plan stub into the cache.

As the plan cache grows, you can expect the time it takes to perform a lookup or insertion to grow linearly with the cache. Busy server + optimize for ad hoc = LOLSLOW.

Dans une Ancienne discussion sur ce forum on peut y voir SQL Pro et mikedavem proposer d'utiliser

Code:

SET PARAMETERIZATION FORCED

A la place
et dans une autre discussion sur ce forum on peut y lire janlouk dire

Citation:

Voici ce que j'ai reçu de Microsoft suite à leur "offline assessment" il y a quelques jours chez nous.

Optimize for ad hoc workloads Server Configuration Option
https://docs.microsoft.com/en-us/sql...uration-option
In most cases you either benefit from enabling the setting or there’s no large difference. In rare cases you don’t benefit and need to keep the setting disabled.

et mikedavem plussoyer en disant

Citation:

+1 avec Microsoft et cela fait parti de nos BPs chez nous.

Le risque est relativement faible pour la majorité des charges de travail même si on ne gagne rien au final en fonction du type de workload (purement adhoc ou non).

•Dans le cas le plus défavorable un batch est exécuté la première fois, on compile et on obtient un stub de plan d'exécution (moins de place en cache => optimisation). La 2ème fois le même batch est exécuté et relance une compilation de plan et cette fois et on obtient un vrai plan d'exécution.
•Dans le cas le plus favorable, le même batch n'est jamais exécuté une seconde fois et on reste avec notre stub de plan mais on passera par la phase de compilation quoi qu'il en soit (adhoc).

Quel est votre avis A ce sujet ?

Cordialement,

BERNARDOS

Par exemple sur un serveur ou j'ai 23390 plan d'exécution, j'en ai 10677 qui n'on été exécuté qu'une fois.
Par le passé, je n'aurais même pas hésité une seconde à activer "optimize for ad hoc workload" alors là je ne sais plus quoi penser.

Quels sont les critères A regarder pour orienter vers la meilleure décision?
Dans mom cas j'ai donc un serveur avec actuellement 23390 plans d'exécution(le serveur a du être redémarrer le w-e)
en passant

Code:

1
2
3
4
5
SELECT usecounts,
       count(*) AS NombrePlans
FROM sys.dm_exec_cached_plans 
GROUP BY usecounts 
ORDER BY usecounts

j'obtiens

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
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
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
usecounts	NombrePlans
1	10677
2	6182
3	1130
4	1198
5	612
6	470
7	165
8	175
9	132
10	128
11	71
12	84
13	54
14	320
15	58
16	57
17	46
18	41
19	20
20	18
21	35
22	34
23	40
24	24
25	22
26	11
27	17
28	684
29	12
30	13
31	10
32	13
33	10
34	6
35	7
36	11
37	4
38	5
39	13
40	4
41	4
42	15
43	4
44	5
45	5
46	3
47	6
48	4
49	1
50	6
51	5
52	6
53	4
54	8
55	5
56	2
57	5
58	2
59	3
60	3
61	4
62	5
63	13
64	11
65	17
66	4
67	2
68	6
69	3
70	1
71	8
72	3
73	3
74	3
75	4
76	3
79	1
80	4
82	1
84	3
85	2
86	1
87	2
88	2
89	7
91	4
92	1
93	5
94	4
95	8
96	2
97	1
98	3
100	3
102	1
106	2
107	1
110	2
112	3
113	1
114	2
115	5
116	3
117	4
118	4
119	9
120	6
121	3
122	1
123	4
124	1
125	3
127	1
128	1
129	4
131	3
132	2
133	1
134	1
136	1
138	2
140	2
141	2
142	2
144	1
145	2
148	4
150	4
152	1
153	2
154	3
155	1
157	3
158	2
162	1
163	2
166	1
167	1
168	4
169	1
171	1
172	1
176	1
177	1
179	1
186	1
188	1
190	1
192	1
193	1
196	1
198	1
201	1
204	1
206	1
207	1
212	1
224	1
225	1
228	1
232	1
233	1
234	2
235	1
240	2
244	1
252	1
259	1
268	1
278	1
281	1
282	3
288	2
290	1
291	1
292	1
297	1
300	1
301	1
305	1
307	1
315	1
317	1
319	1
327	2
334	1
336	1
342	1
346	1
350	2
354	1
386	1
401	1
408	2
411	1
414	5
425	2
428	1
431	1
436	3
441	1
442	1
470	1
494	1
502	1
513	1
514	1
515	2
516	3
517	1
518	2
519	3
520	1
521	4
522	2
523	1
524	3
525	1
531	1
576	1
583	1
593	2
596	1
598	2
610	1
622	1
630	3
643	2
665	1
672	1
703	1
735	1
747	1
751	1
778	2
819	1
836	1
867	1
877	1
897	1
899	1
908	1
970	1
1002	1
1008	2
1068	1
1133	1
1134	1
1136	1
1138	3
1139	1
1140	2
1141	1
1143	1
1154	1
1155	1
1169	2
1177	1
1183	1
1216	1
1258	1
1280	1
1285	1
1290	1
1333	1
1339	1
1349	1
1377	1
1396	1
1459	1
1487	3
1510	1
1511	4
1592	1
1682	1
1729	1
1759	1
1788	1
1876	1
1895	1
1944	1
1992	1
2004	4
2005	3
2006	5
2020	1
2021	6
2022	6
2023	2
2024	1
2030	1
2033	1
2047	1
2060	1
2064	1
2074	1
2082	1
2083	1
2139	1
2143	1
2145	1
2147	1
2178	1
2407	1
2415	1
2559	1
2658	1
2690	1
2694	1
2714	3
2715	1
2960	1
3009	3
3021	5
3102	1
4008	3
4010	9
4012	1
4022	1
4026	1
4027	1
4038	3
4040	1
4041	1
4042	6
4044	5
4046	5
4057	1
4069	1
4070	4
4081	1
4127	1
4153	5
4154	2
4155	5
4156	7
4157	13
4158	1
4159	3
4160	1
4441	2
4760	2
4770	1
4808	1
5368	1
5913	1
6065	1
6105	1
6137	1
6210	1
6507	1
6745	1
6790	2
8161	1
8497	1
8863	1
10197	1
11144	1
12438	6
12440	1
14548	1
14702	1
15292	1
19519	1
19523	1
19685	1
20302	1
24876	58
71002	1
71620	1
220682	1
225327	1
225329	1
229687	1

on voit donc qu'il y en a également beaucoup qui ne sont exécuter que 2 fois et que quelques-uns sont énormément exécutés
dans ce cas que faire?

10/02/2020, 17h32
SQLpro

PARAMETERIZATION FORCED c'est pas la même chose que "optimize for ad hoc workloads"

optimize for ad hoc workloads : par défaut SQL Server met en cache TOUS les plans de requête des requêtes "ad hoc". Les requêtes "ad hoc" étant les requêtes hors des routines, celles venant directement des applicatifs.
Or un certain nombre de ces requêtes vont être uniques…. Notamment lorsque les développeurs font des requêtes assemblées par des bouts de chaines de caractères…

Conserver un grand nombre de plan utilisés une seule fois n'a pas un grand intérêt, sauf si vous avez 12345678976543234567890 To de RAM !

A +

Bonjour Loïc,

J'utilise aussi ceci pour m'aider :

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
--https://dba.stackexchange.com/questions/35500/why-would-i-not-use-the-sql-server-option-optimize-for-ad-hoc-workloads
--- for 2008 and up .. Optimize ad-hoc for workload 
IF EXISTS (
        -- this is for 2008 and up
        SELECT 1
        FROM sys.configurations
        WHERE NAME = 'optimize for ad hoc workloads'
        )
BEGIN
    DECLARE @AdHocSizeInMB DECIMAL(14, 2)
        ,@TotalSizeInMB DECIMAL(14, 2)
        ,@ObjType NVARCHAR(34)
 
    SELECT @AdHocSizeInMB = SUM(CAST((
                    CASE 
                        WHEN usecounts = 1
                            AND LOWER(objtype) = 'adhoc'
                            THEN size_in_bytes
                        ELSE 0
                        END
                    ) AS DECIMAL(14, 2))) / 1048576
        ,@TotalSizeInMB = SUM(CAST(size_in_bytes AS DECIMAL(14, 2))) / 1048576
    FROM sys.dm_exec_cached_plans
 
    SELECT 'SQL Server Configuration' AS GROUP_TYPE
        ,' Total cache plan size (MB): ' + cast(@TotalSizeInMB AS VARCHAR(max)) + '. Current memory occupied by adhoc plans only used once (MB):' + cast(@AdHocSizeInMB AS VARCHAR(max)) + '.  Percentage of total cache plan occupied by adhoc plans only used once :' + cast(CAST((@AdHocSizeInMB / @TotalSizeInMB) * 100 AS DECIMAL(14, 2)) AS VARCHAR(max)) + '%' + ' ' AS COMMENTS
        ,' ' + CASE 
            WHEN @AdHocSizeInMB > 200
                OR ((@AdHocSizeInMB / @TotalSizeInMB) * 100) > 25 -- 200MB or > 25%
                THEN 'Switch on Optimize for ad hoc workloads as it will make a significant difference. Ref: http://sqlserverperformance.idera.com/memory/optimize-ad-hoc-workloads-option-sql-server-2008/. http://www.sqlskills.com/blogs/kimberly/post/procedure-cache-and-optimizing-for-adhoc-workloads.aspx'
            ELSE 'Setting Optimize for ad hoc workloads will make little difference !!'
            END + ' ' AS RECOMMENDATIONS
END

Sur 6 clients, j'ai eu 3 audits de Microsoft car c'étaient inclus dans leur contrat annuel. Et donc ils me le recommandaient. Je me suis posé les mêmes questions que toi et un jour une personne m'a dit "On ne te reprochera jamais de suivre les conseils et Best Practice de Microsoft donc dans ce cas-ci, il vaut mieux le faire pour pas qu'un jour on te le reproche". Et je t'avoue, pour mon nouveau client chez qui je suis depuis 1 mois, c'est dans ma ToDo list.

1 pièce(s) jointe(s)

janlouk : Merci pour le script
Le résultat est intéressant
Code:

1 2 3 4 Total cache plan size (MB): 4532.17. Current memory occupied by adhoc plans only used once (MB):20.55. Percentage of total cache plan occupied by adhoc plans only used once :0.45% Setting Optimize for ad hoc workloads will make little difference !!
Du coup, je constate que j'ai très peu de requêtes adhoc.
je passe la requête suivante
[CODE]
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 SELECT objtype , cacheobjtype, COUNT_BIG(*) AS [Total Plans], SUM(CAST(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs], AVG(usecounts) AS [Avg Use Count], SUM(CAST((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs – USE Count 1], SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans – USE Count 1] FROM sys.dm_exec_cached_plans GROUP BY objtype,cacheobjtype ORDER BY [Total MBs – USE Count 1] DESC
qui me donne le résultat suivant
Pièce jointe 537713@
Mon constat est donc que j'ai 28439 plans dans mom cache dont 15301 n'ont été exécutée qu'une seule fois.
Pour la petite histoire, 99,6% de ces requetes sont des insert.
J'ai Seulement 210 plans qui concernent des Query Ad hoc.

Là me vient une question : est-ce que lorsque optimize adhoc workloads est activé les plans qui ne correspondent pas à des requêtes ad hoc ont le même comportement c-à-d à la première exécution ce n'est pas le plan qui est stocké mais un "compiled plan stub"?

Merci d'avance,

BERNARDOS

11/02/2020, 17h39
Bernardos

Citation:

Envoyé par SQLpro

PARAMETERIZATION FORCED c'est pas la même chose que "optimize for ad hoc workloads"

oui je sais, c'est juste qu'en cherchant à renforcer ma conviction sur le sujet je suis tombé sur cette discussion entre toi, elsuket et mikedavem ou la question de base portait sur l'option optimize for adhoc workloads et tu avais embrayé directement sur la paramétrisation forcée

Citation:

Envoyé par SQLpro

optimize for ad hoc workloads : par défaut SQL Server met en cache TOUS les plans de requête des requêtes "ad hoc". Les requêtes "ad hoc" étant les requêtes hors des routines, celles venant directement des applicatifs.
Or un certain nombre de ces requêtes vont être uniques…. Notamment lorsque les développeurs font des requêtes assemblées par des bouts de chaines de caractères…

Conserver un grand nombre de plan utilisés une seule fois n'a pas un grand intérêt, sauf si vous avez 12345678976543234567890 To de RAM !+

oui ej connais cette option, je sais ce qu'elle fait et je suis d'accord avec ta conclusion.

Donc, en fait c'est vrai que j'ai un peu compliqué ma question en argumentant avec différentes discussions.
en fait ma question est simple, est ce qu'on peut partir sur la principe qu' il faut l'activer systématiquement sur toutes les instances ou non car il y a un risque d'effet de bord? par exemple si on a énormément de requête exécutée exactement 2 fois ou dans d'autres cas et si oui lesquels

Merci d'avance,
Cordialement,
12/02/2020, 08h02
janlouk

Perso, je le fais par défaut car l'impacte est en général positif plutôt que négatif. En plus, si tu dois analyser pour chaque base, ça prend des semaines et je ne sais pas toi mais je suis souvent seul donc impossible de le faire comme cela.
13/02/2020, 15h07
SQLpro

Je plussois !