locked
index fragmentation versus no index RRS feed

  • Question

  • Hi,

    So, as we know depending on the size of the table, defrag is not necessarily the best :)

    Now, I was wondering between fragmented indexes and no index at all. Typically, I have the following:

    page_count      name avg_fragmentation_in_percent
    433    IX_msg_queue_process_id_comm_to 97.45958
    202    IX_msg_id_process_id_comm_to 97.0297
    35125    IX_log_engine_process_id 76.88826
    112166     IX_msg_type_status_style_version 64.10677

    So the first 2, they are highly fragmented but the tables are small

    IX_msg_queue_process_id_comm_to, IX_msg_id_process_id_comm_to are on a table containing 5k rows for about 15 MB of data. IX_msg_id_process_id_comm_to

    IX_log_engine_process_id is on a table containing 4.4 million rows for about 4 GB of data.

    IX_msg_type_status_style_version is on a table containing 16 million rows for about 162 GB of data

    Index operational stats:

    OBJECT NAME INDEX NAME LEAF_INSERT_COUNT LEAF_UPDATE_COUNT LEAF_DELETE_COUNT
    msg IX_msg_type_status_style_version 356485 0 0
    msg_queue IX_msg_id_process_id_comm_to 58898 0 0
    msg_queue IX_msg_queue_process_id_comm_to 59102 166968 0
    log_engine IX_log_engine_process_id 83756 0 0

    Index usage stats:

    OBJECT NAME INDEX NAME USER_SEEKS USER_SCANS USER_LOOKUPS USER_UPDATES
    msg IX_msg_type_status_style_version 18709 0 0 425600
    log_engine IX_log_engine_process_id 0 0 0 52386
    msg_queue IX_msg_queue_process_id_comm_to 66927 1 0 359342
    msg_queue IX_msg_id_process_id_comm_to 0 0 0 117914

    To me, I would have better performances if I would drop these 3:

    IX_msg_type_status_style_version, highly fragmented, somewhat used but has been pretty much deprecated

    IX_log_engine_process_id, rarely used and usually only when we do manually query on the engine log table

    IX_msg_id_process_id_comm_to, table is super small and never used so I would call this one a definite yes

    But in a more general aspect, is there a rule of thumbs as to what point an index becomes pretty much worthless. Especially when they are rebuilt nightly and get 90%+ fragmentation levels after a 3 hours of normal loads.

    Thanks for any insight / good papers links on this.

    Note: Fragmentation is due to how the application operates at the moment and it won't be fixed for at least a year or 2.

    Monday, January 11, 2016 4:58 PM

Answers

All replies

  • Fragmentation matters for scans, but there is only one single scan on these three tables. The rest is seeks.

    Of course, the unused indexes could be dropped, but it seems that one of the was used for a particular query. Even if it's manual, it may be essential that is there.

    Monday, January 11, 2016 10:55 PM
  • Two indexes appear wortheless to me

    1. msg_queue  IX_msg_id_process_id Insert =83756 while seek=0

    2. log_engine   IX_log_engine_process_id    insert=83756  Seek =0

    If user seek,scan,lookups are Very mich less than user updates it points to fact that index is more being inserted than used. Such index is of less use its more a overhead.

    In your case User seek =0 which means index is not used at all

    

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP

    Tuesday, January 12, 2016 7:51 AM
  • Thanks for the responses and explanations.

    I also found a pretty good article for people interested: https://www.simple-talk.com/sql/performance/tune-your-indexing-strategy-with-sql-server-dmvs/

    And for a good laugh, I found some PK that have never been seeked or scanned as well as non clustered index that have the same definition as PK :)


    Tuesday, January 12, 2016 12:49 PM
  • Hi O.Ragain,

    Glad to hear that the issue is resolved. Thanks for your sharing, you can mark your reply as answer, other community members could benefit from your solution.

    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support


    Tuesday, January 19, 2016 9:53 AM