Logical Scan fragmentation increasing

  • My 4 main tables had the following fragmentation:

    Date25-3-2009

    Company 62,53%

    *_RM_Assessment 25,52%

    *_Assessment_Team_Memb 10,37%

    *_RM_Acceptance 89,29%

    My dba ran an Rebuild index process and statistics and we then had the following:

    Date7-4-2009

    Company 16.91%

    *_RM_Assessment 0.10%

    *_Assessment_Team_Memb 0.11%

    *_RM_Acceptance 0.27%

    The fragmentation increases everyday. The dba has scheduled and reindex process and statistics to run every sunday but every monday, the fragmentation % has not decreased and I am not sure why. presently, the values are as follows:

    Date29-4-2009

    Company 23,48%

    *_RM_Assessment 0.51%

    *_Assessment_Team_Memb 0.75%

    *_RM_Acceptance 3.04

    The only table that concerns me is the copany table but the overall picture is that the fragmentation goes up everyday and the reindex process does not bring it down again. Any advise would be appreciated

  • How much data is in the table, specifically how many pages?

    For very small tables, less than 10 pages, you might not see any change in fragmentation, even after a rebuild of the index and certainly not after a defrag. For tables less than 1000 pages, again, a defrag might not affect it and, according to Microsoft, you're unlikely to run into issues.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Hi grant

    thanks for the reply. the table I am concerned about is the company table and it has 16965 tables.

    the full scan info :

    - Pages Scanned................................: 16965

    - Extents Scanned..............................: 2133

    - Extent Switches..............................: 5584

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

    - Scan Density [Best Count:Actual Count].......: 37.98% [2121:5585]

    - Logical Scan Fragmentation ..................: 23.48%

    - Extent Scan Fragmentation ...................: 9.75%

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

    - Avg. Page Density (full).....................: 82.91%

    At the moment, the performance of scripts querying this table is still good but I am worried that we will see a degredation in speed as the fragmentation increases.

  • It could be something you have to monitor. Do you have an idea as to why you're getting so many page splits? Does the table have a clustered index that's less than optimal?

    You might just have a table that's a hot spot. Make sure that you keep statistics up to date on the table. Use a full scan if you have to. You can run the defrag more frequently, even once a day during slow periods, but I'd make sure that it checks the fragmentation first.

    Also, use sys.dm_db_index_physical_stats to get information on the fragmentation. It's easier to read and easier to work with than the old DBCC stuff.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Are you shrinking the database? Manual or autoshrink?

    I usually only worry about fragmentation if it's above about 25-30%.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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