Reads too high on indexed-table update

Question

Post reply

Reads too high on indexed-table update

Viewing 6 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

neil-560592 SSCommitted Points: 1557 More actions · Answer 1

The same thing happens on SQL 2005, so I suspect it is universal to all versions.

Any ideas as to the cause?

http://90.212.51.111 domain

SpringTownDBA SSCertifiable Points: 6917 More actions · Answer 2

These are logical reads, not physical reads.

Every seek into a table gets counted as a logical read.

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 3

SpringTownDBA (11/23/2012)
These are logical reads, not physical reads.
Every seek into a table gets counted as a logical read.

That doesn't explain why the number of reads jumps so high when the index is present. It also doesn't explain why the index (which is on a totally different column, BTW) comes into play at all.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SpringTownDBA SSCertifiable Points: 6917 More actions · Answer 4

I would guess that being a heap is making the difference. I haven't fired up my work laptop yet this weekend, but some agent alerts hitting my phone say I should. I'll try to repo this with larger row counts.

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 5

SpringTownDBA (11/24/2012)
I would guess that being a heap is making the difference. I haven't fired up my work laptop yet this weekend, but some agent alerts hitting my phone say I should. I'll try to repo this with larger row counts.

I thought the same thing so I added an IDENTITY column as the clustered index. All that did was double the number of reads during the update because the original index is still being used even after rebuilding the table, adding the clustered index first, and then adding the non-clustered index. What's even more odd is the output from the appears to be a rowkey of sorts but there isn't a Key Lookup anywhere in the execution plan. You would thik that all of this would fall back into a simple table scan for the original update on the original heap.

I also tried it with rowcounts of 100,000 and, on my humble box anyway, it did the same thing.

I hope Gail Shaw shows up on this one. Except for saying that the optimizer obviously thinks it's less costly to use the unrelated index than to use a table scan, I can't explain this one. I also think the optimizer made a bad choice here, as well. It's almost like it's doing some form of Triangular Join.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Vedran Kesegic SSCrazy Eights Points: 9302 More actions · Answer 6

It indeed scans myindex for no obvious reason, except to make a query a bit slower (bug in optimizer?).

However, this:

set statistics io on

update mytable set max_length = 88 -- 789 logical reads, scans myindex. Why? Bug?

update mytable set max_length = max_length-max_length+88 -- 3 logical reads, full table scan.

Add and substract a column and you have your full table scan (with just 3 logical reads) back.

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths