Home About Us SQL Interview Book Contact Us RSS
Articles
Tools
Code Snippets
Links
Tips & Tricks
FAQ
Resources
Articles
Code Snippets
Links
FAQ
Resources

Saravana Kumar
Vinod Kumar


Sizing of databases

This topic is a delicate one to handle. But here is a tool that allows you to analyze how your database is organized. To walk you through some of the detials of the tool. The tool uses excel macro and SQL-DMO to query the database. The data from the database are entered into the corresponding fields. The snap shot of the tool

Above shows the northwind database's Product table's output. The primary documentation for the tool:

1

Fill Factor (Fill_Factor)

The amount of space left free on every data pages

2

Number of rows in the table

Specifies the number of rows present in the table.

3

Number of columns

-

4

Sum of bytes in all fixed-length columns

-

5

Number of Fixed Columns

Includes columns like Int, Money, UniqueIdentifier, DateTime and TimeStamp

6

Number of variable-length columns

Variable length columns include Varchar Columns

7

Maximum size of all variable-length columns

-

8

Null Bitmap (Null_Bitmap)

If there are fixed-length columns in the table, a portion of the row, known as the null bitmap, is reserved to manage column nullability

9

Total size of variable-length columns (Variable_Data_Size)

Length of the maximum data for all varchar columns.

10

Total row size (Row_Size)

Estimated minimum row rize.

11

Number of rows per page (Rows_Per_Page)

Because rows do not span pages, the number of rows per page should be rounded down to the nearest whole row.

12

Number of free rows per page (Free_Rows_Per_Page)

The fill factor used in the calculation is an integer value rather than a percentage.
Because rows do not span pages, the number of rows per page should be rounded down to the nearest whole row. As the fill factor grows, more data will be stored on each page and there will be fewer pages.

13

Number of pages (Num_Pages)

The number of pages estimated should be rounded up to the nearest whole page.

14

Table size (bytes)

Calculates the amount of space required to store the data in a table.

Keep a close watch on the total row size and Number of rows per page values. These ring alarm to most of the database design. The tool assumes the tables used has a clustered index. The tool doesnot account for the space utilized by the indexes. The tool takes an inventory for the data pages only. We donot account for the text datatype columns also. These are some of the limitation we are worked with. This tool is intended to look at how we have taken care of sizing of database.

Download tool