Feeds:
Posts
Comments

Archive for January, 2014

When you have large tables in your database you want to plan maintenance strategically. Its a fine balance that you plan carefully to make sure that you

  • Perform necessary maintenance successfully
  • But not cause any blocking to table access
  • Dependent nonclustered index are rebuilt (if needed) or not rebuild.

What commands does Sql Server provide to facilitate these operations.

Lets take an example:

CREATE TABLE dbo.Frag_CI
(
	  ID INT NOT NULL IDENTITY(1,1)
	, Frag VARCHAR(800)
)
GO

CREATE CLUSTERED INDEX PK_Frag_CI_ID
	ON dbo.Frag_CI (ID ASC)
GO

CREATE NONCLUSTERED INDEX IX_Frag_CI_Frag
	ON dbo.Frag_CI (Frag ASC)
GO

INSERT INTO dbo.Frag_CI (Frag) VALUES ('1')
GO 1000

Now, let’s check fragmentation. We see that both the clustered and nonclustered indexes are significantly fragmented.

select    OBJECT_NAME(object_id) AS Table_Name
		, 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_count
from sys.dm_db_index_physical_stats(db_id(),default,default,default,DEFAULT)
where object_id = object_id('Frag_CI')
GO
CI_nCI_Rebuild_Before

CI_nCI_Rebuild_Before

Solution

Option 1 :

Rebuild the table, resulting in rebuilding both CI and nCI together.

ALTER INDEX ALL ON dbo.Frag_CI REBUILD

Above code allows us to rebuild all the indexes on the table at the same time while applying all the options available to reduce the duration of overall rebuild time i.e. MAXDOP, ALLOW_PAGE_LOCKS, etc

CI_nCI_Rebuild

CI_nCI_Rebuild

Option 2 (new):

Slight modification to previous ALTER INDEX command allows us to rebuild just the CLUSTERED index and not rebuild relevant non-clustered indexes.

When you run ALTER INDEX with ALL, it affects all the indexes (obviously). Without ALL keyword it only affects the selected index.

ALTER INDEX PK_Frag_Col1 ON dbo.Frag_CI REBUILD
GO

Option 3:

Here we rebuild the table, using ALTER TABLE command, but surprisingly it only rebuilds the clustered index and does not touch the nonclustered indexes. Interesting to see the difference in behavior between ALTER TABLE….REBUILD ALTER INDEX…REBUILD. Also, there is no ALL keyword option for ALTER TABLE. (Please note that PARTITION=ALL  only affects the partitions selected to rebuild with in the CI).

ALTER TABLE dbo.Frag_CI REBUILD
CI_Rebuild_no_nCI

CI_Rebuild_no_nCI

Option 4:

In this, we rebuild the index using “CREATE INDEX…WITH DROP_EXISTING” option. As expected, this only rebuilds clustered index (nonclustered index is not rebuilt). In a clustered and non-clustered index setup, clustering key is present in the leaf pages of the nonoclustered index. So, when CI is rebuilt, clustering key does not change, so there is no need to rebuild nCI.


CREATE CLUSTERED INDEX PK_Frag_CI_ID
 ON dbo.Frag_CI (ID ASC)
 WITH (DROP_EXISTING = ON)
 GO
CI_Rebuild_no_nCI

CI_Rebuild_no_nCI

So, in conclusion, we see that when we rebuild CLUSTERED index, only the CI is affected. None of the relevant non-clustered indexes (nCIs) are rebuilt. Each nCI, at the leaf level has clustering key from the clustered index. Since that does not change during rebuild, the nCI does not need to be rebuilt.

Hope this helps,
_Sqltimes

Read Full Post »

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.

CREATE TABLE dbo.Frag_Heap
(
  ID INT NOT NULL IDENTITY(1,1)
, Frag VARCHAR(800)
)
GO

CREATE NONCLUSTERED INDEX IX_Frag_Frag
ON 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.

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.

-- Cause fragmentation and a lot of forwarding pointers
UPDATE dbo.Frag_Heap
SET 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.

-- Lets check the fragmentation
select 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_count
from sys.dm_db_index_physical_stats(db_id(), default, default, default, DEFAULT)
where object_id = object_id('Frag_Heap')
GO
Heap_nCI_Fragmentation

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.

ALTER TABLE dbo.Frag_Heap REBUILD

Now, lets check the fragmentation and forwarding pointers status.

Heap_nCI_Fragmentation_After_Rebuild

Heap_nCI_Fragmentation_After_Rebuild

 

Next, is it possible to reorganize heap table?

 

Hope this helps,
_Sqltimes

Read Full Post »