DatabaseProcApplicationCreatedLinks
sybsystemprocssp_shmdumpsize  31 Aug 14Defects Dependencies

1     
2     /*
3     ** SP_SHMDUMPSIZE
4     **
5     ** This stored procedure calculates the estimated size for a shared memory
6     ** dump file.  It is not meant to be executed from the command line, but
7     ** is called by the sp_shmdumpconfig stored procedure when displaying
8     ** current configuration informmtion.  Expect the values calculated to be
9     ** conservative and to overestimate the size significantly.
10    **
11    ** Returns the estimated size in the @size output parameter.  This value
12    ** is in megabyte units.
13    **
14    ** History:
15    ** 16dec96	pdorfman	written
16    ** 28aug97	pdorfman	corrected arithmetic overflow problem
17    ** 31mar99	pdorfman	More improvments in memory calculation (197105)
18    ** 20apr04	pdorfman	Correct procedure cache size calculations
19    */
20    
21    create procedure sp_shmdumpsize
22        @pagecache varchar(20), /* Page cache setting for condition           */
23        @proccache varchar(20), /* Procedure cache setting for condition      */
24        @size int output /* Estimated size of dump file in megabytes
25    				** (return value) 
26    				*/
27    as
28        declare @cache_size int,
29            @proc_percent int,
30            @proc_size int,
31            @defpagecache int,
32            @defproccache int,
33            @memory int
34    
35    
36    
37        select @cache_size = 0
38        select @proc_size = 0
39    
40        /*
41        ** Determine the default settings for page caches and procedure cache
42        */
43        select @defpagecache = int_value
44        from master.dbo.sysattributes sa
45        where sa.class = 7
46            and sa.object_type = "DC"
47            and sa.attribute = 5
48            and sa.object_info1 = 5
49    
50        select @defproccache = int_value
51        from master.dbo.sysattributes sa
52        where sa.class = 7
53            and sa.object_type = "DC"
54            and sa.attribute = 5
55            and sa.object_info1 = 6
56    
57        /*
58        ** 19 is the config # for named caches.
59        ** This value is in kilobytes.
60        ** For SDC server, ther are two kinds of data caches:
61        ** 1. Global cache
62        **    For this kind of cache, the instanceid in sysconfigures will be NULL.
63        ** 2. Private cache for an instance
64        **    For this kind of cache, the instanceid in sysconfigures will be the
65        **    instanceid of the instance on which the cache is configured.
66        ** So, for sysconfigures.instanceid, it might have below values:
67        **    (1) NULL
68        **        This means the cache is a global cache.
69        **    (2) instance id
70        **        This mean the cache is for instance.
71        **
72        ** In this procedure, we referred the implementation in sp_helpcache.
73        ** First, we will insert all the caches including the global cache and
74        ** private cache for instances into a temporary table. Then, we will
75        ** scan every rows in this temporaty table. If a row's instanceid is NULL,
76        ** this means it is a global cache. In this case, we need to check if there
77        ** are private cache with the same name for the instance. If there is such
78        ** a cache, we need to skip this row to avoid duplicated calculation.
79        */
80    
81        select @cache_size = sum(cu.value)
82        from master.dbo.sysconfigures co, master.dbo.syscurconfigs cu
83        where co.config = 19
84            and co.config = cu.config
85            and co.name = cu.comment
86    
87    
88        /*
89        ** Obtain the current total memory allocated to the server. 104 is the
90        ** configuration # for total logical memory. 
91        ** For SDC server, there might have multiple instance running. If system_view
92        ** was set as cluster, the total logical memory for all instances will be 
93        ** returned.  This is why sum is needed here.
94        */
95        select @memory = sum(convert(int, comment))
96        from master.dbo.syscurconfigs
97        where config = 104
98    
99        /*
100       ** Set initial size to total memory value.  Subsequent
101       ** operations subtract from this amount.
102       */
103       select @size = @memory
104   
105       /*
106       ** Determine whether and how much memory to exclude for page caches
107       */
108       if (@pagecache = "Omit" or (@pagecache = "Default" and @defpagecache != 1))
109       begin
110           select @size = @size - @cache_size
111       end
112   
113       /*
114       ** Determine whether and how much memory to subtract for procedure cache.
115       ** Since the default action is to include the procedure cache in the dump
116       ** file, only perform this operation if the user has explicitly configured
117       ** this condition to omit the procedure cache or if the dump mode for
118       ** the procedure cache in the Defaults setting is 2 (omit).
119       ** This calculation is very conservative and tends to over estimate
120       ** the amount of space required for the dump file.
121       */
122       if (@proccache = "Omit" or (@proccache = "Default" and @defproccache = 2))
123       begin
124           /*
125           ** Combine procedure cache size and statement cache
126           ** size to get total memory allocated to procedure cache.
127           ** Config # 146 is the procedure cache size value.
128           ** Config # 414 is the statement cache size value.
129           */
130           select @proc_size = sum(convert(int, comment))
131           from master.dbo.syscurconfigs
132           where config in (146, 414)
133   
134           select @size = @size - @proc_size
135       end
136   
137       /*
138       ** Convert @size from kilobytes to megabytes
139       */
140       select @size = (@size / 1024) + 1
141   
142       return 0
143   
144   


exec sp_procxmode 'sp_shmdumpsize', 'AnyMode'
go

Grant Execute on sp_shmdumpsize to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MINU 4 Unique Index with nullable columns master..sysconfigures master..sysconfigures
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 45
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 47
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 52
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 54
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 83
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 97
 TNOI 4 Table with no index master..syscurconfigs master..syscurconfigs
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..sysconfigures  
 MGTP 3 Grant to public master..syscurconfigs  
 MGTP 3 Grant to public sybsystemprocs..sp_shmdumpsize  
 MUCO 3 Useless Code Useless Brackets 108
 MUCO 3 Useless Code Useless Brackets 122
 MUCO 3 Useless Code Useless Brackets 140
 QAFM 3 Var Assignment from potentially many rows 43
 QAFM 3 Var Assignment from potentially many rows 50
 QNAJ 3 Not using ANSI Inner Join 82
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_info1, object_type, attribute, class}		 45
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_info1, object_type, attribute, class}		 52
 VUNU 3 Variable is not used @proc_percent 29
 MTR1 2 Metrics: Comments Ratio Comments: 67% 21
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 15 = 14dec - 1exi + 2 21
 MTR3 2 Metrics: Query Complexity Complexity: 41 21
 PRED_QUERY_COLLECTION 2 {c=master..syscurconfigs, c2=master..sysconfigures} 0 81

DEPENDENCIES
PROCS AND TABLES USED
reads table master..sysattributes (1)  
reads table master..sysconfigures (1)  
reads table master..syscurconfigs (1)  

CALLERS
called by proc sybsystemprocs..sp_shmdumpdisp  
   called by proc sybsystemprocs..sp_shmdumpconfig