Heap Pages Can Be Linked

Paul Randal gives a very special case in which heap pages can be (temporarily) linked together:

Now the pages are linked together!

Note that this is an OFFLINE rebuild, which is the default. What happened is that the offline ALTER TABLE … REBUILD operation uses the part of the underlying functionality for an offline ALTER INDEX … REBUILD operation that builds the leaf level of the index. As that functionality builds a doubly-linked list of pages, the newly rebuilt heap initially has a doubly-linked list of pages! This doesn’t happen for an ONLINE rebuild of the heap, which uses a totally different mechanism.

Although the pages appear doubly-linked, that’s just an artifact of the mechanism used to build the new heap – the linkages aren’t used or maintained.

In other words, you get zero benefit from this.  Click through to see Paul’s test script.

Related Posts

The (Un)Importance of Index Fragmentation

Tibor Karaszi argues that index fragmentation is a less serious issue than most DBAs think: You know the story. Every week or so, we defragment the indexes. Many of us uses Ola Hallengren’s great script for this, some uses Maintenance Plans, and there are of course other alternatives as well. But are we just wasting […]

Read More

The Performance Hit From Ignoring Duplicate Keys

Paul White explains why there is a big performance hit when using IGNORE_DUP_KEY on clustered indexes: The IGNORE_DUP_KEY index option can be specified for both clustered and nonclustered unique indexes. Using it on a clustered index can result in much poorer performance than for a nonclustered unique index. The size of the performance difference depends on how […]

Read More

Categories

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