How to rebuild HEAP tables including its nonclustered indexes
- Posted by Sqltimes
- On January 18, 2014
- 0 Comments
Heap tables are present in some databases, they are not necessarily what we prefer, but they exist. We need to include them into our maintenance activities to keep the performance high.
Question
- How do we rebuild heap tables
- Does rebuilding heap, automatically rebuild the dependent non-clustered indexes
Let’s take an example:
Create a sample heap table with a nonclustered index; Create some fragmentation.
|
1
2
3
4
5
6
7
8
9
10
|
CREATE TABLE dbo.Frag_Heap( ID INT NOT NULL IDENTITY(1,1), Frag VARCHAR(800))GOCREATE NONCLUSTERED INDEX IX_Frag_FragON dbo.Frag_Heap (Frag ASC)GO |
Now load some records. In a column with VARCHAR(800), we are only INSERTing a single character. So each data page is dense with many rows. Fragmentation here, though existing, is less.
|
1
2
|
INSERT INTO dbo.Frag_Heap (Frag) VALUES ('1')GO 100000 |
Now, lets UPDATE the table in a way, that it causes a lot of ‘forwarding pointers’. Meaning, when a row does not fit in the existing data page, it gets moved to a different page. Pointer to the new page is added to the previous location.
|
1
2
3
4
|
-- Cause fragmentation and a lot of forwarding pointersUPDATE dbo.Frag_HeapSET Frag = REPLICATE('a',800)GO |
Now let’s check the fragmentation level and forwarding pointers count. Keep in mind that in the non-clustered indexes, the RID that points to the HEAP table still points to the old pointer. So when nCI is traversed, it reached the RID. Using RID it searches in the heap table. And from heap it goes to the new forwarding pointer location. Too many hops !! We can fix this by rebuilding heap table and its dependent non-clustered indexes.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
-- Lets check the fragmentationselect OBJECT_NAME(object_id) , index_type_desc , index_depth , index_level , avg_fragmentation_in_percent , fragment_count , avg_fragment_size_in_pages , page_count , avg_page_space_used_in_percent , record_count , forwarded_record_countfrom sys.dm_db_index_physical_stats(db_id(), default, default, default, DEFAULT)where object_id = object_id('Frag_Heap')GO |
Heap_nCI_Fragmentation
Fragmentation is significantly gone up. Forwarding pointers are up significantly up. How do we rebuild heap table along with its non-clustered indexes.
Solution
Starting Sql Server 2005, we have a way to rebuild tables: “ALTER TABLE..”. This allows us to rebuild the heap, along with its dependent nonclustered indexes. As you can see in the image below, forwarding pointers are also gone. So now, the entire heap table data pages are together. One caveat here is, if you run this on ONLINE mode, it can only be single-threaded. But OFFLINE more allows multi-threaded rebuild.
|
1
|
ALTER TABLE dbo.Frag_Heap REBUILD |
Now, lets check the fragmentation and forwarding pointers status.
Heap_nCI_Fragmentation_After_Rebuild
Next, is it possible to reorganize heap table?
Hope this helps,
_Sqltimes
0 Comments