Deleting From Heaps

Uwe Ricken discusses a downside to leaving an active table as a heap:

The simplest way of removing empty data pages from a heap is via the exclusive locking of the table. But keep in mind that there is no such thing as a free lunch. Blocking the table from other processes will lead to a system that will not scale. As long as the DELETE process locks the table, no other process can access the table. The only exception is the “SNAPSHOT ISOLATION” but explaining the pros and cons of this technique is outside the scope of this article.

The next example (with the new created table) demonstrates the behavior.

The section on lock escalation was rather interesting.

Related Posts

Generating Index Drop And Create Statements

Drew Furgiuele says “Game over, man, game over!” to indexes: The premise is simple: it will generate a series of DROP and then CREATE INDEX commands for every index. The process is a little more complex in practice, but at a high level it: Creates a special schema to house a temporary object, Creates a […]

Read More

Index That Column Or Include It?

Jeanne Combrinck lays out her recommendations on whether to make a particular column part of an index or have it be an included column: The original question we wanted to answer was whether we would see a performance difference when a query used the index with all columns in the key, versus the index with […]

Read More

Categories

September 2016
MTWTFSS
« Aug Oct »
 1234
567891011
12131415161718
19202122232425
2627282930