Deferred Update

  • Amit Lohia

    SSCertifiable

    Points: 6104

    What are the different situations where we can see "deferred update". I am aware of "any column that is part of a unique constraint/Clustured Index is updated"

     


    Kindest Regards,

    Amit Lohia

  • TriggerMe

    SSChampion

    Points: 11752

    I think you need to elaborate here!

    What are you talking about?

    What does "Deferred Update" have to do with Replication?


    Kindest Regards,

  • Amit Lohia

    SSCertifiable

    Points: 6104

    http://support.microsoft.com/kb/238254/EN-US/

     


    Kindest Regards,

    Amit Lohia

  • TriggerMe

    SSChampion

    Points: 11752

    Cool. I learn someting new every day!

    Now, what is the problem?

    Obviously on a UNIQUE Constraint, it will send the INSERT/DELETE/UPDATE as a pair instaed of a single transaction.


    Kindest Regards,

  • Amit Lohia

    SSCertifiable

    Points: 6104

    I learn every hour

    I change the server setting to send as a pure update instead of INSERT/DELETE pair but I want to learn (know) what are the other scenario for "deferred update"


    Kindest Regards,

    Amit Lohia

  • homebrew01

    SSC Guru

    Points: 55137

    We have a third party application that seems to do deferred updates as part of their processing. I noticed this when analyzing the results of triggers from their replicated tables. When balancing Inserts, Updates & Deletes, I realized that many transactions were coming through as Deletes and Inserts instead of just an Update.  So If I get a delete followed by an Insert for the same key, then I count it as an update.  It was over a year ago, but I remember being a little confused for a while.

    So, SQL may not generated deferred updates, but your software might !

  • Amit Lohia

    SSCertifiable

    Points: 6104

    This is happening in Transaction Replication.

     


    Kindest Regards,

    Amit Lohia

  • R.P.Rozema

    SSChampion

    Points: 12300

    Deferred updates are nowadays even more hot than they were 12 years back: today not only transactional replication uses the log reader's output, but also CDC uses the same output. Transaction replication's code was hidden for us end users, so we hardly needed to be aware of deferred updates. CDC however exposes our code to the deferred updates . The fn_cdc_get_net_changes_ functions are defined to return __$operation = 1 for deletes, __$operation = 2 for inserts, __$operation = 3 for an update's old value and __$operation = 4 for an update's new value. Some issues in the fn_cdc_get_net_changes_ functions in relation to deferred updates have been fixed, but not all of them (still surplus rows with __$operation = 1's can be returned). But the work arounds have come at a serious performance cost: fn_cdc_get_net_chages functions will choke-up on large(r) sets of changes in the _CT tables because at least 3 scans of the _CT tables are needed to do the workarounds correctly. Instead of putting work arounds in the fn_cdc_get_net_changes function, MS should be putting her resources onto having the log reader fixed to have it properly write __$operation 3 & 4 for all updates, instead of 1 & 2 for the deferred updates.

    This thread shows how little knowledge is generally available on deferred updates. So I'd like to leave a link here to the only article I've found -albeit for SQL 7.0- that explains in detail what a deferred update actually is: http://sqlmag.com/sql-server/sql-server-70-update-strategies



    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]


    If you don't have time to do it right, when will you have time to do it over?

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply