INF: Sizing Considerations for SQL Databases (68231)

MORE INFORMATION

1. Calculation for determining the number of 2K pages needed for a table with no indexes or text/image columns:
   
   The overhead per data row is roughly 7 bytes plus the number of variable-length and/or nulls-allowed fields per row. The formula is as follows
   

   Row length = 7 bytes + (data length) + (#var or null fields)

   where:
   

         data length = is the sum of the column lengths.
         #var or null fields = 1 byte is counted for every data element
                               defined as varchar or able to accept
                               null values.
   
   						

   Consider the following table definition:
   

         first_element           char(2) null,
         second_element          varchar(10),
         third_element           char(3),
         fourth_element          varchar(20) null
   
         row length = 7 + (35) + (3)
         row length = 45
   
   						

   NOTE: When the datatype varchar is defined to accept NULLs, only 1 byte is counted for this condition.
   
   Because rows must fit into 2K pages, where each page has 32 bytes of overhead, The number of pages required to hold a table is defined with the following formula:
   

   pages = [number of rows] / (2016/(row length))

   For example,
   

   (800000 rows) / (2016/(100 bytes per row)) = ~40000 pages

   -or-
   
   pages = [number of rows] * ((row length)/2016)
   
   For example,
   

   (800000 rows) * ((100 bytes per row)/2016)= ~40000 pages

2. Considerations for tables with text/image columns:
   
   When a table has a text/image column, 16 bytes are used for managing the text/image column (4 bytes are used for a page pointer, 8 bytes for a timestamp, and 4 bytes for other management functions). One page (2K) is reserved for each text/image column in a row.
3. Considerations for page management:
   
   There is a page-management page for every 255 user pages. Each page is 2048 (2016 + 32 bytes for overhead); therefore, this amounts to two page-management pages per megabyte with 4K of page-management overhead per megabyte.
   
   Another consideration for the page-management technique is that a maximum 32 distinct objects can be allocated per page-management page. Each index on a table counts as an additional object. For example: if 8 tables, each with 3 indexes, were created, 1/2 megabyte would be reserved, even if no data were in the table: (3 indexes + table) * 8 = 32 objects.
4. Considerations for indexes on tables:
   
   Indexes also consume pages; the value being indexed is replicated in each row. The formulas for calculating the page requirement is the same as for data.
   
   The overhead per clustered index row is roughly 8 bytes plus the number of variable-length/nulls-allowed fields per row. The formula is defined as:
   

   row length = 8 + (data length) + (#var or null fields)

   The overhead per non-clustered index row is roughly 12 bytes plus the number of variable-length/nulls-allowed fields per row. The formula is defined as:

   row length = 12 + (data length) + (#var or null fields)

   The number of pages in an index depends on how many index rows fit in a page, and this is determined the same way as for data. The formula is defined as:
   

         pages = [number of rows] / (2016/(row length))
   
         For example,
   
            (800000 rows) / (2016/(100 bytes per row)) = ~40000 pages
   
         -or-
   
         pages = [number of rows] * ((row length)/2016)
   
         For example,
   
            (800000 rows) * ((100 bytes per row)/2016)= ~40000 pages
   
   						

   Please keep in mind that these formulas will give you a rough estimate on space required for your database.

For more information on this topic, see Appendix A of the "Microsoft SQL Server System Administration Guide" for version 4.2.