Results 1 to 1 of 1
  1. #1
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210

    Unanswered: DB went from 2GB to 3GB overnight

    I changed the Identity column in our 3.5 million row Order Line Items to clustered. Suddenly; the .mdf file grew from 2GB to 3GB.

    Is this normal? Do I need to reindex or something? Fill Factor is 85% - should I have made that something different?

    This was done because the new software going in had massive performance improvement on this (it does millions of user function calls all based on this key).

    All is behaving OK but I'd love to understand more about what I did. Thanks so much for any link or instruction on this matter.

    DBCC Contig shows the following:
    DBCC SHOWCONTIG scanning 'tbInvoiceLine' table...

    Table: 'tbInvoiceLine' (1406732164); index ID: 1, database ID: 7

    TABLE level scan performed.

    - Pages Scanned................................: 134403

    - Extents Scanned..............................: 16832

    - Extent Switches..............................: 16831

    - Avg. Pages per Extent........................: 8.0

    - Scan Density [Best Count:Actual Count].......: 99.82% [16801:16832]

    - Logical Scan Fragmentation ..................: 4.11%

    - Extent Scan Fragmentation ...................: 7.52%

    - Avg. Bytes Free per Page.....................: 977.3

    - Avg. Page Density (full).....................: 87.93%
    The table should probably be about 95% fill factor based on how much gets added after initial creation. EDIT: I just checked out the typical Insert and 5% would be high even. So; I just changed the fill factor to 95%. This based on BOL best practices writeup here.

    This table does see a TON of indexed lookups using the Clustered Key, but it also gets FTS quite often (I think ... not absolutely sure). Wonder if there's a way to say "Show me all queries that have a query plan of FTS on this table". I know it's looked at by all the Sales Reports - lookup by an alternate key (Order #).

    Anyway; I don't see how leaving an extra 15% wasted could have resulted in a 50% growth of the entire database. Granted; this is by far the largest table in the database - it's just not adding up. Why?

    Thanks.
    Last edited by vich; 08-07-07 at 05:10.

Posting Permissions

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