-1

After an UPDATE statement on a large table, the execution plan shows updates of indexes (all non-clustered) that include the updated columns.

Before each index update, there is an Eager Spool operator followed by a very costly Sort.

Overall, the updates of the indexes consume about 50% of the execution time.

Is there a way to optimize the indexes and minimize the costs?

enter image description here

1 Answer 1

5

Overall, the updates of the indexes consume about 50% of the execution time.

No, the percentages shown are the proportion of each operator's estimated cost according to the SQL Server cost model. These are used internally by the optimizer to choose between plan alternatives - they are not meant to show real-world performance, and often do not.

To get runtime performance data, you would need to be running a fairly modern version of SQL Server and SSMS. See New Showplan enhancements by the SQL Server Tiger Team for details.

It is impossible to say from the plan graphic alone whether you should be concerned about the chosen update plan shape. It certainly looks like it should be pretty quick judging from the thickness of the lines alone.

The plan you are seeing is a wide (or per-index) update plan. This gathers all the change information needed to update all nonclustered index into a table spool, then replays the appropriate change data for each index separately. The Sort operator is to ensure changes are applied to the index in key order to optimize for sequential I/O. You can read more about update plan shapes in my article Optimizing T-SQL queries that change data.

As mentioned in the article, SQL Server can often consider a narrow (per-row) update plan where all nonclustered indexes are maintained by the Clustered Index Update operator. Why the optimizer did not (or could not) choose that option in your case is impossible to tell from the information provided.

You are welcome to ask a follow-up question about performance tuning your update query, if you find that to be necessary after looking at runtime performance. You must provide enough detail for someone suitably qualified to answer. This typically includes table and index definitions, and a runtime ('actual') execution plan. See How to create a Minimal, Complete, and Verifiable Example for database-related questions in the Help Centre.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.