INF: Sizing Considerations for SQL Databases

ID: Q68231

The information in this article applies to:

SUMMARY

Listed below is information on sizing considerations for databases.


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.

Additional query words: 4.20 


Keywords          : kbother SSrvAdmin SSrvGen 
Version           : 4.2 4.21 4.21a
Platform          : OS/2 WINDOWS 
Issue type        :

Last Reviewed: March 10, 1999