table_refresh_method: dml, the default, holds a Sch-M lock on the scratch table for the entire load

[Benjamin-Knight]

Summary

On a materialized="table" model with table_refresh_method: dml, the scratch table is
built by a single fused SELECT * INTO. That one statement both creates the object and
loads it, so SQL Server holds a Sch-M lock on the new object from
the moment the statement starts until it finishes, rather than for the instant of creation.

Sch-M is the one lock mode incompatible with the Sch-S lock that every metadata reader
takes. So for the entire duration of the load, any session reading metadata for that object
blocks. On a model whose load takes minutes, that is minutes of blocked metadata readers.

Environment

• dbt-sqlserver 1.12.0rc2, dbt-core 1.12.0
• SQL Server, ODBC Driver 18, RCSI enabled
• Model config: materialized="table", table_refresh_method: dml,
  full_refresh_build: prebuilt, as_columnstore: false, clustered PAGE index in indexes

Reproduction conditions

A steady-state run (no --full-refresh) of a table model with table_refresh_method: dml
whose load takes a non-trivial amount of time. Note that full_refresh_build: prebuilt is
set but not reached — see Code path below.

Observed

The statement holding the lock:

/* {"app": "dbt", "node_id": "model.my_project.my_model"} */
SELECT * INTO "my_db"."my_schema"."my_model__dbt_refresh"
FROM  "my_db"."my_schema"."my_model__dbt_refresh__dbt_tmp_vw"

Code path

Line references against dbt-sqlserver 1.12.0rc2 as installed.

macros/materializations/models/table/table.sql:30-39 — the gate:

{%- set use_prebuilt = (
  full_refresh_build == 'prebuilt'
  and (should_full_refresh() or existing_relation is none)
) -%}
{%- set use_dml_refresh = (
  table_refresh_method == 'dml'
  and not use_prebuilt
  and existing_relation is not none
  and existing_relation.type == 'table'
) -%}

On a steady-state run should_full_refresh() is false and the relation exists, so
use_prebuilt is false and the DML path takes over regardless of the prebuilt setting.

macros/materializations/models/table/table_dml_refresh.sql:52-54 — the fused statement:

{% call statement('main') -%}
  SELECT * INTO {{ refresh_relation }} FROM {{ tmp_vw_relation }} {{ query_label }}
{%- endcall %}

Cause

Two factors independently hold Sch-M across the load.

The fused statement. SELECT ... INTO merges DDL and data movement into a single
statement. The Sch-M lock on the new object is taken when the statement begins and released
only when it completes. This holds even under autocommit, since a single statement always
holds its own locks for its full duration.

The ambient transaction. dml_refresh_cleanup_pre (table_dml_refresh.sql:41-44)
issues DROP TABLE IF EXISTS {{ refresh_relation }} as the run's first statement. That opens
the ambient dbt transaction and takes Sch-M on the prior scratch table, held until the
materialization's trailing adapter.commit() (table.sql:139) — spanning the load
independently of the fusion above.

The same fusion appears in sqlserver__create_table_as (macros/relations/table/create.sql:53),
which serves the rename-swap path, so that path is expected to have the same exposure.

Impact

A slow model blocks metadata reads for that object across every other session on the
database, for as long as the load runs. The cost of a slow model should fall on the session
running it.