Database | Proc | Application | Created | Links |
sybsystemprocs | sp_sysmon_maccess | 31 Aug 14 | Defects Dependencies |
1 2 /* This stored procedure produces a report containing a summary of 3 ** SQL Server monitor access. 4 */ 5 create procedure sp_sysmon_maccess 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_total int /* temp var for summing 'total #s' data */ 15 declare @sum2line char(80) 16 declare @blankline char(1) /* to print blank line */ 17 declare @psign char(3) /* hold a percent sign (%) for print out */ 18 declare @na_str char(3) /* holds 'n/a' for 'not applicable' strings */ 19 declare @rptline char(80) /* formatted statistics line for print 20 ** statement */ 21 declare @section char(80) /* string to delimit sections on printout */ 22 /* ------------- Variables for Tuning Recommendations ------------*/ 23 declare @recotxt char(80) 24 declare @recoline char(80) 25 declare @reco_hdr_prn bit 26 declare @char_str varchar(30) 27 declare @char_trimmed varchar(30) 28 29 /* --------- Setup Environment --------- */ 30 set nocount on /* disable row counts being sent to client */ 31 32 select @blankline = " " 33 select @psign = " %%" /* extra % symbol because '%' is escape char in print statement */ 34 select @na_str = "n/a" 35 select @sum2line = " ------------ ------------ ---------- ----------" 36 select @section = "===============================================================================" 37 38 /* ======================= Monitor Access to Executing SQL Section =================== */ 39 if not exists (select * 40 from #tempmonitors 41 where group_name = "monitor_access" and 42 field_name = "spin_for_plan") 43 begin 44 print @blankline 45 return 0 46 end 47 48 print @section 49 print @blankline 50 print "Monitor Access to Executing SQL" 51 print "-------------------------------" 52 print " per sec per xact count %% of total" 53 print @sum2line 54 55 select @tmp_int = value 56 from #tempmonitors 57 where group_name = "monitor_access" and 58 field_name = "spin_for_plan" 59 60 select @rptline = " Waits on Execution Plans" + space(3) + 61 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) 62 + space(2) + 63 str(@tmp_int / convert(real, @NumXacts), 12, 1) 64 + space(2) + 65 str(@tmp_int, 10) + space(7) + 66 @na_str 67 print @rptline 68 69 select @tmp_int = value 70 from #tempmonitors 71 where group_name = "monitor_access" and 72 field_name = "sql_mon_txt_size_overflows" 73 74 select @rptline = " Number of SQL Text Overflows" + 75 str(@tmp_int / (@NumElapsedMs / 1000.0), 11, 1) 76 + space(2) + 77 str(@tmp_int / convert(real, @NumXacts), 12, 1) 78 + space(2) + 79 str(@tmp_int, 10) + space(7) + 80 @na_str 81 print @rptline 82 83 select @tmp_int = value 84 from #tempmonitors 85 where group_name = "monitor_access" and 86 field_name = "sql_mon_txt_reqd_hwm" 87 88 select @rptline = " Maximum SQL Text Requested" + 89 space(10) + @na_str + 90 space(11) + @na_str + 91 space(2) + str(@tmp_int, 10) + 92 space(7) + @na_str 93 print @rptline 94 select @rptline = " (since beginning of sample)" 95 print @rptline 96 97 print @blankline 98 99 if (@Reco = 'Y') 100 begin 101 print @blankline 102 select @recotxt = " Tuning Recommendations for Monitor Access to Executing SQL" 103 select @recoline = " ----------------------------------------------------------" 104 select @reco_hdr_prn = 0 105 106 select @tmp_total = 0 107 select @tmp_int = 0 108 select @tmp_int2 = 0 109 110 select @tmp_int = convert(integer, value) from #tempconfigures 111 where name like "max SQL text monitored" 112 113 if (@tmp_int > 0) 114 begin 115 select @tmp_int2 = value from #tempmonitors 116 where group_name = "monitor_access" and 117 field_name = "sql_mon_txt_reqd_hwm" 118 119 /* 120 ** If the high water mark for the sql text monitored 121 ** is greater than 'max SQL text monitored' consider 122 ** increasing the 'max SQL text monitored' configuration 123 ** parameter 124 */ 125 if (@tmp_int2 > @tmp_int) 126 begin 127 if (@reco_hdr_prn = 0) 128 begin 129 print @recotxt 130 print @recoline 131 select @reco_hdr_prn = 1 132 end 133 select @char_str = str(@tmp_int + ((@tmp_int2 - @tmp_int) / 2)) 134 select @char_trimmed = ltrim(@char_str) 135 print " - Consider increasing the 'max SQL text monitored' parameter " 136 print " to at least %1! (i.e., half way from its current value ", 137 @char_trimmed 138 print " to Maximum SQL Text Requested)." 139 print @blankline 140 end 141 else 142 /* 143 ** If the 'max SQL text monitored' is greater than the 144 ** high water mark for sql text monitored 145 ** consider decreasing the 'max SQL text monitored' 146 ** configuration parameter 147 */ 148 if (@tmp_int > @tmp_int2) 149 begin 150 if (@reco_hdr_prn = 0) 151 begin 152 print @recotxt 153 print @recoline 154 select @reco_hdr_prn = 1 155 end 156 select @char_str = str(@tmp_int2 + ((@tmp_int - @tmp_int2) / 2)) 157 select @char_trimmed = ltrim(@char_str) 158 print " - Consider decreasing the 'max SQL text monitored' parameter " 159 print " to %1! (i.e., half way from its current value to Maximum ", 160 @char_trimmed 161 print " SQL Text Requested)." 162 print @blankline 163 end 164 end 165 end 166 return 0 167
exec sp_procxmode 'sp_sysmon_maccess', 'AnyMode' go Grant Execute on sp_sysmon_maccess to public go
DEPENDENCIES |
PROCS AND TABLES USED reads table tempdb..#tempmonitors (1) reads table tempdb..#tempconfigures (1) CALLERS called by proc sybsystemprocs..sp_sysmon_analyze called by proc sybsystemprocs..sp_sysmon |