• 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, MVP, M.Sc (Comp Sci)
    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