DatabaseProcApplicationCreatedLinks
sybsystemprocssp_sysmon_esp  31 Aug 14Defects 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_esp
6         @NumElapsedMs int, /* for "per Elapsed second" calculations */
7         @NumXacts int /* for per transactions calculations */
8     as
9     
10        /* --------- declare local variables --------- */
11        declare @tmp_int int /* temp var for integer storage */
12        declare @tmp_total int /* temp var for summing 'total #s' data */
13        declare @sum1line char(80) /* string to delimit total lines without 
14        ** percent calc on printout */
15        declare @blankline char(1) /* to print blank line */
16        declare @psign char(3) /* hold a percent sign (%) for print out */
17        declare @na_str char(3) /* holds 'n/a' for 'not applicable' strings */
18        declare @rptline char(80) /* formatted stats line for print statement */
19        declare @section char(80) /* string to delimit sections on printout */
20    
21        /* --------- Setup Environment --------- */
22        set nocount on /* disable row counts being sent to client */
23    
24        select @sum1line = "---------------------------  ------------  ------------  ----------  ----------"
25        select @blankline = " "
26        select @psign = " %%" /* extra % symbol because '%' is escape char in print statement */
27        select @na_str = "n/a"
28        select @section = "==============================================================================="
29    
30        print @section
31        print @blankline
32    
33        print "ESP Management                    per sec      per xact       count  %% of total"
34        print @sum1line
35    
36        select @tmp_total = value
37        from #tempmonitors
38        where group_name = "access" and
39            field_name = "esp_requests"
40    
41        select @rptline = "  ESP Requests" + space(15) +
42            str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
43            space(2) +
44            str(@tmp_total / convert(real, @NumXacts), 12, 1) +
45            space(2) +
46            str(@tmp_total, 10) + space(7) +
47            @na_str
48        print @rptline
49    
50        if @tmp_total != 0
51        begin
52            select @tmp_int = convert(int, m.value * (convert(float, n.value) / 1000000))
53    
54            from #tempmonitors m, #tempconfigures n
55            where m.group_name = "access" and
56                m.field_name = "esp_total_exec_ticks" and
57                n.name = "sql server clock tick length"
58    
59            select @rptline = "  Avg. Time to Execute an ESP" +
60                str(convert(real, @tmp_int) / @tmp_total, 12, 5) +
61                " seconds"
62            print @rptline
63    
64        end
65    
66        return
67    


exec sp_procxmode 'sp_sysmon_esp', 'AnyMode'
go

Grant Execute on sp_sysmon_esp to public
go
DEFECTS
 QCAR 6 Cartesian product between tables #tempmonitors m and [#tempconfigures n] 54
 MGTP 3 Grant to public sybsystemprocs..sp_sysmon_esp  
 MLCH 3 Char type with length>30 char(80) 13
 MLCH 3 Char type with length>30 char(80) 18
 MLCH 3 Char type with length>30 char(80) 19
 QAFM 3 Var Assignment from potentially many rows 36
 QAFM 3 Var Assignment from potentially many rows 52
 QNAJ 3 Not using ANSI Inner Join 54
 VNRD 3 Variable is not read @psign 26
 MTR1 2 Metrics: Comments Ratio Comments: 32% 5
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 5 = 4dec - 1exi + 2 5
 MTR3 2 Metrics: Query Complexity Complexity: 36 5

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

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