Microsoft KB Archive/68231

= INF: Sizing Considerations for SQL Databases =

Article ID: 68231

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 4.2 for OS/2
 * Microsoft SQL Server 4.21a Standard Edition

-



This article was previously published under Q68231



SUMMARY
Listed below is information on sizing considerations for databases.



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

 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. 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.  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 &quot;Microsoft SQL Server System Administration Guide&quot; for version 4.2.

Additional query words: 4.20

Keywords: kbother KB68231

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.