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

Forced Parameterization and Filtered Indexes

Aaron Bertrand walks us through a case where filtered indexes become unhelpful: Again, focusing on the areas highlighted in orange: the statement has a parameter @0 (previously it had @1) but, more importantly, the clustered index is scanned now instead of the filtered index. This has impacts throughout the plan, including how many rows are […]

Read More

When Indexes Collide

Andy Mallon gives us a case where it makes sense to have a non-clustered index which shares the same columns as your clustered index columns: First off, let’s remember the difference between clustered & nonclustered indexes The clustered index is organized by the key columns. It also includes every other column as part of the row structure […]

Read More

Categories

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