DatabaseProcApplicationCreatedLinks
sybsystemprocssp_sysmon_maccess  31 Aug 14Defects 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
DEFECTS
 MGTP 3 Grant to public sybsystemprocs..sp_sysmon_maccess  
 MLCH 3 Char type with length>30 char(80) 15
 MLCH 3 Char type with length>30 char(80) 19
 MLCH 3 Char type with length>30 char(80) 21
 MLCH 3 Char type with length>30 char(80) 23
 MLCH 3 Char type with length>30 char(80) 24
 MUCO 3 Useless Code Useless Brackets 99
 MUCO 3 Useless Code Useless Brackets 113
 MUCO 3 Useless Code Useless Brackets 125
 MUCO 3 Useless Code Useless Brackets 127
 MUCO 3 Useless Code Useless Brackets 133
 MUCO 3 Useless Code Useless Brackets 148
 MUCO 3 Useless Code Useless Brackets 150
 MUCO 3 Useless Code Useless Brackets 156
 QAFM 3 Var Assignment from potentially many rows 55
 QAFM 3 Var Assignment from potentially many rows 69
 QAFM 3 Var Assignment from potentially many rows 83
 QAFM 3 Var Assignment from potentially many rows 110
 QAFM 3 Var Assignment from potentially many rows 115
 VNRD 3 Variable is not read @psign 33
 VNRD 3 Variable is not read @tmp_total 106
 VNRD 3 Variable is not read @reco_hdr_prn 154
 MSUB 2 Subquery Marker 39
 MTR1 2 Metrics: Comments Ratio Comments: 21% 5
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 12 = 12dec - 2exi + 2 5
 MTR3 2 Metrics: Query Complexity Complexity: 100 5

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