SQL Server Index Cost

Question

I read that one of the tradeoffs for adding table indexes in SQL Server is to increase the cost of insert / update / delete queries to improve the performance of selected queries.

I can conceptually understand what happens when I insert, because SQL Server needs to write records to each index corresponding to the new rows, but updating and deleting is a little darker for me, because I cannot completely wrap my head around what I have to do database engine.

Take DELETE as an example and suppose I have the following schema (pardon the-pseudo-SQL)

TABLE Foo col1 int ,col2 int ,col3 int ,col4 int PRIMARY KEY (col1,col2) INDEX IX_1 col3 INCLUDE col4 

Now, if I issue an instruction

 DELETE FROM Foo WHERE col1=12 AND col2 > 34 

I understand what the engine must do to update the table (or the clustered index, if you want). The index is set up so that it is easy to find the range of rows to be deleted, and do it.

However, at this stage, IX_1 also needs to be updated, and the query I gave it does not provide an obvious effective way for the database engine to find rows for updating. Is he currently forced to perform a full index scan? Does the row engine from the clustered index check first and generate smarter internal delete by index?

It can help me wrap my head around this if I better understand what is happening under the hood, but I think that my real question is this. I have a database that spends a considerable amount of time removing, and I'm trying to figure out what I can do about it.

When I show the execution plan for deletion, it just shows the entry for "Clustered Index Delete" in the Foo table, which lists in the details section other indexes that need updating, but I do not get any indication of the relative cost of these other indicators.

Are they all equal in this case? Is there a way to evaluate the impact of deleting one or more of these indices without having to try it?

Answer 1

Nonclustered indexes also store clustered keys .
It should not perform a full scan, as:

• your query will use a clustered index to search for strings. Strings
• contain a different index value (c3)
• using a different index value (c3) and cluster index values ​​(c1, c2), it can find matching entries in another index.

(Note. I had problems interpreting the documents, but I would suggest that IX_1 in your case can be defined as if it were also sorted by c1, c2. Since they are already stored in the index, it will make sense to use them for more efficient search of records, for example, for updates and deletions.)

All of this, however, has a cost. For each relevant line:

• he must read the line to find out the value for c3
• he should find the entry for (c3, c1, c2) in a non-clustered index
• he must also delete the entry.

In addition, although a range query can be effective for a clustered index in your case (linear access, after finding a match), servicing other indexes is likely to result in random access to them for each corresponding row. Random access has a much higher cost than just listing B + tree leaf nodes starting with a given match.
Given the above request, more time is spent on servicing a non-clustered index - the amount depends heavily on the number of records selected by the predicate col1 = 12 AND col2 > 34 .

I assume that the cost is conceptually the same as if you did not have a secondary index, but, for example, a separate table, holding (c3, c1, c2) as the only columns in the cluster key, and you did a DELETE for each corresponding row using (c3, c1, c2). Obviously, index maintenance is internal to SQL Server and is faster, but conceptually, I think it's close.

The foregoing would mean that the costs of maintaining the indexes would remain fairly close to each other, since the number of records in each secondary index is the same (the number of records) and deletion can continue only one after the other on each index.

If you need indexes, in terms of performance, depending on the number of records deleted, you might be better off planning the deletion by discarding indexes - which are not used during the deletion - before deleting and adding them after, Depending on the number of records affected, index recovery may be faster .