Reads too high on indexed-table update

Question

Post reply

Reads too high on indexed-table update

neil-560592

SSCommitted

Points: 1557
More actions
November 22, 2012 at 7:21 am

#285084

I'm trying to understand why SQL Profiler shows high values of reads in the following scenario:
Run the following code to add around 600 rows of data to a table, and add an index to one of the columns:
use master
go
select object_id, system_type_id, max_length INTO mytable
from sys.columns
CREATE NONCLUSTERED INDEX [myindex] ON [dbo].[mytable]
(
[system_type_id] ASC
)
Then with SQL Profiler running, run:
select * from mytable
It (correctly) says reads are around 6 (6 * 8Kb pages = 48Kb, which is a little above the amount of data retreived by the query).
But now run:
update mytable
set max_length = 88
This time the reads value is much higher, around 700, which implies 700 * 8Kb = 5600 Kb
Yet the total size of the table + index is much less than 5600 Kb, so why is SQL Server apparently reading so much more data than it would appear to need?
When this occurs, it is doing an index scan to find the rows, then a table update.
Sometimes I can get SQL Server to switch to doing it as a table scan to find the rows, then a table update to update them, and in this situation reads drops down to around 6 again.
And if I delete the index reads drops down to 6.
What's happening here?
(This is a simplified version of a problem I'm looking at, albeit on a much larger scale).
Thanks
http://90.212.51.111 domain

Viewing 7 posts - 1 through 6 (of 6 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: 1003857 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: 1003857 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