Heaps Of Fun

Rob Farley talks about an issue with heaps:

What I found was that the list of IDs was being stored in a table without a clustered index. A heap. Now – I’m not opposed to heaps at all. Heaps are often very good, and shouldn’t be derided. But you need to understand something about heaps – which is that they’re not suited to tables that have a large amount of deletes. Every time you insert a row into a heap, it goes into the first available slot on the last page of the heap. If there aren’t any slots available, it creates a new page, and the story continues. It doesn’t keep track of what’s happened earlier. They can be excellent for getting data in – and Lookups are very quick because every row is addressed by the actual Row ID, rather than some key values which then require a Seek operation to find them (that said, it’s often cheap to avoid Lookups, by adding extra columns to the Include list of a non-clustered index). But because they don’t think about what kind of state the earlier pages might be in, you can end up with heaps that are completely empty, a bunch of pointers from page to page, with header information, but no actual rows therein. If you’re deleting rows from a heap, this is what you’ll get.

This guy’s heap had only a few rows in it. 8 in fact, when I looked – although I think a few moments later those 8 had disappeared, and were replaced by 13 others.

But the table was more than 400MB in size. For 8 small rows.

Read the whole thing, including Rob’s reluctance to post on this topic.

Related Posts

DevOps T-SQL Tuesday Roundup

Grant Fritchey puts a bow on the latest T-SQL Tuesday: Thank you to everyone who participated in T-SQL Tuesday #091 which was on databases and DevOps. As I anticipated, this brought out quite a bit of variety on the posts. This is because DevOps is still… not quite cooked…(?) in many peoples minds. I think […]

Read More

Minecraft And DevOps

Richie Lee has an essay comparing Minecraft to DevOps: Let me be clear about something: If you don’t have your databases in source control, there’s no point in thinking about anything else. Everything else follows on from this point. Getting your code in source control is the absolute starting point of all deployment pipelines. Some […]

Read More

Categories

March 2017
MTWTFSS
« Feb Apr »
 12345
6789101112
13141516171819
20212223242526
2728293031