asked
SQL 2008 R2 uses nonclustered index to update non-indexed rows?
Without posting a lot of test scripts and examples, I just want to pose the question in case this is a known behavior. Googling about nonclustered indexes results in a bazillion pages that do not help answer my question. In the case where an update is performed with no criteria (update tbl set col = 1), is it common for SQL Server to use the single nonclustered index (PK, in this case) to get to the underlying data instead of doing a simple table scan? What I've found in our examples is that updating millions of rows for a simple value which is neither part of the key or part of an index still took a dreadfully long time. When I looked at the plan, it was using the nonclustered primary key index to get to the rows. The only solution I found for this was to use the hint "WITH (INDEX(0))" to force the table scan, and then performance was normal. I know people suggest that tables should always have something clustered. In this case, we don't, and I don't see a great reason to based on the way the data is used. But if someone can comment about the behavior of a nonclustered index being used (badly) for a non-indexed column update, it might help me make some better code. Thanks!
Heap trees always take more time for UPDATEs and DELETEs. This is because of the default structure of the way the data stored in SQL Server. As deletions, updates and additions are made to a heap, it becomes badly fragmented and significantly slows down page scans and server operation. Secondly, I cannot think of a table design where a NON-CLUSTERED PRIMARY KEY is there and that/there cannot be a CLUSTERED INDEX. (Can you please give more details about that?) If your design is not correct, you cannot expect optimal performance and you would find yourself mostly in such awkward positions. Now as far as why the primary key index is slow, most probably, because of the index fragmentation. You should rebuild it and you could see the difference (Keep in mind that it could take some time). Another performance gain can be obtained by CREATING a clustered index and then DROP it. This way your heap table would be in a much better shape for the DMLs(But only until the structure is not defragmented again)
You might be seeing a difference between the non-clustered index scan and the heap scan because the index is going to be ordered differently than the heap and the hops required between the index and the locations on the disk for the data when you're reference from the non-clustered index back to the data is worse than simply scanning the data. It's not a surprise. As @Usman Butt says, it could also be fragmentation at the root. As to whether or not you need a clustered index, most tables, most of the time, do. SQL Server builds it's access mechanisms around the clustered index, which is why that index actually stores the data.
4 People are following this question.
