Do you rebuild your indexes periodically?

Most of you will – hopefully – have some index maintenance strategy. That is, you need some criteria, when to rebuild or reorganize an index. I will not repeat every advice that you may find elsewhere on the internet, e.g. here, or here.

In almost all given advices the dynamic management view sys.dm_db_index_physical_stats plays a central role, since it can easily be used to detect the degree of fragmentation of an index and the course of action to perform. Generally spoken, if index fragmentation exceeds a distinct value (let’s say 40%), you should perform a complete rebuild of the index. By doing so, the whole index tree is built from scratch and also the index related statistics is rebuilt. If the fragmentation is noticeable, but not too high (somewhere between 10% and 40%), an index reorganize may be sufficient. By performing a reorganize only the index pages in the leaf level are rearranged which is less cost (I/O) intensive than a complete index rebuild.

Unfortunately, sys.dm_db_index_physical_stats can create huge I/O stress, as you can check inside this blog post of Paul Randal (yes, the guru). There is, of course, an opportunity to affect the I/O load created, by specifying the last parameter of the sys.dm_db_index_physical_stats DMF. IF you use LIMITED here, the I/O load is as minimal as possible. DETAILED, on the other hand, may create very noticeable physical I/O, especially for larger indexes, although it should reveal very detailed and realistic.

But be aware that querying the fragmentation by the use of sys.dm_db_index_physical_stats, as also suggested in Books Online (see here), may not be sufficient to detect candidates for necessary rebuilds.

Look at the following example.

We start by creating a simple table and inserting 400,000 rows:

create table t1 (   c1 uniqueidentifier not null default newsequentialid() primary key  ,c2 nchar(513) not null default '#' ) go -- Insert 400,000 rows insert t1(c2)   select top 400000 '?' from sys.trace_event_bindings c1,sys.trace_event_bindings as c2 go

Now, let’s see the fragmentation of the primary key (which is also the clustered key in our case). Oh, btw.: I know very well that a clustered index on a GUID column is less than optimal, but I see this practice all the time. It’s just tat developers love the idea of creating objects (table rows) that are unique throughout the whole universe. But that’s not the point here, so let’s see how the fragmentation as well as the space used values look like. First, we use the LIMITED mode:

select index_level, avg_fragmentation_in_percent, avg_page_space_used_in_percent   from sys.dm_db_index_physical_stats(db_id(),object_id('t1'),null,null,'limited')

Here’s the result:

Looks pretty good, he? 0.7% fragmentation, so no need for any actions, right? (avg_space_used_in_percent is not shown in LIMITED mode.) Well, let’s see what sys.dm_db_index_physical_stats returns when running in DETAILED mode:

select index_level, avg_fragmentation_in_percent, avg_page_space_used_in_percent   from sys.dm_db_index_physical_stats(db_id(),object_id('t1'),null,null,'detailed')

Here’s the result:

Also nothing to worry about, right? Almost no fragmentation. But wait! Why has the index tree a depth of 4? That seems too deep! So let’s see, if an index rebuild will shrink the depth:

alter index all on t1 rebuild go select index_level, avg_fragmentation_in_percent, avg_page_space_used_in_percent, ''   from sys.dm_db_index_physical_stats(db_id(),object_id('t1'),null,null,'detailed')

Now the index looks like that:

So, the index three has one level less than before! Therefore, every index seek has to perform one read less – that’s 25% less!

If you look at the avg_space_used_in_percent column, you see why.The non-leaf levels of the index are more packed with data and don’t contain as much space after the rebuild as before.

So be aware, when following the best practices guides in BOL or the internet. Those guidelines are definitely very good and helpful, but may not be sufficient under all circumstances.

Non Clustered Indexes may prevent dead locks

If a table is very tiny, so that all table’s data fit into one single data page e.g., will any index be helpful when accessing/modifying the table’s data? You may think no, since for reading the data, the one and only data page has to be touched anyway, so a table (or clustered index) scan will get by with a single page read, and that’s the most efficient method for reading the data. So, there’s no need for an index, right?

Well, as the guru always says: It depends.

Have a look at the following table:

create table t1  (    c1 int not null primary key clustered   ,c2 int not null  )

Now, lets say we modify some data simultaneously from two different connections. Try the following scripts from two separate connections:

-- Connection 1 begin tran   insert t1(c1, c2) values (1, 1111)     -- pretend, we do something here   waitfor delay '00:00:10'   delete t1 where c2 = 1111 commit

 

-- Connection 2 begin tran   insert t1(c1, c2) values (2, 2222)     -- pretend, we do something here   waitfor delay '00:00:10'   delete t1 where c2 = 2222 commit

After a few seconds you will see an abortion one of the two transactions with a deadlock error like this:

Msg 1205, Level 13, State 51, Line 7
Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Now, let’s create a nonclustered index on column c2.

create nonclustered index ix1 on t1(c2)

You may think, this index isn’t any useful, since our table only contains two rows and the optimizer will never decide for an index seek therefore. But if you repeat the above experiment, no deadlock shows up this time. So the index is good, helpful and may be even necessary.

Ok, let’s see what happened here. We start with the first case, without the nonclustered index. Our focus should be on the DELETE statement, since this is the cause for the problem. The execution plan (without the nonclustered index) looks like this:

In order to find the row that has to be deleted, a clustered index scan must be performed.

A call of sp_lock during the execution of the two statements reveals a situation like this:

spid	IndId	Type	Resource	Mode	Status
52	1	KEY	(8194443284a0)	U	WAIT
52	1	PAG	1:115	IX	GRANT
52	0	TAB		IX	GRANT
52	1	KEY	(61a06abd401c)	X	GRANT
54	1	KEY	(8194443284a0)	X	GRANT
54	0	TAB		IX	GRANT
54	1	PAG	1:115	IX	GRANT
54	1	KEY	(61a06abd401c)	U	WAIT

We see two connections (SPIDs) holding or waiting for locks. I’ve highlighted the rows of interest, where I used a green background for granted locks and a red background for waits. The resource column of the highlighted rows is related to a table (clustered index) row. You may investigate this on your own by using the undocumented %%lockres%% column. (See this blog post by James Rowland-Jones)  It is very obvious that the two connections are waiting on each other to release a lock on a row which is locked by the other connection, while – at the same time - holding a lock on the row the other connection is waiting for. Classical deadlock situation.

Now, let’s see how this changes with the nonclustered index in place. Here’s the execution plan for the delete:

Ok, the index is used for looking up the row that has to be deleted. Very good. The optimizer is aware of the fact that an index seek will be more appropriate for determining the row, since this will lead to fewer resource locks.

Here’s what sp_lock reveals if the two scripts are executed with an existing nonclustered index:

spid	IndId	Type	Resource	Mode	Status
52	3	PAG	1:126	IX	GRANT
52	1	PAG	1:120	IX	GRANT
52	3	KEY	(0fcd4e06ed44)	X	GRANT
52	0	TAB		IX	GRANT
52	1	KEY	(61a06abd401c)	X	GRANT
54	1	KEY	(8194443284a0)	X	GRANT
54	1	PAG	1:120	IX	GRANT
54	3	PAG	1:126	IX	GRANT
54	0	TAB		IX	GRANT
54	3	KEY	(917735e52a83)	X	GRANT

Very good! Only granted locks this time, no waits.