locked
Index fragmentation RRS feed

  • Question

  • Hi All,

    I observed there are several scripts which can be ran against a database for finding the index fragmentation.

    But, none of them returns me the results in quick time. I am after a script which can return results in less than 5 minutes.

    For example:

    SELECT o.name,
      i
    .name,
    ips
    .index_level,
    ips
    .index_type_desc,
    ips
    .page_count,
    ips
    .record_count,
    ips
    .avg_fragmentation_in_percent,
    ips
    .avg_fragment_size_in_pages,
    ips
    .avg_page_space_used_in_percent
    FROM sys.objects o INNER JOIN sys.indexes i
    ON (o.object_id = i.object_id) INNER JOIN sys.dm_db_index_physical_stats(db_id(), DEFAULT, DEFAULT, DEFAULT, 'DETAILED') ips
    ON (
    i
    .object_id = ips.object_id AND
    i
    .index_id = ips.index_id
    )
    WHERE o.is_ms_shipped = 0 AND
    ips
    .page_count >= 1000 AND
    ips
    .avg_fragmentation_in_percent >= 60
    ORDER BY
    o
    .name ASC,
    i
    .index_id ASC,
    ips
    .index_level ASC;

    Any help?

    Regards,

    kccrga

    Wednesday, September 25, 2013 4:02 AM

Answers

  • I did tried with LIMIED and SAMPLED. But it is taking lot of time of execute.
    The time taking to get those info with LIMITED mode option is the minimum you would get. The reason for high execution time is because you are looking for entire table ingo for the database. If you have a specific table, then you can filter for the object which would be much lesser than entire objects.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, September 26, 2013 6:11 AM

All replies

  • You may try with Mode value as LIMITED or SAMPLED instead of 'DETAILED'. This would reduce the performance hit by DETAILED option. But, it will return only the logical fragmentation and page count by reading the minimum number of pages.

    I used SAMPLED in the below query, you can even use LIMITED and check. Hope this would help you...

    SELECT o.name,
      i.name,
    ips.index_level,
    ips.index_type_desc,
    ips.page_count,
    ips.record_count,
    ips.avg_fragmentation_in_percent,
    ips.avg_fragment_size_in_pages,
    ips.avg_page_space_used_in_percent
    FROM sys.objects o 
    INNER JOIN sys.indexes i ON (o.object_id = i.object_id) 
    INNER JOIN sys.dm_db_index_physical_stats(db_id(), DEFAULT, DEFAULT, DEFAULT, 'SAMPLED') ips
    ON (
    i.object_id = ips.object_id AND
    i.index_id = ips.index_id
    )
    WHERE o.is_ms_shipped = 0 AND
    ips.page_count >= 1000 AND
    ips.avg_fragmentation_in_percent >= 60
    ORDER BY
    o.name ASC,
    i.index_id ASC,
    ips.index_level ASC;



    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, September 25, 2013 4:32 AM
  • Hi,

    Even the above the script is very slow with SAMPLED keyword. Is there any other way to calculate in much faster way?

    Regards,

    kccrga

    Wednesday, September 25, 2013 5:14 AM
  • Hallo kccrga,

    Latheesh has pointed to another valid option: SAMPLED / LIMITED.

    http://technet.microsoft.com/en-us/library/ms188917.aspx

    You need to be aware of the three different options you can use for the scanning of data:

    DETAILED

    - scans ALL pages (LEAF and B-TREE / IAM / PFS)

    LIMITED (is the default!)

    - scans only B-TREE / IAM / PFS

    SAMPLED

    - scans only each 10.000th page of the LEAF and B-TREE / IAM / PFS


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)

    Wednesday, September 25, 2013 5:28 AM
  • Hi,

    Even the above the script is very slow with SAMPLED keyword. Is there any other way to calculate in much faster way?

    Regards,

    kccrga

    What do you mean by SLOW? There is no reduction in total time,I wonder?

    Ok, Did you try with LIMITED? 


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, September 25, 2013 5:39 AM
  • Total time to execute the query and get the results.
    Thursday, September 26, 2013 12:04 AM
  • Total time to execute the query and get the results.

    But you dint answer the question.

    Now, to be clear, SAMPLED means if you have less than 10000 pages, then it acts like DETAILED mode. if you have more than 10000 pages, then it will use Sampling of 1%. I assume, may be, your number of pages are less than 10000 Pages probabaly. But its a wild guess with very limited information.

    You may try with LIMITED option, where you will look at only the fragmented leaf instead of All pages which would be helpful to reduce your total execution time. Please try and let me know.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, September 26, 2013 4:52 AM
  • I did tried with LIMIED and SAMPLED. But it is taking lot of time of execute.
    Thursday, September 26, 2013 5:54 AM
  • I did tried with LIMIED and SAMPLED. But it is taking lot of time of execute.
    The time taking to get those info with LIMITED mode option is the minimum you would get. The reason for high execution time is because you are looking for entire table ingo for the database. If you have a specific table, then you can filter for the object which would be much lesser than entire objects.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, September 26, 2013 6:11 AM