Database | Proc | Application | Created | Links |
sybsystemprocs | sp_sysmon_pcache | 31 Aug 14 | Defects Dependencies |
1 2 /* This stored procedure produces a report containing a summary of 3 ** of how effectively the procedure cache is being utilized. 4 */ 5 create procedure sp_sysmon_pcache 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_total int /* temp var for summing 'total #s' data */ 14 declare @tmp_reco_total int /* temp var for summing 'total #s' data */ 15 declare @sum1line char(80) /* string to delimit total lines without 16 ** percent calc on printout */ 17 declare @blankline char(1) /* to print blank line */ 18 declare @psign char(3) /* hold a percent sign (%) for print out */ 19 declare @na_str char(3) /* holds 'n/a' for 'not applicable' strings */ 20 declare @rptline char(80) /* formatted stats line for print statement */ 21 declare @section char(80) /* string to delimit sections on printout */ 22 23 /* ------------- Variables for Tuning Recommendations ------------*/ 24 declare @recotxt char(80) /* Header for tuning recommendation */ 25 declare @recoline char(80) /* to underline recotxt */ 26 declare @reco_hdr_prn bit /* to indicate if the recotxt is already printed */ 27 declare @reco_phdr_read_from_disk real 28 29 /* --------- Setup Environment --------- */ 30 set nocount on /* disable row counts being sent to client */ 31 32 select @sum1line = "--------------------------- ------------ ------------ ---------- ----------" 33 select @blankline = " " 34 select @psign = " %%" /* extra % symbol because '%' is escape char in print statement */ 35 select @na_str = "n/a" 36 select @section = "===============================================================================" 37 38 print @section 39 print @blankline 40 41 print "Procedure Cache Management per sec per xact count %% of total" 42 print @sum1line 43 44 select @tmp_total = value 45 from #tempmonitors 46 where group_name = "procmgr" and 47 field_name = "procedure_requests" 48 49 select @rptline = " Procedure Requests" + space(9) + 50 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 51 space(2) + 52 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 53 space(2) + 54 str(@tmp_total, 10) + space(7) + 55 @na_str 56 print @rptline 57 58 if @tmp_total != 0 59 begin 60 select @tmp_int = value 61 from #tempmonitors 62 where group_name = "procmgr" and 63 field_name = "procedure_reads" 64 65 select @rptline = " Procedure Reads from Disk" + space(2) + 66 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 67 space(2) + 68 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 69 space(2) + 70 str(@tmp_int, 10) + space(5) + 71 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 72 print @rptline 73 74 select @reco_phdr_read_from_disk = 75 convert(int, 100.0 * ((1.0 * @tmp_int) / @tmp_total)) 76 77 select @tmp_int = value 78 from #tempmonitors 79 where group_name = "procmgr" and 80 field_name = "procedure_writes" 81 82 select @rptline = " Procedure Writes to Disk" + space(3) + 83 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 84 space(2) + 85 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 86 space(2) + 87 str(@tmp_int, 10) + space(5) + 88 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 89 print @rptline 90 91 select @tmp_int = value 92 from #tempmonitors 93 where group_name = "procmgr" and 94 field_name = "procedure_removes" 95 96 select @rptline = " Procedure Removals" + space(9) + 97 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 98 space(2) + 99 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 100 space(2) + 101 str(@tmp_int, 10) + space(7) + 102 @na_str 103 104 print @rptline 105 106 select @tmp_reco_total = sum(value) 107 from #tempmonitors 108 where group_name = "procmgr" and 109 field_name in ("proc_recompilation_exec", 110 "proc_recompilation_comp", 111 "proc_recompilation_exec_curs", 112 "proc_recompilation_redef") 113 114 select @rptline = " Procedure Recompilations" + space(3) + 115 str(@tmp_reco_total / (@NumElapsedMs / 1000.0), 12, 1) + 116 space(2) + 117 str(@tmp_reco_total / convert(real, @NumXacts), 12, 1) + 118 space(2) + 119 str(@tmp_reco_total, 10) + space(7) + 120 @na_str 121 122 print @rptline 123 124 if (@tmp_reco_total != 0) 125 begin 126 print @blankline 127 128 print " Recompilations Requests:" 129 130 select @tmp_int = value 131 from #tempmonitors 132 where group_name = "procmgr" and 133 field_name = "proc_recompilation_exec" 134 135 select @rptline = " Execution Phase" + space(10) + 136 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 137 space(2) + 138 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 139 space(2) + 140 str(@tmp_int, 10) + space(5) + 141 str(100.0 * @tmp_int / @tmp_reco_total, 5, 1) + @psign 142 143 print @rptline 144 145 select @tmp_int = value 146 from #tempmonitors 147 where group_name = "procmgr" and 148 field_name = "proc_recompilation_comp" 149 150 select @rptline = " Compilation Phase" + space(8) + 151 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 152 space(2) + 153 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 154 space(2) + 155 str(@tmp_int, 10) + space(5) + 156 str(100.0 * @tmp_int / @tmp_reco_total, 5, 1) + @psign 157 158 print @rptline 159 160 select @tmp_int = value 161 from #tempmonitors 162 where group_name = "procmgr" and 163 field_name = "proc_recompilation_exec_curs" 164 165 select @rptline = " Execute Cursor Execution" + space(1) + 166 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 167 space(2) + 168 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 169 space(2) + 170 str(@tmp_int, 10) + space(5) + 171 str(100.0 * @tmp_int / @tmp_reco_total, 5, 1) + @psign 172 173 print @rptline 174 175 select @tmp_int = value 176 from #tempmonitors 177 where group_name = "procmgr" and 178 field_name = "proc_recompilation_redef" 179 180 select @rptline = " Redefinition Phase" + space(7) + 181 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 182 space(2) + 183 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 184 space(2) + 185 str(@tmp_int, 10) + space(5) + 186 str(100.0 * @tmp_int / @tmp_reco_total, 5, 1) + @psign 187 188 print @rptline 189 190 print @blankline 191 192 print " Recompilation Reasons:" 193 194 select @tmp_int = value 195 from #tempmonitors 196 where group_name = "procmgr" and 197 field_name = "proc_recomp_tabmissing" 198 199 select @rptline = " Table Missing" + space(12) + 200 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 201 space(2) + 202 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 203 space(2) + 204 str(@tmp_int, 10) + space(7) + 205 @na_str 206 207 print @rptline 208 209 select @tmp_int = value 210 from #tempmonitors 211 where group_name = "procmgr" and 212 field_name = "proc_recomp_tempmissing" 213 214 select @rptline = " Temporary Table Missing" + space(2) + 215 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 216 space(2) + 217 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 218 space(2) + 219 str(@tmp_int, 10) + space(7) + 220 @na_str 221 222 print @rptline 223 224 select @tmp_int = value 225 from #tempmonitors 226 where group_name = "procmgr" and 227 field_name = "proc_recomp_schemacount" 228 229 select @rptline = " Schema Change" + space(12) + 230 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 231 space(2) + 232 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 233 space(2) + 234 str(@tmp_int, 10) + space(7) + 235 @na_str 236 237 print @rptline 238 239 select @tmp_int = value 240 from #tempmonitors 241 where group_name = "procmgr" and 242 field_name = "proc_recomp_idxchange" 243 244 select @rptline = " Index Change" + space(13) + 245 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 246 space(2) + 247 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 248 space(2) + 249 str(@tmp_int, 10) + space(7) + 250 @na_str 251 252 print @rptline 253 254 select @tmp_int = value 255 from #tempmonitors 256 where group_name = "procmgr" and 257 field_name = "proc_recomp_isolevel" 258 259 select @rptline = " Isolation Level Change" + space(3) + 260 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 261 space(2) + 262 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 263 space(2) + 264 str(@tmp_int, 10) + space(7) + 265 @na_str 266 267 print @rptline 268 269 select @tmp_int = value 270 from #tempmonitors 271 where group_name = "procmgr" and 272 field_name = "proc_recomp_permissions" 273 274 select @rptline = " Permissions Change" + space(7) + 275 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 276 space(2) + 277 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 278 space(2) + 279 str(@tmp_int, 10) + space(7) + 280 @na_str 281 282 print @rptline 283 284 select @tmp_int = value 285 from #tempmonitors 286 where group_name = "procmgr" and 287 field_name = "proc_recomp_cursor_perm" 288 289 select @rptline = " Cursor Permissions Change" + 290 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 291 space(2) + 292 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 293 space(2) + 294 str(@tmp_int, 10) + space(7) + 295 @na_str 296 297 print @rptline 298 end 299 end 300 301 print @blankline 302 303 print " SQL Statement Cache:" 304 305 select @tmp_total = value 306 from #tempmonitors 307 where group_name = "procmgr" and 308 field_name = "proc_ssql_procs_available" 309 310 select @rptline = " Statements Cached" + space(8) + 311 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 312 space(2) + 313 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 314 space(2) + 315 str(@tmp_total, 10) + space(7) + 316 @na_str 317 print @rptline 318 319 select @tmp_total = value 320 from #tempmonitors 321 where group_name = "procmgr" and 322 field_name = "proc_ssql_found" 323 324 select @rptline = " Statements Found in Cache" + 325 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 326 space(2) + 327 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 328 space(2) + 329 str(@tmp_total, 10) + space(7) + 330 @na_str 331 print @rptline 332 333 select @tmp_total = value 334 from #tempmonitors 335 where group_name = "procmgr" and 336 field_name = "proc_ssql_not_found" 337 338 select @rptline = " Statements Not Found" + space(5) + 339 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 340 space(2) + 341 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 342 space(2) + 343 str(@tmp_total, 10) + space(7) + 344 @na_str 345 print @rptline 346 347 select @tmp_total = value 348 from #tempmonitors 349 where group_name = "procmgr" and 350 field_name = "proc_ssql_dropped" 351 352 select @rptline = " Statements Dropped" + space(7) + 353 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 354 space(2) + 355 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 356 space(2) + 357 str(@tmp_total, 10) + space(7) + 358 @na_str 359 print @rptline 360 361 select @tmp_total = value 362 from #tempmonitors 363 where group_name = "procmgr" and 364 field_name = "proc_ssql_restored" 365 366 select @rptline = " Statements Restored" + space(6) + 367 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 368 space(2) + 369 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 370 space(2) + 371 str(@tmp_total, 10) + space(7) + 372 @na_str 373 print @rptline 374 375 select @tmp_total = value 376 from #tempmonitors 377 where group_name = "procmgr" and 378 field_name = "proc_ssql_notcached" 379 380 select @rptline = " Statements Not Cached" + space(4) + 381 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 382 space(2) + 383 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 384 space(2) + 385 str(@tmp_total, 10) + space(7) + 386 @na_str 387 print @rptline 388 389 print @blankline 390 if @Reco = 'Y' 391 begin 392 select @recotxt = " Tuning Recommendations for Procedure cache management" 393 select @recoline = " -----------------------------------------------------" 394 select @reco_hdr_prn = 0 395 396 /* 397 ** If % of the number of times a procedure was read from disk is 398 ** is > 10% consider increasing the procedure cache size 399 */ 400 if @reco_phdr_read_from_disk > 10 401 begin 402 if (@reco_hdr_prn = 0) 403 begin 404 print @recotxt 405 print @recoline 406 select @reco_hdr_prn = 1 407 end 408 409 print " - Consider increasing the 'procedure cache size'" 410 print " configuration parameter." 411 print @blankline 412 end 413 end 414 415 416 print @blankline 417 return 418
exec sp_procxmode 'sp_sysmon_pcache', 'AnyMode' go Grant Execute on sp_sysmon_pcache 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 |