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

SQLServer low throughput tables increase usage of TempDB

XMLWordPrintable

    • False
    • False
    • Undefined
    • Hide

      Configure a DB test with CDC enabled.

      Create a table in that DB test.low_throughtput and insert only one row.

      Deploy a Debezium SQLServer connector to ingest data from that DB.

      Wait until SQLServer CDC clean up job clears the LSN info. After this happens sys.fn_cdc_get_max_lsn() will start returning a null value as second column.

      After this step, each time Debezium asks for new changes there is a constant increase in the usage of SQLServer TempDB.

      Show
      Configure a DB test with CDC enabled. Create a table in that DB test.low_throughtput and insert only one row. Deploy a Debezium SQLServer connector to ingest data from that DB. Wait until SQLServer CDC clean up job clears the LSN info. After this happens sys.fn_cdc_get_max_lsn() will start returning a  null value as second column. After this step, each time Debezium asks for new changes there is a constant increase in the usage of SQLServer TempDB.

      When ingesting data from a SQLServer table if the throughput of that table is very low (i.e. the gap between inserts/updates is large) causes an ever growing increase of usage of TempDB which in time becomes significant enough.

       

      We've experienced this in databases where we store configuration like data that changes at most once a month.

       

      Digging into Debezium's code we've found that the issue is caused by the JDBC Connection is not committed neither roll-backed when there are no new changes and that if the situation last for long it ends up affecting TempDB as each result returned by sys.fn_cdc_get_max_lsn() gets stuck there.

       

      As a side note, when reading from a Read Only DB (applicationIntent: ReadOnly) the issue cannot be reproduced and it is because of the Connection being roll-backed before every read from LSN function.

      This last statement makes me aks: What is the reason behind disabling JDBC Connection auto-commit and if it is possible to avoid such behaviour?

       

            jpechane Jiri Pechanec
            zurvarian Joaquin Montesinos (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            7 Start watching this issue

              Created:
              Updated:
              Resolved: