By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,496 Members | 1,489 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,496 IT Pros & Developers. It's quick & easy.

Calculating Row Size including blobs

P: n/a
I've seen plenty of posts regarding the estimation of table size,
usually in the processing of planning for server storage needs.

Well, I've got a different problem. I need to know how much data each
of our Customers are using in a Database. (1 SQL DB stores multiple
customers).

Basically, I want to be able to say: Customer A: 45.5 MB, Customer B:
655 MB.

So, how can I ask SQL Server how much data each Row in each table is
taking up? I want to be able to calculate nightly the total size, so I
would take each row in each table that belong to the customer, and add
all the sizes together. I want to take into account blobs that are
storing images and PDF files also.

Thanks in advance,
Jesse Wolgamott
Jul 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
[posted and mailed, please reply in news]

Jesse Wolgamott (je*************@gmail.com) writes:
I've seen plenty of posts regarding the estimation of table size,
usually in the processing of planning for server storage needs.

Well, I've got a different problem. I need to know how much data each
of our Customers are using in a Database. (1 SQL DB stores multiple
customers).

Basically, I want to be able to say: Customer A: 45.5 MB, Customer B:
655 MB.

So, how can I ask SQL Server how much data each Row in each table is
taking up? I want to be able to calculate nightly the total size, so I
would take each row in each table that belong to the customer, and add
all the sizes together. I want to take into account blobs that are
storing images and PDF files also.


This is not a problem with a clearcut answer, since if a table gets
fragmented, that extra space is not really any particular customer,
but presumably someone should be accounted for it.

Therefore I would for the non-blob data take the number of rows for
each customer and scale that with the number of rows for each customer.
For the blob data I would simply sum the sizes of each field:

SELECT t.CustomerID,
MB = ((1E0 * COUNT(*) * i.reserved / i.rows) * 8192 +
SUM(coalesce(datalength(t.blobcol), 0)) / 1E6
FROM tbl t
CROSS JOIN sysindexes i
WHERE i.id = object_id('tbl')
AND i.indid IN (0, 1)
GROUP BY t.CustomerID

You should run DBCC UPDATEUSAGE on the database prior to running this-

The result will be skewed if you have tables with long non-blob
character/binary columns where different customer have very differing
average length on these columns. Also, if you are using the "text in row",
customers with small blobs may be accounted twice for the same thing.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.