Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Sean_B
Posting Yak Master
111 Posts |
 Posted - 2010-01-13 : 07:22:19
|
| Hi,I've a general question about how I can calculate the storage overhead for a table.I can work out the amount of storage used for a table by adding the number bytes per column together and multiplying by the number of rows.Are there other storage overheads associated with the table ?How do I work out the storage requirements of an index, is it just the number of columns in the index plus some pointer value per row ?Any answers / suggested reading would be appreciated.Sean |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-01-13 : 07:27:32
|
Yes there is.1) Nullable columns have a bitmap2) Bit columns have a bitmap3) If there is a nonclustered index but not a clustered index there is a 14 byte overhead per record for row identifier4) varchar columns have an overhead
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
Sean_B
Posting Yak Master
111 Posts |
Posted - 2010-01-13 : 07:31:43
|
| Thanksso an index would be the size of the columns in the index plus 14 bytesSean |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-13 : 08:29:43
|
| This may help (its SQL 2008, but I'm sure there is a similar one in the SQL 2005 Microsoft DOCs):Estimating size of Database / Table / Index:http://msdn.microsoft.com/en-us/library/ms187445.aspx |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2010-01-13 : 08:34:30
|
| Check sys.allocation_units . This will give you amongst other things) used_pages - which you could convert into kb. You'll have to do a join between sys.indexes and sys.partitions , if you want to reference the objectid of indexJack Vamvas--------------------http://www.ITjobfeed.com |
|
|
|
|
|
|
Storeage overheads