Sql-server – Fragmentation Level for Heaps

fragmentationheapola-hallengrensql serversql server 2014

I am currently using scripts provided by Mr. Ola Hallengren for executing maintenance job and of-late I have been noticing that there are many tables (heaps) fragmentation level is alarmingly high and needs to be looked and taken action upon. I checked FAQ at the site and seems his script doesn't support rebuilding heaps. I used below query to find the fragmentation level:

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() and dbindexes.name is null
ORDER BY page_count desc, indexstats.avg_fragmentation_in_percent desc

My application is supported by vendor and I have been communicating with them to change these heaps to tables and create clustered index however it hasn't yielded any meaningful result yet since they have defined primary key as unique non-clustered index and it is also part of foreign key, so needs to change at many level before doing any change. First of all, it took many days for me to explain the difference between clustered index and primary key with unique index.

I also went through the tweaks suggested by Mr. Brent Ozar for changing the defaults at script provided by Mr. Ola Hallengren for index optimize in order to make it more efficient however I didn't find any details of heap rebuilding.

As per my understanding heap's fragmentation can be handled in two ways as described here:

To create clustered index on table and drop it – This would clear all the fragmentation and also rebuild all non-clustered index however it would be time and I/O consuming.
Rebuilding the heap – This would also clear the fragmentation and rebuild all non-clustered index part of table rebuild.

I can't go for option 1 because I am not aware of columns where clustered index can be created and also this could take longer than option 2.

I am looking for possibility of implementing option 1 in the scripts by Ola Hallengren or alternative method for handling this. Also to add, I would like to rebuild my heaps only when the size of heap is more than 10,000 pages and fragmentation level is more than 80.

I am using Microsoft SQL Server 2014 SP3 Enterprise Edition.

As a DBA – I don't prefer to have heaps in my database however since it is vendor supported application and since they have already defined primary key as unique index and these keys are foreign keys, its very difficult to change them to clustered due to references as well as likeliness of down time.

EDIT: I went through the link provided by Mr. Erik Darling and I can confirm that I have number of heaps with forwarded records or deletes across the databases. Now, I am back to point from where I had started i.e. with those two options. As I mentioned earlier, creating clustered index is very very difficult in my scenario and will require at least months(being optimistic) with likeliness of downtime considering complex foreign key structure. Need advise on rebuilding the heaps and possible side effect.

Best Answer

Heaps have a few special challenges that you can't experience with clustered indexes:

Forwarded Records
Captive Pages

I'd suggest running sp_BlitzIndex against your database to find out if either of these things is happening with your Heaps. If not, then leave them alone. If they are, you may need to consider rebuilding them.

At this time, you can't reorganize a Heap table, and rebuilding a Heap table will also rebuild any nonclustered indexes on it. It may be cheaper to drop them, rebuild the Heap table, and then recreate the nonclustered indexes afterwards.

You can read more about this stuff here:

Related Solutions

Sql-server – Index fragmentation increase as more row being deleted

This isn't fragmentation.

Fragmentation is generated of course, but deletes will simply create "islands" of remaining pages, which is less evil then GUID/clustered key INSERT fragmentation.

If you're PK is an IDENTITY, then CreationDate should roughly track this so you're actually deleting chunks of contiguous rows anyway.

Do you have an index on CreationDate
Do you have delete cascades?
Is the TOP 1000 in a single transaction?

For point 3, doing a loop inside a transaction is pointless: is this it?

At some point, a statistics update may be needed if you delete enough rows but I don't think it's that.

Other options:

why not use TRUNCATE TABLE, wrapped in a stored procedure with EXECUTE AS OWNER
use SYNONYMs for poor man's partitioning

Sql-server – Heap having high fragmentation

I've seen this question pop up enough times over the years that I figured it finally warranted a real answer. Yes, this answer is years after the fact, but facts remain the same now as they did in 2015.

Heap Fragmentation is a red-herring, and frankly I wouldn't pay too much attention to it. Honestly I say that about fragmentation in general and, at best, it is an indicator of issues to come, with the real detriment to performance being excessive white space per page. Since that's not your question, I won't digress any further and just point you towards a post from Brent Ozar that will further wet your appetite if you wish to go down that rabbit hole.

What you need to be concerned with for heaps is forwarded records/forward pointers counts. To quote one of the better posts on the topic by Kalen Delaney:

when a row in a heap has to move, SQL Server leaves behind a forwarding pointer in place of the row that has moved

and

SQL Server jumps out of sequence and follows the forwarding pointer for every forwarded record.

This is actual fragmentation that does matter as it's introducing additional reads, so depending on how many forwarded records you have in your heap, this could represent a significant chunk of I/O that you need to perform whenever you read the heap into memory.

How do you find out how many forwarded record pointers exist in your heap? You need to simply run the following query for the heap in question:

SELECT    index_type_desc
        , page_count
        , avg_page_space_used_in_percent
        , avg_fragmentation_in_percent
        , avg_record_size_in_bytes
        , forwarded_record_count, 1.0*(forwarded_record_count)/(1.0*(page_count)) AS forwarded_record_pct
FROM    sys.dm_db_index_physical_stats(db_id(), object_id('schema.tablename'),null, null, 'detailed')
WHERE   index_type_desc = 'HEAP'
GO

Below are results from some actual tables in a vendor supplied application database, I currently support:

Results Table 1:

Results Table 2:

As you can see, fragmentation in both tables in actually pretty high, but I'm not going to waste my maintenance time rebuilding either table as there are not enough forwarded records to be concerned about it yet. My reasoning here is that a heap is only ever brought into memory by a table scan operation or a RID lookup (in the event that a nonclustered index defined on the heap is non-covering). When the table scan is performed, the entire table is loaded into memory, at which point fragmentation doesn't matter nearly as much. I want to avoid table scans in general if possible, but when they occur, I want them as efficient as possible, so reducing forwarded pointers directly helps with that. RID lookups are also affected by forward pointers, so even seek operations against a NCI with a RID lookup is subject to extra I/O if it runs into a forwarded record. When I hit a certain threshold of forwarded pointers within my heap, I will then rebuild the table. That threshold changes based on the table in question, but my general rule is once the heap has 10% or more forwarded record pages in comparison to total pages, I'm looking to rebuild the heap. Most of the heaps in this database don't suffer from forwarded records, so I don't have to worry about it too much, but that's not necessarily true for your situation.

Also, there are situations where heaps may not properly deallocate empty pages. Basically, if you find your workloads against a heap table is not primarily insert-driven or you find forwarded records are occurring often, you will want to look into clustering your table with a clustered index.

Best Answer

Related Solutions

Sql-server – Index fragmentation increase as more row being deleted

Sql-server – Heap having high fragmentation

Related Question