|
Home > Archive > SQL server exams > October 2002 > Coco's question of the week #14
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Coco's question of the week #14
|
|
| cocolocopolo 2002-10-14, 4:27 pm |
| RE: disk space and pages
How can I count (1) disk space and (2) pages if there is 100,000 rows from the following table.
Create Table MyTable
(a int not null, b char (400) not null, c datetime not null) | |
| 2lazybutsmart 2002-10-15, 10:07 pm |
| quote:
Originally posted by cocolocopolo
RE: disk space and pages
How can I count (1) disk space and (2) pages if there is 100,000 rows from the following table.
Create Table MyTable
(a int not null, b char (400) not null, c datetime not null)
To calculate the amount of disk space required for the database, you should calculate the sum of the estimated sizes of all tables. The following is the proccess, but keep one thing in mind, you should take into account that nchar and nvarchar data types require 2 bytes to represent each character, and the number you specify as the size indicates the number of characters for nchar and nvarchar columns, not the number of bytes of storage space. In your example, you don't have any nchar or nvarchar data types, but if you had, you would always multiply the size * 2 to get the total bytes required by every column of the nchar or nvarchar data type.
Steps:
1- Calculate the total size of all comlumns in a specific table to get bytes per row: in this scenario, 4 bytes for the int column, 400 for the char, and 8 for the datetime column (i.e. an estimated total of 420 bytes per row).
2- Calculate the size of each page: a page is 8 KB, or 8,192 bytes. However, only 8,060 bytes are available for data storage. SQL server uses the other 132 bytes for header information. anywayz, since there is no fill factor, we will divide the page size by the total bytes per row inorder to get the approximate rows per page. Thus, (8,060 / 420) = 20 rows per page.
3- Calculate the total number of pages required by each table: there are 100,000 rows in our table, which requires (100,000 / 20) = 5,000 pages, or (8 KB * 5,000) = 40,000 KB. Thus the total size of this table will be 40,000 KB or 40 MB. | |
| cocolocopolo 2002-10-16, 12:35 am |
| Thanks dear 2lazybutsmart.
First column 4 bytes + second column 400 bytes + third column 8 bytes = 412 bytes.
Wondering how comes up to 420 bytes?
The extra bytes is overhead???
Any formula for the overhead??? | |
| 2lazybutsmart 2002-10-16, 7:49 am |
|  , i know, but this is a rounded number. make sure those numbers end with a zero .
for example, if you end up with 991 bytes, then it's 1 KB for sure. in my scenario, it's 420 cuz it was 412.
get it big boy?
as for the overhead, you need not worry yourself with it. Just make room for it, and forget it COMPLETLEY
cheers,
2lazybutsmart | |
| cocolocopolo 2002-10-17, 2:11 am |
| Thanks 2lazybutsmart.
I read several books and no books showed
round to the nearest '0' to get some extra
room. That's why I am cursious if there
is a formula can count the overhead.
Thanks thanks. | |
| 2lazybutsmart 2002-10-17, 3:26 pm |
| coco,
the overhead, as i mentioned before, is a 132 byte data block reserved by SQL server for information regarding the current page. Such as information about page number, leaf number, root number, b-tree structure info, etc.. and so on and so forth.
This block of 132 bytes is inaccessible. You can't count on it. It's not there for you, it's for SQL Sever.
Now, the rounding to the nearest '0' doesn't connote any special meaning other than having whole number that end with zero. this makes your calculations easier. makes life easy. But that rounding has nothing to do with the overhead. The overhead is FIXED. NOONE/NOBODY whatsoever can use that space to store data. CASE CLOSED.
, that's the M$ way of expressing it's dictatorship
cheers,
2lazybutsmart | |
| cocolocopolo 2002-10-19, 12:04 am |
| Thaks dear 2lazybutsmart. |
|
|
|
|