Memo from Julian Fox of PricewaterhouseCoopers
-------------------- Start of message text --------------------
Hi Kevin.
Regarding your first question: try searching the index of online books for
'estimating table size' where you'll find the following lengthy but useful
advice.
Regards,
Julian.
Estimating the Size of a Table with a Clustered Index
The following steps can be used to estimate the amount of space required to
store the data and any additional nonclustered indexes on a table that has
a clustered index.
Calculate the space used to store data.
Calculate the space used to store the clustered index.
Calculate the space used to store each additional nonclustered index.
Sum the values calculated.
For each calculation, specify the number of rows that will be present in
the table. The number of rows in your table will have a direct effect on
the size of your table.
Number of rows in the table = Num_Rows
CALCULATE THE SPACE USED TO STORE DATA:
To calculate the space used to store data, see Estimating the Size of a
Table.
Note the value calculated:
Space used to store data = Data_Space_Used
CALCULATE THE SPACE USED TO STORE THE CLUSTERED INDEX:
The following steps can be used to estimate the amount of space required to
store the clustered index.
Your clustered index definition can include fixed-length and
variable-length columns. To estimate the size of the clustered index, you
will need to specify the space each of these groups of columns occupies
within the index row.
Number of columns in index key = Num_CKey_Cols
Sum of bytes in all fixed-length key columns = Fixed_CKey_Size
Number of variable-length columns in index key = Num_Variable_CKey_Cols
Maximum size of all variable-length key columns = Max_Var_CKey_Size
If there are fixed-length columns in the clustered index, a portion of the
index row is reserved for the null bitmap. Calculate its size:
Index Null Bitmap (CIndex_Null_Bitmap) = 2 + (( Num_CKey_Cols + 7) / 8 )
Only the integer portion of the above expression should be used; discard
any remainder.
If there are variable-length columns in the index, determine how much space
is used to store the columns within the index row:
Total size of variable length columns (Variable_CKey_Size) = 2 +
(Num_Variable_CKey_Cols x 2) + Max_Var_CKey_Size
If there are no variable-length columns, set Variable_CKey_Size to 0.
This formula assumes that all variable-length key columns are 100 percent
full. If you anticipate that a lower percentage of the variable-length key
column storage space will be used, you can adjust the result by that
percentage to yield a more accurate estimate of the overall index size.
Calculate the index row size:
Total index row size (CIndex_Row_Size) = Fixed_CKey_Size +
Variable_CKey_Size + CIndex_Null_Bitmap + 1 + 8
Next, calculate the number of index rows per page (8096 free bytes per
page):
Number of index rows per page (CIndex_Rows_Per_Page) = ( 8096 ) /
(CIndex_Row_Size + 2)
Because index rows do not span pages, the number of index rows per page
should be rounded down to the nearest whole row.
Next, calculate the number of pages required to store all the index rows at
each level of the index.
Number of pages (level 0) (Num_Pages_CLevel_0) = (Data_Space_Used / 8192) /
CIndex_Rows_Per_Page
Number of pages (level 1) (Num_Pages_CLevel_1) = Num_Pages_CLevel_0 /
CIndex_Rows_Per_Page
Repeat the second calculation, dividing the number of pages calculated from
the previous level n by CIndex_Rows_Per_Page until the number of pages for
a given level n (Num_Pages_CLevel_n) equals one (index root page). For
example, to calculate the number of pages required for the second index
level:
Number of pages (level 2) (Num_Pages_CLevel_2) = Num_Pages_CLevel_1 /
CIndex_Rows_Per_Page
For each level, the number of pages estimated should be rounded up to the
nearest whole page.
Sum the number of pages required to store each level of the index:
Total number of pages (Num_CIndex_Pages) = Num_Pages_CLevel_0 +
Num_Pages_CLevel_1 +
Num_Pages_CLevel_2 + ... + Num_Pages_CLevel_n
Calculate the size of the clustered index (8192 total bytes per page):
Clustered index size (bytes) = 8192 x Num_CIndex_Pages
CALCULATE THE SPACE USED TO STORE EACH ADDITIONAL NONCLUSTERED INDEX:
The following steps can be used to estimate the amount of space required to
store each additional nonclustered index.
Your nonclustered index definition can include fixed-length and
variable-length columns. To estimate the size of the nonclustered index,
you will need to calculate the space each of these groups of columns
occupies within the index row.
Number of columns in index key = Num_Key_Cols
Sum of bytes in all fixed-length key columns = Fixed_Key_Size
Number of variable-length columns in index key = Num_Variable_Key_Cols
Maximum size of all variable-length key columns = Max_Var_Key_Size
If there are fixed-length columns in the index, a portion of the index row
is reserved for the null bitmap. Calculate its size:
Index Null Bitmap (Index_Null_Bitmap) = 2 + (( Num_Key_Cols + 7) / 8 )
Only the integer portion of the above expression should be used; discard
any remainder.
If there are variable-length columns in the index, determine how much space
is used to store the columns within the index row:
Total size of variable length columns (Variable_Key_Size) = 2 +
(Num_Variable_Key_Cols x 2) + Max_Var_Key_Size
If there are no variable-length columns, set Variable_Key_Size to 0.
This formula assumes that all variable-length key columns are 100 percent
full. If you anticipate that a lower percentage of the variable-length key
column storage space will be used, you can adjust the result by that
percentage to yield a more accurate estimate of the overall index size.
Calculate the nonleaf index row size:
Total nonleaf index row size (NL_Index_Row_Size) = Fixed_Key_Size +
Variable_Key_Size + Index_Null_Bitmap + 1 + 8
Next, calculate the number of nonleaf index rows per page:
Number of nonleaf index rows per page (NL_Index_Rows_Per_Page) =
( 8096 ) / (NL_Index_Row_Size + 2)
Because index rows do not span pages, the number of index rows per page
should be rounded down to the nearest whole row.
Calculate the leaf index row size:
Total leaf index row size (Index_Row_Size) = CIndex_Row_Size +
Fixed_Key_Size + Variable_Key_Size + Index_Null_Bitmap + 1
The final value of 1 represents the index row header. CIndex_Row_Size is
the total index row size for the clustered index key.
Next, calculate the number of leaf level index rows per page:
Number of leaf level index rows per page (Index_Rows_Per_Page) = ( 8096 ) /
(Index_Row_Size + 2)
Because index rows do not span pages, the number of index rows per page
should be rounded down to the nearest whole row.
Calculate the number of reserved free index rows per page based on the fill
factor specified for the nonclustered index. For more information, see Fill
Factor.
Number of free index rows per page (Free_Index_Rows_Per_Page) = 8096 x
((100 - Fill_Factor) / 100) /
Index_Row_Size
The fill factor used in the calculation is an integer value rather than a
percentage.
Because index rows do not span pages, the number of index rows per page
should be rounded down to the nearest whole row.
Next, calculate the number of pages required to store all the index rows at
each level of the index:
Number of pages (level 0) (Num_Pages_Level_0) = Num_Rows /
(Index_Rows_Per_Page - Free_Index_Rows_Per_Page)
Number of pages (level 1) (Num_Pages_Level_1) = Num_Pages_Level_0 /
NL_Index_Rows_Per_Page
Repeat the second calculation, dividing the number of pages calculated from
the previous level n by NL_Index_Rows_Per_Page until the number of pages
for a given level n (Num_Pages_Level_n) equals one (root page).
For example, to calculate the number of pages required for the second and
third index levels:
Number of data pages (level 2) (Num_Pages_Level_2) = Num_Pages_Level_1 /
NL_Index_Rows_Per_Page
Number of data pages (level 3) (Num_Pages_Level_3) = Num_Pages_Level_2 /
NL_Index_Rows_Per_Page
For each level, the number of pages estimated should be rounded up to the
nearest whole page.
Sum the number of pages required to store each level of the index:
Total number of pages (Num_Index_Pages) = Num_Pages_Level_0 +
Num_Pages_Level_1 +Num_Pages_Level_2 + ... + Num_Pages_Level_n
Finally, calculate the size of the nonclustered index:
Nonclustered index size (bytes) = 8192 x Num_Index_Pages
CALCULATE THE SIZE OF THE TABLE:
Calculate the size of the table:
Total table size (bytes) = Data_Space_Used + Clustered index size +
Nonclustered index size + ...n
"Sexton, Kevin" <kevin.[Email Address Removed] on 11/01/2002 12:44:51
To: [Email Address Removed] restore and index size
2 questions:
1. Is there any way to estimate how large an index will be based on the
size of the table? I know it depends but....if I had a 60 GB table with
an Index how large would the index be. How about if it was a clustered
index?
2. How long should it take to restore a 20GB dump file?
Kevin J. Sexton
kevin.[Email Address Removed] End of message text --------------------
This e-mail is intended only for the person to whom it is
addressed. If an addressing or transmission error has
misdirected this e-mail, please notify the author by replying to
this e-mail. If you are not the intended recipient you must not
use, disclose, copy, print or rely on this e-mail.
The principal place of business of PricewaterhouseCoopers and
its associate partnerships is 1 Embankment Place, London
WC2N 6RH where lists of the partners' names are available for
inspection. All partners in the associate partnerships are
authorised to conduct business as agents of, and all contracts
for services to clients are with, PricewaterhouseCoopers. The
UK firm of PricewaterhouseCoopers is authorised by the
Institute of Chartered Accountants in England and Wales to
carry on investment business. PricewaterhouseCoopers is a
member of the world-wide PricewaterhouseCoopers
organisation.
PricewaterhouseCoopers may monitor outgoing and incoming
e-mails and other telecommunications on its e-mail and
telecommunications systems.
----------------------------------------------------------------
Visit our website http://www.pwcglobal.com
----------------------------------------------------------------
The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of, or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited. If you received
this in error, please contact the sender and delete the material from any
computer.
MS Sql Server LazyDBA home page