{"id":471,"date":"2012-09-20T02:28:00","date_gmt":"2012-09-20T02:28:00","guid":{"rendered":"\/blogs\/jonathan\/post\/Looking-at-Non-Updating-Updates-with-Extended-Events.aspx"},"modified":"2017-04-13T14:40:51","modified_gmt":"2017-04-13T18:40:51","slug":"looking-at-non-updating-updates-with-extended-events","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/looking-at-non-updating-updates-with-extended-events\/","title":{"rendered":"Looking at Non-Updating Updates with Extended Events"},"content":{"rendered":"

\nFor a few weeks now, there has been a long running thread on SQL Server Central<\/a> about UPDATE operations that don't perform any changes because the statement sets a column to the same value as already exists in the row.  While I believe that one of the posters is incredibly snarky and outright rude to the people trying to answer the question and explain the behaviors being noticed, the topic is quite interesting and I finally got some time on my flight to Seattle to take a look at some of the internals of this with Extended Events in SQL Server 2012. \n<\/p>\n

Setting up the Tests<\/h2>\n

\nTo setup the tests to be performed I am going to use the example table and row of data that are provided in that thread for the investigation with the exception that I am going to use a user database instead of tempdb for holding the test table so that I can create separate tests for a case-sensitive collation versus case-insensitive collations.\n<\/p>\n

\n

\n\t— Create the case-insensitive database
\n\tCREATE DATABASE TestUpdate COLLATE SQL_Latin1_General_CP1_CI_AI
\n\tGO
\n\tUSE TestUpdate;
\n\tGO
\n\tCREATE TABLE Test
\n\t(
\n\t    ID         INT IDENTITY(1,1) NOT NULL,
\n\t    Name    VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL
\n\t);
\n\tGO
\n\tINSERT dbo.Test
\n\t    (Name)
\n\tVALUES
\n\t    ('Tom');
\n\tGO
\n\tCHECKPOINT;
\n\tGO\n\t<\/p>\n

\n\t— Create the case-sensitive database
\n\tCREATE DATABASE [TestUpdateCS] COLLATE SQL_Latin1_General_CP1_CS_AS
\n\tGO
\n\tUSE TestUpdateCS;
\n\tGO
\n\tCREATE TABLE Test
\n\t(
\n\t    ID      INT IDENTITY(1,1) NOT NULL,
\n\t    Name    VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL
\n\t);
\n\tGO
\n\tINSERT dbo.Test
\n\t    (Name)
\n\tVALUES
\n\t    ('Tom');
\n\tGO
\n\tCHECKPOINT;
\n\tGO\n\t<\/p>\n<\/blockquote>\n

\nThen to setup the Extended Events session for collecting information about the operations being performed, we are going to collect a number of events in the engine along with the callstack for each even and the filter on the session_id where the tests are actually being run to prevent collecting data that is not related to the tests being executed.  I originally started with nearly double the events, but removed the events that were not firing for any of the tests from the final event session shown below.\n<\/p>\n

\n

\n\tIF EXISTS (SELECT 1 FROM sys.server_event_sessions
\n\t            WHERE name = 'SQLskills_InvestigatingUpdates')
\n\tBEGIN
\n\t    DROP EVENT SESSION [SQLskills_InvestigatingUpdates] ON SERVER;
\n\tEND
\n\tGO
\n\tCREATE EVENT SESSION [SQLskills_InvestigatingUpdates]
\n\tON SERVER
\n\t— Track sparse file writes for snapshots
\n\tADD EVENT sqlserver.file_written_to_replica(
\n\t    ACTION(package0.callstack,sqlserver.session_id)
\n\t    WHERE ([sqlserver].[session_id]=(62))),
\n\t— Track memory page allocations
\n\tADD EVENT sqlos.page_allocated(
\n\t    ACTION(package0.callstack,sqlserver.session_id)
\n\t    WHERE ([sqlserver].[session_id]=(62))),
\n\tADD EVENT sqlos.page_freed(
\n\t    ACTION(package0.callstack,sqlserver.session_id)
\n\t    WHERE ([sqlserver].[session_id]=(62))),
\n\t— Track latching
\n\tADD EVENT sqlserver.latch_acquire_time(
\n\t    ACTION(package0.callstack,sqlserver.session_id)
\n\t    WHERE ([sqlserver].[session_id]=(62))),
\n\tADD EVENT sqlserver.latch_suspend_begin(
\n\t    ACTION(package0.callstack,sqlserver.session_id)
\n\t    WHERE ([sqlserver].[session_id]=(62))),
\n\tADD EVENT sqlserver.latch_suspend_end(
\n\t    ACTION(package0.callstack,sqlserver.session_id)
\n\t    WHERE ([sqlserver].[session_id]=(62))),
\n\t— Track locking
\n\tADD EVENT sqlserver.lock_acquired(
\n\t    SET collect_database_name = 1
\n\t    ACTION(package0.callstack,sqlserver.session_id)
\n\t    WHERE ([sqlserver].[session_id]=(62))),
\n\tADD EVENT sqlserver.lock_released(
\n\t    SET collect_database_name = 1
\n\t    ACTION(package0.callstack,sqlserver.session_id)
\n\t    WHERE ([sqlserver].[session_id]=(62))),
\n\t— Track transaction log activity
\n\tADD EVENT sqlserver.log_flush_requested(
\n\t    ACTION(package0.callstack,sqlserver.session_id)
\n\t    WHERE ([sqlserver].[session_id]=(62))),
\n\tADD EVENT sqlserver.log_single_record(
\n\t    ACTION(package0.callstack,sqlserver.session_id)
\n\t    WHERE ([sqlserver].[session_id]=(62))),
\n\tADD EVENT sqlserver.transaction_log(
\n\t    ACTION(package0.callstack,sqlserver.session_id)
\n\t    WHERE ([sqlserver].[session_id]=(62))),
\n\t— Track batch and statement activity
\n\tADD EVENT sqlserver.sql_batch_completed(
\n\t    ACTION(package0.callstack,sqlserver.session_id)
\n\t    WHERE ([sqlserver].[session_id]=(62))),
\n\tADD EVENT sqlserver.sql_batch_starting(
\n\t    ACTION(package0.callstack,sqlserver.session_id)
\n\t    WHERE ([sqlserver].[session_id]=(62))),
\n\tADD EVENT sqlserver.sql_statement_completed(
\n\t    ACTION(package0.callstack,sqlserver.session_id)
\n\t    WHERE ([sqlserver].[session_id]=(62))),
\n\tADD EVENT sqlserver.sql_statement_starting(
\n\t    ACTION(package0.callstack,sqlserver.session_id)
\n\t    WHERE ([sqlserver].[session_id]=(62)))
\n\t— Store event data in ring_buffer target
\n\tADD TARGET package0.ring_buffer;
\n\tGO\n\t<\/p>\n<\/blockquote>\n

Testing Methodolgy<\/h2>\n

\nFrom the original thread on SQL Server Central, there are a couple of sticking points that keep getting brought up so in the interests of attempting to be thorough I am going to provide the testing methodology being used, and then list all of the tests that are going to be run. \n<\/p>\n

    \n
  1. All tests will be conducted against both of the databases to identify behavior changes between CS and CI collations if they occur.  <\/li>\n
  2. At the start of every test, the data in the tables will have 'Tom' for the Name column.  If a previous test has changed the value to something different it will be reset before the test is executed. <\/li>\n
  3. The event session will be stopped and started between each test to isolate the data collect to each individual test. <\/li>\n
  4. At least one minute of time will be waited for between tests to allow the background thread that performs row version cleanup to execute and remove row versions that may have been created by the tests. (http:\/\/msdn.microsoft.com\/en-us\/library\/ms175492(v=sql.105).aspx<\/a>) <\/li>\n
  5. All of the tests will be run once initially to allow the execution plan to be generated and cached in the plan cache, and then the test will be reset and rerun so that the event session doesn't capture the page allocations and locking\/latching associated with compiling\/caching the plans. <\/li>\n<\/ol>\n

    Tests<\/h2>\n

    \nThe tests that will be performed are as follows:\n<\/p>\n

      \n
    1. Determine the way that SQL Server handles UPDATE statements that set the column to the same value that previously existed without filtering.
      \n
      \n

      \n\t\t— Non-updating update
      \n\t\tUPDATE dbo.Test
      \n\t\tSET Name = Name;
      \n\t\tGO\n\t\t<\/p>\n<\/blockquote>\n

      \n

      \n\t\t— Non-updating update
      \n\t\tUPDATE dbo.Test
      \n\t\tSET Name = 'Tom';
      \n\t\tGO\n\t\t<\/p>\n<\/blockquote>\n<\/li>\n

    2. Determine the way that SQL Server handles UPDATE statements that change the value that previously existed without filtering.
      \n
      \n

      \n\t\t— Updating update
      \n\t\tUPDATE dbo.Test
      \n\t\tSET Name = 'TOM';
      \n\t\tGO\n\t\t<\/p>\n<\/blockquote>\n<\/li>\n

    3. Determine the way that SQL Server handles UPDATE statements that set the column to the same value that previously existed with filtering.
      \n
      \n

      \n\t\t— Non-Updating with filter
      \n\t\tUPDATE dbo.Test
      \n\t\tSET Name = Name
      \n\t\tWHERE Name != 'Tom';
      \n\t\tGO\n\t\t<\/p>\n

      \n\t\t— Non-Updating with filter
      \n\t\tUPDATE dbo.Test
      \n\t\tSET Name = 'Tom'
      \n\t\tWHERE Name != 'Tom';
      \n\t\tGO\n\t\t<\/p>\n<\/blockquote>\n<\/li>\n

    4. Determine the way that SQL Server handles UPDATE statements that change the value that previously existed with filtering.
      \n
      \n

      \n\t\t— Updating update
      \n\t\tUPDATE dbo.Test
      \n\t\tSET Name = 'TOM'
      \n\t\tWHERE Name != 'TOM';
      \n\t\tGO\n\t\t<\/p>\n<\/blockquote>\n<\/li>\n<\/ol>\n

      \nAll of these tests will be run against the CS and CI database.  Additionally after the first tests are performed against a table without a trigger, the tests will be repeated with the following FOR\/AFTER UPDATE trigger on the tables.\n<\/p>\n

      \n

      \n\tCREATE TRIGGER trg_AuditChanges_Test
      \n\tON Test
      \n\tFOR UPDATE
      \n\tAS
      \n\tBEGIN
      \n\tDECLARE @ins INT;
      \n\tDECLARE @del INT;\n\t<\/p>\n

      \n\tSELECT @ins = COUNT(*)
      \n\tFROM inserted;\n\t<\/p>\n

      \n\tSELECT @del = COUNT(*)
      \n\tFROM deleted; <\/p>\n

      \tEND
      \n\tGO\n\t<\/p>\n<\/blockquote>\n

      \nI am not going to include the 16 frame callstack in the results in this post except where it provides useful information for the investigation of the results being collected.  The callstack results in this post will be materialized through the use of Trace Flag 3656 and the public symbols as I explained in my blog post about the callstack action in the XEvent a Day series, An XEvent a Day (24 of 31) – What is the callstack?<\/a>.\n<\/p>\n

      Results No Trigger<\/h2>\n

      \nFor both of the UPDATE statements in test number 1, the following events ( they are identical for both scenarios ) are generated by the event session.  Note that the locks acquired include a conversion of the U lock on the RID to an X lock as a part of the execution,  and even though the UPDATE doesn't actually make any changes, two log records are generated for the BEGIN_XACT and COMMIT_XACT, and these have to also be flushed to the transaction log file, as shown by the log_flush_requested event.\n<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
      event_name<\/strong><\/font><\/font><\/td>\nlock_resource_type<\/strong><\/font><\/font><\/td>\nlock_mode<\/strong><\/font><\/font><\/td>\nstatus<\/strong><\/font><\/font><\/td>\nlog_op_name<\/strong><\/font><\/font><\/td>\ncontext<\/strong><\/font><\/font><\/td>\ndatabase_id<\/strong><\/font><\/font><\/td>\nresource_0<\/strong><\/font><\/font><\/td>\n<\/tr>\n
      sql_batch_starting<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      sql_statement_starting<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      lock_acquired<\/font><\/font><\/td>\nOBJECT<\/font><\/font><\/td>\nIX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\n245575913<\/font><\/font><\/td>\n<\/tr>\n
      latch_acquire_time<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nHASHED, ONLRU<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      lock_acquired<\/font><\/font><\/td>\nPAGE<\/font><\/font><\/td>\nIU<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\n228<\/font><\/font><\/td>\n<\/tr>\n
      lock_acquired<\/font><\/font><\/td>\nRID<\/font><\/font><\/td>\nU<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\n228<\/font><\/font><\/td>\n<\/tr>\n
      lock_acquired<\/font><\/font><\/td>\nPAGE<\/font><\/font><\/td>\nIX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\n228<\/font><\/font><\/td>\n<\/tr>\n
      lock_acquired<\/font><\/font><\/td>\nRID<\/font><\/font><\/td>\nX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\n228<\/font><\/font><\/td>\n<\/tr>\n
      log_single_record<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nLOP_BEGIN_XACT<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      transaction_log<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nLOP_BEGIN_XACT<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      lock_released<\/font><\/font><\/td>\nRID<\/font><\/font><\/td>\nNL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\n228<\/font><\/font><\/td>\n<\/tr>\n
      lock_released<\/font><\/font><\/td>\nPAGE<\/font><\/font><\/td>\nNL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\n228<\/font><\/font><\/td>\n<\/tr>\n
      log_single_record<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nLOP_COMMIT_XACT<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      transaction_log<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nLOP_COMMIT_XACT<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      log_flush_requested<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      lock_released<\/font><\/font><\/td>\nRID<\/font><\/font><\/td>\nX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\n228<\/font><\/font><\/td>\n<\/tr>\n
      lock_released<\/font><\/font><\/td>\nPAGE<\/font><\/font><\/td>\nIX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\n228<\/font><\/font><\/td>\n<\/tr>\n
      lock_released<\/font><\/font><\/td>\nOBJECT<\/font><\/font><\/td>\nIX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\n245575913<\/font><\/font><\/td>\n<\/tr>\n
      sql_statement_completed<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      sql_batch_completed<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

      \nIf we compare this to the UPDATE statement that actually performs a change to the data in the row from test number 2, we will see that additional log records are generated for the MODIFY_ROW operation, and then flushed to the log file.\n<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
      event_name<\/strong><\/font><\/font><\/td>\nlock_resource_type<\/strong><\/font><\/font><\/td>\nlock_mode<\/strong><\/font><\/font><\/td>\nstatus<\/strong><\/font><\/font><\/td>\nlog_op_name<\/strong><\/font><\/font><\/td>\ncontext<\/strong><\/font><\/font><\/td>\ndatabase_id<\/strong><\/font><\/font><\/td>\nresource_0<\/strong><\/font><\/font><\/td>\n<\/tr>\n
      sql_batch_starting<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      sql_statement_starting<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      lock_acquired<\/font><\/font><\/td>\nOBJECT<\/font><\/font><\/td>\nIX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\n245575913<\/font><\/font><\/td>\n<\/tr>\n
      lock_acquired<\/font><\/font><\/td>\nPAGE<\/font><\/font><\/td>\nIU<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\n228<\/font><\/font><\/td>\n<\/tr>\n
      lock_acquired<\/font><\/font><\/td>\nRID<\/font><\/font><\/td>\nU<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\n228<\/font><\/font><\/td>\n<\/tr>\n
      lock_acquired<\/font><\/font><\/td>\nPAGE<\/font><\/font><\/td>\nIX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\n228<\/font><\/font><\/td>\n<\/tr>\n
      lock_acquired<\/font><\/font><\/td>\nRID<\/font><\/font><\/td>\nX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\n228<\/font><\/font><\/td>\n<\/tr>\n
      log_single_record<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nLOP_BEGIN_XACT<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      transaction_log<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nLOP_BEGIN_XACT<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      log_single_record<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nLOP_MODIFY_ROW<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      transaction_log<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nLOP_MODIFY_ROW<\/font><\/font><\/td>\nHEAP<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      lock_released<\/font><\/font><\/td>\nRID<\/font><\/font><\/td>\nNL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\n228<\/font><\/font><\/td>\n<\/tr>\n
      lock_released<\/font><\/font><\/td>\nPAGE<\/font><\/font><\/td>\nNL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\n228<\/font><\/font><\/td>\n<\/tr>\n
      log_single_record<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nLOP_COMMIT_XACT<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      transaction_log<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nLOP_COMMIT_XACT<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      log_flush_requested<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      lock_released<\/font><\/font><\/td>\nRID<\/font><\/font><\/td>\nX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\n228<\/font><\/font><\/td>\n<\/tr>\n
      lock_released<\/font><\/font><\/td>\nPAGE<\/font><\/font><\/td>\nIX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\n228<\/font><\/font><\/td>\n<\/tr>\n
      lock_released<\/font><\/font><\/td>\nOBJECT<\/font><\/font><\/td>\nIX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\n245575913<\/font><\/font><\/td>\n<\/tr>\n
      sql_statement_completed<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      sql_batch_completed<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

      \nFor the UPDATE statement in test number 3 where a WHERE clause is applied to check the column value against the value being set to ensure that they are not the same values, the results are different as shown below:\n<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n
      event_name<\/strong><\/font><\/font><\/td>\nlock_resource_type<\/strong><\/font><\/font><\/td>\nlock_mode<\/strong><\/font><\/font><\/td>\nstatus<\/strong><\/font><\/font><\/td>\nlog_op_name<\/strong><\/font><\/font><\/td>\ncontext<\/strong><\/font><\/font><\/td>\ndatabase_id<\/strong><\/font><\/font><\/td>\nresource_0<\/strong><\/font><\/font><\/td>\n<\/tr>\n
      sql_batch_starting<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      sql_statement_starting<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      lock_acquired<\/font><\/font><\/td>\nOBJECT<\/font><\/font><\/td>\nIX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\n245575913<\/font><\/font><\/td>\n<\/tr>\n
      lock_acquired<\/font><\/font><\/td>\nPAGE<\/font><\/font><\/td>\nIU<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\n228<\/font><\/font><\/td>\n<\/tr>\n
      lock_acquired<\/font><\/font><\/td>\nRID<\/font><\/font><\/td>\nU<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\n228<\/font><\/font><\/td>\n<\/tr>\n
      lock_released<\/font><\/font><\/td>\nRID<\/font><\/font><\/td>\nU<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\n228<\/font><\/font><\/td>\n<\/tr>\n
      lock_released<\/font><\/font><\/td>\nPAGE<\/font><\/font><\/td>\nIU<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\n228<\/font><\/font><\/td>\n<\/tr>\n
      lock_released<\/font><\/font><\/td>\nOBJECT<\/font><\/font><\/td>\nIX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n9<\/font><\/font><\/td>\n245575913<\/font><\/font><\/td>\n<\/tr>\n
      sql_statement_completed<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      sql_batch_completed<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

      \nNotice that here we don't have the lock conversion for the RID from U to X, and we don't have the log records being generated and flushed to the transaction log for the BEGIN_XACT and COMMIT_XACT. \n<\/p>\n

      \nThe results for the UPDATE in test number 4 has the same outcome as shown above for test number 3 when run against the case-insensitive collation database.  However, running test number 4 against the case-sensitive collation database results in the same events being fired for the non-filtered UPDATE and data changes in the table with the LOP_MODIFY_ROWS log records being generated. (Feel free to test and validate this using the provided code and CS collation database)\n<\/p>\n

      Results FOR\/AFTER UPDATE Triggers<\/h2>\n

      \nThe results for the two UPDATE statements in test number 1 when a trigger exists on the table are different than when the table does not have the trigger on it.\n<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
      event_name<\/strong><\/font><\/font><\/td>\nlock_resource_type<\/strong><\/font><\/font><\/td>\nlock_mode<\/strong><\/font><\/font><\/td>\nstatus<\/strong><\/font><\/font><\/td>\nlog_op_name<\/strong><\/font><\/font><\/td>\ncontext<\/strong><\/font><\/font><\/td>\ndatabase_id<\/strong><\/font><\/font><\/td>\nresource_0<\/strong><\/font><\/font><\/td>\n<\/tr>\n
      sql_batch_starting<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      sql_statement_starting<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      lock_acquired<\/font><\/font><\/td>\nOBJECT<\/font><\/font><\/td>\nIX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n10<\/font><\/font><\/td>\n245575913<\/font><\/font><\/td>\n<\/tr>\n
      lock_acquired<\/font><\/font><\/td>\nPAGE<\/font><\/font><\/td>\nIU<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n10<\/font><\/font><\/td>\n357<\/font><\/font><\/td>\n<\/tr>\n
      lock_acquired<\/font><\/font><\/td>\nRID<\/font><\/font><\/td>\nU<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n10<\/font><\/font><\/td>\n357<\/font><\/font><\/td>\n<\/tr>\n
      lock_acquired<\/font><\/font><\/td>\nPAGE<\/font><\/font><\/td>\nIX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n10<\/font><\/font><\/td>\n357<\/font><\/font><\/td>\n<\/tr>\n
      lock_acquired<\/font><\/font><\/td>\nRID<\/font><\/font><\/td>\nX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n10<\/font><\/font><\/td>\n357<\/font><\/font><\/td>\n<\/tr>\n
      page_allocated<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      log_single_record<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nLOP_BEGIN_XACT<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n10<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      transaction_log<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nLOP_BEGIN_XACT<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n10<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      lock_acquired<\/font><\/font><\/td>\nEXTENT<\/font><\/font><\/td>\nX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n2<\/font><\/font><\/td>\n1568<\/font><\/font><\/td>\n<\/tr>\n
      lock_released<\/font><\/font><\/td>\nEXTENT<\/font><\/font><\/td>\nX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n2<\/font><\/font><\/td>\n1568<\/font><\/font><\/td>\n<\/tr>\n
      page_allocated<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      page_allocated<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      page_allocated<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      page_allocated<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      page_allocated<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      page_allocated<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      page_allocated<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      page_allocated<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      latch_acquire_time<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nCHKGEN, DIRTY, HASHED, ON<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      lock_acquired<\/font><\/font><\/td>\nEXTENT<\/font><\/font><\/td>\nU<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n2<\/font><\/font><\/td>\n312<\/font><\/font><\/td>\n<\/tr>\n
      lock_acquired<\/font><\/font><\/td>\nPAGE<\/font><\/font><\/td>\nX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n2<\/font><\/font><\/td>\n318<\/font><\/font><\/td>\n<\/tr>\n
      lock_released<\/font><\/font><\/td>\nEXTENT<\/font><\/font><\/td>\nU<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n2<\/font><\/font><\/td>\n312<\/font><\/font><\/td>\n<\/tr>\n
      lock_released<\/font><\/font><\/td>\nPAGE<\/font><\/font><\/td>\nX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n2<\/font><\/font><\/td>\n318<\/font><\/font><\/td>\n<\/tr>\n
      lock_released<\/font><\/font><\/td>\nRID<\/font><\/font><\/td>\nNL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n10<\/font><\/font><\/td>\n357<\/font><\/font><\/td>\n<\/tr>\n
      lock_released<\/font><\/font><\/td>\nPAGE<\/font><\/font><\/td>\nNL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n10<\/font><\/font><\/td>\n357<\/font><\/font><\/td>\n<\/tr>\n
      latch_acquire_time<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nCHKGEN, DIRTY, HASHED, ON<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      log_single_record<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nLOP_COMMIT_XACT<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n10<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      transaction_log<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nLOP_COMMIT_XACT<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n10<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      log_flush_requested<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n10<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      lock_released<\/font><\/font><\/td>\nRID<\/font><\/font><\/td>\nX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n10<\/font><\/font><\/td>\n357<\/font><\/font><\/td>\n<\/tr>\n
      lock_released<\/font><\/font><\/td>\nPAGE<\/font><\/font><\/td>\nIX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n10<\/font><\/font><\/td>\n357<\/font><\/font><\/td>\n<\/tr>\n
      lock_released<\/font><\/font><\/td>\nOBJECT<\/font><\/font><\/td>\nIX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n10<\/font><\/font><\/td>\n245575913<\/font><\/font><\/td>\n<\/tr>\n
      sql_statement_completed<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      sql_batch_completed<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

      \nAs you can see in the table above, we now have exclusive locks on an extent in tempdb, and memory page allocations occurring (shown by the page_allocated events) in the new result set.  If we take a look at the execution callstack for the page_allocated events, we'll see:\n<\/p>\n

      \n

      \n\tMemoryClerkInternal::AllocatePages+26f [ @ 0+0x0
      \n\tBPool::Steal+35a [ @ 0+0x0
      \n\tBPool::NewPage+7af [ @ 0+0x0
      \n\tPageRef::FormatInternal+8e [ @ 0+0x0
      \n\tAppendOnlyStorageUnit::SetupPageHeaderPreAllocation+af [ @ 0+0x0
      \n\tAllocationReq::AllocateExtent+285 [ @ 0+0x0
      \n\tAllocationReq::AllocateFirst+a9a [ @ 0+0x0
      \n\tAllocationReq::Allocate+e9 [ @ 0+0x0
      \n\tAppendOnlyStorageUnit::AllocateNewExtent+29a [ @ 0+0x0
      \n\tAppendOnlyVerStoreMgr::AddRecord+e07 [ @ 0+0x0
      \n\tVersionMgr::InsertNewImage+13e [ @ 0+0x0
      \n\t<\/strong>HeapPageRef::Update+b4e [ @ 0+0x0
      \n\tHeapFragment::Update+3b7 [ @ 0+0x0
      \n\tHeapRowObject::Update+67d9 [ @ 0+0x0
      \n\tHeapDataSetSession::SetDataInternal+8dcb [ @ 0+0x0
      \n\tDatasetSession::SetData+16d [ @ 0+0x0
      \n\tRowsetNewSS::SetData+6a [ @ 0+0x0
      \n\tCValRow::SetDataX+63 [ @ 0+0x0
      \n\tCEsExec::GeneralEval4+e7 [ @ 0+0x0
      \n\tCQScanUpdateNew::GetRow+23a [ @ 0+0x0
      \n\tCQueryScan::GetRow+81 [ @ 0+0x0
      \n\tCXStmtQuery::ErsqExecuteQuery+36a [ @ 0+0x0
      \n\tCXStmtDML::XretDMLExecute+2b9 [ @ 0+0x0
      \n\tCXStmtDML::XretExecute+ad [ @ 0+0x0\n\t<\/p>\n<\/blockquote>\n

      \nBy adding the trigger, we now have version store records being generated associated with the execution of the FOR\/AFTER UPDATE trigger.  The version store records are also generated for test number 2 where the column data is actually changed by the UPDATE statement being executed.  On the case-insensitive collation, test number 3 and number 4 do not generate version store records.\n<\/p>\n

      \nHowever, for the case-sensitive collation, test number 4 does generate version store records, as shown below:\n<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
      event_name<\/strong><\/font><\/font><\/td>\nlock_resource_type<\/strong><\/font><\/font><\/td>\nlock_mode<\/strong><\/font><\/font><\/td>\nstatus<\/strong><\/font><\/font><\/td>\nlog_op_name<\/strong><\/font><\/font><\/td>\ncontext<\/strong><\/font><\/font><\/td>\ndatabase_id<\/strong><\/font><\/font><\/td>\nresource_0<\/strong><\/font><\/font><\/td>\n<\/tr>\n
      sql_batch_starting<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      sql_statement_starting<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      lock_acquired<\/font><\/font><\/td>\nOBJECT<\/font><\/font><\/td>\nIX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n10<\/font><\/font><\/td>\n325576198<\/font><\/font><\/td>\n<\/tr>\n
      latch_acquire_time<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nHASHED, ONLRU<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      latch_acquire_time<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nDIRTY, HASHED, ONLRU<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      lock_acquired<\/font><\/font><\/td>\nPAGE<\/font><\/font><\/td>\nIU<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n10<\/font><\/font><\/td>\n73<\/font><\/font><\/td>\n<\/tr>\n
      lock_acquired<\/font><\/font><\/td>\nRID<\/font><\/font><\/td>\nU<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n10<\/font><\/font><\/td>\n73<\/font><\/font><\/td>\n<\/tr>\n
      lock_acquired<\/font><\/font><\/td>\nPAGE<\/font><\/font><\/td>\nIX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n10<\/font><\/font><\/td>\n73<\/font><\/font><\/td>\n<\/tr>\n
      lock_acquired<\/font><\/font><\/td>\nRID<\/font><\/font><\/td>\nX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n10<\/font><\/font><\/td>\n73<\/font><\/font><\/td>\n<\/tr>\n
      log_single_record<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nLOP_BEGIN_XACT<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n10<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      transaction_log<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nLOP_BEGIN_XACT<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n10<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      latch_acquire_time<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nHASHED, ONLRU<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      lock_acquired<\/font><\/font><\/td>\nEXTENT<\/font><\/font><\/td>\nX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n2<\/font><\/font><\/td>\n2944<\/font><\/font><\/td>\n<\/tr>\n
      lock_released<\/font><\/font><\/td>\nEXTENT<\/font><\/font><\/td>\nX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n2<\/font><\/font><\/td>\n2944<\/font><\/font><\/td>\n<\/tr>\n
      lock_acquired<\/font><\/font><\/td>\nEXTENT<\/font><\/font><\/td>\nU<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n2<\/font><\/font><\/td>\n264<\/font><\/font><\/td>\n<\/tr>\n
      lock_acquired<\/font><\/font><\/td>\nPAGE<\/font><\/font><\/td>\nX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n2<\/font><\/font><\/td>\n269<\/font><\/font><\/td>\n<\/tr>\n
      lock_released<\/font><\/font><\/td>\nEXTENT<\/font><\/font><\/td>\nU<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n2<\/font><\/font><\/td>\n264<\/font><\/font><\/td>\n<\/tr>\n
      lock_released<\/font><\/font><\/td>\nPAGE<\/font><\/font><\/td>\nX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n2<\/font><\/font><\/td>\n269<\/font><\/font><\/td>\n<\/tr>\n
      log_single_record<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nLOP_MODIFY_ROW<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n10<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      transaction_log<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nLOP_MODIFY_ROW<\/font><\/font><\/td>\nHEAP<\/font><\/font><\/td>\n10<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      lock_released<\/font><\/font><\/td>\nRID<\/font><\/font><\/td>\nNL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n10<\/font><\/font><\/td>\n73<\/font><\/font><\/td>\n<\/tr>\n
      lock_released<\/font><\/font><\/td>\nPAGE<\/font><\/font><\/td>\nNL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n10<\/font><\/font><\/td>\n73<\/font><\/font><\/td>\n<\/tr>\n
      log_single_record<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nLOP_COMMIT_XACT<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n10<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      transaction_log<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nLOP_COMMIT_XACT<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n10<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      log_flush_requested<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n10<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      lock_released<\/font><\/font><\/td>\nRID<\/font><\/font><\/td>\nX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n10<\/font><\/font><\/td>\n73<\/font><\/font><\/td>\n<\/tr>\n
      lock_released<\/font><\/font><\/td>\nPAGE<\/font><\/font><\/td>\nIX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n10<\/font><\/font><\/td>\n73<\/font><\/font><\/td>\n<\/tr>\n
      lock_released<\/font><\/font><\/td>\nOBJECT<\/font><\/font><\/td>\nIX<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n10<\/font><\/font><\/td>\n325576198<\/font><\/font><\/td>\n<\/tr>\n
      sql_statement_completed<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n
      sql_batch_completed<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\nNULL<\/font><\/font><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

      \nThe page_allocated events have the same callstack as the previously listed callstack in this post.\n<\/p>\n

      Conclusions<\/h2>\n

      \nAs can be seen by the results of these tests, UPDATE statements that do not actually change the value of a column in a row, can still have overhead associated with them, even when the database engine is recognizes that no change is actually occurring and doesn't actually affect the row on the page.  There is still overhead for the exclusive lock conversion and the implicit transaction that occurs as a part of the UPDATE occurring when no filter is provided to check the value currently on the row against the value being SET, and there is added overhead for row versioning if triggers exist on the table.\n<\/p>\n

      \nLooking at the callstack for the LOP_MODIFY_ROW log operation, we can also see that changes being made affect the specific row on the page.  The changes are NOT<\/strong> made on a separate page allocated by SQL Server to allow a binary comparison of the two pages, as suggested in the original forum thread that lead to this investigation. This is NOT how SQL Server behaves.\n<\/p>\n

      \n

      \n\tSQLServerLogMgr::FireAppendXevent+11b [ @ 0+0x0
      \n\tSQLServerLogMgr::AppendLogRequest+6d8 [ @ 0+0x0
      \n\tSQLServerLogMgr::ReserveAndAppend+74 [ @ 0+0x0
      \n\tXdesRMReadWrite::GenerateLogRec+5ac [ @ 0+0x0
      \n\t
      \n\tPageRef::ModifyRow+2b4a [ @ 0+0x0 <\/strong>
      \n\t
      \n\tPageRef::ModifyColumnsInternal+162 [ @ 0+0x0 <\/strong>
      \n\tHeapPageRef::Update+208d [ @ 0+0x0
      \n\tHeapFragment::Update+3b7 [ @ 0+0x0
      \n\tHeapRowObject::Update+67d9 [ @ 0+0x0
      \n\tHeapDataSetSession::SetDataInternal+8dcb [ @ 0+0x0
      \n\tDatasetSession::SetData+16d [ @ 0+0x0
      \n\tRowsetNewSS::SetData+6a [ @ 0+0x0
      \n\tCValRow::SetDataX+63 [ @ 0+0x0
      \n\tCEsExec::GeneralEval4+e7 [ @ 0+0x0
      \n\t
      \n\tCQScanUpdateNew::GetRow+23a [ @ 0+0x0
      \n\tCQueryScan::GetRow+81 [ @ 0+0x0 <\/strong>
      \n\tCXStmtQuery::ErsqExecuteQuery+36a [ @ 0+0x0
      \n\tCXStmtDML::XretDMLExecute+2b9 [ @ 0+0x0
      \n\tCXStmtDML::XretExecute+ad [ @ 0+0x0
      \n\tCMsqlExecContext::ExecuteStmts<0,0>+1a71 [ @ 0+0x0
      \n\tCMsqlExecContext::FExecute+a4f [ @ 0+0x0
      \n\tCSQLSource::Execute+866 [ @ 0+0x0
      \n\tCStmtPrepQuery::XretPrepQueryExecute+3e2 [ @ 0+0x0
      \n\tCMsqlExecContext::ExecuteStmts<1,0>+17c6 [ @ 0+0x0\n\t<\/p>\n<\/blockquote>\n

      \nThis is further supported by the fact that no<\/strong> additional page_allocated events fire outside of the version store information being generated by the UPDATE.  Since these tests only focused on the internal operations of UPDATE statements against a single row table, no realistic determination can be made for the actual performance overhead of comparing the column value to the value being SET by the UPDATE statement, specifically for what additional overhead might be incurred by a less ideal execution plan based on the additional filter criteria being optimized.  This is something that I will definitely be looking at in more detail for a possible later post.  However, we can see that comparing the current value to the value being set, can affect row version generation, lock conversions, and the number of log records that are generated where the data would not actually be changed by the UPDATE statement being issued.\n<\/p>\n

      \nAddition:<\/strong>\n<\/p>\n

      \nFor further reading on this subject, take a look at Paul Whites blog post <\/a>from a few years ago.  The outcomes are the same, but he uses a different method of investigation <\/p>\n","protected":false},"excerpt":{"rendered":"

      For a few weeks now, there has been a long running thread on SQL Server Central about UPDATE operations that don't perform any changes because the statement sets a column to the same value as already exists in the row.  While I believe that one of the posters is incredibly snarky and outright rude to […]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23,27,39],"tags":[],"class_list":["post-471","post","type-post","status-publish","format-standard","hentry","category-extended-events","category-internals","category-sql-server-2012"],"yoast_head":"\nLooking at Non-Updating Updates with Extended Events - Jonathan Kehayias<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/looking-at-non-updating-updates-with-extended-events\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Looking at Non-Updating Updates with Extended Events - Jonathan Kehayias\" \/>\n<meta property=\"og:description\" content=\"For a few weeks now, there has been a long running thread on SQL Server Central about UPDATE operations that don't perform any changes because the statement sets a column to the same value as already exists in the row.  While I believe that one of the posters is incredibly snarky and outright rude to […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/looking-at-non-updating-updates-with-extended-events\/\" \/>\n<meta property=\"og:site_name\" content=\"Jonathan Kehayias\" \/>\n<meta property=\"article:published_time\" content=\"2012-09-20T02:28:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T18:40:51+00:00\" \/>\n<meta name=\"author\" content=\"Jonathan Kehayias\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Jonathan Kehayias\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"17 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/looking-at-non-updating-updates-with-extended-events\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/looking-at-non-updating-updates-with-extended-events\\\/\"},\"author\":{\"name\":\"Jonathan Kehayias\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"headline\":\"Looking at Non-Updating Updates with Extended Events\",\"datePublished\":\"2012-09-20T02:28:00+00:00\",\"dateModified\":\"2017-04-13T18:40:51+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/looking-at-non-updating-updates-with-extended-events\\\/\"},\"wordCount\":3491,\"commentCount\":1,\"articleSection\":[\"Extended Events\",\"Internals\",\"SQL Server 2012\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/looking-at-non-updating-updates-with-extended-events\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/looking-at-non-updating-updates-with-extended-events\\\/\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/looking-at-non-updating-updates-with-extended-events\\\/\",\"name\":\"Looking at Non-Updating Updates with Extended Events - Jonathan Kehayias\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\"},\"datePublished\":\"2012-09-20T02:28:00+00:00\",\"dateModified\":\"2017-04-13T18:40:51+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/looking-at-non-updating-updates-with-extended-events\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/looking-at-non-updating-updates-with-extended-events\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/looking-at-non-updating-updates-with-extended-events\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Extended Events\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/category\\\/extended-events\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Looking at Non-Updating Updates with Extended Events\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\",\"name\":\"Jonathan Kehayias - The Rambling DBA\",\"description\":\"The Rambling DBA\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\",\"name\":\"Jonathan Kehayias\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"caption\":\"Jonathan Kehayias\"},\"sameAs\":[\"http:\\\/\\\/3.209.169.194\\\/blogs\\\/jonathan\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Looking at Non-Updating Updates with Extended Events - Jonathan Kehayias","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/looking-at-non-updating-updates-with-extended-events\/","og_locale":"en_US","og_type":"article","og_title":"Looking at Non-Updating Updates with Extended Events - Jonathan Kehayias","og_description":"For a few weeks now, there has been a long running thread on SQL Server Central about UPDATE operations that don't perform any changes because the statement sets a column to the same value as already exists in the row.  While I believe that one of the posters is incredibly snarky and outright rude to […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/looking-at-non-updating-updates-with-extended-events\/","og_site_name":"Jonathan Kehayias","article_published_time":"2012-09-20T02:28:00+00:00","article_modified_time":"2017-04-13T18:40:51+00:00","author":"Jonathan Kehayias","twitter_misc":{"Written by":"Jonathan Kehayias","Est. reading time":"17 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/looking-at-non-updating-updates-with-extended-events\/#article","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/looking-at-non-updating-updates-with-extended-events\/"},"author":{"name":"Jonathan Kehayias","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"headline":"Looking at Non-Updating Updates with Extended Events","datePublished":"2012-09-20T02:28:00+00:00","dateModified":"2017-04-13T18:40:51+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/looking-at-non-updating-updates-with-extended-events\/"},"wordCount":3491,"commentCount":1,"articleSection":["Extended Events","Internals","SQL Server 2012"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/looking-at-non-updating-updates-with-extended-events\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/looking-at-non-updating-updates-with-extended-events\/","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/looking-at-non-updating-updates-with-extended-events\/","name":"Looking at Non-Updating Updates with Extended Events - Jonathan Kehayias","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website"},"datePublished":"2012-09-20T02:28:00+00:00","dateModified":"2017-04-13T18:40:51+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/looking-at-non-updating-updates-with-extended-events\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/looking-at-non-updating-updates-with-extended-events\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/looking-at-non-updating-updates-with-extended-events\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/"},{"@type":"ListItem","position":2,"name":"Extended Events","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/category\/extended-events\/"},{"@type":"ListItem","position":3,"name":"Looking at Non-Updating Updates with Extended Events"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/","name":"Jonathan Kehayias - The Rambling DBA","description":"The Rambling DBA","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c","name":"Jonathan Kehayias","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","caption":"Jonathan Kehayias"},"sameAs":["http:\/\/3.209.169.194\/blogs\/jonathan"]}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/471","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/comments?post=471"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/471\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/media?parent=471"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/categories?post=471"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/tags?post=471"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}