Geek City: What Triggered This Post?

Kalen Delaney — Tue, 01 Jan 2013 00:00:00 GMT

I’d really like to get another post up onto my much neglected blog before the end of 2012. This will also start one of my New Year’s resolutions, which is to write at least one blog post a month. I’m going to tell you about a change in SQL Server that wasn’t announced in any “What’s New” list that I ever saw, perhaps because it was just a chance in internal behavior, and nothing that required any change in user applications.

Do you retest what you know is true for every new version? When I update my books, I do test all the scripts, but if there isn’t a script, I don’t retest every ‘fact’ that I have known for years is true. And sometimes, things change. And sometimes my reviewers notice those unreported changes, and sometimes they don’t.

You might be aware of the fact that SQL Server can perform UPDATE operations in two different ways. The UPDATE can be performed as a two-step process: delete the old row and then insert a whole new row, or, the UPDATE can be performed (much more efficiently) as an update-in-place. When the two-step UPDATE is performed, it is a LOT more work. Not only does SQL Server have to log the entire old row and the entire new row, but each nonclustered index is also modified twice, and each of those index changes also has to be logged. So it’s nice when an update-in-place is done, because only the bytes changed are logged, and only indexes on the updated columns are affected.

Prior to SQL Server 7, there were actually four different ways that UPDATE could be done. The two-step UPDATE had some variations that could make it even slower in some cases! But that was a long time ago, so I’m not going to go into the details now. But I will say that back then, in order to get an update-in-place to occur, there was a big long list of prerequisites that had to be met and if you missed just one, you’d get one of the slower UPDATE operations.

As of SQL Server 7, update-in-place became the default. The only time it doesn’t happen is when the row can’t stay in the same location (such as when you update a clustered index key column) or when SQL Server really needs the old and new versions of the row.

In SQL 7, one of the places that SQL needed the old and new version of the updates rows was when processing triggers. Triggers need the transaction log to get the contents for the DELETED and INSERTED pseudo-tables. And because triggers needed the entire old and new versions of the updated rows, the UPDATE was performed as a two-step operation. DELETE the old row, log the entire old row, and the INSERT the new row with the new values, and log the entire new row.

But as of 2005, we now have the version store, primarily used for SNAPSHOT isolation, but available for other uses as well. In SNAPSHOT isolation, the version stores stores ‘old versions’ of rows that have been updated or deleted. I knew that the version store was also used for triggers, but it only occurred to me just recently that maybe, because the old and new versions of the row were not needed from the log, perhaps UPDATEs did not always need to be performed internally as a two-step UPDATE.

So I decided to test it out.

-- DEMO: If there is an UPDATE trigger, are updates logged as DELETE + INSERT?
-- First build a new database.

USE master;
GO
IF (SELECT db_id('TestTrigger')) IS NOT NULL
DROP DATABASE TestTrigger;
GO
CREATE DATABASE TestTrigger;
GO
ALTER DATABASE TestTrigger SET RECOVERY SIMPLE;
GO
SELECT db_id('TestTrigger');
GO

USE TestTrigger;
GO

-- Just for a warmup, look at the function fn_dblog, which works in the current database

SELECT * FROM fn_dblog(null, null);
GO

-- Create a new table to work with
IF (SELECT object_id('objects')) IS NOT NULL
DROP TABLE objects;
GO
SELECT TOP 100 * INTO objects FROM sys.objects;
GO

-- Create a clustered index on the table
CREATE CLUSTERED INDEX objects_clustered on objects(name);
GO

-- First examine an update we know is NOT done in place,
-- i.e. updating a clustered key value

UPDATE objects SET name = 'newrowsets' WHERE name = 'sysrowsets';
GO

-- Look at last 10 rows; notice a LOP_DELETE_ROWS and LOP_INSERT_ROWS
-- The AllocUniteName column shows the object affected is the clustered index on dbo.objects
SELECT Operation, [Transaction ID], AllocUnitName FROM fn_dblog(null, null);
GO

-- Now examine an update we know is done in place,
-- i.e. updating an unindexed column on a table with no triggers
UPDATE objects SET parent_object_id = 1 WHERE name = 'sysfiles1';
GO

-- Look at last 3 rows; notice a LOP_MODIFY_ROW on the dbo.objects allocation unit
SELECT Operation, [Transaction ID], AllocUnitName FROM fn_dblog(null, null);
GO

-- Create an update trigger
-- Will the update be done with the siple LOP_MODIFY_ROW or with the LOP_DELETE_ROWS and LOP_INSERT_ROWS
CREATE TRIGGER trg_update_objects ON objects FOR UPDATE
as
SELECT * FROM DELETED; SELECT * FROM INSERTED;
RETURN;
GO

-- Now perform update again
UPDATE objects SET parent_object_id = 10 WHERE name = 'sysfiles1';
GO

-- Look at last 3 rows; notice a LOP_MODIFY_ROW
SELECT * FROM fn_dblog(null, null);
GO

Since the database is in SIMPLE recovery model, you can issue a CHECKPOINT before each UPDATE if you want to reduce the number of rows in the log to make it easier to examine.

So it seems that I need to update my course and some of my writings. There might also be special cases that still require that an two-step UPDATE be performed in the presence of triggers, but it seems like a two-step UPDATE is not ALWAYS required anymore. That is very good news!

I hope you all have a wonder-filled and joyous New Year!

~Kalen

Search results matching tag 'update-in-place'

Geek City: What Triggered This Post?