DatabaseProcApplicationCreatedLinks
sybsystemprocssp_sysmon_hk  31 Aug 14Defects Dependencies

1     
2     /* This stored procedure produces a report containing a summary of
3     ** SQL Server housekeeper activities.
4     */
5     create procedure sp_sysmon_hk
6         @NumElapsedMs int, /* for "per Elapsed second" calculations */
7         @NumXacts int, /* for per transactions calculations */
8         @Reco char(1), /* Flag for recommendations */
9         @instid smallint = NULL /* optional SDC instance id */
10    as
11    
12        /* --------- declare local variables --------- */
13        declare @tmp_int int /* temp var for integer storage */
14        declare @tmp_int2 int /* temp var for integer storage */
15        declare @tmp_total int /* temp var for summing 'total #s' data */
16        declare @sum2line char(80)
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 statistics line for print 
21        ** statement */
22        declare @section char(80) /* string to delimit sections on printout */
23        /* ------------- Variables for Tuning Recommendations ------------*/
24        declare @recotxt char(80)
25        declare @recoline char(80)
26        declare @reco_hdr_prn bit
27        declare @char_str varchar(30)
28        declare @char_trimmed varchar(30)
29        declare @engines tinyint
30        declare @eng_count int
31        declare @tmp_grp_name varchar(80)
32        declare @reco_avg_busy real /* Avg cpu busy utilization */
33        declare @reco_percent real /* temp var for percentage storage */
34        declare @reco_idle_ticks real /* temp var for percentage storage */
35        declare @reco_clock_ticks real /* temp var for percentage storage */
36        declare @reco_dirty real
37        declare @reco_clean real
38    
39        declare @i int
40    
41        /* --------- Setup Environment --------- */
42        set nocount on /* disable row counts being sent to client */
43    
44        select @blankline = " "
45        select @psign = " %%" /* extra % symbol because '%' is escape char in print statement */
46        select @na_str = "n/a"
47        select @sum2line = "                             ------------  ------------  ----------"
48        select @section = "==============================================================================="
49    
50        /* ======================= Housekeeper Section =================== */
51        print @section
52        print @blankline
53        print "Housekeeper Task Activity"
54        print "-------------------------"
55        print "                                  per sec      per xact       count  %% of total"
56        print @sum2line
57    
58        select @tmp_total = sum(value)
59        from #tempmonitors
60        where group_name like "buffer_%" and
61            field_name = "hk_wash"
62    
63        if (@tmp_total > 0)
64        begin
65            select @rptline = "Buffer Cache Washes"
66    
67            print @rptline
68    
69            select @tmp_int2 = sum(value)
70            from #tempmonitors
71            where group_name like "buffer_%" and
72                field_name = "hk_washclean"
73    
74            select @rptline = "  Clean" + space(22) +
75                str(@tmp_int2 / (@NumElapsedMs / 1000.0), 11, 1)
76                + space(2) +
77                str(@tmp_int2 / convert(real, @NumXacts), 12, 1)
78                + space(2) +
79                str(@tmp_int2, 10) + space(5) +
80                str(100.0 * @tmp_int2 / @tmp_total, 5, 1) + @psign
81    
82            select @reco_clean = convert(int, 100.0 * ((1.0 * @tmp_int2) / @tmp_total))
83    
84            print @rptline
85    
86            select @tmp_int = @tmp_total - @tmp_int2
87    
88            select @rptline = "  Dirty" + space(22) +
89                str(@tmp_int / (@NumElapsedMs / 1000.0), 11, 1)
90                + space(2) +
91                str(@tmp_int / convert(real, @NumXacts), 12, 1)
92                + space(2) +
93                str(@tmp_int, 10) + space(5) +
94                str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
95    
96            select @reco_dirty = convert(int, 100.0 * ((1.0 * @tmp_int2) / @tmp_total))
97    
98            print @rptline
99    
100           print @sum2line
101   
102           select @rptline = "Total Washes" + space(17) +
103               str(@tmp_total / (@NumElapsedMs / 1000.0), 11, 1)
104               + space(2) +
105               str(@tmp_total / convert(real, @NumXacts), 12, 1)
106               + space(2) +
107               str(@tmp_total, 10)
108           print @rptline
109   
110           print @blankline
111       end
112   
113       select @tmp_int = value
114       from #tempmonitors
115       where group_name = "housekeeper" and
116           field_name = "hk_gc_wakes"
117   
118       select @rptline = "Garbage Collections" + space(9) +
119           str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1)
120           + space(2) +
121           str(@tmp_int / convert(real, @NumXacts), 12, 1)
122           + space(2) +
123           str(@tmp_int, 10) + space(7) +
124           @na_str
125       print @rptline
126   
127       select @tmp_int = value
128       from #tempmonitors
129       where group_name = "housekeeper" and
130           field_name = "hk_gc_numgoodpages"
131   
132       select @rptline = "Pages Processed in GC" + space(8) +
133           str(@tmp_int / (@NumElapsedMs / 1000.0), 11, 1)
134           + space(2) +
135           str(@tmp_int / convert(real, @NumXacts), 12, 1)
136           + space(2) +
137           str(@tmp_int, 10) + space(7) +
138           @na_str
139       print @rptline
140   
141       print @blankline
142   
143       select @tmp_int = value
144       from #tempmonitors
145       where group_name = "housekeeper" and
146           field_name = "hk_stats_wakes"
147   
148       select @rptline = "Statistics Updates" + space(11) +
149           str(@tmp_int / (@NumElapsedMs / 1000.0), 11, 1)
150           + space(2) +
151           str(@tmp_int / convert(real, @NumXacts), 12, 1)
152           + space(2) +
153           str(@tmp_int, 10) + space(7) +
154           @na_str
155       print @rptline
156   
157       print @blankline
158   
159       if @Reco = 'Y'
160       begin
161           select @recotxt = "  Tuning Recommendations for Housekeeper"
162           select @recoline = "  --------------------------------------"
163           select @reco_hdr_prn = 0
164   
165           select @i = 0, @eng_count = 0
166   
167   
168           select @engines = count(engine)
169           from master.dbo.sysengines
170           where status != "offline"
171   
172   
173           select @reco_clock_ticks = SUM(value)
174           from #tempmonitors
175           where group_name like "engine_%" and
176               field_name = "clock_ticks" and
177               value > 0
178   
179           select @reco_idle_ticks = SUM(value)
180           from #tempmonitors
181           where group_name like "engine_%" and
182               field_name = "idle_ticks" and
183               value > 0
184   
185           select @reco_percent =
186               100.0 * ((@reco_clock_ticks - @reco_idle_ticks) / @reco_clock_ticks)
187   
188           if @engines > 1
189           begin
190               select @reco_avg_busy = @reco_percent / @engines
191           end
192   
193           /*
194           ** If the average CPU busy is > 95% and if the
195           ** percentage of masses washed clean by HK is
196           ** greater than 95%, we have an overactive 
197           ** housekeeper which could be intruding with
198           ** user tasks, so reduce the 'housekeeper free write
199           ** percent' configuration parameter.
200           */
201           if @reco_avg_busy > 95 AND @reco_clean > 95
202           begin
203               if (@reco_hdr_prn = 0)
204               begin
205                   print @recotxt
206                   print @recoline
207                   select @reco_hdr_prn = 1
208               end
209   
210               print "  - Consider decreasing the 'housekeeper free write percent'"
211               print "    configuration parameter."
212               select @reco_hdr_prn = 1
213               print @blankline
214           end
215   
216           /*
217           ** If the average CPU busy is < 5% and if the
218           ** percentage of masses washed dirty by HK is
219           ** greater than 50%, we have a lot more washing to do
220           ** and since we have the cycles to do it, do it.
221           */
222           if @reco_avg_busy < 5 AND @reco_dirty > 50
223           begin
224               if (@reco_hdr_prn = 0)
225               begin
226                   print @recotxt
227                   print @recoline
228                   select @reco_hdr_prn = 1
229               end
230               print "  - Consider increasing the 'housekeeper free write percent'"
231               print "    configuration parameter."
232               select @reco_hdr_prn = 1
233               print @blankline
234           end
235       end
236   
237   
238       return 0
239   


exec sp_procxmode 'sp_sysmon_hk', 'AnyMode'
go

Grant Execute on sp_sysmon_hk to public
go
DEFECTS
 TNOI 4 Table with no index master..sysengines master..sysengines
 MGTP 3 Grant to public master..sysengines  
 MGTP 3 Grant to public sybsystemprocs..sp_sysmon_hk  
 MLCH 3 Char type with length>30 char(80) 16
 MLCH 3 Char type with length>30 char(80) 20
 MLCH 3 Char type with length>30 char(80) 22
 MLCH 3 Char type with length>30 char(80) 24
 MLCH 3 Char type with length>30 char(80) 25
 MUCO 3 Useless Code Useless Brackets 63
 MUCO 3 Useless Code Useless Brackets 203
 MUCO 3 Useless Code Useless Brackets 224
 QAFM 3 Var Assignment from potentially many rows 113
 QAFM 3 Var Assignment from potentially many rows 127
 QAFM 3 Var Assignment from potentially many rows 143
 VNRD 3 Variable is not read @eng_count 165
 VNRD 3 Variable is not read @i 165
 VNRD 3 Variable is not read @reco_hdr_prn 232
 VUNU 3 Variable is not used @instid 9
 VUNU 3 Variable is not used @char_str 27
 VUNU 3 Variable is not used @char_trimmed 28
 VUNU 3 Variable is not used @tmp_grp_name 31
 MTR1 2 Metrics: Comments Ratio Comments: 20% 5
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 19 = 18dec - 1exi + 2 5
 MTR3 2 Metrics: Query Complexity Complexity: 128 5

DEPENDENCIES
PROCS AND TABLES USED
reads table tempdb..#tempmonitors (1) 
reads table master..sysengines (1)  

CALLERS
called by proc sybsystemprocs..sp_sysmon_analyze  
   called by proc sybsystemprocs..sp_sysmon