UPDATE when the values are the same

Question

UPDATE when the values are the same

Viewing 15 posts - 46 through 60 (of 159 total)

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

Paul White SSC Guru Points: 150468 More actions · Answer 1

Sergiy (8/23/2012)
Talking about real world. I mentioned at the beginning of this thread - I managed to remove 8+GB from daily LOG file drowth on a database simply by adding WHERE Status <> 0 to the UPDATE Status = 0 statement called within a regular Agent job. 8+GB of useless data. Every day. On a database of 1.5 GB. Clogging data and backup repositories, causing performance issues, etc. Because of a single non-update UPDATE. Is it "real world" enough for you?

Yes I see, and that reply caused the questioner to respond, "Ask a simple yes/no question and get a few tangent replies, damn.". No-one is arguing against the idea that SQL should be written to only affect records that need updating. That is obvious.

That aside, as the questioner said, "Does the SQL Server edit the MDF file and overwrite the current value of that record or does it carry out a check to see if their the same to save writing to the HDD something that's already there."

So the question was whether updating to the same value dirties the page and causes it to be written to disk. Your reply was that it does, which you were wrong about. Somehow, we have ended up debating whether the page is memory is written to or not.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Paul White SSC Guru Points: 150468 More actions · Answer 2

Sergiy (8/16/2012)
Like I said from the beginning, it's the storage engine (I name it CHECKPOINT, probably not the best choice of word)...

Probably not the best choice of word? Well, it's completely wrong, so I guess that counts. CHECKPOINT is a completely separate process.

...which checks the content of the UPDATED PAGE and finds out that the content is still the same as in the version of the page stored on the disk, so there is no point in flushing it to disk and add a record to the log file.

The documented behaviour is that CHECKPOINT scans BUF structures looking for those with the is_dirty flag set. The storage engine sets the is_dirty bit when it changes a page. CHECKPOINT writes those pages to persistent storage based on that flag alone, there is no comparison with on-disk pages. That would be horribly inefficient as Gail told you.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

MissTippsInOz Hall of Fame Points: 3160 More actions · Answer 3

Such passion for our topic!! It amused me to note that the original poster announced he'd got the answer he required 9 days ago now and has, presumably, gone off to do something worthwhile 😀

Meanwhile the debate rages. Still I'm not quite sure why...it seems so clear to me.

toddasd SSCertifiable Points: 6298 More actions · Answer 4

MissTippsInOz (8/24/2012)
Such passion for our topic!! It amused me to note that the original poster announced he'd got the answer he required 9 days ago now and has, presumably, gone off to do something worthwhile 😀
Meanwhile the debate rages. Still I'm not quite sure why...it seems so clear to me.

This is why: http://xkcd.com/386/

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

Paul White SSC Guru Points: 150468 More actions · Answer 5

toddasd (8/24/2012)
This is why: http://xkcd.com/386/

No it's not that. The original questioner has his answer, but educational opportunities remain in this thread.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

MissTippsInOz (8/24/2012)
Such passion for our topic!! It amused me to note that the original poster announced he'd got the answer he required 9 days ago now and has, presumably, gone off to do something worthwhile 😀
Meanwhile the debate rages. Still I'm not quite sure why...it seems so clear to me.

Sayre's law: "In any dispute the intensity of feeling is inversely proportional to the value of the issues at stake."

http://en.wikipedia.org/wiki/Sayre's_law

Sergiy SSC Guru Points: 110208 More actions · Answer 7

SQL Kiwi (8/23/2012)
Sergiy,
A transaction is indeed started and an exclusive lock is taken in preparation for an update, but how do you know for sure that the page is actually written to?

I know. For sure. How? In a scientific way. More to the point: why do you care? 😛

Can you prove my knowledge is wrong?

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 8

SQL Kiwi (8/24/2012)
That aside, as the questioner said, "Does the SQL Server edit the MDF file and overwrite the current value of that record or does it carry out a check to see if their the same to save writing to the HDD something that's already there."

Where did you find that?

Get to the 1st page:

Does an UPDATE query overwrite existing values if the already existing value is the same as the one your updating with?

Where did you find "MDF", "HDD" and other stuff from your version of the question???

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 9

SQL Kiwi (8/24/2012)
So the question was whether updating to the same value dirties the page and causes it to be written to disk. Your reply was that it does, which you were wrong about. Somehow, we have ended up debating whether the page is memory is written to or not.

Can't see where the OP was asking about that.

And can't see where in my answer to OP I mentioned anything about dirty pages.

The matter of dirty pages was brought into the topic by Gila Monster and yourself.

That's how we ended up discussing writing page memory to disk.

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 10

SQL Kiwi (8/24/2012)

The storage engine sets the is_dirty bit when it changes a page.

If you'd only understand your own words...

:hehe:

Check the lock applied during the transactions.

They are all ROW ones.

And Storage Engine, as you say, marks page dirty if the page is changed.

The UPDATE query applies changes to the rows.

Row <> Page.

Same row may be allocated over differen pages.

Same page can contain many rows.

Figuring out which pages have become dirty as a result of the UPDATE is a totally different matter. The check can be carried on only after UPDATE is finished and updated versions of affected pages are present.

_____________
Code for TallyGenerator

Paul White SSC Guru Points: 150468 More actions · Answer 11

Sergiy (8/26/2012)
Where did you find "MDF", "HDD" and other stuff from your version of the question???

OP's third post. http://www.sqlservercentral.com/Forums/FindPost1344046.aspx

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Sergiy SSC Guru Points: 110208 More actions · Answer 12

MissTippsInOz (8/24/2012)
Such passion for our topic!!

Be passioned about what you do should be a good thing, right?

🙂

It amused me to note that the original poster announced he'd got the answer he required 9 days ago

Oh, about that... :unsure:

Meanwhile the debate rages. Still I'm not quite sure why...

Does it? OK, I'm not sure either.

it seems so clear to me.

Lucky you!

Was not so clear to me, as it turns out.

Learned couple of things here, thanks to the good samaritans.

:hehe:

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 13

SQL Kiwi (8/24/2012)

Sergiy (8/23/2012) No-one is arguing against the idea that SQL should be written to only affect records that need updating. That is obvious.

Are you sure? Why?

It was not that obvious to the OP.

It was not obvious to CELKO.

Can you put some solid grounds under you "obvious" comment?

_____________
Code for TallyGenerator

Paul White SSC Guru Points: 150468 More actions · Answer 14

Sergiy (8/26/2012)
Was not so clear to me, as it turns out. Learned couple of things here, thanks to the good samaritans.

Good. Well the last (important) thing to be learned is that the storage engine does not maintain an 'old version' of the page to compare with the 'new version' after the update. The engine knows whether it had to make any changes or not, and simply sets the is_dirty bit if so. As Gail mentioned, the way you have been thinking it works would be horribly inefficient. If the engine knows that it didn't have to update any bytes on the page, it doesn't set the dirty bit. That's what I was explaining to you when I said:

"Say we are 'updating' from 'tom' to 'tom'; how do you know that the storage engine doesn't simply compare what is already there on the page (bytes 0x546F6D) with what the query processor has asked it to update the value to (also bytes 0x546F6D) and simply not bother doing anything? That explains the observed behaviour just as well, doesn't it?"

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Sergiy SSC Guru Points: 110208 More actions · Answer 15

SQL Kiwi (8/26/2012)
That's what I was explaining to you when I said:
"Say we are 'updating' from 'tom' to 'tom'; how do you know that the storage engine doesn't simply compare what is already there on the page (bytes 0x546F6D) with what the query processor has asked it to update the value to (also bytes 0x546F6D) and simply not bother doing anything? That explains the observed behaviour just as well, doesn't it?"

You failed to explain how does it compare pages while the locks are applied to RID.

And at which stage "Tom" is translated to 0x546F6D.

To perform a valid comparison SQL Server needs to apply at least shared lock to the page, to make sure no other records occupiyng other parts of the page are changed by some other process.

_____________
Code for TallyGenerator