Updating Heaps. Not Awesome.

I’ve found myself speaking a lot lately about things that actually go wrong when heaps are used inappropriately. Most of us have heard that heaps aren’t recommended for update-heavy workloads, but it is still disturbingly common for me to start on a new project and find heaps of heaps. I’m pretty sure that the issue is more that there isn’t a lot of awareness about what kinds of things can break when best practices aren’t followed than it is about anybody not hearing the guidance.

Setup

Basically all that’s needed to demonstrate this particular issue is a database with a table that doesn’t have a clustered index. It’s helpful if the record size for that heap is large enough to be interesting. Not being terribly creative, here’s what I used

USE master
GO

CREATE DATABASE HeapDB
	ON( NAME=HeapDB, FILENAME='C:\your_path_here\HeapDB.mdf', SIZE=10GB, FILEGROWTH=1GB )
	LOG ON( NAME=Heap_log, FILENAME='C:\your_path_here\HeapDB.ldf', SIZE=1GB, FILEGROWTH=1GB)
GO

ALTER DATABASE HeapDB SET RECOVERY SIMPLE;
GO

USE HeapDB
GO

CREATE TABLE dbo.BigHeap(
	ID            INT          NOT NULL,
	BigBlobbyData VARCHAR(MAX) NOT NULL
);
GO

-- Add some data

DECLARE @x   INT = 1;
DECLARE @xvc VARCHAR(10);
WHILE @x <= 1000
BEGIN
	SET @xvc = CAST(@x AS VARCHAR(10));
	INSERT INTO dbo.BigHeap( ID, BigBlobbyData ) VALUES( @x, REPLICATE( '0', 4000 ) + @xvc);

	SET @x += 1;
END
GO

Breaking Stuff

Perhaps the easiest issue to demonstrate on this setup happens if we simply do enough simultaneous updates on a table. For my test, I opened up two new query windows in SSMS and in both windows ran this code (at the same time)

USE HeapDB
GO

UPDATE dbo.BigHeap
	SET BigBlobbyData = REPLICATE( '0', 10000) + CAST(RAND() AS VARCHAR(100))
	WHERE ID = FLOOR(RAND() * 10) + 1;
GO 100000

After a while, it should be possible to scroll through the contents of the messages tab and see something like this


shot_20170613_1

Fortunately with current versions of SQL Server we have an easy way to dig into deadlocks. Opening up the system_health extended event session and finding the most recent deadlock gives me this graph


shot_20170613_3

What does this mean? It means that at some point each session had an exclusive lock on different RIDs (a RID, or row identifier, is essentially just what we call a record in a heap). And each session was attempting to get an update lock on the record which the opposing session exclusively locked. Update locks can not be granted as long as another transaction holds an exclusive lock on that resource, so no amount of waiting will allow us to make progress and a deadlock results.

But why? In a nutshell this particular deadlock happens because there is no structure in a heap. To update a record SQL Server must scan until it is found … but even once the record has been found the search must continue. There is no primary key, so there is no way to know that more records matching the WHERE clause of the update do not exist. If two separate sessions make changes to a record that has not yet been scanned by their counterpart, this kind of deadlock will result.

Will it help if we add a unique nonclustered index? Maybe a little, in that a deadlock may be less likely. But we can still see deadlocks because both the heap and index may need to be updated, and the fact that pages in both need to be locked opens the door to a different kind of deadlock. But more to the point, why would we do this? If we have a candidate key that could be used as a unique index, it may also be a great candidate for a clustering key.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s