February 21, 2012 at 2:19 pm
I'm sure this has to be a n00b thing, but I'm just not getting what's going on here.
I have a procedure that's taking way too long to execute. I checked the execution plan, and found that 56% of the query cost is coming from this line:
UPDATE JBM_TKI
SET Order_In_Grid = Order_In_Grid + 1
WHERE PHAS_Rec_Num = @PHAS_Rec_Num
AND Order_In_Grid >= @OrderInGrid
The execution plan shows that fourteen indexes are being updated, but the field Order_In_Grid only appears in four of them, all as INCLUDEs. Why would ten other indexes, which have no relation to this field that I can see, be getting updated?
thanks
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
February 21, 2012 at 2:22 pm
First guess is that there is a clustered index on the table and I believe that all non-clustered indexes get updated when the clustered index does.
February 21, 2012 at 2:22 pm
AKKK! Yes, that's it. Well this is gonna get ugly, I guess. Thanks for the pointer.
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
February 21, 2012 at 2:23 pm
The clustered index key is present in all nonclustered indexes. That's one of the reasons that the clustered index is recommended to be on a non-changing column.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 21, 2012 at 2:26 pm
GilaMonster (2/21/2012)
The clustered index key is present in all nonclustered indexes. That's one of the reasons that the clustered index is recommended to be on a non-changing column.
Hmmm... that makes a lot of sense. This is certainly a good learning experience -- I do have an identity column that I can use as the clustered index instead, and make a separate unique non-clustered index instead. So I think I can fix this pretty easily.
See? Told you it was a n00b thing. 😉
Thanks for the education!
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
February 21, 2012 at 2:29 pm
Maybe worth a read: http://www.sqlservercentral.com/articles/Indexing/68439/
http://www.sqlservercentral.com/articles/Indexing/68563/
http://www.sqlservercentral.com/articles/Indexing/68636/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply