Fragmentation accuracy? 98% fragmented?!!

  • Hi guys,

    Today/2am this morning I received my first call out (yay!) for a terrible server I hadn't heard of before. Besides the main poor points (backups on the same drive as datafiles, no compression & a filegroup dedicated to logging user activity somehow taking up 120GB over the past 1 month when the total database is 200gb including the 120gb logging; so 80gb without) I also looked at the filegroups dedicated to indexes. What I found was surprising & leads me to believe it's inaccurate, the following query:

    SELECT ps.database_id, ps.OBJECT_ID,ps.index_id, b.name, ps.avg_fragmentation_in_percent

    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps

    INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID

    AND ps.index_id = b.index_id

    WHERE ps.database_id = DB_ID()

    ORDER BY ps.OBJECT_ID

    Lead to values like (note: I only copied the index names & fragmentation columns):

    I_SEM_AGENT_USN 90.5616676317313

    I_SEM_AGENT_COMP 98.3505154639175

    I_SEM_AGENT_GRP 86.5384615384615

    I_SEM_AGENT_RT 76.453488372093

    I_SEM_AGENT_ID_PLUS 88.4057971014493

    I_SEM_AGENT_PATTERN 97.7766013763896

    I_SEM_AGENT_AGENT_VERSION 85.8789625360231

    I_SEM_AGENT_STATUS 99.2708333333333

    I_SEM_AGENT_AGENT_ID 98.9637305699482

    Judging by this it would lead me to believe that only 1-2% of each page is being used? Which seems a little strange to me...does anybody notice an error in the query that may lead to the values being wrong? Or this column isn't supposed to be trusted? Or I need to be doing some rebuilds next weekend? 😛

    Dird


    Dird

  • Not necessarily. I would add the following columns to your query: ps.page_count, ps.avg_fragment_size_in_pages.

  • Lynn Pettis (5/26/2013)


    Not necessarily. I would add the following columns to your query: ps.page_count, ps.avg_fragment_size_in_pages.

    Here's some results with the extra columns...I take it you asked for these to make sure the indexes didnt just have 1 page?

    nameavg_fragmentation_in_percentpage_countavg_fragment_size_in_pages

    I_AGENT_SYSTEM_LOG_1_COMPUTER_ID_PLUS75.12194742883539007981.31537922373762

    I_SERVER_CLIENT_LOG_1_CLIENT_ID83.18600332432183591711.18837927976813

    I_SERVER_CLIENT_LOG_1_LOG_IDX99.22318898576143398511

    I_AGENT_TRAFFIC_LOG_1_LOG_IDX99.21425143953941833921

    I_AGENT_TRAFFIC_LOG_1_TIME_PLUS56.55940662310091813351.71507613733094

    NULL40.516415049125313235617.1467806710714

    NULL99.8127340823971152908.00958732805335

    I_AGENT_BEHAVIOR_LOG_1_LOG_IDX99.24384638326991139981

    I_SERVER_CLIENT_LOG_1_TIME30.37259465894351125623.18376467260642

    I_SERVER_CLIENT_LOG_1_ID99.21343748588971107351

    Do you know what the NULL index name represents? Table fragmentation?

    Dird


    Dird

  • The table is a heap (no clustered index). The index_id will be a 0 (zero). You may want to add this column to the query also: ps.forwarded_record_count.

  • Dird (5/26/2013)


    Judging by this it would lead me to believe that only 1-2% of each page is being used?

    That's not what fragmentation means. Nothing wrong with the query or the accuracy of the column, just the interpretation.

    Fragmentation is a measure of what percentage of the pages in the index are 'out of order'. Out of order being where the logical order of the index (as defined by the index key values) and the physical order of the index within the data file are not the same. So a high logical fragmentation tells you nothing about what percentage of the pages are full of data, it tells you what percentage of the pages in the index have a physical order that doesn't match the logical order.

    With a fragmentation that high, I would suspect there's some database shrink operations?

    To see on average how full the pages are, you need to look at the avg_page_space_used column.

    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
  • Dup

    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
  • To use avg_page_space_used_in_percent you will also have to change your query to run in DETAILED mode, like this:

    ...

    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') AS ps

    ...

  • Oh OK, so it's just about the amount of page splits? Added the specified columns (assume the one is what Gail was requesting):

    index_idnameavg_fragmentation_in_percentpage_countavg_fragment_size_in_pagesforwarded_record_countavg_page_space_used_in_percent

    13I_AGENT_SYSTEM_LOG_1_COMPUTER_ID_PLUS75.12559969274429008781.31531934029481NULLNULL

    13I_AGENT_SYSTEM_LOG_2_COMPUTER_ID_PLUS0.0171315663.7942570891851NULLNULL

    10I_SERVER_CLIENT_LOG_1_CLIENT_ID83.18965637221473592551.18832693834348NULLNULL

    5I_SERVER_CLIENT_LOG_1_LOG_IDX99.22318898576143398511NULLNULL

    0NULL99.15917657291973310178.06532332732323NULLNULL

    0NULL22.897467619360231660434.3313814790718NULLNULL

    7I_AGENT_TRAFFIC_LOG_1_LOG_IDX99.21425143953941833921NULLNULL

    6I_AGENT_BEHAVIOR_LOG_1_LOG_IDX99.24384638326991139981NULLNULL

    Dird


    Dird

  • Dird (5/26/2013)


    Oh OK, so it's just about the amount of page splits?

    Not necessarily. Other things than page splits can cause fragmentation, like database/file shrink operations.

    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
  • Autoshrink is disabled in 10.5. There wouldn't be any manual shrinking because there's no real maintenance for our SQL Servers =//

    The 'DETAILED' option is so slow >_<

    Dird


    Dird

  • Dird (5/26/2013)


    Autoshrink is disabled in 10.5. There wouldn't be any manual shrinking because there's no real maintenance for our SQL Servers =//

    The 'DETAILED' option is so slow >_<

    Dird

    You won't get the avg_page_space_used_in_percent unless you run it using 'DETAILED'. In 'LIMITED" (or NULL in your case) this value is always null.

  • Lynn Pettis (5/26/2013)


    You won't get the avg_page_space_used_in_percent unless you run it using 'DETAILED'. In 'LIMITED" (or NULL in your case) this value is always null.

    Yep, 17 minutes & counting for the result set -_


    Dird

  • Dird (5/26/2013)


    Autoshrink is disabled in 10.5.

    ???

    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
  • The AutoShrink setting for the database (properties -> options -> AutoShrink). It's not what you were talking about?

    Here's the extra column in the query:

    index_idnameavg_fragmentation_in_percentpage_countavg_fragment_size_in_pagesforwarded_record_countavg_page_space_used_in_percent

    10I_SERVER_CLIENT_LOG_1_CLIENT_ID83.19381286074483593261.1882827200545NULL64.1397949098097

    5I_SERVER_CLIENT_LOG_1_LOG_IDX99.22318898576143398511NULL67.8164319248826

    7I_AGENT_TRAFFIC_LOG_1_LOG_IDX99.21425143953941833921NULL66.8855942673585

    4I_AGENT_TRAFFIC_LOG_1_TIME_PLUS56.57114188682571813841.71474489265355NULL74.9793427230047

    4I_AGENT_TRAFFIC_LOG_2_TIME_PLUS01454481212.06666666667NULL99.5304917222634

    0NULL40.516415049125313235617.146780671071403.01700024709661

    7I_AGENT_TRAFFIC_LOG_2_LOG_IDX01305731450.81111111111NULL99.9996911292315

    0NULL99.81287684524221153738.00923290524124095.6287867556214

    6I_AGENT_BEHAVIOR_LOG_1_LOG_IDX99.24384638326991139981NULL67.7993946132938


    Dird

  • Looks to me like you may have some big records in some of those tables with an avg_fragment_size_in_pages = 1. I'd be interested in the following as well:

    min_record_size_in_bytes

    max_record_size_in_bytes

    avg_record_size_in_bytes

    These also need to be run using 'DETAILED'.

Viewing 15 posts - 1 through 15 (of 31 total)

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