Update performance: clustered versus covering index

Question

I have a simple, 3-column table with about 50 million rows in it. This table gets about 5,000 inserts/updates per second, and perhaps 20 queries per second are executed against the table. The table looks like this:

Controller: Int
ExecutionTime: DateTime
Result: Int

To maximize the efficiency of my queries, I need two indexes. (Result Includes Execution Time) and (Controller, ExecutionTime). These two indexes fully cover my queries - all information is served directly from the indices, no table lookups required.

I chose nonclustered indices because I was worred about the performance hit using a clustered index with so many updates. But it occurs to me that since I am fully covering the queries, this might not be a valid concern - perhaps my covering, nonclustered indices require the same amount of maintenance as a clustered index would.

So my question: In a table with a lot of inserts/updates, will a covering, nonclustered index usually have a lower UPDATE performance hit than a clustered index?

Thanks for your time and help!

Answer 1

Under the covers clustered and nonclustered indexes are the same. The clustered index just has the additional property that is is guaranteed to INCLUDE all columns. Therefore the data does not need to be maintained somewhere else. So, a clustered index and a nonclustered index that INCLUDEs all columns are virtually the same from an update cost perspective.

However, every index needs to be maintained if it contains a column that was changed during an updated. That means, the more indexes you have, the more expensive updates get.

So in your situation, I would try to keep the number of indexes to a minimum. That will help update performance more than worrying about if a particular index is better clustered or covering.

That all being said, your updates still need to find the row(s) to update as quickly as possible. Because you have two orders of magnitude more updates then select, updates should be looked at first when designing the indexing strategy. After they are taken care of, look at providing the minimal number of appropriate indexes for the read queries.

Answer 2

In your case your non-clustered indexes both include all but one column from the table and both are 3/4 the size of the full record (assuming you are using the 8-byte DATETIME). Based on this the IO cost for each non-clustered index should be about 75% of the IO cost for the clustered index if you had one.

Now, because you don't have a clustered index, you have a heap, which obviously needs to be maintained just like the indexes.

Let's say the cost to insert the full row (into a heap or clustered index) is 100% and let's consider what has to happen during an insert:

1) insert new row into heap (cost=100%)
2) insert new row into non-clustered index #1 (cost=75%)
3) insert new row into non-clustered index #2 (cost=75%)

And during an update (assuming you only update the Result):

1) Update the row in the heap  (cost=100%)
2) Update non-clustered index #1 (cost=75%)

Total cost for 1 insert, 1 update = 425%

Let's see what would happen if you made your (Controller, ExecutionTime) clustered.

For an insert:

1) insert into the clustered index  (cost=100%)
2) insert into the non-clustered index (Result Includes Execution Time) (cost=75%)

For an update (assuming you only update the Result, hope that's a correct assumption):

1) update the clustered index   (cost=100%)
2) update the non-clustered index (Result Includes Execution Time) (cost=75%)

Total cost for 1 insert, 1 update = 350%

So in terms of writes it makes sense to make your (Controller, ExecutionTime) clustered an get rid of the heap.

I also should mention that heaps are notorious for fragmentation (which may not be a problem in your case since you did not mention deletes and all your columns are fixed size), and are generally not recommended for OLTP traffic.