| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_sysmon_parallel | | 31 Aug 14 | Defects Dependencies |
1 2 /* This stored procedure produces a report describing the parallel 3 ** features: activity. 4 ** 5 */ 6 create procedure sp_sysmon_parallel 7 @NumElapsedMs int, /* for "per Elapsed second" calculations */ 8 @NumXacts int, /* for per transactions calculations */ 9 @Reco char(1) /* Flag for recommendations */ 10 as 11 12 /* --------- declare local variables --------- */ 13 declare @NumTaskSwitch int /* Total Number of Task Context Switches 14 ** across all engines */ 15 declare @KnownTaskSwitch int /* Count of Number of Task Context Switches 16 ** by Known Causes */ 17 declare @i smallint /* loop index to iterate through multi-group 18 ** counters (engine, disk, & buffer) */ 19 declare @tmp_grp varchar(25) /* temp var for building group_names 20 ** ie. engine_N, disk_N */ 21 declare @tmp_int int /* temp var for integer storage */ 22 declare @tmp_total int /* temp var for integer storage, totals */ 23 declare @sum1line char(80) /* string to delimit total lines without 24 ** percent calc on printout */ 25 declare @sum2line char(67) /* string to delimit total lines with percent 26 ** calc on printout */ 27 declare @blankline char(1) /* to print blank line */ 28 declare @psign char(3) /* hold a percent sign (%) for print out */ 29 declare @na_str char(3) /* holds 'n/a' for 'not applicable' strings */ 30 declare @rptline char(80) /* formatted stats line for print statement */ 31 declare @section char(80) /* string to delimit sections on printout */ 32 33 /* ------------- Variables for Tuning Recommendations ------------*/ 34 declare @reco_no_thread_adjusts int 35 declare @recotxt char(80) 36 declare @recoline char(80) 37 declare @reco_hdr_prn bit 38 declare @reco_total_waits int 39 declare @reco_runtime_adjust int 40 declare @reco_total_queries int 41 42 /* --------- Setup Environment --------- */ 43 set nocount on /* disable row counts being sent to client */ 44 45 select @sum1line = " ------------------------- ------------ ------------ ---------- ----------" 46 select @sum2line = " ------------------------- ------------ ------------ ----------" 47 select @blankline = " " 48 select @psign = " %%" /* extra % symbol because '%' is escape char in print statement */ 49 select @na_str = "n/a" 50 select @section = "===============================================================================" 51 52 /* ===================== Parallel Query Management Section ============== */ 53 54 if not exists (select * 55 from #tempmonitors 56 where group_name = "parallel" and 57 field_name = "pll_total_number_parallel_queries") 58 begin 59 print @blankline 60 return 0 61 end 62 63 print @section 64 print @blankline 65 /* 66 ** ------ 67 */ 68 print "Parallel Query Management" 69 print "-------------------------" 70 print @blankline 71 72 73 select @tmp_total = value, @reco_total_queries = value 74 from #tempmonitors 75 where group_name = "parallel" and 76 field_name = "pll_total_number_parallel_queries" 77 78 select @reco_no_thread_adjusts = 0 79 select @reco_runtime_adjust = 0 80 select @reco_total_waits = 0 81 82 print " Parallel Query Usage per sec per xact count %% of total" 83 print @sum1line 84 select @rptline = " Total Parallel Queries" + space(5) + 85 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 86 space(2) + 87 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 88 space(2) + 89 str(@tmp_total, 10) + space(7) + 90 @na_str 91 print @rptline 92 if @tmp_total > 0 93 begin 94 select @tmp_int = value 95 from #tempmonitors 96 where group_name = "parallel" and 97 field_name = "pll_runtime_adjust_to_thread_limit" 98 99 select @reco_runtime_adjust = convert(int, 100 100.0 * ((1.0 * @tmp_int) / @reco_total_queries)) 101 102 select @rptline = " WP Adjustments Made" 103 print @rptline 104 105 select @rptline = " Due to WP Limit " + space(9) + 106 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 107 space(2) + 108 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 109 space(2) + 110 str(@tmp_int, 10) + space(5) + 111 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 112 @psign 113 print @rptline 114 115 116 select @tmp_int = value 117 from #tempmonitors 118 where group_name = "parallel" and 119 field_name = "pll_runtime_adjust_dueto_nothreads" 120 121 select @reco_no_thread_adjusts = convert(int, 122 100.0 * ((1.0 * @tmp_int) / @reco_total_queries)) 123 124 select @rptline = " Due to No WPs" + space(12) + 125 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 126 space(2) + 127 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 128 space(2) + 129 str(@tmp_int, 10) + space(5) + 130 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 131 @psign 132 print @rptline 133 end 134 135 136 137 138 print @blankline 139 select @tmp_total = sum(value) 140 from #tempmonitors 141 where group_name = "parallel" 142 and field_name like "pll_%lock_%" 143 print " Merge Lock Requests per sec per xact count %% of total" 144 print @sum1line 145 146 if @tmp_total = 0 147 begin 148 select @rptline = " Total # of Requests 0.0 0.0 0 n/a" 149 print @rptline 150 end 151 else 152 begin 153 select @rptline = " Network Buffer Merge Locks" 154 print @rptline 155 156 select @tmp_int = sum(value) 157 from #tempmonitors 158 where group_name = "parallel" and 159 field_name like "pll_tbm_lock_immed%" 160 161 select @rptline = " Granted with no wait" + space(3) + 162 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 163 space(2) + 164 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 165 space(2) + 166 str(@tmp_int, 10) + space(5) + 167 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 168 @psign 169 print @rptline 170 select @tmp_int = sum(value) 171 from #tempmonitors 172 where group_name = "parallel" and 173 field_name like "pll_tbm_lock_waited%" 174 175 select @rptline = " Granted after wait" + space(5) + 176 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 177 space(2) + 178 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 179 space(2) + 180 str(@tmp_int, 10) + space(5) + 181 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 182 @psign 183 print @rptline 184 print @blankline 185 186 select @rptline = " Result Buffer Merge Locks" 187 print @rptline 188 189 select @tmp_int = sum(value) 190 from #tempmonitors 191 where group_name = "parallel" and 192 field_name like "pll_rbm_lock_immed%" 193 194 select @rptline = " Granted with no wait" + space(3) + 195 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 196 space(2) + 197 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 198 space(2) + 199 str(@tmp_int, 10) + space(5) + 200 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 201 @psign 202 print @rptline 203 204 select @tmp_int = sum(value) 205 from #tempmonitors 206 where group_name = "parallel" and 207 field_name like "pll_rbm_lock_waited%" 208 209 select @rptline = " Granted after wait" + space(5) + 210 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 211 space(2) + 212 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 213 space(2) + 214 str(@tmp_int, 10) + space(5) + 215 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 216 @psign 217 print @rptline 218 print @blankline 219 220 select @rptline = " Work Table Merge Locks" 221 print @rptline 222 223 select @tmp_int = sum(value) 224 from #tempmonitors 225 where group_name = "parallel" and 226 field_name like "pll_wkm_lock_immed%" 227 228 select @rptline = " Granted with no wait" + space(3) + 229 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 230 space(2) + 231 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 232 space(2) + 233 str(@tmp_int, 10) + space(5) + 234 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 235 @psign 236 print @rptline 237 238 select @tmp_int = sum(value) 239 from #tempmonitors 240 where group_name = "parallel" and 241 field_name like "pll_wkm_lock_waited%" 242 243 select @rptline = " Granted after wait" + space(5) + 244 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 245 space(2) + 246 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 247 space(2) + 248 str(@tmp_int, 10) + space(5) + 249 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 250 @psign 251 print @rptline 252 253 print @sum2line 254 select @rptline = " Total # of Requests" + space(8) + 255 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 256 space(2) + 257 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 258 space(2) + 259 str(@tmp_total, 10) 260 print @rptline 261 end 262 263 print @blankline 264 select @tmp_total = sum(value) 265 from #tempmonitors 266 where group_name = "parallel" 267 and field_name like "pll_pipe_%buf_waited" 268 269 if (@reco_total_queries > 0) 270 begin 271 select @reco_total_waits = convert(int, 272 100.0 * ((1.0 * @tmp_total) / @reco_total_queries)) 273 end 274 275 print " Sort Buffer Waits per sec per xact count %% of total" 276 print @sum1line 277 278 if @tmp_total = 0 279 begin 280 select @rptline = " Total # of Waits 0.0 0.0 0 n/a" 281 print @rptline 282 end 283 else 284 begin 285 select @tmp_int = value 286 from #tempmonitors 287 where group_name = "parallel" and 288 field_name = "pll_pipe_allocbuf_waited" 289 290 select @rptline = " Producer Waits" + space(11) + 291 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 292 space(2) + 293 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 294 space(2) + 295 str(@tmp_int, 10) + space(5) + 296 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 297 @psign 298 299 print @rptline 300 select @tmp_int = value 301 from #tempmonitors 302 where group_name = "parallel" and 303 field_name = "pll_pipe_recvbuf_waited" 304 305 select @rptline = " Consumer Waits" + space(11) + 306 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 307 space(2) + 308 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 309 space(2) + 310 str(@tmp_int, 10) + space(5) + 311 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 312 @psign 313 314 print @rptline 315 print @sum2line 316 select @rptline = " Total # of Waits" + space(11) + 317 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 318 space(2) + 319 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 320 space(2) + 321 str(@tmp_total, 10) 322 print @rptline 323 end 324 print @blankline 325 if @Reco = 'Y' 326 begin 327 select @recotxt = " Tuning Recommendations for Parallel Query Management" 328 select @recoline = " ----------------------------------------------------" 329 select @reco_hdr_prn = 0 330 331 /* 332 ** If the % of number of thread adjusts due to no threads 333 ** to the number of parallel queries run is > 10%, 334 ** then consider increasing the 'number of worker processes' 335 ** configuration parameter 336 */ 337 if (@reco_no_thread_adjusts > 10) 338 begin 339 if (@reco_hdr_prn = 0) 340 begin 341 print @recotxt 342 print @recoline 343 select @reco_hdr_prn = 1 344 end 345 print " - Consider increasing the 'number of worker processes'" 346 print " configuration parameter." 347 print @blankline 348 end 349 350 /* 351 ** If the number of thread adjusts due to worker process limit 352 ** to the number of parallel queries run is > 10% 353 ** then consider tuning your session level limits 354 */ 355 if (@reco_runtime_adjust > 10) 356 begin 357 if (@reco_hdr_prn = 0) 358 begin 359 print @recotxt 360 print @recoline 361 select @reco_hdr_prn = 1 362 end 363 print " - Consider re-evaluating your session level limts set with" 364 print " 'set scan_parallel_degree' or 'set parallel_degree'." 365 print @blankline 366 end 367 /* 368 ** If the number of waits due to pipe buffers is greater 369 ** to the number of parallel queries run in > 5% 370 ** consider increasing the 'number of sort buffers' 371 */ 372 if (@reco_total_waits > 5) 373 begin 374 if (@reco_hdr_prn = 0) 375 begin 376 print @recotxt 377 print @recoline 378 select @reco_hdr_prn = 1 379 end 380 print " - Consider increasing the 'number of sort buffers'" 381 print " configuration parameter." 382 print @blankline 383 end 384 end 385 return 0 386
exec sp_procxmode 'sp_sysmon_parallel', 'AnyMode' go Grant Execute on sp_sysmon_parallel to public go
| DEPENDENCIES |
| PROCS AND TABLES USED reads table tempdb..#tempmonitors (1) CALLERS called by proc sybsystemprocs..sp_sysmon_analyze  called by proc sybsystemprocs..sp_sysmon  |
