I discovered that the heap gets very fragmented in SQL Server in some of our solutions. Large tables, such as Items, Shared-, Versioned- and Unversioned-fields, Blobs, Descendants and Links tables, that easily occupies a few GB on disk, also suffered from great fragmentation. More than 90% fragmentation was common.

From what I’ve found, the only way to fix SQL Server Heap fragmentation (the heap is where all the table data is stored), is to have a clustered index on each table.

However, I noticed that no tables in the Sitecore databases have any clustered indexes. All indexes are non-clustered in the common master/core/web databases. Sitecore used to have clustered indexes back in 5.2, but over the course of multiple Sitecore versions, the database schema has changed to non-clustered indexes.

A clustered index means that the table rows as stored in the index order physically on disk. That’s also why there can be only one clustered index per table. With a non-clustered index, there is a second list that has pointers to the physical rows. It’s generally faster to read from a clustered index, but it may be slower to write to it as there may be a need to rearrange the table data.

You can find the fragmentation for all indexes and the table heap with the query below. Returning rows where the Index column is NULL, shows the fragmentation of the heap, i.e. the table data in this case.

SELECT dbschemas.[name] as 'Schema', 
  dbtables.[name] as 'Table', 
  dbindexes.[name] as 'Index',
  indexstats.alloc_unit_type_desc,
  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
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

Rebuild regular indexes

Note: The regular indexes will get fragmented too. That should always be addressed with for example regular scheduled maintenance jobs in SQL Server. Consider having a 80%-90% fill factor to reduce further fragmentation and ensure the indexes are in good shape. You can also easily rebuild all the indexes on demand with the following SQL statement.

DECLARE @TableName varchar(255) 
DECLARE TableCursor CURSOR FOR 
SELECT table_name FROM information_schema.tables WHERE table_type = 'base table' 
OPEN TableCursor 
	FETCH NEXT FROM TableCursor INTO @TableName 
	WHILE @@FETCH_STATUS = 0 
	BEGIN 
		DBCC DBREINDEX(@TableName,' ',80) 
		FETCH NEXT FROM TableCursor INTO @TableName 
	END 
CLOSE TableCursor 
DEALLOCATE TableCursor

Rebuild/defragment the table data

Unfortunately there is no way to defragment the heap without a clustered index. The screenshot below is from one of my master databases. As you see, it’s very fragmented.

You can read more about Clustered and Non clustered indexes here:
https://stackoverflow.com/questions/1251636/what-do-clustered-and-non-clustered-index-actually-mean/1251652

We can replace some of the non-clustered indexes with clustered indexes, to make the heap organized. The SQL statements below can be applied to the core, master and web databases, but check how fragmented your tables are first. If it’s not too fragmented, this may even have a negative effect since data writes may reorganize the table data. Also notice the fill factors. I’ve used 80% or 90%, but the optimal values depends on how data written in your solution.

You can use he SQL Server commands below to replace existing non-clustered indexes with a corresponding clustered index.

DROP INDEX [ndxID] ON [Items]
CREATE UNIQUE CLUSTERED INDEX [ndxID] ON [Items] ([ID] ASC) WITH (FILLFACTOR = 80)

DROP INDEX [ndxUnique] ON [SharedFields]
CREATE UNIQUE CLUSTERED INDEX [ndxUnique] ON [SharedFields] ([ItemId] ASC, [FieldId] ASC) WITH (FILLFACTOR = 80)

DROP INDEX [ndxUnique] ON [UnversionedFields]
CREATE UNIQUE CLUSTERED INDEX [ndxUnique] ON [UnversionedFields] ([ItemId] ASC, [Language] ASC, [FieldId] ASC) WITH (FILLFACTOR = 80)

DROP INDEX [ndxUnique] ON [VersionedFields]
CREATE UNIQUE CLUSTERED INDEX [ndxUnique] ON [VersionedFields] ([ItemId] ASC, [Language] ASC, [Version] ASC, [FieldId] ASC) WITH (FILLFACTOR = 80)

DROP INDEX [ndxBlobId] ON [Blobs]
CREATE CLUSTERED INDEX [ndxBlobId] ON [Blobs] ([BlobId] ASC) WITH (FILLFACTOR = 80)

ALTER TABLE [Descendants] DROP CONSTRAINT [Descendants_PK]
ALTER TABLE [Descendants] ADD  CONSTRAINT [Descendants_PK] PRIMARY KEY CLUSTERED ([Ancestor] ASC, [Descendant] ASC) WITH (FILLFACTOR = 80) ON [PRIMARY]

DROP INDEX [ndxID] ON [Links]
CREATE UNIQUE CLUSTERED INDEX [ndxID] ON [Links] ([ID] ASC) WITH (FILLFACTOR = 80)

ALTER TABLE [Notifications] DROP CONSTRAINT [PK_Notifications]
ALTER TABLE [Notifications] ADD  CONSTRAINT [PK_Notifications] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (FILLFACTOR = 80) ON [PRIMARY]

DROP INDEX [ndxCreated] ON [History]
CREATE CLUSTERED INDEX [ndxCreated] ON [History] ([Created] ASC) WITH (FILLFACTOR = 80)

DROP INDEX [ndxID] ON [WorkflowHistory]
CREATE UNIQUE CLUSTERED INDEX [ndxID] ON [WorkflowHistory] ([ID] ASC) WITH (FILLFACTOR = 90)

DROP INDEX [ndxID] ON [Properties]
CREATE UNIQUE CLUSTERED INDEX [ndxID] ON [Properties] ([ID] ASC) WITH (FILLFACTOR = 80)

You may also see other very fragmented tables, such as the Archive*-tables. Those are however used quite rarely, so I don’t really see any point in having a clustered index on those.

As far as I understand Sitecore support, this should be a supported action to perform. But again, don’t do it unless you gain something from it. Some say a larger prefetch cache may be more beneficial, however that hasn’t helped me much.