Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

UPDATE when the values are the same Expand / Collapse
Author
Message
Posted Monday, August 13, 2012 10:30 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:10 PM
Points: 4,576, Visits: 8,349
GilaMonster (8/13/2012)
As far as I can tell, updating a row and not actually changing the values does not mark the page as dirty, meaning it won't be written back to disk when the checkpoint process runs.

Actually, it does.

I can offer you a simple test.
Create a new database with FULL recovery mode. Create a single table in it.
Insert several rows of data.
Then run a script having a loop with a single statement:

UPDATE MyTable
Set ColValue = ColValue

And watch the Log file growing.
What means that pages are certainly written to disk.
Post #1344520
Posted Tuesday, August 14, 2012 2:15 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:52 PM
Points: 42,849, Visits: 35,978
Sergiy (8/13/2012)
GilaMonster (8/13/2012)
As far as I can tell, updating a row and not actually changing the values does not mark the page as dirty, meaning it won't be written back to disk when the checkpoint process runs.

Actually, it does.

I can offer you a simple test.
Create a new database with FULL recovery mode. Create a single table in it.
Insert several rows of data.
Then run a script having a loop with a single statement:

UPDATE MyTable
Set ColValue = ColValue

And watch the Log file growing.
What means that pages are certainly written to disk.


Nope. There will be operations, like checkpoints, transactions, background processes, but the update itself is neither logged (it's not actually a data modification) nor are the pages dirtied in the process.

Trivial to prove.

CREATE TABLE [dbo].[Test](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](100) NULL
) ON [PRIMARY]

insert into Test (Name) Values ('Tom')
Checkpoint -- all dirty pages to disk, truncate log

Now, identify the page no of that table's data page (exercise left for the reader)

-- update the row to itself
CHECKPOINT -- just to be sure and to allow for multiple tests
BEGIN TRANSACTION
UPDATE test SET name = name

SELECT [Current LSN] ,
Operation ,
Context ,
[Transaction ID] ,
AllocUnitId ,
AllocUnitName ,
[Checkpoint Begin] ,
[Num Transactions] ,
[Checkpoint End] ,
[Dirty Pages] ,
[Log Record] FROM fn_dblog(NULL, null)

SELECT * FROM sys.dm_os_buffer_descriptors AS bd WHERE page_id = '3783' -- the sole data page for this table
COMMIT

Results:


The log has nothing more than the checkpoint and the begin transaction, there's no LOP_MODIFY_ROW which would be there if the update was logged. (if the query had been after the commit, there's be a LOP_COMMIT_XACT as well). It's the checkpoint, begin and commit which would have caused the log to grow in your test.

The DMV showing the state of the pages in memory shows that the page that the table's sole row it on is unmodified after the update.

Now there will likely be cases where this is not true, but for this trivial case, updating the row to itself neither dirties the page nor logs the update.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass



  Post Attachments 
2012-08-14 10-07-28.jpg (412 views, 90.93 KB)
Post #1344562
Posted Tuesday, August 14, 2012 8:31 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:10 PM
Points: 4,576, Visits: 8,349
CHECKPOINT -- just to be sure and to allow for multiple tests

DECLARE @I int
SET @I = 10

WHILE @I > 0
BEGIN
BEGIN TRANSACTION
UPDATE test SET name = name

SELECT [Current LSN] ,
Operation ,
Context ,
[Transaction ID] ,
AllocUnitId ,
AllocUnitName ,
[Checkpoint Begin] ,
[Num Transactions] ,
[Checkpoint End] ,
[Dirty Pages] ,
[Log Record] FROM fn_dblog(NULL, null)

COMMIT

SET @I = @I - 1

END


Now - run it and watch the number of records in fn_dblog growing by 2 after each cycle.
Post #1345109
Posted Wednesday, August 15, 2012 1:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:52 PM
Points: 42,849, Visits: 35,978
Yes as I showed in the screenshot in the post right above there's the begin xact and commit xact log records (which would be your two per cycle).

This is what's is in the log after that test of 10 updates:

The update itself is not logged. If it was, there would be a LOP_MODIFY_ROW for each update (10 of them), which there is not.
(btw, you'd get the begin and commit even if there was no explicit begin and commit, as all modifications are in implicit transactions)

If I change the query and update name to "Bob", the log records look like this


That Lop_Modify_Row is the log record for the actual update, and it is missing in the case where the row is updated to itself, hence we can conclude that the update is not logged when updating the value to itself.

The question was not, does the update cause any log records to be written to the log. The question was, does the update dirty the page if the update makes no changes. It does not, as I showed with the DMV previously.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass



  Post Attachments 
2012-08-15 09-38-24.jpg (380 views, 78.06 KB)
Log records.jpg (374 views, 264.93 KB)
Post #1345143
Posted Wednesday, August 15, 2012 2:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 22, 2012 6:07 AM
Points: 5, Visits: 9
The log file increaseing in size merely implies the mdf file is being written to, it's not really conclusive evidence that it is being writtent to. Just because one grows it doesn't mean the other must surely.
Post #1345153
Posted Wednesday, August 15, 2012 9:03 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:10 PM
Points: 4,576, Visits: 8,349
GilaMonster (8/15/2012)

The question was not, does the update cause any log records to be written to the log. The question was, does the update dirty the page if the update makes no changes. It does not, as I showed with the DMV previously.


I would not be so sure.
I changed the script to bring out more info:
USE [TEST] 
GO
CREATE TABLE [dbo].[Test](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](100) NULL
) ON [PRIMARY]
insert into Test (Name) Values ('TOM')
insert into Test (Name) Values ('Andy')
insert into Test (Name) Values ('Bob')
insert into Test (Name) Values ('Tim')
Checkpoint -- all dirty pages to disk, truncate log

SELECT OBJECT_ID('[dbo].[Test]') ObjID, * FROM TEST

Checkpoint -- all dirty pages to disk, truncate log

UPDATE test
SET name = 'Tom'
WHERE NAME = 'Tom'

SELECT [Current LSN] ,
Operation ,
Context ,
[Transaction ID] ,
AllocUnitId ,
AllocUnitName ,
[Checkpoint Begin] ,
[Num Transactions] ,
[Checkpoint End] ,
[Dirty Pages] ,
[Log Record] FROM fn_dblog(NULL, null)

Checkpoint -- all dirty pages to disk, truncate log
SELECT * FROM TEST

DECLARE @I int
SET @I = 4

WHILE @I > 0
BEGIN

BEGIN TRANSACTION
UPDATE test
SET name = NAME
WHERE @I%2 = 0 OR name <> NAME

EXEC sp_lock

COMMIT
SELECT [Current LSN] ,
Operation ,
Context ,
[Transaction ID] ,
AllocUnitId ,
AllocUnitName ,
[Checkpoint Begin] ,
[Num Transactions] ,
[Checkpoint End] ,
[Dirty Pages] ,
[Log Record] FROM fn_dblog(NULL, null)

Checkpoint -- all dirty pages to disk, truncate log



SET @I = @I - 1

END

GO

Please run the code with Execution Plan recorded.

1. Define "the same".
the code
UPDATE test 
SET name = 'Tom'
WHERE NAME = 'Tom'

updates the value with the same, according to the column definition.
But for some reason SQL Server ignores the collation settings and actually writes the page.
So, it's not "the same" by data definition, it's "the same" by binary contents of the page(s).

2. Execution plan shows that "even" updates have 4 records committed to the UPDATE part, and "odd" ones have 0 records committed.

3. Messages show "(4 row(s) affected)" for "even" updates and "(0 row(s) affected)" for "odd" updates.

4. SP_LOCK indicates exclusive locks applied on each of 4 records in "even" updates, when condition name <> NAME is not applied.
To me it means that SQL Server treats those records as perfectly "dirty".

5. "Even" updates add LOP_BEGIN_XACT and LOP_COMMIT_XACT to the log, when "Odd" ones do not do this.

But you're right - those updates don't end up in the log file.

Considering all of the above I can see only one explanation:

1. UPDATE actually writes to the pages, regardless if the new values are the same or different from the old values.
But it writes to the pages in memory.

2. CHECKPOINT compares binary contents of the pages in memory and writes to disk only those ones which have become different after completed transactions.
When same values are updated there is a record about a committed transaction, so CHECKPOINT has to verify the dirty pages and write to the disk any ones that have been changed. When the query filters out the same values there is no record about a committed transaction, so CHECKPOINT will not even start analysing contents of the pages.

3. DMV shows the outcome of CHECKPOINT, not UPDATE, therefore the pages updated in memory don't appear there.
Post #1345639
Posted Thursday, August 16, 2012 1:54 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:52 PM
Points: 42,849, Visits: 35,978
Sergiy (8/15/2012)
1. UPDATE actually writes to the pages, regardless if the new values are the same or different from the old values.
But it writes to the pages in memory.


I disagree. If it did, the page would be marked dirty after the update. It is not as I showed with the DMV (I can prove the same thing with DBCC Page, it's just a lot harder)

2. CHECKPOINT compares binary contents of the pages in memory and writes to disk only those ones which have become different after completed transactions.


Checkpoint just writes dirty pages to disk. It doesn't check the pages and it's quite happy to write out pages dirtied by transactions that have not committed.
If checkpoint had to take the page in memory, read the matching page from disk, compare the two and only write out changed pages it would be massively less efficient than it is and would require a lot of extra memory.

3. DMV shows the outcome of CHECKPOINT, not UPDATE, therefore the pages updated in memory don't appear there.


The DMV is buffer descriptors (ie buffer pool), it's a dmv that shows pages in memory, it does not have anything to do with checkpoint, it does not look at the pages on disk.
Per BoL:
Returns information about all the data pages that are currently in the SQL Server buffer pool. The output of this view can be used to determine the distribution of database pages in the buffer pool according to database, object, or type.

and
is_modified bit 1 = Page has been modified after it was read from the disk.


As I said earlier, the update runs, it marks all rows as updated in the rows affected (even in cases it didn't change the values), those rows will appear in the trigger pseudo-tables. Locks will have to be taken, execution plan will have to run the update, SQL doesn't know the rows are the same until the update actually runs. There is simply an optimisation that does not log updates where the value has not changed and does not mark the pages as dirty until a value on it is actually changed.

Your odd examples, no rows qualify for the update at all so there's no transaction to begin or commit and no rows to take locks on. Hence SQL can optimise away the update even earlier and not even begin a transaction.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1345710
Posted Thursday, August 16, 2012 3:03 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 27, 2014 4:17 AM
Points: 75, Visits: 443
This theme was covered in details here
I encourage you to read it.Paul White: Page Free Space - The Impact of Non-Updating Updates



I am really sorry for my poor gramma. And I hope that value of my answers will outweigh the harm for your eyes.
Blog: http://somewheresomehow.ru
Twitter: @SomewereSomehow
Post #1345761
Posted Thursday, August 16, 2012 3:30 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:10 PM
Points: 4,576, Visits: 8,349
GilaMonster (8/16/2012)
until the update actually runs.

What do you mean by that?
What's exactly happening when update is running?
does not log updates where the value has not changed and does not mark the pages as dirty until a value on it is actually changed.

Value changed by what?
What's happening during update that it's necessary to check if "a value on it is actually changed"?
Post #1345774
Posted Thursday, August 16, 2012 3:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:52 PM
Points: 42,849, Visits: 35,978
Update runs = query execution engine processes the update plan that was generated by the query optimiser. Runs the various operators in the query plan

Value changed by the query execution engine processing the update. It's just an optimisation that SQL has not to do unnecessary work when an update does not change the value of the column

Edit: See Paul's blog post, he comes to the same conclusions I did.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1345779
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse