SQL Server Performance

FRAGMENTATION

Discussion in 'Performance Tuning for DBAs' started by Raulie, Dec 24, 2003.

  1. Raulie New Member

    I have a databases where I am performing some house cleanup. I ran DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES on a database 258 out of 604 indexes reported scan density below 70. I ran a job DBCC REINDEX and sp_update stats and the numbers didnt change much. What are exceptable numbers for Scan Density? And how do I go about raising these fragmentation levels? Do I need to create drop existing all CLUSTERED INDEXES for it to work?
  2. Luis Martin Moderator

    When you run DBCC SHOWCONTIG first index ID 0 is table itself.
    There is no way, execpt recreate table and export/import data.
    Look ID different than 0, those can defragement using DBCC RBINDEX.
    Also search in this Forum for similars topics about defragmentation.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  3. bradmcgehee New Member

    By definition, scan density if the ratio of Extents Scanned to Extents Switches. For example, if the Extents Scanned is 2,374 and the Extent Switches is 9,239, then the scan density is 25.7%.

    To make sense out of this, we need to know what Extents Scanned and Extent Switches mean.

    Extents Scanned is the number of extents in a table or index.

    Extent Switches is the number of times that the DBCC commands moved from one extent to another extent while transversing all the pages of the table or index. In other words, this is the number of times a page in the scan was on a different extent than the previous page in the scan.

    Also, Scan Density is only a valuable indicator of density if the table or index does not span multiple files. If multiple file are involved, then Scan Density is not a reliable piece of data and should be discarded.

    To resolve fragmentation issues, you can either rebuild your indexes, or run DBCC INDEXDEFRAG.

    So if you have done this already, and it appears you have, and you still have a low Scan Density value, then it may be possible that your tables or indexes span one more than one file.


    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  4. Raulie New Member

    Thanks for the response Luis and Brad. All indexes and files are in the same file. I gues my question is which method is proved to produce better results DBCC DBREINDEX, DBCC INDEXDEFRAG OR REBuilding the indexes entirely?

  5. Luis Martin Moderator

    There is a lot of documentation and discussion, even in this forum, about DBREINDEX and DBCC INDEXDEFRAG. There is nothing better like rebuilding (delete and create) entirely.
    The question is: is neccesary?.
    If no way to reduce fragmentation, go ahead.
    But if were you delete and create is last chance.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  6. thomas New Member

    What is the fill factor of the indexes on these tables? A specified low fillfactor will affect scan density.

    Tom Pullen
    DBA, Oxfam GB
  7. Raulie New Member

    All fill factors are set to default (0). I am still analyzing which tables are static and which are produce a lot of activity Insert, update, delete, so that I can adjust accordingly. I dont think that should of mattered much since I am doing all this on a test server where there is not activity to cause additional fragment.
  8. Luis Martin Moderator

    How many index per table do you have?.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  9. satya Moderator

    Is it a dedicated SQL server or OS resources are shared by other applications?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  10. thomas New Member

    If the tables have very small numbers of rows, they are likely to be on shared extents, which means they will have low scan densities. Typical values such as 50, 25 and so on will be seen if that is the case.

    Tom Pullen
    DBA, Oxfam GB
  11. Raulie New Member

    Sorry for not getting back sooner. Yeah most of the tables that had a low scan density were smaller tables. It is a dedicated SQL server, but satya can you please explain how that would affect scan density results? The tables are well indexed.
  12. Luis Martin Moderator

    Small tables has severals indexs or only one cluster index?

    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  13. Raulie New Member

    Yes the small tables also have indexes.
  14. Luis Martin Moderator

    Did you try using DBREINDEX insteaD REINDEX?, some times I found differences.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  15. satya Moderator

    How about DBREINDEX and INDEXDEFRAG, any gain in performance?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  16. Twan New Member

    Hi ya,

    DBCC DBREINDEX is the same as dropping and recreating all indexes. This won't alter the data in the table unless there is a clustered index on the table.

    DBCC INDEXDEFRAG is a lighter weight method. It won't tidy up the index as well as DBREINDEX

    Cheers
    Twan
  17. Raulie New Member

    Sorry for the typo, but I was administering DBCC "DBREINDEX" not "REINDEX".
  18. Luis Martin Moderator

    Could you post DBCC SHOWCONTIG after RBREINDEX of any of those tables?


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  19. Raulie New Member


    Here are the results from one of the tables after running dbbc dbreindex. This database has about 100 tables so i just showed one example

    DBCC SHOWCONTIG scanning 'PM30200' table...
    Table: 'PM30200' (864722133); index ID: 0, database ID: 10
    TABLE level scan performed.
    - Pages Scanned................................: 471
    - Extents Scanned..............................: 70
    - Extent Switches..............................: 69
    - Avg. Pages per Extent........................: 6.7
    - Scan Density [Best Count:Actual Count].......: 84.29% [59:70]
    - Extent Scan Fragmentation ...................: 94.29%
    - Avg. Bytes Free per Page.....................: 1602.4
    - Avg. Page Density (full).....................: 80.20%
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.


    DBCC SHOWCONTIG scanning 'PM30200' table...
    Table: 'PM30200' (864722133); index ID: 2, database ID: 10
    LEAF level scan performed.
    - Pages Scanned................................: 20
    - Extents Scanned..............................: 5
    - Extent Switches..............................: 6
    - Avg. Pages per Extent........................: 4.0
    - Scan Density [Best Count:Actual Count].......: 42.86% [3:7]
    - Logical Scan Fragmentation ..................: 10.00%
    - Extent Scan Fragmentation ...................: 40.00%
    - Avg. Bytes Free per Page.....................: 944.1
    - Avg. Page Density (full).....................: 88.34%
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    DBCC SHOWCONTIG scanning 'PM30200' table...
    Table: 'PM30200' (864722133); index ID: 3, database ID: 10
    LEAF level scan performed.
    - Pages Scanned................................: 18
    - Extents Scanned..............................: 5
    - Extent Switches..............................: 6
    - Avg. Pages per Extent........................: 3.6
    - Scan Density [Best Count:Actual Count].......: 42.86% [3:7]
    - Logical Scan Fragmentation ..................: 11.11%
    - Extent Scan Fragmentation ...................: 60.00%
    - Avg. Bytes Free per Page.....................: 1084.3
    - Avg. Page Density (full).....................: 86.60%
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  20. Luis Martin Moderator

    If that was after RBINDEX, well this is infrequent.

    Read the following to find out what I mean.

    http://www.sql-server-performance.com/rd_index_fragmentation.asp

    HTH

    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell

Share This Page