Script to Estimate Table size

Question

Script to Estimate Table size

repent_kog_is_near

SSCertifiable

Points: 7278
More actions
March 24, 2010 at 6:50 am

#99122

Has there been any script written to automate the formulas given in, so it can be done for many tables at a time:
http://msdn.microsoft.com/en-us/library/aa933068%28SQL.80%29.aspx
Dan

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i have this saved in my snippets; it is slightly different from the formula you pointed to, but you could change it easily:

select object_name(object_id),sum(max_length) from sys.columns group by object_id

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

repent_kog_is_near SSCertifiable Points: 7278 More actions · Answer 2

I see your logic. It seems too simple, but then why all the extra procedure explained in the article? It is a matter of exact vs approx? (in your method, if there are varchar(100), in reality, won't be accurate)

Lowell SSC Guru Points: 323518 More actions · Answer 3

well mine calculates the max row size, but it would be misleading for for tables that have a varchar(max) columns, since their size is -1...when it is really what,a 4 byte pointer if it's on disk. that's easy enough to take into consideration though.

if you mean ACTUAL row size, where a definition is varchar(200), but it has a five char value, that is a different query, because you have to calculate each row, not just a specific row.

the real question is what you are trying to do...plan in general for how much disk space a database might need, or calculating the actula , right now size.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

repent_kog_is_near SSCertifiable Points: 7278 More actions · Answer 4

how can you find how many and what are the varchar(max) columns in the database?

we can find current space from sp_spaceused, and I can divide by number of rows to get avereage space for each row. To estimate, how close to ok will be it to just multiply by the project volume of rows- if the result from your query give 300 bytes for tableA, and if you estimate, 1 M rows in the next month, is it safe to assume (1Mx300) it will grow by 286 MB in a month?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Here's a View that I wrote awhile back that should do most of what you need: http://www.sqlservercentral.com/scripts/tables/62545/

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]