| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_sysmon_wpm | | 31 Aug 14 | Defects Dependencies |
1 2 /* This stored procedure produces a report containing a summary of 3 ** SQL Server worker process activity. 4 */ 5 create procedure sp_sysmon_wpm 6 @NumElapsedMs int, /* for "per Elapsed second" calculations */ 7 @NumXacts int, /* for per transactions calculations */ 8 @Reco char(1) /* Flag for recommendations */ 9 as 10 11 /* --------- declare local variables --------- */ 12 declare @tmp_int int /* temp var for integer storage */ 13 declare @tmp_int2 int /* temp var for integer storage */ 14 declare @tmp_int3 int /* temp var for integer storage */ 15 declare @tmp_total int /* temp var for summing 'total #s' data */ 16 declare @sum1line char(80) /* string to delimit total lines without 17 ** percent calc on printout */ 18 declare @sum2line char(80) 19 declare @blankline char(1) /* to print blank line */ 20 declare @psign char(3) /* hold a percent sign (%) for print out */ 21 declare @na_str char(3) /* holds 'n/a' for 'not applicable' strings */ 22 declare @rptline char(80) /* formatted statistics line for print 23 ** statement */ 24 declare @section char(80) /* string to delimit sections on printout */ 25 /* ------------- Variables for Tuning Recommendations ------------*/ 26 declare @recotxt char(80) 27 declare @recoline char(80) 28 declare @reco_hdr_prn bit 29 30 /* --------- Setup Environment --------- */ 31 set nocount on /* disable row counts being sent to client */ 32 33 select @blankline = " " 34 select @psign = " %%" /* extra % symbol because '%' is escape char in print statement */ 35 select @na_str = "n/a" 36 select @sum1line = " -------------------------- ------------ ------------ ----------" 37 select @sum2line = " ------------ ------------ ---------- ----------" 38 select @section = "===============================================================================" 39 40 /* ======================= Worker Process Management Section =================== */ 41 42 /* This subordinate should not appear for pre 11.1 servers */ 43 44 if not exists (select * 45 from #tempmonitors 46 where group_name = "kernel" and 47 field_name = "wtm_connect_requests") 48 begin 49 print @blankline 50 return 0 51 end 52 53 print @section 54 print @blankline 55 print "Worker Process Management" 56 print "-------------------------" 57 print " per sec per xact count %% of total" 58 print @sum2line 59 print " Worker Process Requests" 60 61 62 select @tmp_total = value 63 from #tempmonitors 64 where group_name = "kernel" and 65 field_name = "wtm_connect_requests" 66 67 68 69 if @tmp_total = 0 /* Avoid divide by zero errors - just print zero's */ 70 begin 71 select @rptline = " Total Requests 0.0 0.0 0 n/a" 72 print @rptline 73 end 74 else 75 begin 76 select @tmp_int = value 77 from #tempmonitors 78 where group_name = "kernel" and 79 field_name = "wtm_denied_requests" 80 81 select @tmp_int2 = @tmp_total - @tmp_int 82 83 select @rptline = " Requests Granted " + space(10) + 84 str(@tmp_int2 / (@NumElapsedMs / 1000.0), 12, 1) 85 + space(2) + 86 str(@tmp_int2 / convert(real, @NumXacts), 12, 1) 87 + space(2) + 88 str(@tmp_int2, 10) + space(5) + 89 str(100.0 * @tmp_int2 / @tmp_total, 5, 1) 90 + @psign 91 print @rptline 92 93 select @rptline = " Requests Denied " + space(10) + 94 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) 95 + space(2) + 96 str(@tmp_int / convert(real, @NumXacts), 12, 1) 97 + space(2) + 98 str(@tmp_int, 10) + space(5) + 99 str(100.0 * @tmp_int / @tmp_total, 5, 1) 100 + @psign 101 print @rptline 102 103 print @sum1line 104 105 select @rptline = " Total Requests " + space(12) + 106 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) 107 + space(2) + 108 str(@tmp_total / convert(real, @NumXacts), 12, 1) 109 + space(2) + 110 str(@tmp_total, 10) 111 print @rptline 112 113 print @blankline 114 115 select @tmp_int = value 116 from #tempmonitors 117 where group_name = "kernel" and 118 field_name = "wtm_terminations" 119 120 select @rptline = " Requests Terminated " + space(7) + 121 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) 122 + space(2) + 123 str(@tmp_int / convert(real, @NumXacts), 12, 1) 124 + space(2) + 125 str(@tmp_int, 10) + space(5) + 126 str(100.0 * @tmp_int / @tmp_total, 5, 1) 127 + @psign 128 print @rptline 129 130 end 131 132 print @blankline 133 print " Worker Process Usage" 134 135 select @tmp_int = value 136 from #tempmonitors 137 where group_name = "kernel" and 138 field_name = "wtm_thread_requests" 139 140 select @rptline = " Total Used" + space(17) + 141 str(@tmp_int / (@NumElapsedMs / 1000.0), 11, 1) 142 + space(3) + 143 str(@tmp_int / convert(real, @NumXacts), 11, 1) 144 + space(2) + 145 str(@tmp_int, 10) + space(7) + 146 @na_str 147 148 print @rptline 149 150 select @tmp_int3 = value 151 from #tempmonitors 152 where group_name = "kernel" and 153 field_name = "wtm_high_water_mark" 154 155 select @rptline = " Max Ever Used During Sample" + 156 str(@tmp_int3 / (@NumElapsedMs / 1000.0), 11, 1) 157 + space(3) + 158 str(@tmp_int3 / convert(real, @NumXacts), 11, 1) 159 + space(2) + 160 str(@tmp_int3, 10) + space(7) + 161 @na_str 162 print @rptline 163 164 print @blankline 165 print " Memory Requests for Worker Processes" 166 167 select @tmp_total = value 168 from #tempmonitors 169 where group_name = "kernel" and 170 field_name = "wtm_memory_allocs" 171 172 if @tmp_total = 0 /* Avoid divide by zero errors - just print zero's */ 173 begin 174 select @rptline = " Total Requests 0.0 0.0 0 n/a" 175 print @rptline 176 end 177 else 178 begin 179 select @tmp_int = value 180 from #tempmonitors 181 where group_name = "kernel" and 182 field_name = "wtm_memory_allocs_failed" 183 184 select @tmp_int2 = @tmp_total - @tmp_int 185 186 select @rptline = " Succeeded " + space(12) + 187 str(@tmp_int2 / (@NumElapsedMs / 1000.0), 12, 1) 188 + space(2) + 189 str(@tmp_int2 / convert(real, @NumXacts), 12, 1) 190 + space(2) + 191 str(@tmp_int2, 10) + space(5) + 192 str(100.0 * @tmp_int2 / @tmp_total, 5, 1) 193 + @psign 194 print @rptline 195 196 select @rptline = " Failed " + space(12) + 197 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) 198 + space(2) + 199 str(@tmp_int / convert(real, @NumXacts), 12, 1) 200 + space(2) + 201 str(@tmp_int, 10) + space(5) + 202 str(100.0 * @tmp_int / @tmp_total, 5, 1) 203 + @psign 204 print @rptline 205 206 print @sum1line 207 208 select @rptline = " Total Requests" + space(12) + 209 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) 210 + space(2) + 211 str(@tmp_total / convert(real, @NumXacts), 12, 1) 212 + space(2) + 213 str(@tmp_total, 10) 214 print @rptline 215 216 217 print @blankline 218 219 if (@tmp_int3 > 0) 220 begin 221 222 select @tmp_int = value 223 from #tempmonitors 224 where group_name = "kernel" and 225 field_name = "wtm_mem_alloc_hwm" 226 227 select @rptline = " Avg Mem Ever Used by a WP (in bytes)" + space(1) + 228 @na_str + 229 + space(11) + 230 @na_str + 231 + space(2) + 232 str(@tmp_int / convert(real, @tmp_int3), 10, 1) + space(7) + 233 @na_str 234 print @rptline 235 end 236 end 237 238 239 /* If requested, print worker process recommendations (if any) */ 240 241 if (@Reco = 'Y') 242 begin 243 print @blankline 244 select @recotxt = " Tuning Recommendations for Worker Processes" 245 select @recoline = " -------------------------------------------" 246 select @reco_hdr_prn = 0 247 248 select @tmp_total = 0 249 select @tmp_int = 0 250 select @tmp_int2 = 0 251 252 select @tmp_int = convert(integer, value) from #tempconfigures 253 where name like "%number of worker processes%" 254 255 if (@tmp_int > 0) 256 begin 257 select @tmp_int2 = value from #tempmonitors 258 where group_name = "kernel" and 259 field_name = "wtm_high_water_mark" 260 261 select @tmp_total = convert(int, (100.0 * a.value / b.value)) 262 from #tempmonitors a, #tempmonitors b 263 where a.group_name = "kernel" 264 and a.field_name = "wtm_denied_requests" 265 and b.group_name = "kernel" 266 and b.field_name = "wtm_connect_requests" 267 and b.value != 0 268 269 /* 270 ** If the number of worker processes configured is less 271 ** than the high water mark, consider increasing 272 ** the 'number of worker processes' 273 */ 274 if (@tmp_int <= @tmp_int2 and @tmp_total > 80) 275 begin 276 if (@reco_hdr_prn = 0) 277 begin 278 print @recotxt 279 print @recoline 280 select @reco_hdr_prn = 1 281 end 282 print " - Consider increasing the 'number of worker processes'" 283 print " configuration parameter." 284 print @blankline 285 end 286 else 287 if ((100.0 * @tmp_int2 / @tmp_int) <= 25 and @tmp_total = 0) 288 begin 289 if (@reco_hdr_prn = 0) 290 begin 291 print @recotxt 292 print @recoline 293 select @reco_hdr_prn = 1 294 end 295 print " - Consider decreasing the 'number of worker processes'" 296 print " configuration parameter." 297 print @blankline 298 end 299 300 end 301 302 select @tmp_total = 0 303 select @tmp_int2 = 0 304 305 select @tmp_int = value from #tempmonitors 306 where group_name = "kernel" and 307 field_name = "wtm_high_water_mark" 308 309 if (@tmp_int > 0) 310 begin 311 select @tmp_int2 = convert(integer, value) from #tempconfigures 312 where name like "%memory per worker process%" 313 314 if (@tmp_int2 > 0) 315 begin 316 select @tmp_int3 = convert(integer, value / @tmp_int) from #tempmonitors 317 where group_name = "kernel" and 318 field_name = "wtm_mem_alloc_hwm" 319 320 select @tmp_total = convert(int, (100.0 * a.value / b.value)) 321 from #tempmonitors a, #tempmonitors b 322 where a.group_name = "kernel" 323 and a.field_name = "wtm_memory_allocs_failed" 324 and b.group_name = "kernel" 325 and b.field_name = "wtm_memory_allocs" 326 and b.value != 0 327 328 /* 329 ** If the % of times worker thread memory 330 ** allocations failed to the number of allocations 331 ** is > 80% then consider increasing the 332 ** 'memory per worker process' configuration parameter 333 ** else consider decreasing the 'memory per worker process' 334 ** configuration parameter. 335 */ 336 if (@tmp_int2 <= @tmp_int3 and @tmp_total > 80) 337 begin 338 if (@reco_hdr_prn = 0) 339 begin 340 print @recotxt 341 print @recoline 342 select @reco_hdr_prn = 1 343 end 344 print " - Consider increasing the 'memory per worker process'" 345 print " configuration parameter." 346 print @blankline 347 end 348 else 349 if ((@tmp_int2 - @tmp_int3) > 512 and @tmp_total = 0) 350 begin 351 if (@reco_hdr_prn = 0) 352 begin 353 print @recotxt 354 print @recoline 355 select @reco_hdr_prn = 1 356 end 357 print " - Consider decreasing the 'memory per worker process'" 358 print " configuration parameter." 359 print @blankline 360 end 361 end 362 end 363 end 364 365 print @blankline 366 return 0 367
exec sp_procxmode 'sp_sysmon_wpm', 'AnyMode' go Grant Execute on sp_sysmon_wpm to public go
| DEPENDENCIES |
| PROCS AND TABLES USED reads table tempdb..#tempconfigures (1) reads table tempdb..#tempmonitors (1) CALLERS called by proc sybsystemprocs..sp_sysmon_analyze  called by proc sybsystemprocs..sp_sysmon  |
