Search code examples
sql-servert-sqllockingisolation-leveltransaction-isolation

Best suitable transaction isolation level when business rules are not strict

Short version: I am trying to determine which is the best transaction isolation level for the SQL server in our premises.

Long version: I am extracting data from API calls and loading it into staging tables which then are incrementally loaded into destination tables. These destination tables are used in multiple ways, some of which are mentioned below:

  1. Load data into a CRM through SSIS
  2. Feed PowerBI reports (scheduled refreshes)
  3. Apply business transformations to the data and load it into a Data Warehouse
  4. Extract data into excel documents
  5. (Most importantly) Do changes to the destination tables outside of the initial ETL process (From API to Staging to Destination)

Due to the large datasets, the issues I am facing are:

  1. Deadlocks which I avoid by utilizing temporary tables and CTEs
  2. Long waits between the updates of the tables (since one stored procedure that updates a destination table may wait up to an hour until this table is not used by another update)
  3. Long PowerBI refresh waits and sometimes refresh timeouts when the SQL tables are being updated
  4. Long Select statement waits when the SQL tables are being updated

Given that:

  1. The industry I'm working is not banking or a sort of industry in which the data needs to be 100% accurate all the time
  2. The PowerBI reports refresh only twice a day
  3. I urgently need to utilize the data in those destination tables for other reporting purposes too
  4. The datasets contain millions of records

What isolation level is suitable for this occasion? Or would it be better to set individual isolation levels through table hints?

Note1 : My employer and I would not mind if we had some dirty reads in the report refreshes as long as this means the reports refresh in a consequent manner and the tables can be used in other stored procedures (both read and update) without having to wait.

Note2 : The is_read_committed_snapshot_on is 0 in our SQL server.

Solution

  • READ COMMITTED with READ COMMITTED SNAPSHOT ISOLATION set for the database will enable readers to read without getting blocked by writers, prevent the writers from being blocked by the readers, and it doesn’t cause dirty reads.

    So that’s the obvious first step.