Sql-server – Update performance: clustered versus covering index

indexperformancequery-performancesql serversql-server-2008

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!

Best Answer

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.

Related Solutions

Sql-server – Non clustered index : performance issue

1&2 - Every update query must update the data for the table, wherever it may appear - in the clustered index (always), in any index that has the column in its definition, and in any index which just includes this column.

3 - Removing records may or may not help, it depends on 4:

4 - Probably, because sometimes it will not use the index, but will instead choose to scan the entire table. If it's scanning the entire table, then reducing the size of the table (via (3) above) will help. It will scan the entire table if, so far as the optimizer is concerned, that will be more efficient than using the index, then accessing the clustered index to complete the operation. It may choose to do this because it's actually true that the index will not help, or if statistics are out of date, or just sometimes it gets this wrong. Examine execution plans to determine if this has happened.

4a - If there's a lot of activity on the table, then other operation may have locks that prevent your query from processing as fast as it could if it was the only activity occurring in the database. Waiting for locks can take seconds, minutes, or hours - it depends on how timely the other operations are

5 - Solution to what?

824392 isn't that many rows, but it may make for a big table - it depends on how wide those rows are, really.

Sql-server – computed columns, index, clustered index and covering index

Computed Columns can be stored in the data page in one of two ways. Either by creating them as PERSISTED or by including them in the clustered index definition.

If they are included in the clustered index definition then even if the columns are not marked as PERSISTED then the values will still be stored in each row. These index key values will additionally be stored in the upper level pages.

If the computed column is imprecise (e.g. float) or not verifiable as deterministic (e.g. CLR functions) then it is a requirement for the column to be marked as PERSISTED in order to be made part of an index key.

So to give an example

CREATE TABLE T
(
A INT,
C1 AS REPLICATE(CHAR(A),100) PERSISTED,
C2 AS REPLICATE(CHAR(A),200),
C3 AS CAST(A AS FLOAT) PERSISTED,
C4 AS CAST(A + 1 AS FLOAT)
)

CREATE UNIQUE CLUSTERED INDEX IX ON T(C2,C3)

C1 will be stored in just the data page rows as it is marked as PERSISTED but not indexed.
C2 will be stored in both the rows on the data page and the index higher levels as it is an index key column.
C3 will be stored as for C2. As it is imprecise it is a requirement to mark it as PERSISTED however.
C4 won't be stored anywhere as it is neither marked as PERSISTED nor indexed.

Similarly all computed columns referenced in non clustered index definitions as key columns need to be stored at all levels of the index as they are part of the index key. There is the same requirement regarding precise/deterministic results.

Fails

CREATE NONCLUSTERED INDEX IX2 ON T(A,C4)

With the error.

Cannot create index or statistics 'IX2' on table 'T' because the computed column 'C4' is imprecise and not persisted. Consider removing column from index or statistics key or marking computed column persisted.

To include it as part of the non clustered index key it must also be stored in the clustered index data pages. However

Succeeds.

CREATE NONCLUSTERED INDEX IX2 ON T(A) INCLUDE (C4)

Computed columns that are only INCLUDEd columns are persisted to the NCI leaf page and do not have the requirement that they also be persisted in the data page.

Best Answer

Related Solutions

Sql-server – Non clustered index : performance issue

Sql-server – computed columns, index, clustered index and covering index

Related Question