Bonjour.
sp_sysmon rapporte un grand nombre de "flushs" par system logs records
Je comprend que ce type de "flush" serait causé par des écritures sur les bd définies en "mixed log and data"
Or, nous avons très peu d'écritures dans master,
data et logs sont séparés sur toutes les bd applicatives.
Nous utilisons 4 tempdbs temporaires en ramdisk pour le OLTP (data et logs séparés)
2 autres tempdbs sont en "mixed logs and data" mais sont très peu utilisées.
pendant la période de la prise d'échantillon sp_sysmon.
Qu'est ce qui provoque ces flush???
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13 ================================================================== sampling sampling ________Percent_ULC_Flushes_to_Xact_Log_______ started ended Full end change Si.log pages at at ULC Transa databa record unpin others -------- -------- ------ ------ ------ ------ ------ ------ 09:00:43 09:15:43 0.2% 34.6% 0.6% 50.2% 8.7% 5.7% 09:16:02 09:17:03 0.2% 42.4% 0.7% 47.1% 4.0% 5.6% 09:17:14 09:18:14 0.4% 39.1% 0.7% 47.7% 5.4% 6.7% 09:18:26 09:19:26 0.4% 38.6% 0.7% 48.1% 6.2% 6.0% 09:19:36 09:20:36 0.5% 36.7% 0.6% 48.1% 8.0% 6.2% 09:20:44 09:21:45 0.3% 39.2% 0.7% 50.0% 3.5% 6.4% 09:21:57 09:22:57 0.4% 39.5% 0.7% 45.9% 5.0% 8.5% 09:23:07 09:24:08 0.4% 38.8% 0.8% 45.7% 7.2% 7.1%
Les tables applicatives sont définies avec les clustered index. Je pense que les APL heap inserts sont ceux dans les tempdbs.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13 ========================================================================================================================== sampling sampling ________per_sec_ULC_Flushes_to_Xact_Log_______ ULC log Max ULC LOG _per_seconde__ AVG started ended Full end change Si.log pages Records ULC semap semap transactionLOG writes at at ULC Transa databa record unpin others by tran size wait wait writes allocs log pg -------- -------- ------ ------ ------ ------ ------ ------ ------- ----- ----- ----- ------ ------ -------- 09:00:43 09:15:43 2.1 326.8 5.6 474.5 82.5 54.1 41.6 16384 0.4 0.6 211.4 784.2 0.26962 09:16:02 09:17:03 1.8 354.3 6.0 394.0 33.2 46.6 20.7 16384 0.5 0.2 169.2 375.1 0.45111 09:17:14 09:18:14 3.2 347.9 6.2 424.5 47.8 59.6 26.3 16384 0.5 0.5 170.5 467.8 0.36452 09:18:26 09:19:26 4.3 445.6 8.3 554.8 71.3 69.5 27.3 16384 0.4 0.5 226.5 620.8 0.36488 09:19:36 09:20:36 5.1 404.0 6.6 529.5 88.5 68.1 37.6 16384 1.3 0.5 244.4 737.3 0.33143 09:20:44 09:21:45 3.2 402.4 7.1 513.5 35.6 65.9 24.2 16384 0.4 0.4 179.5 523.8 0.34267 09:21:57 09:22:57 4.0 390.3 7.3 453.6 49.1 84.5 20.4 16384 0.3 0.3 174.9 459.1 0.38087 09:23:07 09:24:08 4.1 393.8 7.7 463.3 73.3 71.6 29.2 16384 0.4 0.2 215.6 569.2 0.37884
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14 *** TRANSACTION PROFILE *** ================================================================================================== sampling sampling ___________________________________per_sec____________________________________ started ended commit _________inserts_________ ____updates_____ _________deletes_________ at at Xacts APLheap APLclus DOL APL DOL APL-def APL-dir DOL -------- -------- ------ ------- ------- ------- ------- ------- ------- ------- ------- 09:00:43 09:15:43 360 8893.0 2897.6 65.1 131.2 33.1 775.0 2904.4 65.0 09:16:02 09:17:03 385 6006.0 1901.8 72.9 183.4 34.0 876.3 77.1 73.7 09:17:14 09:18:14 387 6970.6 3059.9 74.4 179.4 45.0 968.4 87.2 74.0 09:18:26 09:19:26 489 7563.8 4878.2 98.1 230.5 53.5 1272.6 99.7 97.8 09:19:36 09:20:36 450 12567.3 6284.6 89.0 234.3 47.7 1171.2 93.9 88.9 09:20:44 09:21:45 444 6651.1 1659.8 88.7 224.7 47.5 1163.8 88.4 88.2 09:21:57 09:22:57 449 4108.8 2585.6 81.0 283.2 54.3 1153.6 76.7 80.5 09:23:07 09:24:08 439 4852.8 5556.0 83.7 229.0 52.8 1067.4 100.3 83.3
Voici les I/O dans les devices tempdb et master
Des idées?
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 DEVICE DETAILS for tempdb_dx_r_RAM_01 (0) ======================================================== sampling sampling _________Per_Second_________ %%%%%% started ended APF nonAPF Utili- at at Reads Reads Writes zation -------- -------- -------- -------- -------- ------ 09:00:43 09:15:43 0.0 0.2 47.0 3.7% 09:16:02 09:17:03 0.0 0.4 69.2 9.6% 09:17:14 09:18:14 0.0 0.0 29.7 5.5% 09:18:26 09:19:26 0.0 0.1 47.1 3.8% 09:19:36 09:20:36 0.0 0.2 83.8 7.9% 09:20:44 09:21:45 0.0 0.0 22.2 4.3% 09:21:57 09:22:57 0.0 0.1 44.1 6.5% 09:23:07 09:24:08 0.0 0.0 42.9 3.5% 09:24:19 09:25:19 0.0 0.1 54.7 6.0% DEVICE DETAILS for tempdb2_dx_r_RAM_01 (0) ======================================================== sampling sampling _________Per_Second_________ %%%%%% started ended APF nonAPF Utili- at at Reads Reads Writes zation -------- -------- -------- -------- -------- ------ 09:00:43 09:15:43 13.3 3.7 71.5 6.9% 09:16:02 09:17:03 0.0 0.0 14.5 2.0% 09:17:14 09:18:14 0.0 0.1 22.8 4.3% 09:18:26 09:19:26 0.0 0.1 32.0 2.6% 09:19:36 09:20:36 0.0 0.0 24.4 2.3% 09:20:44 09:21:45 0.0 0.1 30.3 5.9% 09:21:57 09:22:57 0.0 0.0 31.9 4.7% 09:23:07 09:24:08 0.0 0.0 21.2 1.7% 09:24:19 09:25:19 0.0 0.1 24.6 2.7% DEVICE DETAILS for tempdb3_dx_r_RAM_01 (0) ======================================================== sampling sampling _________Per_Second_________ %%%%%% started ended APF nonAPF Utili- at at Reads Reads Writes zation -------- -------- -------- -------- -------- ------ 09:00:43 09:15:43 0.0 0.1 13.5 1.1% 09:16:02 09:17:03 0.0 0.1 14.1 2.0% 09:17:14 09:18:14 0.0 0.0 16.4 3.1% 09:18:26 09:19:26 0.0 0.1 20.3 1.6% 09:19:36 09:20:36 0.0 0.1 26.0 2.5% 09:20:44 09:21:45 0.0 0.0 13.6 2.6% 09:21:57 09:22:57 0.0 0.1 14.6 2.1% 09:23:07 09:24:08 0.0 0.1 20.2 1.7% 09:24:19 09:25:19 0.0 0.1 26.2 2.9% DEVICE DETAILS for tempdb4_dx_r_RAM_01 (0) ======================================================== sampling sampling _________Per_Second_________ %%%%%% started ended APF nonAPF Utili- at at Reads Reads Writes zation -------- -------- -------- -------- -------- ------ 09:00:43 09:15:43 24.3 5.2 112.0 11.1% 09:16:02 09:17:03 0.0 0.1 37.0 5.1% 09:17:14 09:18:14 0.0 0.1 34.0 6.3% 09:18:26 09:19:26 0.0 0.2 43.2 3.5% 09:19:36 09:20:36 0.0 0.2 36.1 3.4% 09:20:44 09:21:45 0.0 0.1 38.7 7.5% 09:21:57 09:22:57 0.0 0.1 29.1 4.3% 09:23:07 09:24:08 0.0 0.0 39.7 3.3% 09:24:19 09:25:19 0.0 0.1 33.0 3.6% DEVICE DETAILS for tempdb_l_r_RAM_01 (0) ======================================================== sampling sampling _________Per_Second_________ %%%%%% started ended APF nonAPF Utili- at at Reads Reads Writes zation -------- -------- -------- -------- -------- ------ 09:00:43 09:15:43 0.0 0.6 44.7 3.6% 09:16:02 09:17:03 0.0 0.5 36.1 5.0% 09:17:14 09:18:14 0.0 0.7 50.1 9.4% 09:18:26 09:19:26 0.0 0.9 71.8 5.9% 09:19:36 09:20:36 0.0 1.1 84.5 8.1% 09:20:44 09:21:45 0.0 0.4 33.1 6.5% 09:21:57 09:22:57 0.0 0.5 41.2 6.1% 09:23:07 09:24:08 0.0 0.9 72.4 6.0% DEVICE DETAILS for tempdb2_l_r_RAM_01 (0) ======================================================== sampling sampling _________Per_Second_________ %%%%%% started ended APF nonAPF Utili- at at Reads Reads Writes zation -------- -------- -------- -------- -------- ------ 09:00:43 09:15:43 0.9 1.7 47.4 3.9% 09:16:02 09:17:03 0.0 0.2 18.6 2.6% 09:17:14 09:18:14 0.0 0.4 30.0 5.7% 09:18:26 09:19:26 0.0 0.5 38.1 3.1% 09:19:36 09:20:36 0.0 0.5 32.3 3.1% 09:20:44 09:21:45 0.0 0.5 36.9 7.2% 09:21:57 09:22:57 0.0 0.6 39.4 5.9% 09:23:07 09:24:08 0.0 0.4 28.5 2.4% 09:24:19 09:25:19 0.0 0.4 31.7 3.5% DEVICE DETAILS for tempdb3_l_r_RAM_01 (0) ======================================================== sampling sampling _________Per_Second_________ %%%%%% started ended APF nonAPF Utili- at at Reads Reads Writes zation -------- -------- -------- -------- -------- ------ 09:00:43 09:15:43 0.0 0.2 19.5 1.5% 09:16:02 09:17:03 0.0 0.1 12.7 1.8% 09:17:14 09:18:14 0.0 0.3 22.9 4.3% 09:18:26 09:19:26 0.0 0.4 30.8 2.5% 09:19:36 09:20:36 0.0 0.6 42.9 4.1% 09:20:44 09:21:45 0.0 0.2 19.5 3.8% 09:21:57 09:22:57 0.0 0.2 20.8 3.1% 09:23:07 09:24:08 0.0 0.4 32.1 2.7% 09:24:19 09:25:19 0.0 0.5 39.8 4.4% DEVICE DETAILS for tempdb4_l_r_RAM_01 (0) ======================================================== sampling sampling _________Per_Second_________ %%%%%% started ended APF nonAPF Utili- at at Reads Reads Writes zation -------- -------- -------- -------- -------- ------ 09:00:43 09:15:43 1.3 2.7 75.0 6.2% 09:16:02 09:17:03 0.0 0.6 47.6 6.6% 09:17:14 09:18:14 0.0 0.5 41.6 7.8% 09:18:26 09:19:26 0.0 0.6 55.3 4.5% 09:19:36 09:20:36 0.0 0.9 59.0 5.6% 09:20:44 09:21:45 0.0 0.8 61.6 12.1% 09:21:57 09:22:57 0.0 0.5 39.8 5.9% 09:23:07 09:24:08 0.0 0.6 50.4 4.2% DEVICE DETAILS for tempdbbatch_dxl_r_tempdb1 (0) ======================================================== sampling sampling _________Per_Second_________ %%%%%% started ended APF nonAPF Utili- at at Reads Reads Writes zation -------- -------- -------- -------- -------- ------ 09:00:43 09:15:43 0.0 0.0 0.5 0.0% 09:16:02 09:17:03 0.0 0.0 0.1 0.0% 09:17:14 09:18:14 0.0 0.0 0.5 0.1% 09:18:26 09:19:26 0.0 0.0 1.6 0.1% 09:19:36 09:20:36 0.0 0.0 0.1 0.0% 09:20:44 09:21:45 0.0 0.0 0.1 0.0% 09:21:57 09:22:57 0.0 0.0 2.5 0.4% 09:23:07 09:24:08 0.0 0.0 0.1 0.0% DEVICE DETAILS for tempDBAIBM_dxl_r_tempdb1# (0) ======================================================== sampling sampling _________Per_Second_________ %%%%%% started ended APF nonAPF Utili- at at Reads Reads Writes zation -------- -------- -------- -------- -------- ------ 09:00:43 09:15:43 -------- -------- -------- 0.0% 09:16:02 09:17:03 -------- -------- -------- 0.0% 09:17:14 09:18:14 -------- -------- -------- 0.0% 09:18:26 09:19:26 -------- -------- -------- 0.0% 09:19:36 09:20:36 -------- -------- -------- 0.0% 09:20:44 09:21:45 -------- -------- -------- 0.0% 09:21:57 09:22:57 -------- -------- -------- 0.0% 09:23:07 09:24:08 -------- -------- -------- 0.0% DEVICE DETAILS for master (0) ======================================================== sampling sampling _________Per_Second_________ %%%%%% started ended APF nonAPF Utili- at at Reads Reads Writes zation -------- -------- -------- -------- -------- ------ 09:00:43 09:15:43 0.0 0.0 1.3 0.1% 09:16:02 09:17:03 0.0 0.0 1.3 0.2% 09:17:14 09:18:14 0.0 0.0 1.4 0.3% 09:18:26 09:19:26 0.0 0.1 1.5 0.1% 09:19:36 09:20:36 0.0 0.0 1.5 0.1% 09:20:44 09:21:45 0.0 0.0 1.3 0.3% 09:21:57 09:22:57 0.0 0.0 1.4 0.2% 09:23:07 09:24:08 0.0 0.0 1.3 0.1% 09:24:19 09:25:19 0.0 0.1 1.4 0.2%
Partager