Post reply

Storage estimation

  • March 4, 2008 at 3:15 am

    #184716

    Hi everybody,

    I was asked to solve a problem due to a bad initial designing of a model.

    I have a SQL Server 2005 DB, that after 3 months of production, is 50Gb large.

    We worry about the size it would have in few months. After a study, we see that a table is hardly half of the DB.

    This table has 13 columns :

    3 int - 2 bigint - 4 datetime - 1 Guid - 1 Varbinary(max) - 2 xml columns.

    In one xml column, there was in a tag, the whole content of the varbinary(max) column : a pdf or a doc file.

    In the second xml column, an image file was stored in a tag of an xml document. It was a logo.

    It was absolutely unuseful. We decide not to store those data. I wrote a stored procedure to update this table : updating these two tags into xml documents by xquery statements (replacing by '').

    It works. The statements ran for more than one hour and after rebuilding index, I got some information about space.

    Initial used space : 22 Gb

    After processing : data space : 8 Gb

    reserved space for this table : 6 Gb

    15 Mb for indexes.

    I suppose that I can get the unused space by DBCC CLEANTABLE but it is not relevant.

    But I want to know why I got that repartition.

    I compute storage size for each column :

    for xml column, it is the average of the datalength(tag in xml column) : 42504 bytes and 3093 bytes;

    for the varbinary(max), the average of len(column) is 31 580 bytes

    The table has about 153 000 records.

    If I calculate the space my sql statement would free, I get :

    153000*(42504 + 3093) = 6.5Gb

    22-6.5 = 15.5Gb

    The fillfactor is set to 50.

    I don't really understand why I get those results. Can you give me some ways to comprehend them ? I read a lot of articles about storage but I never obtain the same final space numbers.

    Thanks

    NB : excuse my english, but I'm french and I tried to be the more comprehensible as possible.

  • March 5, 2008 at 8:12 am

    #785512

    Nobody wants to give me some ways to understand ? :crying:

  • March 6, 2008 at 2:14 pm

    #786349

    Sorry, but my guess is that there isn't enough heavy duty users of varbinary and xml that would be able to give you guidance.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • March 7, 2008 at 1:15 am

    #786500

    It doesn't matter.

    But this size difference will lead me to change the way to store files, by changing the C# implementation.

    Thanks for reading.

  • March 7, 2008 at 8:01 am

    #786682

    I would take a look at storing your files as just that - disk files. Then store the full path and filename in a database field. This will allow your front-end code to get ( and manipulate) the files directly but also allow you to chance the location in the database with a simple update of the filepathname field. I have a client that does this for terabytes of disk files and it works great.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • March 7, 2008 at 10:21 am

    #786806

    Thanks.

    It is what I advised instead of storing directly in the DB. The problem now is to find a file server.

    I dream now because I can't believe that a company like mine, to be unable to find hard drives to store files.

    What a pity.

    Thanks for your reply.

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

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