Table Sizing
-
I shrunk my db using EM and now when I look at the size of my data file it is being reported as 6Gb. I am confused by this, however, because one of my tables has a total column size of 316 and there are 6581010 rows. Based upon these figures I have calculated the number of rows per page to be 25 which means that 263241 pages will be required for all of those rows. If each page is 8Kb that means the total space required is around 16Gb.
What am I doing wrong?!
-
David,
if the column type for some columns is var-something, like varchar or nvarchar then only the actual size will stored. Example: your column is varchar(255) and you would think it is 255 per row, but if nothing is stored in this row then it will take only 2 bytes (See*) as I understand for the column for this row with 0 bytes for actual data. Or if a word "byte" is stored in this row then it will be 4 bytes + (See*). So actual size of the table will be much smaller.
(*) See BOL article "Estimating the Size of a Table"
This article contains calculations how may bytes per row, what is overhead per row, per column, per nullable column etc.
Regards,Yelena Varsha
-
execute DBCC UpdateUsage..
You can execute DBCC ShowContig to see the fragmentation and page density etc for that object..
Also you can to EM and view the tables and index sizes in the taskpad view...
-Krishnan
-
you need to account for your fill factor too.... asuming you're not taking the default.
-
Sorry, still confused because the amount of space used by the database as a whole is 6Gb but my calculation does not fit with this.
The table has 31 columns of which 20 are data type numeric(13 (nullable)), one is uniqueidentifier(16) and the remainder are int(4). I have worked out how many rows do not have data in any of these columns but even excluding those particular columns does not calculate anywhere near 6Gb. On top of this, I have only calculated one table and there are 190 tables!
Thanks
-
Maybe this helps to figure it out ...
Keep in mind it uses systemtables, which is no a best practise
-- Select 'DBCC UPDATEUSAGE (''' + db_name() + ''', ''' + u.name + '.'+ o.name + ''') with count_rows ' + char(13) + ' go '
Select 'exec sp_spaceused ''' + u.name + '.'+ o.name + ''' ' + char(13) + ' go '
from sysobjects o
inner join sysusers u
on (o.uid = u.uid)
where o.xtype = 'u'
-- and u.name = 'Insite_Dev'
-- and o.name = 'T_fromGMPSFA_Step_Feedback'
order by u.name, o.name
First run the generated script for updateusage, the generate the script for spaceused, and run it.
Examine the results.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I got about 1.5 GB when I did the raw math.
Row size = 236 bytes. 20*9 (numeric 13) = 180 + 16 (guid) + 40 (10 ints) = 236 bytes.
6581010 rows * 236 bytes = 1.55 GB. Now adjust somewhat for page density and you are still nowhere near 6GB, let alone 16.
jg
-
can you post your table and index DDL ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
you may also want to run sp_updatestats ! (if you are alowed to)
Did you rebuild your clustering index ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Hi All,
I think Jeff is right, no idea how I came up with my original figure of 16Gb. I am going to recalculate my other tables now!
Thanks to all for your help.
Regards
-
A blind squirrel trips over a nut every now and then.
heh
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply