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:
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
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.