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 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
