After large delete on table - Table Fragmentation ? RRS feed

  • Question

  • Hello, App team is planning on deleting millions or rows in a large table.

    Table has Foreign Keys/Constraints/Indexes but no Nonclustered index.

    After they disable Foreign Keys/Constraints is there such thing as Table Fragmentation?  (Aside ofIndex Fragmentation.)

    If yes how should Table Fragmentation be addressed?

    Also would Index rebuild or reorg should be done as well after large deletes?

    Thanks in advance.

    Wednesday, November 27, 2019 5:37 PM

All replies

  • lets suppose your table has a cluster key based on an increasing key. Then in principle, with inserts only, the leaf level of the table might be somewhat contiguous.

    Suppose you have 2 tables in this manner, and inserts go to both tables

    then each table periodically gets new page/extent allocations, table A gets an extent, then B gets the next extent, so fragments is part of database life.

    If you defragment the table, and actually achieve defragmentation, then delete non-contiguous blocks, then you could have fragmentation,

    foreign keys and constraints really don't enter this picture.

    Unless you do table scans to hard disk storage, fragmentation is probably not an issue.

    A lesser issue is your table packing efficiency, in which a period index rebuild helps.

    if you must have near perfect contiguous allocation of extents for a particular table, there is a way to do it, but its not really necessary for most production databases


    • Proposed as answer by SethWH Wednesday, November 27, 2019 6:00 PM
    Wednesday, November 27, 2019 5:56 PM
  • Thanks for your reply jchang.

    Sorry am bit confused.

    To answer my question...does Table level Fragmentation exist within SQL Server? (Not Index Fragmentation.)

    If yes how to address it?

    Thursday, November 28, 2019 12:16 AM
  • if your table has a clustered index, then that index is the table. either type of table could get fragmented with or without deletes. It is a matter of when each extent (64K or 8 contiguous 8KB pages) is allocated, and whether extents are allocated for other purposes in between.

    if your table has many fragments less than 64KB, you could rebuild the index, and the clustered index is the table.

    its not a big deal to have fragments larger than 64KB today, assuming you are on solid state storage. it mattered more in hard disk days and you made an effort to support high bandwidth scans. Most people did not.

    if your table does not have a clustered index, and you have a fragmented heap, you could build a clustered index, then drop it. Think hard about why you want to do this. Also, if you expect true contiguous allocation, that is more difficult, and requires multiple file groups. see my other responses


    Thursday, November 28, 2019 2:32 AM
  • Hi techresearch7777777,

    Please refer to Detect Fragmentation and Eliminate Fragmentation and Fragmentation, Reorganization and Rebuilds Impact SQL Server Table Scan Speeds which might help.

    Best Regards,

    Amelia Gu

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Thursday, November 28, 2019 7:30 AM
  • So after large delete process should we expect better querying performance or do we need to reclaim space like doing something else like compress table, etc... ?
    Tuesday, December 3, 2019 1:03 AM