As far as I can see with research the only thing that fragments is indexes no other objects in SQL can fragment.

I have to keep track of fragmentation within multiple databases, would it be correct to understand that I only check index fragmentation? Can any other object fragment? Does it need to be checked?

  • 1
    Not technically a case of fragmentation, statistics can become stale and need to be rebuilt. Bad statistics cause horrible performance degradation. Oct 24, 2019 at 20:04
  • @PaulChernoch, thanks this is definitely something I will take into account, was not looking at statistics Oct 25, 2019 at 5:37

4 Answers 4


While this probably fits better on dba stackexchange, it's kind of a grey area/borderline.

The short answer is that indexes get fragmented and need to be periodically checked and reorganized or rebuilt based on how much they have fragmented.

Going at it we find the fragmentation levels primarily through querying:

SELECT * FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, 'LIMITED')

However this query also returns the index_types. Where we see the expected clustered and non clustered indexes. But also the 'HEAP' Index type, which, isn't actually an index (the object_ID also doesn't have any kind of name in the sys.indexes view).

These are for tables with no indexes at all, and thus also no primary keys (which automatically creates an index). These too can get fragmented. Because they represent simply how spread out the data is across the disk.

You should also check and 'amend' these, either simply by actually giving them say a primary key. Or, if it's desired that they have no indexes/PK. Simply by rebuilding the table. This reorganizes the data within the table as well.

A quick example of code:

ALTER INDEX [PK_Foo] ON [dbo].[Foo] REORGANIZE --Generally used on low fragmentation count
ALTER INDEX [PK_Foo] ON [dbo].[Foo] REBUILD --Used for high fragmentation count
ALTER TABLE [dbo].[Bar] REBUILD --Used for HEAP indexes with medium-high fragmentation

There are many options that these statements can have, like padding factor. One thing to note is that REORGANIZE allows the table to stay online and other than using resources don't block users. The other options do lock users from accessing the table during the process.

  • I see someone migrated it to the correct stack :) Thanks for the answer, cleared up quite a bit Oct 25, 2019 at 5:31

It depends on what you mean by "other objects".

Tables with clustered indexes: the clustered index is the table data so that can fragment as with any other index

Tables without a clustered index (heaps): the heap structure can also fragment, and is potentially prone to other issues too depending on use patterns (such as space/efficiency lost over time+use due to forward records, see https://www.brentozar.com/archive/2016/07/fix-forwarded-records/)

Stored procedures, views, etc: the definition text for these is stored as NVARCHAR(MAX) values so they are off-page and if longer than ~8kb (~4K characters, as they are stored two-bytes-per-char) they can potentially fragment, but this is not going to be significant.

  • Sorry, by other objects I meant, things that won't show up within sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, NULL) think I should have made that more clear. Thanks for the explanation on the types made it easier to understand Oct 25, 2019 at 5:34

I am going to assume you are a fairly new DBA. In addition to the two existing answers, there maybe a couple of related things you should look at.

Data Files

For larger database performance the number of datafiles is appropriate for the number of processors you have. I go into some detail about it in my answer at Split .mdf datafile methods

Log Files

You should only have one log file, but it is subdivided into VLF files, as a DBA you have some control over how this is handled. There is a fairly deep look at VLFs in the post Why are virtual log files not always allocated in order?

  • Yea, databases aren't one of my strongest points, my main concern is the indexes and fragmentation of them, not so much the performance, thanks for the tips was a good read Oct 25, 2019 at 5:36
  • @RuanneBornman If you are not worried about performance why do you care about index fragmentation? Oct 25, 2019 at 13:29

Heaps can be "fragmented" in two ways. All depends on how you define "fragmented":

Empty space on ages after deleting rows. And even pages not being deallocated even when empty. I had a case where a SELECT on a table with just 3000 rows took several minutes!

Forwarded records (aka forwarding pointers). I have some info on these here: https://karaszi.com/rebuild-all-fragmented-heaps

Note that you won't see this of you use sys.dm_db_index_physical_stats with NULL for the last parameter, since NULL means LIMITED. You need either DETAILED or SAMPLED.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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