locked
How to find out index fragmentation level for large db RRS feed

  • Question

  • hi folks: 

     I tried the following query on small db and all got results within a few seconds. however, this DB is giving me trouble by showing me the PAGEIOLATCH_SH wait type. DB itself has over 300G on SQL 2012 Ent.

    SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, indexstats.page_count,

    ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 
    indexstats.avg_fragmentation_in_percent 
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
    INNER JOIN sys.indexes ind  
    ON ind.object_id = indexstats.object_id 
    AND ind.index_id = indexstats.index_id 
    WHERE indexstats.avg_fragmentation_in_percent > 10 and indexstats.page_count >1000
    ORDER BY indexstats.avg_fragmentation_in_percent DESC

    Is there any other way to find out index fragmentation info for relatively large db. 

    Thanks

     HS


    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --


    • Edited by cat_ca Tuesday, February 12, 2019 4:42 AM typo
    Tuesday, February 12, 2019 4:42 AM

Answers

  • You could try using SAMPLED for the fifth parameter (mode). That means sample pages. You are already using LIMITED (it is the default), so whether you see improvements or not you'll see...

    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by cat_ca Tuesday, February 12, 2019 11:41 PM
    Tuesday, February 12, 2019 7:54 AM

All replies

  • SELECT db_name(database_id) AS DatabaseName,
    OBJECT_NAME(i.object_id) AS TableName 
    ,
    i.name AS TableIndexName 
    ,
    phystat.avg_fragmentation_in_percent

    FROM
    sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'DETAILED') phystat inner JOIN sys.indexes i 
    ON i.object_id = phystat.object_id 
    AND i.index_id = phystat.index_id WHERE phystat.avg_fragmentation_in_percent > 40 
    and page_count>=1000

    ---for specific table

    SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats

      DB_ID('testdb'),
      OBJECT_ID('dbo.T1'),
      1,
      NULL,
      NULL
    );


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, February 12, 2019 6:20 AM
    Answerer
  • PAGEIOLATCH_SH generally indicates a slower drive subsystem, but try alternative scripts given in the following links. They appear to use query the same DMV that you provided in your script, but try anyway and see if you get results quicker. It could take some time for a VLDB.

    https://logicalread.com/fix-sql-server-index-fragmentation-mc11/#.XGJqSvZFyUk

    https://www.mssqltips.com/sqlservertip/4331/sql-server-index-fragmentation-overview/


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Tuesday, February 12, 2019 6:44 AM
  • That makes things worse, not better. Cat_ca were using the default for te fifth parameters translating to LIMITED meaning traversing the level above the leaf level. You specified DETAILED meaning traversing the leaf level resulting in far more reads than the original query.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, February 12, 2019 7:53 AM
  • You could try using SAMPLED for the fifth parameter (mode). That means sample pages. You are already using LIMITED (it is the default), so whether you see improvements or not you'll see...

    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by cat_ca Tuesday, February 12, 2019 11:41 PM
    Tuesday, February 12, 2019 7:54 AM
  • I did not mention that it takes time, that right bit as you see I added script for single table to minimize reads on the db 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, February 12, 2019 8:17 AM
    Answerer
  • Yes, but if the whole point of the original post is that the command takes too long time, then I suggest that you at least add some text if you post a reply with a command that will take a great deal *longer* time.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, February 12, 2019 12:39 PM
  • thanks all for the inputs. I did use the SAMPLED and results came back  after 30 seconds vs never came back in the default mode. However, the BOL says SAMPLED used only 1% to give approximate result. 

    cheers


    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

    Tuesday, February 12, 2019 8:04 PM
  • thanks all for the inputs. I did use the SAMPLED and results came back  after 30 seconds vs never came back in the default mode. However, the BOL says SAMPLED used only 1% to give approximate result. 

    "SAMPLED" is good enough, if you have a huge table then a small piece is good enough for statistical purposes, you can trust it 99%. :)

    Josh

    Tuesday, February 12, 2019 9:04 PM