locked
Fragmentation on tables RRS feed

  • Question

  • Hi I have seen on my database fragmentation on the table on the following SQL,  According to the image, Tables on high fragmentation which do not have clustered indexes  . Do any one knows reduce the fragmentation on the table unless create a new table and import then create index.(Those are expensive task and required downtime.) 

    SELECT dbschemas.[name] as 'Schema',
    dbtables.[name] as 'Table',
    dbindexes.[name] as 'Index',
    Fill_Factor,
    indexstats.avg_fragmentation_in_percent,
    indexstats.page_count
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
    INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
    INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
    INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
    AND indexstats.index_id = dbindexes.index_id
    ORDER BY indexstats.avg_fragmentation_in_percent desc

     

    • Edited by ashwan Thursday, October 24, 2019 11:19 PM
    Thursday, October 24, 2019 11:17 PM

Answers

  • You can try below,

    1) Alter table TableName rebuild

    2) Create Clustered index and drop it.

    Please note that, Alter Table.. rebuild will rebuild all your non-clustered indexes as heap
     record location will change.

    Creating and dropping cluster index will rebuild your non-clustered indexes for each operation.

    3) Create a clustered index and leave it there. I would always prefer this if possible.



    Regards;
    Vivek Janakiraman
    ----------------------------
    My Blog
    My Linkedin Page
    ----------------------------





    Thursday, October 24, 2019 11:43 PM

All replies

  • You can try below,

    1) Alter table TableName rebuild

    2) Create Clustered index and drop it.

    Please note that, Alter Table.. rebuild will rebuild all your non-clustered indexes as heap
     record location will change.

    Creating and dropping cluster index will rebuild your non-clustered indexes for each operation.

    3) Create a clustered index and leave it there. I would always prefer this if possible.



    Regards;
    Vivek Janakiraman
    ----------------------------
    My Blog
    My Linkedin Page
    ----------------------------





    Thursday, October 24, 2019 11:43 PM
  • Hello Friend,

    You can use script as informed by your previous friend or even maintenance plan to Rebuild Index.

    I recommend Script because it only really does what rebuild is needed.

    But if you find it complicated you can do it for the maintenance plan if your Database is in a Standard version or higher.

    If this answer has helped you, mark it helpful so that someone else with a question or similar problem can find an answer or help more easily. Jefferson Clyton Pereira da Silva - [MCSA | MCP | MCTS | MTA | Database Analyst - Sql Server and Oracle]


    Friday, October 25, 2019 2:33 AM
  • Hi Vivek thank you for the reply.  Some of them have spatial indexes  which created [geometry] data types. as follows . so are still recommended as well?

    regards

    Friday, October 25, 2019 3:40 AM
  • Maint plans has no functionality to get rid of fragmentation in heaps.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Friday, October 25, 2019 9:15 AM