Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    2

    Unanswered: Estimating Size of a Table

    There's seem to be a problem with the formula given from the documentation of SQL Server2K:


    The formula for computing :

    RowsPerPage = 8096 */(RowSize+2);
    FreeRowsPerPage = 8096 * (100-FillFactor)/100)/(RowSize+2)
    NumberOfPages = NumRows/(RowsPerPage - FreeRowsPerPage)
    TableSize = 8192 * NumberOfPages

    Question....what if the FillFactor is zero,
    the NumberOfPages will have an error (divide by zero).....

    Even if this is greater than zero (eg 1, 2), the TableSize computed is too big if compared with the output from SP_SPACEUSED....

    Can anyone help me on this?
    Thank.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11

    Re: Estimating Size of a Table

    The FillFactor is a bit of a fudge factor. It is the minimum percentage of the page that will be filled. If you supply a fillfactor of 1, and the average row takes up 75% of the page, then you get about the same actual result as if you had made the fillfactor 74.

    One other thing to note is that fillfactor is only used at the creation of an index. As time goes by, and rows are updated, inserted, and deleted, the actual page usage can vary widely.

    Hope this helps.
    ~Matt

  3. #3
    Join Date
    Jan 2003
    Posts
    2
    Thanks for the reply Matt.

    I have a very large table (abt 7M rows). The time I created it, I didnt specify the FillFactor, so it defaults to 0 as stated in the doc. So I thought I should use the same FillFactor in the formula, but then it gave me too big a tablesize. From my understanding on your reply that actual page usage varies after a number of DML statements, does it mean that the formula given may not apply anymore?

    I'm creating a program to get the tablesize (from this value, also growth rate) of all my tables (from production db) and put them into a table.

    Is there a another accurate way for me to get the tablesize other than the SP_SPACEUSED?

    Btw, how accurate is the SP_SPACEUSED?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    That is correct, if the table has undergone significant data modifications (insert, update, delete), then the formula is not going to be a very good guide. sp_spaceused can also drift over time, as the data is modified. I have seen import tables that have large amounts of I/O (truncate and bcp) become negative in size, but DBCC UPDATEUSAGE will clear that up admirably.

    sp_spaceused is fairly accurate at times. If you can manage to run DBCC UPDATEUSAGE(0) on the database before you run sp_spaceused, you will get almost exact results. At least, as exact as anything in MS SQL ;-).

  5. #5
    Join Date
    Oct 2002
    Posts
    369
    RE:
    Q1 ...If you can manage to run DBCC UPDATEUSAGE(0) on the database before you run sp_spaceused, you will get almost exact results. At least, as exact as anything in MS SQL ;-).
    A1 You may wish to consider running sp_SpaceUsed with the update option (for each object if that best meets the requirement). If sp_SpaceUsed is run frequently that will address the issue. For example:

    Use Pubs
    Go
    -- Update usage for entire Pubs DB:
    Exec sp_SpaceUsed
    @updateusage = 'True'
    Go

    -- Update usage for Authors table only:
    Exec sp_SpaceUsed
    @objname = 'Authors',
    @updateusage = 'True'


    RE:
    Q2 ...Is there a another accurate way for me to get the tablesize other than the SP_SPACEUSED?

    Q3 Btw, how accurate is the SP_SPACEUSED?
    A3 The sp_SpaceUsed proc queries file page use data, file data, etc., there is not likely a more accurate method. (A2-->) However, nothing prevents one from using it as a starting point in an effort to make a more accurate "sp_MoreAccurateSpaceUsed".

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •