DatabaseProcApplicationCreatedLinks
sybsystemprocssp_index_space_est  14 déc. 14Defects Propagation Dependencies

1     
2     /* 
3     ** SP_INDEX_SPACE_EST
4     **
5     ** internal stored sproc that returns an estimate of the space usage for an 
6     ** index of a table. 
7     **
8     ** Parameters:
9     **	@table_id	-	object for which the estimate is to be computed.
10    **	@index_id	-	index for which the estimate is to be computed.
11    **	@partition_id	-	partition for which the estimate is to be 
12    **				computed. 
13    **	@pagesize	-	page size. if not supplied, the server page size
14    **				will be used.
15    **
16    **
17    ** Pre-req:
18    **
19    ** expects the #indexData table with the following schema
20    **
21    **	id			int
22    **	indid			int
23    **	ptnid			int
24    **	name			varchar(255)
25    **	type			varchar(20)
26    **	indexlevel		int
27    **	ffactor			smallint
28    **	maxrowsperpage		smallint
29    **	datalayerpages		int
30    **	numrows			float
31    **	leafrowsize		float
32    **	nonleafrowsize		float
33    **	lockscheme		tinyint
34    **	numofdatapages		int
35    **	datasizeinkb		float
36    **	numofusedpages		int
37    **	usedsizeinkb		float
38    **
39    ** to be created and appropriately populated by the caller before calling this
40    ** procedure (a row for the input  threesome with 
41    ** indexlevel set to -1 and the ffactor, maxrowsperpage, datalayerpages, 
42    ** numrows, leafrowsize, nonleafrowsize, lockscheme set accordingly). 
43    **
44    ** This temp table acts like a placeholder for these various space related 
45    ** parameters that will be used for the index's space estimates, and are like 
46    ** [indirect] inputs to this sproc. 
47    **
48    **
49    ** Returns:
50    **	Nothing. But insterts values - numofdatapages, datasizeinkb, 
51    **	numofusedpages, usedsizeinkb - for each level of the index 
52    **	in the #indexData. And sums the numofdatapages and numofusedpages and 
53    **	places that in index level = -1.
54    **	
55    **	return status	= 1 => the  threesome 
56    **			       passed is invalid. It does not have a 
57    **			       corresponding input row in #indexData table.
58    **			= 2 => invalid pagesize passed.
59    **			= 3 => invalid lock scheme
60    **			= 4 => index level out of bounds
61    **
62    */
63    
64    create or replace procedure sp_index_space_est(
65        @table_id int
66        , @index_id int
67        , @partition_id int
68        , @pagesize int = NULL
69    )
70    as
71        begin -- {
72    
73            declare
74                @table_type tinyint
75                , @index_name varchar(255)
76                , @index_type varchar(20)
77                , @level smallint
78                , @fillfactor float
79                , @mrpg smallint
80                , @datalayerpages float
81                , @numrows float
82                , @leafrowsize float
83                , @nonleafrowsize float
84                , @level_datapages float
85                , @level_usedpages float
86                , @total_datapages float
87                , @total_usedpages float
88                , @leaf_rows_per_page int
89                , @nonleaf_rows_per_page int
90                , @next_level_rows float
91                , @page_K int
92                , @status2 int
93                , @more bit
94    
95                /* Temporary placeholder variables */
96                , @tmp1 int
97                , @tmp2 int
98                , @tmp3 int
99    
100               /* Constants */
101               , @input_index_level smallint
102               , @dataval_na smallint
103               , @max_apl_rowsperpage_possible smallint
104               , @max_index_level_possible tinyint
105               , @aplpagehdrsize tinyint
106               , @dolpagehdrsize tinyint
107   
108   
109           /* Constant initialization */
110           select @input_index_level = - 1
111               , @dataval_na = - 1
112               , @max_apl_rowsperpage_possible = 256
113               , @max_index_level_possible = 255
114               , @aplpagehdrsize = 32
115               , @dolpagehdrsize = 44
116   
117   
118           /* Parameter checks */
119           if (@pagesize is null)
120           begin
121               -- Get server page size if user did not specify one
122               select @pagesize = @@maxpagesize
123   
124           end
125           else
126           begin
127               if @pagesize not in (2048, 4096, 8192, 16384)
128               begin
129                   return (2)
130               end
131           end
132   
133           select @page_K = @pagesize / 1024
134   
135           /* 
136           ** Check if the  threesome is valid and extract the 
137           ** space parameters required for estimation.
138           */
139           select @tmp1 = id
140               , @tmp2 = indid
141               , @tmp3 = ptnid
142               , @fillfactor = ffactor
143               , @mrpg = maxrowsperpage
144               , @datalayerpages = datalayerpages
145               , @numrows = numrows
146               , @leafrowsize = leafrowsize
147               , @nonleafrowsize = nonleafrowsize
148               , @table_type = lockscheme
149           from #indexData
150           where id = @table_id
151               and indid = @index_id
152               and ptnid = @partition_id
153               and indexlevel = @input_index_level
154   
155           if (@tmp1 is null or @tmp2 is null or @tmp3 is null)
156           begin
157               return (1)
158           end
159   
160           /* Check for valid lock scheme */
161           if @table_type not in (0, 1, 2)
162           begin
163               return (3)
164           end
165   
166           /* Code starts here */
167           select @total_datapages = 0.0
168               , @total_usedpages = 0.0
169               , @fillfactor = @fillfactor / 100.0
170   
171   
172           /* Extract the index name and type */
173           select @index_name = name
174               , @status2 = status2
175           from sysindexes
176           where id = @table_id
177               and indid = @index_id
178   
179           if @index_id = 1
180           begin
181               select @index_type = 'clustered'
182           end
183           else
184           if @index_id > 1
185           begin
186               if (@status2 & 512 = 512)
187               begin
188                   select @index_type = 'clustered'
189               end
190               else
191               begin
192                   select @index_type = 'nonclustered'
193               end
194           end
195   
196           /*
197           ** Note: the case where number of rows is zero can not occur when this
198           ** sproc is called by sp_estspace [it does not allow it].
199           */
200           if @numrows = 0
201           begin -- {
202               if @table_type = 0 -- APL table
203               begin
204                   select @total_datapages = 1
205                       , @total_usedpages = 1
206   
207               end
208               else
209               begin
210                   select @total_datapages = 2
211                       , @total_usedpages = 2
212   
213                   -- Root page (level 1)
214                   insert #indexData
215                   (id, indid, ptnid, name, type, indexlevel, numofdatapages,
216                       datasizeinkb, numofusedpages, usedsizeinkb)
217                   values
218                   (@table_id, @index_id, @partition_id, @index_name,
219                       @index_type, 1, 1, @page_K, 1, @page_K)
220               end
221   
222               -- One leaf page (level 0)
223               insert #indexData
224               (id, indid, ptnid, name, type, indexlevel, numofdatapages,
225                   datasizeinkb, numofusedpages, usedsizeinkb)
226               values
227               (@table_id, @index_id, @partition_id, @index_name, @index_type,
228                   0, 1, @page_K, 1, @page_K)
229   
230   
231               -- Update the total data and used pages
232               update #indexData
233               set numofdatapages = ceiling(@total_datapages)
234                   , numofusedpages = ceiling(@total_usedpages)
235               where id = @table_id
236                   and indid = @index_id
237                   and ptnid = @partition_id
238                   and indexlevel = @input_index_level
239   
240               update #indexData
241               set datasizeinkb = numofdatapages * @page_K
242                   , usedsizeinkb = numofusedpages * @page_K
243               where id = @table_id
244                   and indid = @index_id
245                   and ptnid = @partition_id
246                   and indexlevel = @input_index_level
247   
248               return (0)
249           end -- } 	-- End of if
250   
251           /* 
252           ** Compute the number of rows per page (may be extracted into a 
253           ** separate module later) as folows. 
254           */
255   
256           /* Account for the page header space */
257           select @pagesize = @pagesize - case @table_type
258                   when 1 then @dolpagehdrsize
259                   when 2 then @dolpagehdrsize
260                   else @aplpagehdrsize
261               end
262   
263   
264           /*
265           ** Fill factor applies as follows on the leaf pages:
266           ** -- For APL tables, it applies only on NCI [indid >1].
267           ** -- For DOL tables, it applies on both CI and	NCI [indid >1].
268           ** -- For NCI in APL/DOL tables, if fill factor is not set server-wide 
269           **    default is used.
270           **
271           ** Also, fill factor if set to 0, leaves room for 2 rows in each page. 
272           ** If set to 100, it does not leave any space on the page.
273           */
274           if (@index_id = 1) -- APL CI
275           begin
276               select @leaf_rows_per_page = @pagesize / @leafrowsize - 2
277           end
278           else
279           begin
280   
281               -- Use the fill factor passed, if any. [It could be the value
282               -- passed by user to sp_estspace, or extracted from sysindexes
283               -- by other calling procedures] 
284               --
285               -- If not set, use the server wide default value.
286               --		
287               if @fillfactor = 0
288               begin
289                   select @fillfactor = value / 100.0
290                   from master.dbo.syscurconfigs
291                   where comment = 'dafault fill factor percent'
292               end
293   
294               if (@fillfactor != 0)
295               begin
296                   select @leaf_rows_per_page =
297                       @pagesize * @fillfactor / @leafrowsize
298               end
299               else
300               begin
301                   select @leaf_rows_per_page =
302                       @pagesize / @leafrowsize - 2
303               end
304           end
305   
306           /*
307           ** Fillfactor is not applicable on nonleaf pages. However, we assume 
308           ** this is treated same as the case of fill factor = 0 and leave room
309           ** for 2 rows in each page.
310           */
311           select @nonleaf_rows_per_page = @pagesize / @nonleafrowsize - 2
312   
313           if @leaf_rows_per_page < 1
314           begin
315               select @leaf_rows_per_page = 1
316           end
317   
318           if @nonleaf_rows_per_page < 1
319           begin
320               select @nonleaf_rows_per_page = 1
321           end
322   
323           /* For APL table NCI, account for the max rows per page, if set. */
324           if @table_type = 0 and @index_id != 1 and @mrpg != 0
325           begin
326               if (@leaf_rows_per_page > @mrpg)
327                   select @leaf_rows_per_page = @mrpg
328           end
329   
330           /* 
331           ** For APL tables, the no. of rows per page can not exceed the maximum 
332           ** limit (of 256).
333           */
334           if @table_type = 0
335           begin
336               if @leaf_rows_per_page > @max_apl_rowsperpage_possible
337               begin
338                   select @leaf_rows_per_page
339                       = @max_apl_rowsperpage_possible
340               end
341   
342               if @nonleaf_rows_per_page > @max_apl_rowsperpage_possible
343               begin
344                   select @nonleaf_rows_per_page
345                       = @max_apl_rowsperpage_possible
346               end
347           end
348   
349   
350           /* Prestage index building information */
351           select @more = 1, @level = 0
352   
353           /* Now, estimate the #levels and #pages */
354   
355           /* 
356           ** For APL CI, the first level of index is based on the number of data 
357           ** pages. For APL NCI, and any DOL index, it is the number of data rows
358           */
359           select @next_level_rows = case @index_id
360                   when 1 then @datalayerpages
361                   else @numrows
362               end
363   
364           while @more = 1
365           begin -- {
366               if @level > @max_index_level_possible
367               begin
368                   -- This index is too big to build 
369                   -- raiserror 19414, @index_name
370                   --
371                   return (4)
372               end
373   
374               -- Calculate the number of pages at a single index level
375               select @level_datapages =
376                   @next_level_rows / convert(float,
377                   case @level
378                       when 0 then @leaf_rows_per_page
379                       else @nonleaf_rows_per_page
380                   end
381                   )
382   
383               select @next_level_rows = @level_datapages
384   
385               -- Note: The used pages counts are retained only to be used by 
386               -- 	 sp_estspace that might call this in future.
387               --
388   
389               -- Account for allocation/oam pages.
390               select @level_usedpages = @level_datapages +
391                   (@level_datapages / 256.0) + 1.0
392   
393   
394               -- Insert the row into the results table
395               insert #indexData
396               (id, indid, ptnid, name, type, indexlevel, numofdatapages,
397                   datasizeinkb, numofusedpages, usedsizeinkb)
398               values
399               (@table_id, @index_id, @partition_id, @index_name, @index_type,
400                   @level, ceiling(@level_datapages), @level_datapages * @page_K,
401                   ceiling(@level_usedpages), @level_usedpages * @page_K)
402   
403               select @total_datapages = @total_datapages
404                   + ceiling(@level_datapages)
405                   , @total_usedpages = @total_usedpages
406                   + ceiling(@level_usedpages)
407                   , @level = @level + 1
408   
409               -- Can we fit the next level in 1 page? If yes, we are done
410               if @next_level_rows <= @nonleaf_rows_per_page
411                   select @more = 0
412   
413           end -- } 	-- End of while
414   
415           /* 
416           ** Account for the root page. For APL tables, if the entire index fits 
417           ** on a single page, that becomes the root page and there are no 
418           ** intermediate or leaf levels. DOL tables, however, always have a leaf 
419           ** level between the root page and the data pages.
420           */
421           if @table_type != 0 or @level_datapages > 1
422           begin
423               insert #indexData
424               (id, indid, ptnid, name, type, indexlevel, numofdatapages,
425                   datasizeinkb, numofusedpages, usedsizeinkb)
426               values
427               (@table_id, @index_id, @partition_id, @index_name, @index_type,
428                   @level, 1, @page_K, 1, @page_K)
429   
430               select @total_datapages = @total_datapages + 1
431                   , @total_usedpages = @total_usedpages + 1
432           end
433   
434   
435           /*
436           ** Update the #indexData with the total number of index pages across all 
437           ** levels. The numofdatapages, numofusedpages, datasizeinkb, usedsizeinkb
438           ** for indexlevel = -1 is the place holder for this total count. This will be
439           ** used by sp_spaceusage_object for its estimates. Not by sp_estspace which 
440           ** gives a level-wise break up.
441           */
442           update #indexData
443           set numofdatapages = ceiling(@total_datapages)
444               , numofusedpages = ceiling(@total_usedpages)
445           where id = @table_id
446               and indid = @index_id
447               and ptnid = @partition_id
448               and indexlevel = @input_index_level
449   
450           update #indexData
451           set datasizeinkb = numofdatapages * @page_K
452               , usedsizeinkb = numofusedpages * @page_K
453           where id = @table_id
454               and indid = @index_id
455               and ptnid = @partition_id
456               and indexlevel = @input_index_level
457   
458       end --} 	-- Procedure done!
459   


exec sp_procxmode 'sp_index_space_est', 'AnyMode'
go

Grant Execute on sp_index_space_est to public
go
DEFECTS
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 177
 QTYP 4 Comparison type mismatch smallint = int 177
 TNOI 4 Table with no index master..syscurconfigs master..syscurconfigs
 MAW1 3 Warning message on %name% tempdb..#indexData.id: Warning message on #indexData_ndby_sybsystemprocs__sp_index_space_est 139
 MAW1 3 Warning message on %name% tempdb..#indexData.id: Warning message on #indexData_ndby_sybsystemprocs__sp_index_space_est 150
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 176
 MAW1 3 Warning message on %name% tempdb..#indexData.id: Warning message on #indexData_ndby_sybsystemprocs__sp_index_space_est 215
 MAW1 3 Warning message on %name% tempdb..#indexData.id: Warning message on #indexData_ndby_sybsystemprocs__sp_index_space_est 224
 MAW1 3 Warning message on %name% tempdb..#indexData.id: Warning message on #indexData_ndby_sybsystemprocs__sp_index_space_est 235
 MAW1 3 Warning message on %name% tempdb..#indexData.id: Warning message on #indexData_ndby_sybsystemprocs__sp_index_space_est 243
 MAW1 3 Warning message on %name% tempdb..#indexData.id: Warning message on #indexData_ndby_sybsystemprocs__sp_index_space_est 396
 MAW1 3 Warning message on %name% tempdb..#indexData.id: Warning message on #indexData_ndby_sybsystemprocs__sp_index_space_est 424
 MAW1 3 Warning message on %name% tempdb..#indexData.id: Warning message on #indexData_ndby_sybsystemprocs__sp_index_space_est 445
 MAW1 3 Warning message on %name% tempdb..#indexData.id: Warning message on #indexData_ndby_sybsystemprocs__sp_index_space_est 453
 MGTP 3 Grant to public master..syscurconfigs  
 MGTP 3 Grant to public sybsystemprocs..sp_index_space_est  
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MNER 3 No Error Check should check @@error after insert 214
 MNER 3 No Error Check should check @@error after insert 223
 MNER 3 No Error Check should check @@error after update 232
 MNER 3 No Error Check should check @@error after update 240
 MNER 3 No Error Check should check @@error after insert 395
 MNER 3 No Error Check should check @@error after insert 423
 MNER 3 No Error Check should check @@error after update 442
 MNER 3 No Error Check should check @@error after update 450
 MUCO 3 Useless Code Useless Brackets in create proc 64
 MUCO 3 Useless Code Useless Begin-End Pair 71
 MUCO 3 Useless Code Useless Brackets 119
 MUCO 3 Useless Code Useless Brackets 129
 MUCO 3 Useless Code Useless Brackets 155
 MUCO 3 Useless Code Useless Brackets 157
 MUCO 3 Useless Code Useless Brackets 163
 MUCO 3 Useless Code Useless Brackets 186
 MUCO 3 Useless Code Useless Brackets 248
 MUCO 3 Useless Code Useless Brackets 274
 MUCO 3 Useless Code Useless Brackets 294
 MUCO 3 Useless Code Useless Brackets 326
 MUCO 3 Useless Code Useless Brackets 371
 MUCO 3 Useless Code Useless Brackets 391
 QAFM 3 Var Assignment from potentially many rows 139
 QAFM 3 Var Assignment from potentially many rows 289
 QIWC 3 Insert with not all columns specified missing 7 columns out of 17 215
 QIWC 3 Insert with not all columns specified missing 7 columns out of 17 224
 QIWC 3 Insert with not all columns specified missing 7 columns out of 17 396
 QIWC 3 Insert with not all columns specified missing 7 columns out of 17 424
 VNRD 3 Variable is not read @dataval_na 111
 MTR1 2 Metrics: Comments Ratio Comments: 40% 64
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 29 = 33dec - 6exi + 2 64
 MTR3 2 Metrics: Query Complexity Complexity: 130 64

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysindexes  
reads table master..syscurconfigs (1)  
read_writes table tempdb..#indexData (1) 

CALLERS
called by proc sybsystemprocs..sp_spaceusage_object_populate  
   called by proc sybsystemprocs..sp_spaceusage_object  
      called by proc sybsystemprocs..sp_spaceusage