locked
Fragmentation in Sqlserver RRS feed

  • Question

  • Hi

     Can anyone tell me the query to findout the fragmentation in the database tables.

     and how to defragment.

    please provide me a detailed  explaination.

    Friday, June 27, 2014 1:29 AM

Answers

  • SELECT
        db_name(ps.database_id)AS'Database Name',
        object_name(ps.OBJECT_ID)AS'Database Object',
        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.avg_fragmentation_in_percent DESC
    GO

    Raju Rasagounder Sr MSSQL DBA

    • Marked as answer by Sreenivas DBA Friday, June 27, 2014 11:26 AM
    Friday, June 27, 2014 1:43 AM
  • SELECT b.name AS IndexName,

      a.avg_fragmentation_in_percent AS PercentFragment,

      a.fragment_count AS TotalFrags,

      a.avg_fragment_size_in_pages AS PagesPerFrag,

      a.page_count AS NumPages

    FROM sys.dm_db_index_physical_stats(DB_ID('MOSS_PRD_Content_CRM'),

      OBJECT_ID('Sales.StoreContact'), NULL, NULL , 'DETAILED') AS a

    JOIN sys.indexes AS b

    ON a.object_id = b.object_id

      AND a.index_id = b.index_id

    WHERE a.avg_fragmentation_in_percent > 0

    ORDER BY IndexName

    Raju Rasagounder Sr MSSQL DBA

    • Marked as answer by Sreenivas DBA Friday, June 27, 2014 11:26 AM
    Friday, June 27, 2014 1:47 AM
  • Hi

     Can anyone tell me the query to findout the fragmentation in the database tables.

     and how to defragment.

    please provide me a detailed  explaination.

    Fragmentation script can be taken from here

    http://gallery.technet.microsoft.com/scriptcenter/Check-SQL-Server-a-a5758043

    Defragmentation will require you to rebuild index, there are lot of options for you to rebuild so i would like you to read examples in below link and choose one that suits you

    http://msdn.microsoft.com/en-gb/library/ms188388.aspx


    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 TechNet Wiki Articles


    Friday, June 27, 2014 8:16 AM

All replies

  • SELECT
        db_name(ps.database_id)AS'Database Name',
        object_name(ps.OBJECT_ID)AS'Database Object',
        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.avg_fragmentation_in_percent DESC
    GO

    Raju Rasagounder Sr MSSQL DBA

    • Marked as answer by Sreenivas DBA Friday, June 27, 2014 11:26 AM
    Friday, June 27, 2014 1:43 AM
  • SELECT b.name AS IndexName,

      a.avg_fragmentation_in_percent AS PercentFragment,

      a.fragment_count AS TotalFrags,

      a.avg_fragment_size_in_pages AS PagesPerFrag,

      a.page_count AS NumPages

    FROM sys.dm_db_index_physical_stats(DB_ID('MOSS_PRD_Content_CRM'),

      OBJECT_ID('Sales.StoreContact'), NULL, NULL , 'DETAILED') AS a

    JOIN sys.indexes AS b

    ON a.object_id = b.object_id

      AND a.index_id = b.index_id

    WHERE a.avg_fragmentation_in_percent > 0

    ORDER BY IndexName

    Raju Rasagounder Sr MSSQL DBA

    • Marked as answer by Sreenivas DBA Friday, June 27, 2014 11:26 AM
    Friday, June 27, 2014 1:47 AM
  • Hi

     Can anyone tell me the query to findout the fragmentation in the database tables.

     and how to defragment.

    please provide me a detailed  explaination.

    Fragmentation script can be taken from here

    http://gallery.technet.microsoft.com/scriptcenter/Check-SQL-Server-a-a5758043

    Defragmentation will require you to rebuild index, there are lot of options for you to rebuild so i would like you to read examples in below link and choose one that suits you

    http://msdn.microsoft.com/en-gb/library/ms188388.aspx


    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 TechNet Wiki Articles


    Friday, June 27, 2014 8:16 AM
  • HI Sreenivas,

    I would recommend you to use the solution from http://ola.hallengren.com/

    His maintenance solution is an award winning one, used in many many huge organisations. It will automatically do all the work depending on which indexes are fragmented and which stats are outdated.

    Read this article it explains clearly as to what is fragmentation and how it effects you

    http://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Friday, June 27, 2014 9:01 AM