DatabaseProcApplicationCreatedLinks
sybsystemprocssp_helpconfig_est_compinfo_poolsize  14 déc. 14Defects Propagation Dependencies

1     create or replace procedure sp_helpconfig_est_compinfo_poolsize(
2         @configname varchar(80)
3         , @estimate_clause varchar(256)
4     ) as
5         begin
6             declare @retval int
7                 , @userconns unsigned int
8                 , @workerprocs unsigned int
9                 , @user_workers unsigned int
10                , @maxpardegree int
11                , @numopenobjects unsigned int
12                , @numopenindexes unsigned int
13                , @numopenptns unsigned int
14    
15                , @avgnumcols unsigned int
16    
17                , @maxconcusers unsigned int
18    
19                , @numtables unsigned int
20                , @numcolumns unsigned int
21                , @numcompobjs unsigned int
22                , @numidxkeys unsigned int
23                , @numcompidxs unsigned int
24    
25                , @cpinfo_sz tinyint
26                , @other_sz tinyint
27                , @icpinfo_sz tinyint
28                , @icolinfo_sz tinyint
29                , @memfrag_sz tinyint
30                , @using_found tinyint
31                , @debuglvl tinyint
32    
33                , @one_K smallint
34                , @two_K smallint
35    
36                , @offsetbuf_per_table_K unsigned int
37    
38                , @sprocname varchar(80)
39    
40                -- for all compressed tables that are opened
41                , @memreqd_dcpinfo_K unsigned bigint
42    
43                -- for all compressed indexes that are opened
44                , @memreqd_icpinfo_K unsigned bigint
45    
46                -- for all compressed tables & indexes
47                , @memreqd_cpinfo_K unsigned bigint
48    
49                , @memreqd_peruser_K unsigned bigint
50    
51                , @numbuffers_per_stmt tinyint
52    
53                , @num_idx_rowkey_bufs tinyint
54    
55            set @retval = 0
56                , @one_K = 1024
57    
58            set @two_K = (@one_K * 2)
59    
60            -- Extract the settings of config options that will go into,
61            -- and will affect, consuming memory for this config option.
62            -- RESOLVE: Do we use sysconfigures or syscurconfigs?
63            --
64            select @userconns = value
65            from master..sysconfigures
66            where name = 'number of user connections'
67    
68            select @workerprocs = value
69            from master..sysconfigures
70            where name = 'number of worker processes'
71    
72            select @maxpardegree = value
73            from master..sysconfigures
74            where name = 'max parallel degree'
75    
76            select @numopenobjects = value
77            from master..sysconfigures
78            where name = 'number of open objects'
79    
80            select @numopenindexes = value
81            from master..sysconfigures
82            where name = 'number of open indexes'
83    
84            select @numopenptns = value
85            from master..sysconfigures
86            where name = 'number of open partitions'
87    
88            -- Identify min / avg / max # of concurrent users requesting memory
89            set @user_workers = (@userconns + @workerprocs)
90    
91            set @maxconcusers = @user_workers
92    
93            -- Process the 'estimate' clause to see if there is a USING clause
94            -- speifier which might over-ride some of the defaults for this logic.
95            -- If USING clause is found, generate one estimated output for those
96            -- provided values. If none is found, user just wants to know the 
97            -- max ranges for the memory required using some default values.
98            -- Provide the helpconfig outputs in that case.
99            --
100           set @sprocname = "sp_helpconfig_process_estimate"
101           exec @retval = @sprocname @estimate_clause
102               , @userconns
103               , @workerprocs
104               , @maxpardegree
105               , @numopenobjects
106               , @numopenindexes
107               , @numopenptns
108   
109               , @using_found output
110               , @maxconcusers output
111               , @numtables output
112               , @numcolumns output
113               , @numcompobjs output
114               , @numidxkeys output
115               , @numcompidxs output
116               , @debuglvl output
117   
118           if (@retval != 0)
119               return @retval
120   
121           /* **** Server-wide memory requirements **** */
122   
123           -- Hard-coded based on server-internal structures
124           --
125           set @cpinfo_sz = 48 -- sizeof(DCOMP_CPINFO)
126               , @other_sz = 24 -- for stuff hanging off CPINFO
127               , @icpinfo_sz = 24 -- sizeof(ICOMP_CPINFO)
128               , @icolinfo_sz = 12 -- sizeof(ICOMP_COLINFO)
129   
130               -- sizeof(MEMFRAG)
131               , @memfrag_sz = (case pointer_size() when 8 then 32 else 16 end)
132   
133           -- First, compute memory needed to instantiate DCOMP_CPINFO for all the
134           -- compressed objects that will be accessed. (This is in bytes so far.)
135           --
136           set @memreqd_dcpinfo_K = (@cpinfo_sz
137               + (@other_sz * @numcolumns)
138               + ceiling(@numcolumns / 8.0))
139   
140           -- Round that to multiples of a memory fragment size. This is the
141           -- memory needed, in bytes, for the CPINFO of one compressed object.
142           --
143           set @memreqd_dcpinfo_K = ((((@memreqd_dcpinfo_K - 1) / @memfrag_sz) + 1)
144               * @memfrag_sz)
145   
146           -- First, compute memory needed to instantiate ICOMP_CPINFO for all the
147           -- compressed indexes that will be used. (This is in bytes so far.)
148           --
149           set @memreqd_icpinfo_K = (@icpinfo_sz
150               + (@icolinfo_sz * @numidxkeys)
151               + ceiling(@numidxkeys / 8.0))
152   
153           -- Round that to multiples of a memory fragment size.
154           -- Similar as @memreqd_cpinfo_K.
155           set @memreqd_icpinfo_K = ((((@memreqd_icpinfo_K - 1) / @memfrag_sz) + 1)
156               * @memfrag_sz)
157   
158           if (@debuglvl != 0)
159           begin
160               print "est_compinfo_poolsize: Memory required for one DCOMP_CPINFO %1! bytes, one ICOMP_CPINFO %2! bytes",
161                   @memreqd_dcpinfo_K, @memreqd_icpinfo_K
162           end
163   
164           -- Compute the total memory needed to cache n-compressed objects'
165           -- DCOMP_CPINFO structures, converting the result to KB
166           --
167           set @memreqd_dcpinfo_K = ceiling((@memreqd_dcpinfo_K * @numcompobjs)
168                   / 1024.0)
169   
170           -- Compute the total memory needed to cache n-compressed indexes'
171           -- ICOMP_CPINFO structures, converting the result to KB
172           set @memreqd_icpinfo_K = ceiling((@memreqd_icpinfo_K * @numcompidxs)
173                   / 1024.0)
174   
175           -- Compute the total memory needed to cache all the CPINFO structures.
176           set @memreqd_cpinfo_K = @memreqd_dcpinfo_K + @memreqd_icpinfo_K
177   
178           if (@debuglvl != 0)
179           begin
180               print "est_compinfo_poolsize: Memory required for DCOMP_CPINFO structures for %1! compressed objects is %2! KB",
181                   @numcompobjs, @memreqd_dcpinfo_K
182               print "and for ICOMP_CPINFO structures for %1! compressed indexes is %2! KB",
183                   @numcompidxs, @memreqd_icpinfo_K
184           end
185   
186           /* **** User-/Scan-Specific memory requirements **** */
187   
188           -- Each scan uses 2K for one cached offset table
189           set @offsetbuf_per_table_K = 2
190   
191           -- For index compression/decompression, there are 3 row buffers
192           -- and 1 key buffers per user(PSS), and once these buffers are
193           -- allocated, they will not be freed till the connection end.
194           -- These 4 buffers are all grabbed from memory pool.
195           set @num_idx_rowkey_bufs = 4
196   
197           set @memreqd_peruser_K = ((@offsetbuf_per_table_K * @numtables)
198               + ((@num_idx_rowkey_bufs * @@maxpagesize)
199               / @one_K))
200   
201           -- Add in the logical page size used by decompression.
202           -- # of logical-page sized buffers grabbed from memory pool varies
203           -- from 1 to 3. On an average, assume this is 2.
204   
205           set @numbuffers_per_stmt = 2
206           set @memreqd_peruser_K = @memreqd_peruser_K
207               + ((@numbuffers_per_stmt * @@maxpagesize)
208               / @one_K)
209   
210           -- Provide memory estimates for the user-specified configuration, if
211           -- any. If user has *not* specified USING clause value(s), then this
212           -- will generate an estimate of the memory required using pre-set and
213           -- computed default values for various parameters involved.
214           --
215           exec @retval = sp_helpconfig_gen_estimates_cpi
216               @memreqd_peruser_K
217               , @maxconcusers
218               , @memreqd_cpinfo_K
219               , @numcompobjs
220               , @numcolumns
221               , @numcompidxs
222               , @numidxkeys
223               , @configname
224               , @debuglvl
225   
226           return @retval
227       end
228   


exec sp_procxmode 'sp_helpconfig_est_compinfo_poolsize', 'AnyMode'
go

Grant Execute on sp_helpconfig_est_compinfo_poolsize to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns master..sysconfigures master..sysconfigures
 MTYP 4 Assignment type mismatch @numconcusers: uint = int 217
 MTYP 4 Assignment type mismatch @numcompobjs: uint = int 219
 MTYP 4 Assignment type mismatch @numcolumns: uint = int 220
 MTYP 4 Assignment type mismatch @numcompidxs: uint = int 221
 MTYP 4 Assignment type mismatch @numidxkeys: uint = int 222
 MGTP 3 Grant to public master..sysconfigures  
 MGTP 3 Grant to public sybsystemprocs..sp_helpconfig_est_compinfo_poolsize  
 MNER 3 No Error Check should check return value of exec 215
 MUCO 3 Useless Code Useless Brackets in create proc 1
 MUCO 3 Useless Code Useless Begin-End Pair 5
 MUCO 3 Useless Code Useless Brackets 58
 MUCO 3 Useless Code Useless Brackets 118
 MUCO 3 Useless Code Useless Brackets 131
 MUCO 3 Useless Code Useless Brackets 137
 MUCO 3 Useless Code Useless Brackets 143
 MUCO 3 Useless Code Useless Brackets 150
 MUCO 3 Useless Code Useless Brackets 155
 MUCO 3 Useless Code Useless Brackets 158
 MUCO 3 Useless Code Useless Brackets 178
 MUCO 3 Useless Code Useless Brackets 197
 MUCO 3 Useless Code Useless Brackets 198
 MUCO 3 Useless Code Useless Brackets 207
 QAFM 3 Var Assignment from potentially many rows 64
 QAFM 3 Var Assignment from potentially many rows 68
 QAFM 3 Var Assignment from potentially many rows 72
 QAFM 3 Var Assignment from potentially many rows 76
 QAFM 3 Var Assignment from potentially many rows 80
 QAFM 3 Var Assignment from potentially many rows 84
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {name}
66
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {name}
70
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {name}
74
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {name}
78
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {name}
82
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {name}
86
 VNRD 3 Variable is not read @two_K 58
 VNRD 3 Variable is not read @using_found 109
 VUNU 3 Variable is not used @avgnumcols 15
 MDYE 2 Dynamic Exec Marker exec @retval 101
 MTR1 2 Metrics: Comments Ratio Comments: 35% 1
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 5 = 4dec - 1exi + 2 1
 MTR3 2 Metrics: Query Complexity Complexity: 51 1

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_helpconfig_gen_estimates_cpi  
   calls proc sybsystemprocs..sp_helpconfig  
      calls proc sybsystemprocs..sp_validateconfigname  
         reads table master..sysconfigures (1)  
         writes table sybsystemprocs..sp_validateconfigname_rset_001 
         reads table master..syscurconfigs (1)  
         calls proc sybsystemprocs..sp_getmessage  
            reads table master..sysmessages (1)  
            reads table master..syslanguages (1)  
            reads table sybsystemprocs..sysusermessages  
            calls proc sybsystemprocs..sp_validlang  
               reads table master..syslanguages (1)  
      reads table master..sysmessages (1)  
      calls proc sybsystemprocs..sp_aux_getsize  
      writes table tempdb..#sphelpconfig2rs (1) 
      calls proc sybsystemprocs..sp_getmessage  
      writes table tempdb..#sphelpconfig1rs (1) 
      writes table tempdb..#sphelpconfig3rs (1) 
      calls proc sybsystemprocs..sp_helpconfig_estimate  
      calls proc sybsystemprocs..sp_autoformat  
         reads table tempdb..systypes (1)  
         writes table sybsystemprocs..sp_autoformat_rset_001 
         reads table tempdb..syscolumns (1)  
         writes table sybsystemprocs..sp_autoformat_rset_005 
         writes table sybsystemprocs..sp_autoformat_rset_003 
         reads table master..syscolumns (1)  
         read_writes table tempdb..#colinfo_af (1) 
         reads table master..systypes (1)  
         writes table sybsystemprocs..sp_autoformat_rset_004 
         calls proc sybsystemprocs..sp_autoformat  
         calls proc sybsystemprocs..sp_namecrack  
         writes table sybsystemprocs..sp_autoformat_rset_002 
      calls proc sybsystemprocs..sp_helpconfig_usage  
         calls proc sybsystemprocs..sp_getmessage  
      reads table master..sysconfigures (1)  
      reads table master..syscurconfigs (1)  
calls proc sybsystemprocs..sp_helpconfig_process_estimate  
   reads table sybsystemprocs..sysobjects  
   reads table sybsystemprocs..syscolumns  
   calls proc sybsystemprocs..sp_helpconfig_est_maxconcusers  
   calls proc sybsystemprocs..sp_split_string  
   reads table sybsystemprocs..sysindexes  
reads table master..sysconfigures (1)