0

Context

  • I have a table in SQL Server with millions of rows and a primary key (by default, clustered key).
  • I got the requirement to do housekeeping for that table, so I need to delete rows in bulk from time to time
  • SQL Server stores data in index rows and data rows (data pages)
  • Internal fragmentation happens whenever space is left on data pages
  • In my case, most table data will be going to be deleted in sequence, and some rare cases deleted in between

Questions

  • How big is the chance that internal fragmentation will happen?
  • How I can avoid internal fragmentation?
Improve this question
2
  • 3
    I suggest you simply perform regular index maintenance. Given the rare cases when it will occur, the reduced buffer efficiency will be imperceptible even with out it.
    – Dan Guzman
    Aug 6 at 9:22
  • 1
    Basically Dan is saying you don't need to do anything about index fragmentation as it makes an immeasurable difference. But if you want to spend server resources prettying things up, you can run index maintenance.
    – J.D.
    Aug 6 at 13:22

1 Answer 1

Reset to default
0

We are lacking information in order to give more precise recommendations.

First, a clarification: You wrote:

SQL Server stores data in index rows and data rows (data pages)

I would rather say that you have index pages and heap pages. If the table has a clustered index, then the actual "data" is the clustered index. If the table doesn't have a clustered index, then the "actual data" are your heap pages. And then of course you might have a number of non-clustered index. (Note that I ignore other index types for this reply, like columnstore, XML, geospatial and full-text.)

You say that the table has a clustered index. What other indexes do you have? What indexes are you worried for regarding internal fragmentation?

Also, we need to know the delete pattern regarding the WHERE clause compared to each of your indexes.

Say you have a clustered index on OrderDate and you delete all orders for 2018-05 (May 2018). Then you didn't introduce any (significant) internal fragmentation for the clustered index. But say that you also have a non-clustered index on CustomerID. We do now have internal fragmentation for that index. How much, is it relevant? We don't know since we would have to know even more details in order to answer that question. I.e., things like number of rows per index page, how many rows are affected by the delete, the spread of data for the index key, etc.

However, you already have the answer. Just use sys.dm_dn_index_physical_stats and you know the level of internal fragmentation. You can now determine, for each index, if you feel it is worth the trouble to reduce number of pages in the index by doing a rebuild.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.