DatabaseProcApplicationCreatedLinks
sybsystemprocssp_sysmon_memory  31 Aug 14Defects Dependencies

1     
2     /* This stored procedure produces a report containing a summary of
3     ** memory use within the SQL server's pool of available memory.
4     */
5     create procedure sp_sysmon_memory
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 @sum1line char(80) /* string to delimit total lines without 
13        percent calc on printout*/
14        declare @blankline char(1) /* to print blank line */
15        declare @na_str char(3) /* holds 'n/a' for 'not applicable' strings */
16        declare @rptline char(80) /* formatted stats line for print statement */
17        declare @section char(80) /* string to delimit sections on printout */
18    
19        /* --------- Setup Environment --------- */
20        set nocount on /* disable row counts being sent to client */
21    
22        select @sum1line = "---------------------------  ------------  ------------  ----------  ----------"
23        select @blankline = " "
24        select @na_str = "n/a"
25        select @section = "==============================================================================="
26    
27        print @section
28        print @blankline
29    
30        print "Memory Management                 per sec      per xact       count  %% of total"
31        print @sum1line
32    
33        select @tmp_int = value
34        from #tempmonitors
35        where group_name = "memory" and
36            field_name = "mempages_alloced"
37    
38        select @rptline = "  Pages Allocated" + space(12) +
39            str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
40            space(2) +
41            str(@tmp_int / convert(real, @NumXacts), 12, 1) +
42            space(2) +
43            str(@tmp_int, 10) + space(7) +
44            @na_str
45        print @rptline
46    
47        select @tmp_int = value
48        from #tempmonitors
49        where group_name = "memory" and
50            field_name = "mempages_freed"
51    
52        select @rptline = "  Pages Released" + space(13) +
53            str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
54            space(2) +
55            str(@tmp_int / convert(real, @NumXacts), 12, 1) +
56            space(2) +
57            str(@tmp_int, 10) + space(7) +
58            @na_str
59        print @rptline
60    
61        print @blankline
62        return 0
63    


exec sp_procxmode 'sp_sysmon_memory', 'AnyMode'
go

Grant Execute on sp_sysmon_memory to public
go
DEFECTS
 MGTP 3 Grant to public sybsystemprocs..sp_sysmon_memory  
 MLCH 3 Char type with length>30 char(80) 12
 MLCH 3 Char type with length>30 char(80) 16
 MLCH 3 Char type with length>30 char(80) 17
 QAFM 3 Var Assignment from potentially many rows 33
 QAFM 3 Var Assignment from potentially many rows 47
 MTR1 2 Metrics: Comments Ratio Comments: 29% 5
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 3 = 2dec - 1exi + 2 5
 MTR3 2 Metrics: Query Complexity Complexity: 30 5

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

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