Uploaded image for project: 'Debezium'
  1. Debezium
  2. DBZ-2582

SqlServer - Skip processing of LSNs not associated with change table entries.

    XMLWordPrintable

    Details

      Description

      As described here:
      https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/cdc-lsn-time-mapping-transact-sql?view=sql-server-ver15

      There are dummy LSN values that "allows the table to record the completion of LSN processing in periods of low or no change activity."
      Processing the LSNs that come from this low or no change activity are wasted energy as there's no changes associated with them and they can be safely ignored.

      If we replace using `SELECT sys.fn_cdc_get_max_lsn()` to get the maximum LSN with something like 

      SELECT (SELECT MAX(start_lsn) FROM cdc.lsn_time_mapping) as max_lsn, (SELECT MAX(start_lsn) FROM cdc.lsn_time_mapping WHERE tran_id <> 0x00) as max_lsn_with_transaction
      

      we can use max_lsn_with_transaction in the comparison to determine if there's been any changes in the CTs.

      This is desirable for us as Debezium is causing large spikes in CPU usage on the database every 5 minutes by polling for all the cdc tables, even though there's nothing to process.

       

      Potentially there's a way we could make the LSN handling generic enough to allow customization for this? 

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            jgormley6 James Gormley
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: