InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 02:26:50 GMT20http://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspxFor those that don't have debugger skills and want to look at the internals of this, I wrote a blog post that shows how to dig into the internals using Extended Events and materialized callstacks for each of the events being fired when the UPDATE tests are run.[url=http://sqlskills.com/blogs/jonathan/post/Looking-at-Non-Updating-Updates-with-Extended-Events.aspx]http://sqlskills.com/blogs/jonathan/post/Looking-at-Non-Updating-Updates-with-Extended-Events.aspx[/url]The materialized callstacks validate everything that Paul has been saying in this thread and refutes definitively the argument that SQL Server operates on pages not rows. It also validates that row versions are generated even when nothing changes if a trigger exists.Thu, 20 Sep 2012 10:36:02 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspx[quote][b]L' Eomot Inversé (9/16/2012)[/b][hr]If the query processor figures out that it is not going to update the row (this is decided on the basis of where clause and on clause) that row will not be in the inserted or deleted table. If the query passes the row update to the storage engine and the storage engine decides not to update the row in its buffered page (determined by the update describing the same binary value of each field as is already in the buffer) the row will be in the inserted and deleted tables. These are two completely different occurrences, involving different queries.[/quote][quote]It doesn't perform the same actions in the two cases; in one case the storage engine is invoked (but doesn't modify the page) and the non-updating update can cause a trigger to be fired; in the other case the query processor doesn't do update because the where clause excludes it, the storage engine is not invoked, and there is no non-updating update to cause a trigger to be invoked. This seems to me to be a big and clear difference.[/quote]That matches my understanding, Tom.Mon, 17 Sep 2012 01:59:08 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspx[quote][b]Sergiy (9/16/2012)[/b][hr]Basically no difference comparing to yours one, Paul, except I used REPLICATE to easily and synchronously change from char(200) to char(50) or char(8000).[/quote]I chose a literal string rather than REPLICATE for complicated reasons, and to be fair to all the methods (see my post [url]http://bit.ly/ComputeScalar[/url] if you are interested). To avoid debates about which method might evaluate REPLICATE more times than another, I used a literal string.[quote]"Filtered" solution wins clearly.[/quote]Which is why I say filtering is generally the best option (except in cases like my carefully-constructed example).[quote]With the winning rate which does not leave any doubt: overhead of opening/closing transaction, applying different kind of locks (actually we have the same table lock forced in all cases), etc. cannot bring such a difference. So, what do you think SQL Server is doing during those 1.1 seconds which is the difference between "filtered" and "Non-updating"? Interpretations are welcome.[/quote]It's all about how many rows are produced by the Table Scan. When filtered, no rows are produced, so no update processing happens. The main cost is evaluating 'WHERE NOT Name = @Value' 123,456 times. With a binary collation, this comparison is fast; with a more complex collation it can be quite slow (slower than not filtering at all, and just passing all the rows to the update operator for binary comparison, row by row, in the storage engine).In your CASE test, all 123,456 rows are produced one at a time from the table scan and processed one at a time by the update operator. We now have the overhead of passing rows one at a time between the scan and update operators, and we have to compare the result of the CASE expression with the value already present in the row, for each row. This explains the difference in execution time.Sun, 16 Sep 2012 23:40:25 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspx[quote][b]Sergiy (9/16/2012)[/b][hr]I figured out why you built the test this way and I have no argument about the point:comparing binary content of pages is and will always be more effective than comparing strings row by row (usually, several different comparisons over the data in the same page).[/quote]You're almost there, but the point is the binary comparisons in the storage engine are per row, not per page. It is a shame you are not in a position to run the debugger session yourself, because it shows the row-by-row processing very clearly:- The update operator asks for a row of update data from the table scan operator.- The storage engine performs the 'update' for this row (quotes because a write might not actually occur).- The update operator asks for the next row of information from the table scan operator.- The process repeats until no more rows are available from the table scan.A 'binary comparison of pages' simply does not occur.[quote]But at the end of the day, as it follows from your example:- the query submits affected rows and creates updated versions of the pages;- storage engine compares old and new versions and desides if further action is required.Some still may argue that it's rows, not newer versions of pages which are compared by the storage engine.But so far I'm aware of only page-level activity performed by the storage engine.[/quote]This is the basic misunderstanding - processing occurs [url=http://msdn.microsoft.com/en-us/library/ms191158.aspx]row by row[/url]. This 'iterator model' is common knowledge and documented e.g. in Microsoft Press internals books, by people who wrote the SQL Server code.It would be horribly inefficient for the storage engine to compare pages (not rows) in the general case - we would need to copy the whole page before every row update, perform the update, and then compare the whole new page with the whole old page. Update information is not guaranteed to arrive at the update operator in page order (it depends e.g. on query plan shape) so it could mean copying and comparing a page hundreds of times over, once for each updated row on the page. That would be madness. It is much more efficient not to copy the page at all, and simply compare the binary value passed in for update with the binary value already in the row - no expensive page copy & compare needed.Sun, 16 Sep 2012 22:22:33 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspx[quote][b]Sergiy (9/16/2012)[/b][hr]I don't think so.Even if no rows are updated the tables are still created.SQL Server still allocates pages for the tables, names all the columns, etc.They have nothing in them - well, that means the source of the records for the tables contained no records. But it does not mean the source of the "nothing" was not there.[/quote]The inserted and deleted tables are not separate real tables, SQL Server does not "allocate pages, name all the columns, etc". The inserted and deleted virtual tables are a 'view' of [url=http://msdn.microsoft.com/en-nz/library/ms189050(v=sql.105).aspx]version store records[/url] in SQL Server 2005 and later. Before SQL Server 2005, a backward scan of the transaction log records was used.Without a trigger, no versions are generated. With a trigger, versions [url=http://bit.ly/ForwardGhost]may be[/url] generated. The script below demonstrates this:[code="sql"]CREATE TABLE dbo.Test (col1 varchar(max) NULL);GOINSERT dbo.Test (col1) VALUES ('Hello');GO-- No trigger, nothing in the version storeBEGIN TRANSACTION;UPDATE dbo.Test SET col1 = 'Hello';SELECT * FROM sys.dm_tran_version_store AS dtvs;COMMIT TRANSACTION;GO-- A trigger that does nothingCREATE TRIGGER trgON dbo.Test AFTER UPDATEAS RETURN;GOCHECKPOINT;GO-- Same non-updating updateBEGIN TRANSACTION;UPDATE dbo.Test SET col1 = 'Hello';-- Now we have two version store recordsSELECT decoded = CONVERT(char(5), SUBSTRING(dtvs.record_image_first_part, 12, 5)), *FROM sys.dm_tran_version_store AS dtvs;COMMIT TRANSACTION;[/code]Sun, 16 Sep 2012 21:45:27 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspxI did a little experiment using string values of different length.Here is the base script:[code="sql"]USE tempdb;SET NOCOUNT ON;GOCREATE TABLE dbo.Test( Name char(200) COLLATE Latin1_General_CI_AI NULL -- COLLATE Latin1_General_BIN NULL);GOINSERT dbo.TestSELECT TOP (123456) REPLICATE('X', 200)FROM sys.columns AS cCROSS JOIN sys.columns AS c2;GODBCC SHOWCONTIG ('dbo.Test') WITH all_indexes, TABLERESULTS DBCC FREEPROCCACHE;CHECKPOINT;GODECLARE @start datetime2 = SYSDATETIME(), @Value char(200);SET @Value = REPLICATE('X', 200);SET STATISTICS IO ON;UPDATE dbo.Test WITH (TABLOCKX)SET Name = @ValueWHERE NOT Name = @Value;SET STATISTICS IO OFF;SELECT test = 'Filtered', time_ms = DATEDIFF(MILLISECOND, @start, SYSDATETIME())GODBCC FREEPROCCACHE;CHECKPOINT;GODECLARE @start datetime2 = SYSDATETIME(), @Value char(200);SET @Value = REPLICATE('X', 200);SET STATISTICS IO ON;UPDATE dbo.Test WITH (TABLOCKX)SET Name = Name;SET STATISTICS IO OFF;SELECT test = 'Non-updating', time_ms = DATEDIFF(MILLISECOND, @start, SYSDATETIME());GODBCC FREEPROCCACHE;CHECKPOINT;GODECLARE @start datetime2 = SYSDATETIME(), @Value char(200);SET @Value = REPLICATE('X', 200);SET STATISTICS IO ON;UPDATE dbo.Test WITH (TABLOCKX)SET Name = CASE WHEN Name = @Value THEN @Value ELSE Name end;SET STATISTICS IO OFF;SELECT test = 'Non-updating CASE', time_ms = DATEDIFF(MILLISECOND, @start, SYSDATETIME());GODBCC FREEPROCCACHE;CHECKPOINT;GODECLARE @start datetime2 = SYSDATETIME(), @Value char(200);SET @Value = REPLICATE('Y', 200);SET STATISTICS IO ONUPDATE dbo.Test WITH (TABLOCKX)SET Name = @Value;SET STATISTICS IO OFFSELECT test = 'Updating', time_ms = DATEDIFF(MILLISECOND, @start, SYSDATETIME());GODROP TABLE dbo.Test;[/code]Basically no difference comparing to yours one, Paul, except I used REPLICATE to easily and synchronously change from char(200) to char(50) or char(8000).I recorded the times and calculated the rate of effectiveness of other methods comparing to "Filtered"Here is the outcome [code="other"] char(50) COLLATE Latin1_General_CI_AI Filtered 87 Non-updating CASE 248 285%Non-updating 114 131%Updating 206 237% char(200) COLLATE Latin1_General_CI_AI Filtered 295 Non-updating CASE 472 160%Non-updating 137 46%Updating 275 93% char(500) COLLATE Latin1_General_CI_AI Filtered 716 Non-updating CASE 945 132%Non-updating 194 27%Updating 527 74% char(1000) COLLATE Latin1_General_CI_AI Filtered 1421 Non-updating CASE 1702 120%Non-updating 267 19%Updating 1010 71% char(8000) COLLATE Latin1_General_CI_AI Filtered 10997 Non-updating CASE 12796 116%Non-updating 1321 12%Updating 9091 83%[/code]No surprise so far.Longer strings cause longer comparison operations, so the winning rate for "Non-updating" grows.Then I decided to minimuse the impact of collation rules by chosing the least expensive collation.Here is the outcome:[code="other"]char(50) COLLATE Latin1_General_BIN Filtered 13 Non-updating CASE 151 1162%Non-updating 113 869%Updating 203 1562% char(200) COLLATE Latin1_General_BIN Filtered 17 Non-updating CASE 178 1047%Non-updating 138 812%Updating 252 1482% char(500) COLLATE Latin1_General_BIN Filtered 31 Non-updating CASE 240 774%Non-updating 191 616%Updating 548 1768% char(1000) COLLATE Latin1_General_BIN Filtered 44 Non-updating CASE 322 732%Non-updating 269 611%Updating 1217 2766% char(8000) COLLATE Latin1_General_BIN Filtered 264 Non-updating CASE 1708 647%Non-updating 1328 503%Updating 10785 4085%[/code]"Filtered" solution wins clearly.With the winning rate which does not leave any doubt: overhead of opening/closing transaction, applying different kind of locks (actually we have the same table lock forced in all cases), etc. cannot bring such a difference.So, what do you think SQL Server is doing during those 1.1 seconds which is the difference between "filtered" and "Non-updating"?Interpretations are welcome.Sun, 16 Sep 2012 21:28:53 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspx[quote][b]SQL Kiwi (9/16/2012)[/b][hr][quote][b]Sergiy (9/16/2012)[/b][hr]Actually, you did. Trigger is executed [b]after [/b]UPDATE. If SQL Server figures out that the values are the same and does not proceed with writing to the pages then trigger has no source for "inserted" table.[/quote]You misunderstood. If the reading side of an update plans produces no rows (i.e. they are filtered out by e.g. a WHERE clause predicate) then no rows arrive at the update operator in the plan, so there is nothing in the inserted or deleted tables.[/quote]I don't think so.Even if no rows are updated the tables are still created.SQL Server still allocates pages for the tables, names all the columns, etc.They have nothing in them - well, that means the source of the records for the tables contained no records. But it does not mean the source of the "nothing" was not there.Sun, 16 Sep 2012 20:54:51 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspx[quote][b]Sergiy (9/16/2012)[/b][hr]Actually, you did. Trigger is executed [b]after [/b]UPDATE. If SQL Server figures out that the values are the same and does not proceed with writing to the pages then trigger has no source for "inserted" table.[/quote]You misunderstood. If the reading side of an update plans produces no rows (i.e. they are filtered out by e.g. a WHERE clause predicate) then no rows arrive at the update operator in the plan, so there is nothing in the inserted or deleted tables.[quote]I was talking not about overheads caused by a trigger. I was asking if SQL Server will do something different for a table with trigger in order to have "inserted" and "deleted" tables populated.[/quote]That is asking about the overheads caused by a trigger. Yes, SQL Server does something different for a table with a trigger: it versions the changed rows (non-updating or otherwise). I demonstrated this in my recent blog post [url]http://bit.ly/ForwardGhost[/url][quote]Still not clear why do you think so. If SQL Server performs the same actions as in case with WHERE clause, and binary comparison of pages is faster than collation based comparison of strings - why do you advocate for the worst of these 2 options?[/quote]This is just the same point you made in one of your [url=http://www.sqlservercentral.com/Forums/FindPost1343969.aspx]first replies[/url] to this thread (the Event table update). In general, avoiding a non-updating update is the better option. I provided my counter-example to show an interesting (and fun) exception.Sun, 16 Sep 2012 20:31:25 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspx[quote][b]SQL Kiwi (9/16/2012)[/b][hr]Sergiy, I should add that the fun in the example was in working out *why* that particular case gave the performance results it did. With a long string of a suitable collation, it is more expensive to do the collation-aware comparison than it is to decide not to perform the update based on a binary comparison in the storage engine. This was a point you refused to accept earlier, so I thought you might find it educational.[/quote]If you'd think about it you'd most probably find it aducational for yourself. :-)I figured out why you built the test this way and I have no argument about the point:comparing binary content of pages is and will always be more effective than comparing strings row by row (usually, several different comparisons over the data in the same page).I quickly added another sample test to prove the point:UPDATE dbo.Test WITH (TABLOCKX)SET Name = CASE WHEN Name = @Value THEN @Value ELSE Name end;@Value is the string from you example.On my machine this changed version of "Non-updating" UPDATE usually takes about twice as long as "Filtered" one.But I must admit that results vary from run to run quite significantly. Once I've got "CASE Non-updating" to finish faster than simple "Non-updating".But at the end of the day, as it follows from your example:- the query submits affected rows and creates updated versions of the pages;- storage engine compares old and new versions and desides if further action is required.Some still may argue that it's rows, not newer versions of pages which are compared by the storage engine.But so far I'm aware of only page-level activity performed by the storage engine. Sun, 16 Sep 2012 19:34:45 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspx[quote][b]L' Eomot Inversé (9/16/2012)[/b][hr]Do you really imagine that the query processor's interface to the storage engine operates on pages as opposed to on rows or attributes?[quote]How does it matter what do I imagine?[/quote]Anyway, you've already made in clear when responding to Gail that you aren't interested in looking into any references that are provided for you, so why do you expect I should waste my time providing them for you?[/quote]Did you open that link yourself? I read the book Gail referred to from start to finish.And did not find a single reference in there relevant to this topic.Waste of time.You clearly don't consider posting personal attacks as wasting time, unlike posting any professional comments.You made it clear that you lack professionalism and don't have anything to say on the matter.Would you mind to spare us from your rubbish input?Sun, 16 Sep 2012 18:51:18 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspx[quote][b]L' Eomot Inversé (9/16/2012)[/b][hr]Anyway, you've already made in clear when responding to Gail that you aren't interested in looking into any references that are provided for you, so why do you expect I should waste my time providing them for you?[/quote]Hahahahahaha! Brilliant! :hehe: :hehe: :hehe:Sun, 16 Sep 2012 17:14:18 GMTRobert Davishttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspx[quote][b]Sergiy (9/16/2012)[/b][hr][quote][b]L' Eomot Inversé (9/16/2012)[/b][hr]... the storage engine decides not to update the row [/quote]Can you please provide a reference to a document saying that storage engine operates rows?So far every one I saw was describing page operations.[/quote]Do you really imagine that the query processor's interface to the storage engine operates on pages as opposed to on rows or attributes?Anyway, you've already made in clear when responding to Gail that you aren't interested in looking into any references that are provided for you, so why do you expect I should waste my time providing them for you?Sun, 16 Sep 2012 16:29:52 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspx[quote][b]Sergiy (9/16/2012)[/b][hr][quote]Filtering non-updating updates in the WHERE is the right thing to do, as I have said before.[/quote]Still not clear why do you think so.If SQL Server performs the same actions as in case with WHERE clause, and binary comparison of pages is faster than collation based comparison of strings - why do you advocate for the worst of these 2 options?[/quote]As was already explained, non-updating updates still require an exclusive lock. So are you really wondering why he would prefer the process that does not require exclusive locks for those rows than one that does require exclusive locks? Seems like an easy decision to me.Sun, 16 Sep 2012 09:19:21 GMTRobert Davishttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspx[quote][b]L' Eomot Inversé (9/16/2012)[/b][hr]... the storage engine decides not to update the row [/quote]Can you please provide a reference to a document saying that storage engine operates rows?So far every one I saw was describing page operations.Sun, 16 Sep 2012 09:18:23 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspx[quote][b]Sergiy (9/16/2012)[/b][hr]Trigger is executed [b]after [/b]UPDATE. If SQL Server figures out that the values are the same and does not proceed with writing to the pages then trigger has no source for "inserted" table.[/quote]If the query processor figures out that it is not going to update the row (this is decided on the basis of where clause and on clause) that row will not be in the inserted or deleted table. If the query passes the row update to the storage engine and the storage engine decides not to update the row in its buffered page (determined by the update describing the same binary value of each field as is already in the buffer) the row will be in the inserted and deleted tables. These are two completely different occurrences, involving different queries.[quote]If SQL Server performs the same actions as in case with WHERE clause, and binary comparison of pages is faster than collation based comparison of strings - why do you advocate for the worst of these 2 options?[/quote]It doesn't perform the same actions in the two cases; in one case the storage engine is invoked (but doesn't modify the page) and the non-updating update can cause a trigger to be fired; in the other case the query processor doesn't do update because the where clause excludes it, the storage engine is not invoked, and there is no non-updating update to cause a trigger to be invoked. This seems to me to be a big and clear difference.But probably I'm wasting my time. :( Either your English comprehension is not up to understanding what I have just written or I'm wasting my time feeding the troll.Sun, 16 Sep 2012 08:52:39 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspx[quote][b]SQL Kiwi (9/16/2012)[/b][hr][quote]According to Paul's logic the set in "inserted" table won't be created.[/quote]No! I have never said anything like this. The inserted and deleted pseudo-tables in a trigger will always contain all modified records (including non-updating updates). [/quote]Actually, you did.Trigger is executed [b]after [/b]UPDATE. If SQL Server figures out that the values are the same and does not proceed with writing to the pages then trigger has no source for "inserted" table.[quote]Having a trigger that scans the inserted or deleted tables will obviously mean non-updating updates have a much higher overhead. [/quote]I was talking not about overheads caused by a trigger.I was asking if SQL Server will do something different for a table with trigger in order to have "inserted" and "deleted" tables populated.[quote]Filtering non-updating updates in the WHERE is the right thing to do, as I have said before.[/quote]Still not clear why do you think so.If SQL Server performs the same actions as in case with WHERE clause, and binary comparison of pages is faster than collation based comparison of strings - why do you advocate for the worst of these 2 options?Sun, 16 Sep 2012 07:35:50 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspxSergiy, I should add that the fun in the example was in working out *why* that particular case gave the performance results it did. With a long string of a suitable collation, it is more expensive to do the collation-aware comparison than it is to decide not to perform the update based on a binary comparison in the storage engine. This was a point you refused to accept earlier, so I thought you might find it educational.Sun, 16 Sep 2012 05:16:27 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspx[quote][b]Sergiy (9/16/2012)[/b][hr]No, it's actually quite easy to see. Some proper experiment. Not the one-sided script specifically baked to satisfy desired conclusion. This was one of the proofs "beyond all doubt": [script removed] It took less than 5 minutes for me to modify the script and prove beyond any doubt that the the output of the experiment contained false data and cannot be used for any conclusion. Unfortunately I don't have a possibility to verify the debugger output. But the experiment as it's performed is far from being able to provide any prove at all.[/quote]You have misunderstood. That post showed an exception to the rule that filtering updates is always faster. I found it interesting, and shared it because I thought other people would find it intriguing too. It was absolutely NOT to prove anything at all. [b]It was a curiosity, for fun.[/b] (our debugger sessions were not based on that script).[quote]According to Paul's logic the set in "inserted" table won't be created.[/quote]No! I have never said anything like this. The inserted and deleted pseudo-tables in a trigger will always contain all modified records (including non-updating updates). Having a trigger that scans the inserted or deleted tables will obviously mean non-updating updates have a much higher overhead. Filtering non-updating updates in the WHERE is the right thing to do, as I have said before.Sun, 16 Sep 2012 04:46:00 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspx[quote][b]Eugene Elutin (9/12/2012)[/b][hrIt's hard to see what will "prove it beyond all doubt" for Sergiy. [/quote]No, it's actually quite easy to see.Some proper experiment.Not the one-sided script specifically baked to satisfy desired conclusion.This was one of the proofs "beyond all doubt":[quote][b]SQL Kiwi (9/7/2012)[/b][hr]In SQL Server 2008 or later:[code="sql"]USE tempdb;GOCREATE TABLE Test ( ID integer IDENTITY(1,1) NOT NULL, Name varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL);GOINSERT dbo.Test (Name) VALUES ('Tom');GOCREATE PROCEDURE dbo.UpdateSameASUPDATE dbo.Test SET Name = Name;GOCREATE PROCEDURE dbo.UpdateMixedCaseASUPDATE dbo.Test SET Name = 'Tom';GOCREATE PROCEDURE dbo.UpdateCapsASUPDATE dbo.Test SET Name = 'TOM';GOCHECKPOINT;DBCC FREEPROCCACHE;GOEXECUTE dbo.UpdateSame;GOEXECUTE dbo.UpdateMixedCase;GOEXECUTE dbo.UpdateCaps;GOSELECT proc_name = OBJECT_NAME(deps.[object_id]), deps.last_logical_writes, deps.last_logical_readsFROM sys.dm_exec_procedure_stats AS depsWHERE deps.[object_id] IN ( OBJECT_ID(N'dbo.UpdateSame', N'P'), OBJECT_ID(N'dbo.UpdateMixedCase', N'P'), OBJECT_ID(N'dbo.UpdateCaps', N'P') )GODROP PROCEDURE dbo.UpdateSame, dbo.UpdateMixedCase, dbo.UpdateCaps;GODROP TABLE dbo.Test;[/code]Output:[img]http://www.sqlservercentral.com/Forums/Attachment12329.aspx[/img][/quote]It took less than 5 minutes for me to modify the script and prove beyond any doubt that the the output of the experiment contained false data and cannot be used for any conclusion.Unfortunately I don't have a possibility to verify the debugger output.But the experiment as it's performed is far from being able to provide any prove at all.To provide some certain outcome the experiment must include following cases:- updating string values with exactly same and "same by collation rules" values;- updating a table with a trigger using values inserted and deleted tables (since the tables contain separate sets of data they occupy different pages in memory, even if they contain the same values. According to Paul's logic the set in "inserted" table won't be created. It's important to see if UPDATE performs different set of operations in order to provide both deleted and inserted values to the trigger);- updating with WHERE clause filtering out rows with identical values.Then by comparing the traces generated by these queries we may get to some reasonable conclusion.Sun, 16 Sep 2012 03:20:12 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspx[quote][b]Eugene Elutin (9/12/2012)[/b][hr][quote]...I tend to agree, but I am not certain the debugger output proves it beyond all doubt. ...[/quote]It's hard to see what will "prove it beyond all doubt" for Sergiy. May be forensic examination of the disk before and after? Adding the hardware to track the laser charge to the disk? What else? :w00t:[/quote]+ 9,223,372,036,854,775,806 (a bit more than nine quintillion)!!! :laugh:Sat, 15 Sep 2012 15:54:03 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspx[quote]...I tend to agree, but I am not certain the debugger output proves it beyond all doubt. ...[/quote]It's hard to see what will "prove it beyond all doubt" for Sergiy. May be forensic examination of the disk before and after? Adding the hardware to track the laser charge to the disk? What else? :w00t:I think, the evidences we have so far here are more than satisfactory. What I would think will be more helpful in this thread - is providing some advices for the best practices regarding that topic.There are different cases. Check for value change can improve or hurt performance depending on the real situation. In my experience I've seen both. I've seen significant performance improvements of some ETL processes for data warehouse after introduction of value-change checks. At the same time, having these checks in database layer of OLTP systems, often does lead to performance degradation. In my humble opinion, in OLTP, it's better to be done, if it is really required, in some pre-db layer, usually in DAL (Data Access Layer).Please note, that it has nothing to do with row-versioning, as in "value not changed" case, we don't really care if the data was changed by someone else.Wed, 12 Sep 2012 03:12:19 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspxHi, Paul! Actually, I agree with GilaMonster and you, that there is no so big trouble, if it actually writes to the page in memory or not, but due to persistence of Sergiy, I decided to look.[quote][b]SQL Kiwi (9/11/2012)[/b][hr]Without stepping through the (obfuscated) machine code line by line looking for memcpy versus memcmp calls in the right place, I don't think we could ever be 100% sure.[/quote] Absolutely agree. At first I was going to examine memory, stack and registers to look for values update 43981 (ABCDh) -> 48879 (BEEFh). But I refused of it, to save my time for the reason:[quote][b]SQL Kiwi (9/11/2012)[/b][hr]The method calls do confirm what we already knew about log records, dirtypages and so on.[/quote]As the method calls confirmed what is well known, the probability, that these calls stand for updating row is very high. And because we don't see any kind of that calls for nonupdating case, I conclude there is no update. This conclusion is based on two assumptions: first - "update stuff" is made by the presented calls (in this particular case), second - it is made only by these method calls, i.e. no other method calls perform writing to the page (in this particular case). These assumptions may be a point of doubt, of course. That is why I said, that for [i]myself[/i], I consider it to be convincing. I also said in [i]common[/i] case, because this calls is just a reflection of the behavior for this particular case, that means that there might (and sure will) be many other cases, where there will be different execution path, and another call trace. So to say about it in common, for all possible scenarios, we should either look at sql server source code, or test all possible combinations, which is a huge room for investigation. But, as I said, for now I see no practical sense to do it. That is all IMHO, all the other opinions are welcome.p.s.[quote][b]SQL Kiwi (9/11/2012)[/b][hr]though your debugger looks prettier than WinDbg output, for sure[/quote]Well, in fact it is WinDbg. First screen is raw trace from WinDbg compared for both cases using Total Commander->Compare by Content, second is just more convenient for human representation in xml of the first one, viewed by XML Notepad.Wed, 12 Sep 2012 02:01:44 GMTSomewhereSomehowhttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspx[quote][b]SomewhereSomehow (9/11/2012)[/b][hr]What I observed was function "sqlservr!Record::FindDiff", after call to this one, the subsequent calls for updating and nonupdating differs. For non-updating: nothing happens. For updating: some extra checks before update performed, log record prepared and issued, row modified, page marks dirty.[/quote]Thanks for going to all the trouble, Dima. I did a similar test (though your debugger looks prettier than WinDbg output, for sure). The method calls do confirm what we already knew about log records, dirtypages and so on.[quote]As I conclude from what I've seen in debugger, writing the same value to the page in memory is not performed in common case.[/quote]I tend to agree, but I am not certain the debugger output proves it beyond all doubt. There is certainly no suggestion that a 'before copy' of the page is made, but from what I saw, it is *possible* that the engine constructs a new FixedVarRecord incorporating the 'update' and passes that to Record::FindDiff. On the other hand, it could be that FindDiff simply compares the column value on the page with the value the query processor asked it to be updated to. Without stepping through the (obfuscated) machine code line by line looking for memcpy versus memcmp calls in the right place, I don't think we could ever be 100% sure.Tue, 11 Sep 2012 11:01:31 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspxMy five cents to the topic.I made a simple experiment, created a table like 'create table t1(a int)', inserted one row and updated it with the same value, and with different value with attached debugger. What I observed was function "sqlservr!Record::FindDiff", after call to this one, the subsequent calls for updating and nonupdating differs.For non-updating: nothing happens.For updating: some extra checks before update performed, log record prepared and issued, row modified, page marks dirty.As I conclude from what I've seen in debugger, writing the same value to the page in memory is not performed in common case.Here is two screenshots. One for totalcmd compare window, comparing call trace. Another for shorten form of it, where trace output calls presented hierarchicaly.[img]http://www.somewheresomehow.ru/wp-content/uploads/2012/09/nonupdating-vs-updating.png[/img][img]http://www.somewheresomehow.ru/wp-content/uploads/2012/09/nonupdating_xml.png[/img]For myself I consider it quite a good proof that no writes are actually performed in common case.Tue, 11 Sep 2012 06:42:11 GMTSomewhereSomehowhttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspxIt is interesting that explicitly filtering out updates isn't always faster than performing a non-updating update either:[code="sql"]USE tempdb;SET NOCOUNT ON;GOCREATE TABLE dbo.Test( Name char(200) COLLATE Latin1_General_CI_AI NULL);GOINSERT dbo.TestSELECT TOP (123456) 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'FROM sys.columns AS cCROSS JOIN sys.columns AS c2;GODBCC FREEPROCCACHE;CHECKPOINT;GODECLARE @start datetime2 = SYSDATETIME();SET STATISTICS IO ON;UPDATE dbo.Test WITH (TABLOCKX)SET Name = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';SET STATISTICS IO OFF;SELECT test = 'Non-updating', time_ms = DATEDIFF(MILLISECOND, @start, SYSDATETIME());GODECLARE @start datetime2 = SYSDATETIME();SET STATISTICS IO ON;UPDATE dbo.Test WITH (TABLOCKX)SET Name = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'WHERE Name <> 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';SET STATISTICS IO OFF;SELECT test = 'Filtered', time_ms = DATEDIFF(MILLISECOND, @start, SYSDATETIME())GODECLARE @start datetime2 = SYSDATETIME();SET STATISTICS IO ONUPDATE dbo.Test WITH (TABLOCKX)SET Name = 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY';SET STATISTICS IO OFFSELECT test = 'Updating', time_ms = DATEDIFF(MILLISECOND, @start, SYSDATETIME());GODROP TABLE dbo.Test;[/code][code="plain"]Table 'Test'. Scan count 1, logical reads 3249, physical reads 0, read-ahead reads 0.Table 'Test'. Scan count 1, logical reads 3249, physical reads 0, read-ahead reads 0.Table 'Test'. Scan count 1, logical reads 3249, physical reads 0, read-ahead reads 0.[/code][img]http://www.sqlservercentral.com/Forums/Attachment12352.aspx[/img]Of course, it is usually faster to filter redundant updates (and definitely recommended) but I thought I'd share this interesting exception.Sun, 09 Sep 2012 21:36:15 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspx[quote][b]Sergiy (9/9/2012)[/b][hr]So, despite the value has been changed by UpdateMixedCase from 'TOM' to 'Tom' no logical writes recorded. Obviously, these statistics cannot be used like that for the case.[/quote]If you add a CHECKPOINT between procedure calls, you will see it counts the number of buffer pool pages dirtied by the operation. This is quite different from what the documentation currently says; I'll file a Connect bug to get that clarified. Shame, because I thought it showed how many writes the storage engine thought it had done. Oh well.Existing documentation:[url]http://msdn.microsoft.com/en-us/library/ms189741[/url][url]http://msdn.microsoft.com/en-us/library/cc280701[/url][url]http://msdn.microsoft.com/en-us/library/cc280646[/url]Connect item:[url]https://connect.microsoft.com/SQLServer/feedback/details/761859/clarify-the-description-for-logical-writes-in-sys-dm-exec-xxx-stats[/url]Sun, 09 Sep 2012 20:58:40 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspx[quote][b]SQL Kiwi (9/7/2012)[/b][hr]In SQL Server 2008 or later:[/quote]I changed the execution part of you script a little bit:[code="sql"]EXECUTE dbo.UpdateSame;SELECT * FROM dbo.Test -- added to check that the value in the record is actually changed GOEXECUTE dbo.UpdateCaps;SELECT * FROM dbo.Test GOEXECUTE dbo.UpdateMixedCase;SELECT * FROM dbo.Test [/code]Here is the outcome:[code="plain"]Outcome ID NameUpdateSame 1 TomUpdateCaps 1 TOMUpdateMixedCase 1 TomUpdateCaps 1 3UpdateMixedCase 0 3UpdateSame 0 3[/code]So, despite the value has been changed by UpdateMixedCase from 'TOM' to 'Tom' no logical writes recorded.Obviously, these statistics cannot be used like that for the case.Sun, 09 Sep 2012 18:10:23 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspx[quote][b]SQL Kiwi (9/7/2012)[/b][hr][quote]Try this:....[/quote]I don't think it proves anything.Logical/physical reads/writes are about [b]pages [/b]read/written.There was never an argument that SQL Server finds pages affected by the UPDATE not changed and therefore no further action is taken.Sun, 09 Sep 2012 17:40:11 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspxWow! What a thrilling as well as informative thread this has been! :cool:Fri, 07 Sep 2012 14:21:14 GMTDivine Flamehttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspx[quote][b]Sergiy (9/7/2012)[/b][hr]Because if I'd see anything what would show CLEARLY "that there is NO ROW MODIFICATION where the object of the update is unchanged" I'd happily accept it.[/quote]In SQL Server 2008 or later:[code="sql"]USE tempdb;GOCREATE TABLE Test ( ID integer IDENTITY(1,1) NOT NULL, Name varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL);GOINSERT dbo.Test (Name) VALUES ('Tom');GOCREATE PROCEDURE dbo.UpdateSameASUPDATE dbo.Test SET Name = Name;GOCREATE PROCEDURE dbo.UpdateMixedCaseASUPDATE dbo.Test SET Name = 'Tom';GOCREATE PROCEDURE dbo.UpdateCapsASUPDATE dbo.Test SET Name = 'TOM';GOCHECKPOINT;DBCC FREEPROCCACHE;GOEXECUTE dbo.UpdateSame;GOEXECUTE dbo.UpdateMixedCase;GOEXECUTE dbo.UpdateCaps;GOSELECT proc_name = OBJECT_NAME(deps.[object_id]), deps.last_logical_writes, deps.last_logical_readsFROM sys.dm_exec_procedure_stats AS depsWHERE deps.[object_id] IN ( OBJECT_ID(N'dbo.UpdateSame', N'P'), OBJECT_ID(N'dbo.UpdateMixedCase', N'P'), OBJECT_ID(N'dbo.UpdateCaps', N'P') )GODROP PROCEDURE dbo.UpdateSame, dbo.UpdateMixedCase, dbo.UpdateCaps;GODROP TABLE dbo.Test;[/code]Output:[img]http://www.sqlservercentral.com/Forums/Attachment12329.aspx[/img]Fri, 07 Sep 2012 06:31:10 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspx[quote][b]Sergiy (9/7/2012)[/b][hr]Because if I'd see anything what would show CLEARLY "that there is NO ROW MODIFICATION where the object of the update is unchanged" I'd happily accept it.[/quote]Try this:[code="sql"]USE tempdb;GOIF OBJECT_ID(N'dbo.Test', N'U') IS NOT NULL DROP TABLE dbo.Test;GOCREATE TABLE Test ( ID integer IDENTITY(1,1) NOT NULL, Name varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL);GOINSERT dbo.Test (Name) VALUES ('Tom');GOCHECKPOINT;DBCC FREEPROCCACHE;GOUPDATE dbo.Test SET Name = 'Tom'WHERE 1 <> 2;GOUPDATE dbo.Test SET Name = 'TOM'WHERE 1 <> 2;GOSELECT query = SUBSTRING ( dest.[text], deqs.statement_start_offset / 2 + 1, CASE WHEN deqs.statement_end_offset = -1 THEN DATALENGTH(dest.[text]) ELSE ((deqs.statement_end_offset - deqs.statement_start_offset) / 2) + 1 END ), deqs.last_logical_writes, deqs.last_logical_readsFROM sys.dm_exec_query_stats AS deqsCROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS destWHERE dest.[text] LIKE N'UPDATE%';[/code]Output:[img]http://www.sqlservercentral.com/Forums/Attachment12328.aspx[/img]Fri, 07 Sep 2012 05:45:19 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspx[quote]...[quote]Galileo was the only one which tried to oppose the common knowledge about geocentric model of the Universe.So, who was wrong and who was right?[/quote]I cannot believe you are comparing yourself with Galileo now.[/quote]What I could advise here to Sergiy:Add Galilei to your user name and be prepared to be condemned for "vehement suspicion of heresy".:hehe:And having reviewed some my posts about checking the values for update...It's good idea for client apps to check if the value is changed prior issuing the data modification statement. That is why ORM's quite often do this. It has nothing to do with row versioning on SQL level. From client prospective, even if the value was changed, by someone else, while user is looking into the previous version of it, it's absolutely fine not to issue update if the value is not changed. Otherwise, if row versioning is implemented, requesting non-updating update will return user some indication of data collision as the old (non-changed) version will try to overwrite newer one.Fri, 07 Sep 2012 03:19:01 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspx[quote][b]Sergiy (9/7/2012)[/b][hr][quote][b]MissTippsInOz (9/7/2012)[/b]You have had numerous genuine responses from people who are trying to help you to understand the behaviour of a 'non-updating update'. You have been given code (and you have quoted back this same code) that shows quite clearly that there is NO ROW MODIFICATION where the object of the update is unchanged.[/quote]I must have missed that.Honestly.Because if I'd see anything what would show CLEARLY "that there is NO ROW MODIFICATION where the object of the update is unchanged" I'd happily accept it.[/quote]The page is not marked as dirty, not flushed to disk, and no changes are logged. Are you seriously suggesting that the reason a large non-updating update takes so long is because it follows your idea of writing to the page and comparing it with a previous version?[quote]Galileo was the only one which tried to oppose the common knowledge about geocentric model of the Universe.So, who was wrong and who was right?[/quote]I cannot believe you are comparing yourself with Galileo now.Fri, 07 Sep 2012 01:45:42 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspx[quote][b]Sergiy (9/7/2012)[/b][hr][quote][b]SQL Kiwi (9/7/2012)[/b][hr] I have said, over and over again, that I don't know whether the in-memory page is written to or not. A page that is exactly the same afterward, is not marked as dirty, and doesn't get written to disk...why would anyone care?[/quote]Ask Eugine.I guess he has already answerred.Somewhere in the depth of this topic. Page 5 or 6 or so.When you do such updates on tables with millions of rows on a regular basis you see the difference.Huge difference.[/quote]No-one (aside from Celko, and he doesn't count) is disputing that queries should be written to only change rows that need changing. No-one is saying there isn't significant overhead in performing changes that don't change anything. There is. If that is your only concern, we can all agree on that (we never disagreed) and move on.However, the debate has been about whether SQL Server actually writes to the page or not. Not whether it is inefficient to pass rows along the query plan that don't need updating. Not whether it is inefficient to take so many exclusive locks, latches, or any number of overheads involved in processing rows that do not need processing.Do you seriously think so many experienced people on here don't know that performing redundant updates is wasteful?Fri, 07 Sep 2012 01:35:55 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspx[quote][b]SQL Kiwi (9/7/2012)[/b][hr] I have said, over and over again, that I don't know whether the in-memory page is written to or not. A page that is exactly the same afterward, is not marked as dirty, and doesn't get written to disk...why would anyone care?[/quote]Ask Eugine.I guess he has already answerred.Somewhere in the depth of this topic. Page 5 or 6 or so.When you do such updates on tables with millions of rows on a regular basis you see the difference.Huge difference.Fri, 07 Sep 2012 01:23:33 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspxSergiy,If the only point you are trying to make is that non-updating updates do write to a clustered index or heap page (without changing it in any way) I will happily run a test later with a debugger attached to SQL Server to see if there are any calls to write to the page. This is quite a bit of work, however, so I hope you will accept the result whichever way it goes.I still don't have the faintest idea why you care about this, but if it makes you happy, I am willing to do this for you. I do think you should have tried this for yourself 100 posts ago (as it is only really you that cares).Fri, 07 Sep 2012 01:20:51 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspx[quote][b]Sergiy (9/7/2012)[/b][hr]Key word - changes. No matter if it's rolled back - there was a change. IO operation.Even if everything remained like it was before - there was a change made.[/quote]Yes; even if no change occurred from a non-updating update, it is counted. How does that help you know whether the in-memory page was written to or not?[quote]If you remove a screw from a wall and then immediately put it back - it's all the same after all.The operation is rolled back, the operation did not change anything.But operation was still performed.Actually 2 operations - do and un-do.Energy and resources spent.That's the fact.You cannot get the proof of it from the screw in the wall (unless you were not careful and damaged something around).But you can get a proof from you body temperature or electricity meter (if you used an electric screwdriver).Same with data. To get the proof you need analyse side indicators, like a temperature of the memory chip.And it will surely indicate that X lock means actual writing, as they say in BOL.[/quote]Again, we are all agreed that exclusive locks are taken before any potential modification. How does that help you know whether the in-memory page was written to or not?[quote]Isn't it what the OP question was about?[/quote]No it was not. We have been over this before. I am not going to repeat myself.Fri, 07 Sep 2012 01:17:33 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspx[quote][b]MissTippsInOz (9/7/2012)[/b]You have had numerous genuine responses from people who are trying to help you to understand the behaviour of a 'non-updating update'. You have been given code (and you have quoted back this same code) that shows quite clearly that there is NO ROW MODIFICATION where the object of the update is unchanged.[/quote]I must have missed that.Honestly.Because if I'd see anything what would show CLEARLY "that there is NO ROW MODIFICATION where the object of the update is unchanged" I'd happily accept it.So far the best Paul could come up with was "we cannot know for sure". [quote][quote]Don't you think you might be wrong on this topic?Just like you've been wrong on the topic about index fragmentation?[/quote]check back to add a whole list of others who are in agreement with him on this topic. So everyone is wrong - and you are right?[/quote]Since when the numbers are the proof?The law of scientific consensus: At times of high scientific controversy, the consensus is always wrong. Galileo was the only one which tried to oppose the common knowledge about geocentric model of the Universe.So, who was wrong and who was right?Appealing to the majority comes when you can't argue the case but don't want to admit you've been wrong on the matter.Fri, 07 Sep 2012 01:17:33 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspx[quote][b]Sergiy (9/7/2012)[/b][hr][quote][b]SQL Kiwi (9/7/2012)[/b][hr][quote][quote] If you don't stop being snarky in your replies[/quote]You may notice I just quoted you. Word to word.[/quote]And that is exactly the problem. My question to you was genuine. You were just being snarky.[/quote]Can you imagine my question was also genuine? I just did not want to appear aggressive with my question (because of the cultural difference mentioned above), so I used the wording which is approved to be OK.[/quote]I want to believe you, Sergiy, really I do.[quote]Aren't we both looking for a truth here?[/quote]I'm just doing my best to answer each new question you ask.[quote]Don't you think you might be wrong on this topic?[/quote]Wrong about what? I have said, over and over again, that I don't know whether the in-memory page is written to or not. A page that is exactly the same afterward, is not marked as dirty, and doesn't get written to disk...why would anyone care?[quote]Just like you've been wrong on the topic about index fragmentation?[/quote]I have been wrong many times, and I usually learn something from that. I have no idea what you are talking about regarding index fragmentation though.If it makes your life happier, I will happily agree that the in-memory page might be written to - but only in the case of a clustered index or a heap. Non-clustered indexes, no.Fri, 07 Sep 2012 01:14:03 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspx[quote][b]Sergiy (9/7/2012)[/b][hr][quote][b]SQL Kiwi (9/7/2012)[/b][hr][quote][quote] If you don't stop being snarky in your replies[/quote]You may notice I just quoted you. Word to word.[/quote]And that is exactly the problem. My question to you was genuine. You were just being snarky.Can you imagine my question was also genuine?I just did not want to appear aggressive with my question (because of the cultural difference mentioned above), so I used the wording which is approved to be OK.[/quote][quote]What on earth have I said to deserve this?[/quote]Something like this:"I am reluctantly going to stop assisting you."Aren't we both looking for a truth here?Don't you think you might be wrong on this topic?Just like you've been wrong on the topic about index fragmentation?[/quote]Having watched this thread from day 1...yes, you are being snarky and, at times, aggressive. You have had numerous genuine responses from people who are trying to help you to understand the behaviour of a 'non-updating update'. You have been given code (and you have quoted back this same code) that shows quite clearly that there is NO ROW MODIFICATION where the object of the update is unchanged.You have had many more responses, explaining that the locking mechanism operates in exactly the same way in response to the code being executed, regardless of whether the data ends up being changed or not. If a train is scheduled to stop at a station, then that's what it does. This doesn't mean people will get on or off the trainYou are battling on in vain, seemingly to try and prove a point that was already disproved, with evidence. Why are you persisting in this; and alienating people who only want to teach or learn, in the process?If you want to take down Paul for whatever reason,[quote]Don't you think you might be wrong on this topic?Just like you've been wrong on the topic about index fragmentation?[/quote]check back to add a whole list of others who are in agreement with him on this topic. So everyone is wrong - and you are right?Fri, 07 Sep 2012 00:51:41 GMTMissTippsInOz