Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2003
    Posts
    12

    Unanswered: Query Performance

    Hi Gurus,

    I have run DBCC SHOWCONTIG statement against the table X as shown below:

    DBCC SHOWCONTIG (X)
    GO

    and received the following result:

    DBCC SHOWCONTIG scanning 'X' table...
    Table: 'X' (885578193); index ID: 1, database ID: 10
    TABLE level scan performed.
    - Pages Scanned................................: 1400
    - Extents Scanned..............................: 300
    - Extent Switches..............................: 300
    - Avg. Pages per Extent........................: 4.7
    - Scan Density [Best Count:Actual Count].......: 50.00% [200:400]
    - Logical Scan Fragmentation ..................: 21.43%
    - Extent Scan Fragmentation ...................: 33.33%
    - Avg. Bytes Free per Page.....................: 2107.1
    - Avg. Page Density (full).....................: 73.97%

    Which one of the following is more recommended way to increase query performance against the table X?

    A. Run DBCC DBREINDEX statement with high fill factor value to rebuild the clustered index.
    B. Run DBCC DBREINDEX statement with low fill factor value to rebuild the clustered index.
    C. Drop and re-create the clustered index by using DROP INDEX and CREATE INDEX statements with low fill factor value.
    D. Drop and re-create the clustered index by using DROP INDEX and CREATE INDEX statements with high fill factor value.
    E. Set the truncate log on checkpoint option for the database which contain the Employee table.

    Thank you

  2. #2
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    If they are asking for high query performance, then you don't want a low fill factor, you want as much data per data page. That being said, I would choose (A). (D) would be OK too but it is one more process as you would drop the clustered, recreate it, then any non-clustereds would be rebuilt, that's three steps instead of two.

    HTH
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  3. #3
    Join Date
    Feb 2003
    Posts
    12
    HTH,

    Thanks very much.

Posting Permissions

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