Home 
Performance and Tuning Guide Volumes 1  3 (Online Only) 
Chapter 16 Determining Sizes of Tables and Indexes 
Using formulas to estimate object size 
Use the formulas in this section to help you estimate the future sizes of the tables and indexes in your database. The amount of overhead in each row for tables and indexes that contain variablelength fields is greater than tables that contain only fixedlength fields, so two sets of formulas are required.
The process involves calculating the number of bytes of data and overhead for each row, and dividing that number into the number of bytes available on a data page. Each page requires some overhead, which limits the number of bytes available for data:
For allpageslocked tables, page overhead is 32 bytes, leaving 2016 bytes available for data on a 2K page.
For dataonlylocked tables, 46 bytes, leaving 2002 bytes available for data.
For the most accurate estimate, round down divisions that calculate the number of rows per page (rows are never split across pages), and round up divisions that calculate the number of pages.
Using space management properties can increase the space needed for a table or an index. See "Effects of space management properties", and "max_rows_per_page".
The formulas in this section use the maximum size for variablelength character and binary data.To use the average size instead of the maximum size, see "Using average sizes for variable fields".
If your table includes text or image datatypes or Java offrow columns, use 16 (the size of the text pointer that is stored in the row) in your calculations. Then see "LOB pages" to see how to calculate the storage space required for the actual text or image data.
Indexes on dataonlylocked tables may be smaller than the formulas predict due to two factors:
Duplicate keys are stored only once, followed by a list of row IDs for the key.
Compression of keys on nonleaf levels; only enough of the key to differentiate from the neighboring keys is stored. This is especially effective in reducing the size when long character keys are used.
If the configuration parameter page utilization percent is set to less than 100, Adaptive Server may allocate new extents before filling all pages on the allocated extents. This does not change the number of pages used by an object, but leaves empty pages in the extents allocated to the object. See in the System Administration Guide.
The storage sizes for datatypes are shown in Table 162:
Datatype  Size 
char  Defined size 
nchar  Defined size * @@ncharsize 
unichar  n*@@unicharsize (@@unicharsize equals 2) 
univarchar  the actual number of characters*@@unicharsize 
varchar  Actual number of characters 
nvarchar  Actual number of characters * @@ncharsize 
binary  Defined size 
varbinary  Data size 
int  4 
smallint  2 
tinyint  1 
float  4 or 8, depending on precision 
double precision  8 
real  4 
numeric  217, depending on precision and scale 
decimal  217, depending on precision and scale 
money  8 
smallmoney  4 
datetime  8 
smalldatetime  4 
bit  1 
text  16 bytes + 2K * number of pages used 
image  16 bytes + 2K * number of pages used 
timestamp  8 
The storage size for a numeric or decimal column depends on its precision. The minimum storage requirement is 2 bytes for a 1 or 2digit column. Storage size increases by 1 byte for each additional 2 digits of precision, up to a maximum of 17 bytes.
Any columns defined as NULL are considered variablelength columns, since they involve the overhead associated with variablelength columns.
All calculations in the examples that follow are based on the maximum size for varchar, univarchar, nvarchar, and varbinary datathe defined size of the columns. They also assume that the columns were defined as NOT NULL. If you want to use average values instead, see "Using average sizes for variable fields".
The example illustrates the computations on a table that contains 9,000,000 rows:
The sum of fixedlength column sizes is 100 bytes.
The sum of variablelength column sizes is 50 bytes; there are 2 variablelength columns.
The table has two indexes:
A clustered index, on a fixedlength column, of 4 bytes
A composite nonclustered index with these columns:
A fixed length column, of 4 bytes
A variable length column, of 20 bytes
Different formulas are needed for allpageslocked and dataonlylocked tables, since they have different amounts of overhead on the page and per row:
See "Calculating table and clustered index sizes for allpageslocked tables" for tables that use allpageslocking.
See "Calculating the sizes of dataonlylocked tables" for the formulas to use if tables that use dataonly locking.
The formulas and examples for allpageslocked tables are divided into two sets of steps:
Steps 16 outline the calculations for an allpageslocked table with a clustered index, giving the table size and the size of the index tree.
Steps 712 outline the calculations for computing the space required by nonclustered indexes.
These formulas show how to calculate the sizes of tables and clustered indexes. If your table does not have clustered indexes, skip steps 3, 4, and 5. Once you compute the number of data pages in step 2, go to step 6 to add the number of OAM pages.
Step 1: Calculate the data row sizeRows that store variablelength data require more overhead than rows that contain only fixedlength data, so there are two separate formulas for computing the size of a data row.
Use this formula if the table contains only fixedlength columns, and all are defined as NOT NULL.
Formula  
4  (Overhead)  
+  Sum of bytes in all fixedlength columns  
= Data row size 
Use this formula if the table contains any variablelength columns or columns that allow null values.
The table in the example contains variablelength columns, so the computations are shown in the right column.
Formula  Example  
4  (Overhead)  4  
+  Sum of bytes in all fixedlength columns  +  100  
+  Sum of bytes in all variablelength columns  +  50  
= Subtotal  154  

 
+ 
 (Subtotal / 256) + 1 (Overhead)  1  
+ 
 Number of variablelength columns + 1  3  
+  2  (Overhead)  2  
= Data row size  160 
Formula 
2016 / Data row size = Number of data rows per page 
Number of rows / Rows per page = Number of data pages required 
Example  
2016 / 160  =  12 data rows per page 
9,000,000 / 12  =  750,000 data pages 
Index rows containing variablelength columns require more overhead than index rows containing only fixedlength values. Use the first formula if all the keys are fixed length. Use the second formula if the keys include variablelength columns or allow null values.
The clustered index in the example has only fixed length keys.
Formula  Example  
5  (Overhead)  5  
+  Sum of bytes in the fixedlength index keys  +  4  
= Clustered row size  9 
5  (Overhead)  
+  Sum of bytes in the fixedlength index keys  
+  Sum of bytes in variablelength index keys  
= Subtotal  

 
+ 
 (Subtotal / 256) + 1 (Overhead) 
+ 
 Number of variablelength columns + 1 
+  2  (Overhead) 
= Clustered index row size 
The results of the division (Subtotal / 256) are rounded down.
Step 4: Compute the number of clustered index pagesFormula  Example  
(2016 / Clustered row size)  2  =  No. of clustered index rows per page  (2016 / 9)  2  =  222 
No. of rows / No. of CI rows per page  =  No. of index pages at next level  750,000 / 222  =  3379 
If the result for the "number of index pages at the next level" is greater than 1, repeat the following division step, using the quotient as the next dividend, until the quotient equals 1, which means that you have reached the root level of the index:
Formula  
No. of index pages at last level  /  No. of clustered index rows per page  =  No. of index pages at next level 
Example  
3379 / 222  =  16 index pages (Level 1) 
16 / 222  =  1 index page (Level 2) 
Add the number of pages at each level to determine the total number of pages in the index:
Formula  Example  
Index Levels  Pages 
 Pages  Rows  
2  1  16  
1  +  +  16  3379  
0  +  +  3379  750000  
Total number of index pages  3396 
Each table and each index on a table has an object allocation map (OAM). A single OAM page holds allocation mapping for between 2,000 and 63,750 data pages or index pages. In most cases, the number of OAM pages required is close to the minimum value. To calculate the number of OAM pages for the table, use:
Formula  Example  
Number of reserved data pages / 63,750  =  Minimum OAM pages  750,000 / 63,750  =  12 
Number of reserved data pages / 2000  =  Maximum OAM pages  750,000 / 2000  =  376 
To calculate the number of OAM pages for the index, use:
Formula  Example  
Number of reserved index pages / 63,750  =  Minimum OAM pages  3396/ 63,750  =  1 
Number of reserved index pages / 2000  =  Maximum OAM pages  3396 / 2000  =  2 
Finally, add the number of OAM pages to the earlier totals to determine the total number of pages required:
Formula  Example  
Minimum  Maximum  Minimum  Maximum  
Clustered index pages  3396  3379  
OAM pages  +  +  1  2 
Data pages  +  +  750000  750000 
OAM pages  +  +  12  376 
Total  753409  753773 
Index rows containing variablelength columns require more overhead than index rows containing only fixedlength values.
Use this formula if the index contains only fixedlength keys and are defined as NOT NULL:
Formula  
7  (Overhead)  
+  Sum of fixedlength keys  
= Size of leaf index row 
Use this formula if the index contains any variablelength keys or columns defined as NULL:
Formula  Example  
9  (Overhead)  9  
+  Sum of length of fixedlength keys  +  4  
+  Sum of length of variablelength keys  +  20  
+  Number of variablelength keys + 1  +  2  
= Subtotal  35  
 
+  (Subtotal / 256) + 1 (overhead)  +  1  
= Size of leaf index row  36 
Formula  Example  
(2016 / leaf row size)  =  No. of leaf index rows per page  2016 / 36  =  56 
No. of table rows / No. of leaf rows per page  =  No. of index pages at next level  9,000,000 / 56  =  160,715 
Formula  Example  
Size of leaf index row  36  
+  4  Overhead  +  4 
= Size of nonleaf row  40 
Formula  Example  
(2016 / Size of nonleaf row)  2  =  No. of nonleaf index rows per page  (2016 / 40)  2 = 48 
If the number of leaf pages from step 8 is greater than 1, repeat the following division step, using the quotient as the next dividend, until the quotient equals 1, which means that you have reached the root level of the index:
Formula  
No. of index pages at previous level  /  No. of nonleaf index rows per page  =  No. of index pages at next level 
Example  
160715 / 48 = 3349  Index pages, level 1 
3349 / 48 = 70  Index pages, level 2 
70 / 48 = 2  Index pages, level 3 
2 / 48 = 1  Index page, level 4 (root level) 
Add the number of pages at each level to determine the total number of pages in the index:
Index Levels  Pages 
 Pages  Rows  
4  1  2  
3  +  +  2  70  
2  +  +  70  3348  
1  +  +  3349  160715  
0  +  +  160715  9000000  
Total number of 2K data pages used  164137 
Formula  Example  
Number of index pages / 63,750  =  Minimum OAM pages  164137 / 63,750  =  3 
Number of index pages / 2000  =  Maximum OAM pages  164137 / 2000  =  83 
Add the number of OAM pages to the total in step 11 to determine the total number of index pages:
Formula  Example  
Minimum  Maximum  Minimum  Maximum  
Nonclustered index pages  164137  164137  
OAM pages  +  +  3  83 
Total  164140  164220 
The formulas and examples that follow show how to calculate the sizes of tables and indexes. This example uses the same columns sizes and index as the previous example. See "Tables and indexes used in the formulas" for the specifications.
The formulas for dataonlylocked tables are divided into two sets of steps:
Steps 13 outline the calculations for a dataonlylocked table. The example that follows Step 3 illustrates the computations on a table that has 9,000,000 rows.
Steps 48 outline the calculations for computing the space required by an index, followed by an example using the 9,000,000row table.
Rows that store variablelength data require more overhead than rows that contain only fixedlength data, so there are two separate formulas for computing the size of a data row.
Use this formula if the table contains only fixedlength columns defined as NOT NULL:
6  (Overhead)  
+  Sum of bytes in all fixedlength columns  
Data row size 
Dataonly locked tables must allow room for each row to store a 6byte forwarded row ID. If a dataonlylocked table has rows shorter than 10 bytes, each row is padded to 10 bytes when it is inserted. This affects only data pages, and not indexes, and does not affect allpageslocked tables.
Use this formula if the table contains variablelength columns or columns that allow null values:
Formula  Example  
8  (Overhead)  8  
+  Sum of bytes in all fixedlength columns  +  100  
+  Sum of bytes in all variablelength columns  +  50  
+  Number of variablelength columns * 2  +  4  
Data row size  162 
Formula 
2002 / Data row size = Number of data rows per page 
Number of rows / Rows per page = Number of data pages required 
In the first part of this step, the number of rows per page is rounded down:
Example  
2002 / 162  =  12 data rows per page 
9,000,000 / 12  =  750,000 data pages 
Each table and each index on a table has an object allocation map (OAM). The OAM is stored on pages allocated to the table or index. A single OAM page holds allocation mapping for between 2,000 and 63,750 data pages or index pages. In most cases, the number of OAM pages required is close to the minimum value. To calculate the number of OAM pages for the table, use:
Formula  Example  
Number of reserved data pages / 63,750  =  Minimum OAM pages  750,000 / 63,750  =  12 
Number of reserved data pages / 2000  =  Maximum OAM pages  750,000 / 2000  =  375 
Add the number of OAM pages to the earlier totals to determine the total number of pages required:
Formula  Example  
Minimum  Maximum  Minimum  Maximum  
Data pages  +  +  750000  750000 
OAM pages  +  +  12  375 
Total  750012  750375 
Use these formulas for clustered and nonclustered indexes on dataonlylength tables.
Index rows containing variablelength columns require more overhead than index rows containing only fixedlength values.
Use this formula if the index contains only fixedlength keys defined as NOT NULL:
9  (Overhead)  
+  Sum of fixedlength keys  
Size of index row 
Use this formula if the index contains any variablelength keys or columns that allow null values:
Formula  Example  
9  (Overhead)  9  
+  Sum of length of fixedlength keys  +  4  
+  Sum of length of variablelength keys  +  20  
+  Number of variablelength keys * 2  +  2  
Size of index row  35 
Formula 
2002 / Size of index row = No. of rows per page 
No. of rows in table / No. of rows per page = No. of leaf pages 
Example 
2002 / 35 = 57 Nonclustered index rows per page 
9,000,000 / 57 = 157,895 leaf pages 
Formula  
No. of leaf pages  /  No. of index rows per page  =  No. of pages at next level 
If the number of index pages at the next level above is greater than 1, repeat the following division step, using the quotient as the next dividend, until the quotient equals 1, which means that you have reached the root level of the index:
Formula  
No. of index pages at previous level  /  No. of nonleaf index rows per page  =  No. of index pages at next level 
Example  
157895/57 = 2771  Index pages, level 1 
2770 / 57 = 49  Index pages, level 2 
48 / 57 =1  Index pages, level 3 
Add the number of pages at each level to determine the total number of pages in the index:
Formula  Example  
Index Levels  Pages 
 Pages  Rows  
3  1  49  
2  +  49  2771  
1  +  2771  157895  
0  +  157895  9000000  
Total number of 2K pages used  160716 
Formula 
Number of index pages / 63,750 = Minimum OAM pages 
Number of index pages / 2000 = Maximum OAM pages 
Example 
160713 / 63,750 = 3 (minimum) 
160713 / 2000 = 81 (maximum) 
Add the number of OAM pages to the total in step 8 to determine the total number of index pages:
Formula  Example  
Minimum  Maximum  Minimum  Maximum  
Nonclustered index pages  160716  160716  
OAM pages  +  +  3  81 
Total  160719  160797 
In addition to the effects of data modifications that occur over time, other factors can affect object size and size estimates:
The space management properties
Whether computations used average row size or maximum row size
Very small text rows
Use of text and image data
Values for fillfactor, exp_row_size, reservepagegap and max_rows_per_page can affect object size.
The fillfactor you specify for create index is applied when the index is created. The fillfactor is not maintained during inserts to the table. If a fillfactor has been stored for an index using sp_chgattribute, this value is used when indexes are recreated with alter table...lock commands and reorg rebuild. The main function of fillfactor is to allow space on the index pages, to reduce page splits. Very small fillfactor values can cause the storage space required for a table or an index to be significantly greater.
With the default fillfactor of 0, the index management process leaves room for two additional rows on each index page when you create a new index. When you set fillfactor to 100 percent, it no longer leaves room for these rows. The only effect that fillfactor has on size calculations is when calculating the number of clustered index pages and when calculating the number of nonleaf pages. Both of these calculations subtract 2 from the number of rows per page. Eliminate the 2 from these calculations.
Other values for fillfactor reduce the number of rows per page on data pages and leaf index pages. To compute the correct values when using fillfactor, multiply the size of the available data page (2016) by the fillfactor. For example, if your fillfactor is 75 percent, your data page would hold 1471 bytes. Use this value in place of 2016 when you calculate the number of rows per page. For these calculations, see "Step 2: Compute the number of data pages" and "Step 8: Calculate the number of leaf pages in the index".
Setting an expected row size for a table can increase the amount of storage required. If your tables have many rows that are shorter than the expected row size, setting this value and running reorg rebuild or changing the locking scheme increases the storage space required for the table. However, the space usage for tables that formerly used max_rows_per_page should remain approximately the same.
Setting a reservepagegap for a table or an index leaves empty pages on extents that are allocated to the object when commands that perform extent allocation are executed. Setting reservepagegap to a low value increases the number of empty pages and spreads the data across more extents, so the additional space required is greatest immediately after a command such as create index or reorg rebuild. Row forwarding and inserts into the table fill in the reserved pages. For more information, see "Leaving space for forwarded rows and inserts".
The max_rows_per_page value (specified by create index, create table, alter table, or sp_chgattribute) limits the number of rows on a data page.
To compute the correct values when using max_rows_per_page, use the max_rows_per_page value or the computed number of data rows per page, whichever is smaller, in"Step 2: Compute the number of data pages" and "Step 8: Calculate the number of leaf pages in the index".
Using average sizes for variable fieldsAll of the formulas use the maximum size of the variablelength fields.
optdiag output includes the average length of data rows and index rows. You can use these values for the data and index row lengths, if you want to use average lengths instead.
Adaptive Server cannot store more than 256 data or index rows on a page. Even if your rows are extremely short, the minimum number of data pages is:
Number of Rows / 256 =  Number of data pages required 
Each text or image or Java offrow column stores a 16byte pointer in the data row with the datatype varbinary(16). Each column that is initialized requires at least 2K (one data page) of storage space.
Columns store implicit null values, meaning that the text pointer in the data row remains null and no text page is initialized for the value, saving 2K of storage space.
If a LOB column is defined to allow null values, and the row is created with an insert statement that includes NULL for the column, the column is not initialized, and the storage is not allocated.
If a LOB column is changed in any way with update, then the text page is allocated. Of course, inserts or updates that place actual data in a column initialize the page. If the column is subsequently set to NULL, a single page remains allocated.
Each LOB page stores approximately 1800 bytes of data. To estimate the number of pages that a particular entry will use, use this formula:
Data length / 1800 = Number of 2K pages 
The result should be rounded up in all cases; that is, a data length of 1801 bytes requires two 2K pages.
The total space required for the data may be slightly larger than the calculated value, because some LOB pages store pointer information for other page chains in the column. Adaptive Server uses this pointer information to perform random access and prefetch data when accessing LOB columns. The additional space required to store pointer information depends on the total size and type of the data stored in the column. Use the information in Table 163 to estimate the additional pages required to store pointer information for data in LOB columns.
Data Size and Type  Additional Pages Required for Pointer Information 
400K image  0 to 1 page 
700K image  0 to 2 pages 
5MB image  1 to 11 pages 
400K of multibyte text  1 to 2 pages 
700K of multibyte text  1 to 3 pages 
5MB of multibyte text  2 to 22 pages 
The advantages of using the formulas are:
You learn more details of the internals of data and index storage.
The formulas provide flexibility for specifying averages sizes for character or binary columns.
While computing the index size, you see how many levels each index has, which helps estimate performance.
The disadvantages of using the formulas are:
The estimates are only as good as your estimates of average size for variablelength columns.
The multistep calculations are complex, and skipping steps may lead to errors.
The actual size of an object may be different from the calculations, based on use.