Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2002
    New York

    Unanswered: page density 1% !!?!

    Hi all,
    Our production server is currently running SQL Server 7.0
    sp3. We have a table that is inserted to, and deleted from
    all the time. Data is selected from it as well. There is a
    non clustered index on the primary key fields (2). We have
    a few stored procedures that insert a bunch of records
    into this table, select and insert those records into
    another table, then delete these records when the insert
    into the second table has completed successfully. This
    process is enclosed in a transaction block.

    What I've been seeing is the size of the table grows
    continuously over a period of time, when I actually have
    to drop / recreate the table to keep it manageable. I've
    run a dbcc showcontig and find that the scan density is
    usually up around 98%, but the average page density is
    always under 2%! It's almost as though there is only a few
    records per data page. The record size is only 217 bytes
    (not including overhead), so I don't think this is the
    reason. However, in trying to find more information about
    internal fragmentation, which is what the page density
    represents, I've only found information that indicates
    that this is caused by a large record size.

    Because there is no clustered index on this table, records
    are inserted to a heap. Putting a clustered index on the
    table seems to clear up the continuous table growth, but I
    am concerned that many concurrent processes trying to
    insert records with a key containing sequential integer values will create data page 'hot spots',affecting the performance of the stored procedures that are involved.

    I would appreciate any insight into this behavior. Records
    are constantly being added and then deleted from the
    table. According to the dbcc showcontig output, it appears
    that it's the data pages that are fragmented. Is this true?

    Let me also add that there is a fill factor of 90% on the non clustered index.

  2. #2
    Join Date
    Dec 2001
    Toronto, Canada
    Even without the clustered index you are getting a 'hot page', like you said the table is a heap and all data is being added to the end. Also when the data is deleted from the heap that space will not be reused since all new inserts are added to the end of the table. I would look at creating a clustered index on another column(s), something not sequential.

    Is this data being searched and if so by what column(s) and are these searches range searches (LIKE, BETWEEN or ex. Lastname='Smith'), if so then these column(s) may be good candidates for the clustered index.

    You may want to set your fillfactor to something lower 50-60%, play with it. FYI, the fillfactor is only used on the intial creation of the indexes any page splits I believe uses a fillfactor of 75%, not quite sure on that number.

Posting Permissions

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