April 28, 2004 at 4:26 pm
I'm building a spreadsheet (or script) for customers, so we/they can estimate storage required based on orders they expect to process as well as other factors. Please confirm my calculation of the KB for a single row of a given table is correct. I take results of sp_spaceused table (after updating the usage) and enter it into the formula
rowsize (in KB) = (data + index_size)/rows
This seems to take into account the index size for the table. Is it accurate. I haven't seen anyone caluclate index space for a single row.
Another technique is cool, but seems to refer to only data, not index space:
SELECT RecordLen =
MAX(
ISNULL(DATALENGTH([col1]),0) +
ISNULL(DATALENGTH([col2]),0) + etc.
)
FROM Table
Other techniques that seem to only include the data size are...
1. SELECT SUM(Length) FROM syscolumns WHERE OBJECT_NAME(Id) = 'table'
2. Get maximumrowsize field from...DBCC SHOWCONTIG('table') WITH TABLERESULTS
Any suggestions on getting index size or which of these is best?
Thanks!
smv929
April 29, 2004 at 10:35 am
A few things to consider. Allocations are in extents, so you will always use space according in extent lengths. Similar for pages.
Indexes are separate (nonclustered) and have some small overhead, but will be on separate pages. The size should be close the the column sizes in the index. The table also has some overhead, if there's no clustered index, then another key is added to make the row unique, adds a little overhead to each row.
To get the index size, I don't have a good automated way. I've always just placed a guesstimate amount for OLTP systems. For OLAP type systems, you probably should calculate since there usually are more indxes and they can exceed the size of the data.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 3, 2004 at 7:21 am
So, to calcualte the space for a nonclustered index on colA, you would basically use the same calculation for the colum's data -- something like (size of colA datatype multiplied by the row count)? I will test this and compare a manual calculation of all indexes on a table and compare it to the index size returned by sp_spaceused. Thanks!
smv929
May 3, 2004 at 10:21 am
May not be the format your looking for
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES.
look at the average row size.
"Keep Your Stick On the Ice" ..Red Green
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply