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.

more ▼

asked Feb 04, 2012 at 06:03 PM in Default

andrewpetre gravatar image

0 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort newest

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.
more ▼

answered Feb 06, 2012 at 11:02 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

(comments are locked)
10|1200 characters needed characters left

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)
more ▼

answered Feb 06, 2012 at 06:24 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 04, 2012 at 06:03 PM

Seen: 1882 times

Last Updated: Feb 06, 2012 at 11:02 AM