Clustered index update in execution plan

Question

Hi,

I have come across a strange situation while performing execution plan analysis. I have observed that there is "Clustred Index Update" operation in the execution plan of few stored procedures which is having the maximum cost in the overall plan. Further, I have looked at the code and the index definition and realise that there is no field in update statement which is included to be as part of the clustered index.

I would like to know if I need to check if anything else need to be checked. Please help.

Regards,

---

Phani Note: Please mark the post as answered if it answers your question.

Answer 1

If a table has a clustered index on it, the data of the table *is* the clustered index, so updating the table updates the clustered index because they are one in the same.

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 2

Hi,

As Phil pointed, the leaf nodes of the clustered index are the data pages.


AMB

Answer 3

Hi,

Thanks for the inputs given. I would request for two clarifications from the answers mentioned:

1. Not every update statement is having "clustered index update" in the execution plan. Since it is taking max cost in the execution plan I see this as one of the area to be considered for tuning. What is the approach to tune this?

2. As mentioned above, the leaf nodes of the clustered index are the data pages. I have a basic question here. Are these lead nodes are the actual data pages or pointers to actual data pages? My understanding is that the clustered index will have data physically sorted with the columns that are actually defined as part of it. Leaf nodes of the clustered index is the pointer to the actual data page from which the required data can be fetched. Same is the case with even non-clustered index excepting that the data ordering will not be physically sorted and the data will not be stored as part of index unless there are specific columns included as covering index.

Request for your advice on the same.

---

Phani Note: Please mark the post as answered if it answers your question.

Answer 4

Hi,

Thanks for the inputs given. I would request for two clarifications from the answers mentioned:

1. Not every update statement is having "clustered index update" in the execution plan. Since it is taking max cost in the execution plan I see this as one of the area to be considered for tuning. What is the approach to tune this?

2. As mentioned above, the leaf nodes of the clustered index are the data pages. I have a basic question here. Are these lead nodes are the actual data pages or pointers to actual data pages? My understanding is that the clustered index will have data physically sorted with the columns that are actually defined as part of it. Leaf nodes of the clustered index is the pointer to the actual data page from which the required data can be fetched. Same is the case with even non-clustered index excepting that the data ordering will not be physically sorted and the data will not be stored as part of index unless there are specific columns included as covering index.

Request for your advice on the same.

---

Phani Note: Please mark the post as answered if it answers your question.

1 - If a table has a clustered index on it, you will see a "clustered index update" because to update data in the table, the query optimizer has to update the clustered index because the data of the table *is* the clustered index.  That is my understanding anyway.

2 - A clustered index is the data - no pointers to data.  If a table does not have a clustered index on it, and there is a non-clustered index created, that index will contain row ID values pointing back to the physical data in the heap.

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 5

Let me get to the next level of my query i.e.,

If we see too much cost being consumed by the clustered index update, what is the approach that needs to be taken from tuning perspective. How can we reduce the cost of this operator.

Please provide the pointers/suggestions on the same.

Regards,

---

Phani Note: Please mark the post as answered if it answers your question.

Answer 6

Why do you think the cost is excessive?  Updates are generally the most expensive part of any plan.

If you need to update a table that has a clustered index, there isn't anything you can do - the data has to be updated so you have to incur the cost of the update operator.  If you are looking at the percentages each operator consumes - remember that the overall plan should add up to 100% (that's not always true - sigh) and since most of the time will be spent updating data, the percentage of an update operator will be higher than the rest - even if the time to execute took 1 second.

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 7

Hi Phil,

What you said definitely makes sense. Not sure if I am extending the thread too much, for better clarity, I am putting this question.

Not every update statement is leading to a "Clustered Index Update" in execution plan. Is there any specific reason/situation which we can say as concrete reason for getting the "Clustered Index Update" in execution plan? Please let me know.

Thank you.

Regards,

---

Phani Note: Please mark the post as answered if it answers your question.

Answer 8

Hi Phil,

What you said definitely makes sense. Not sure if I am extending the thread too much, for better clarity, I am putting this question.

Not every update statement is leading to a "Clustered Index Update" in execution plan. Is there any specific reason/situation which we can say as concrete reason for getting the "Clustered Index Update" in execution plan? Please let me know.

Thank you.

Regards,

---

Phani Note: Please mark the post as answered if it answers your question.

You will not see "Clustered Index Update" if you are updating a table that DOES NOT have a clustered index.  If you are updating a table that has a clustered index, then you will see the "Clustered Index Update" operator.

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 9

Thank you.

Regards,

---

Phani Note: Please mark the post as answered if it answers your question.