Правильный план для Cross DB views

Question

На уровне представления собираю данные из нескольких БД с одинаковой структурой:
CREATE VIEW [dbo].[TestCrossView] 
AS
(
SELECT
      ID,
      Result.SText,
      Result.DbName
FROM  ( SELECT
              CAST(0 AS INT) AS ID,
              CAST('' AS NVARCHAR (100)) AS [SText],
              CAST('' AS sysname) AS [DbName]
        UNION 
        SELECT
              [id],
              [SText],
              [DbName] AS [DbName]
        FROM  [DbTestCheckCross1]..TableSession
        UNION 
        SELECT
              [id],
              [SText],
              [DbName] AS [DbName]
        FROM   [DbTestCheckCross2]..TableSession
        UNION 
        SELECT
              [id],
              [SText],
              [DbName] AS [DbName]
        FROM   [DbTestCheckCross3]..TableSession ) AS Result
);

Далее выполняю запрос с фильтрацией по имени БД:
DECLARE @DbName sysname = N'DbTestCheckCross1'

SELECT TOP (1000) tcv.[ID]
      ,tcv.[SText]
      ,tcv.[DbName]
  FROM [DbTestCheckCrossMain].[dbo].[TestCrossView] tcv
  WHERE DbName = @DbName

В плане запроса вижу сканы по всем базам внутри представления.

Если такой запрос выполняется долго, то запрос которому требуется блокировка схемы, будет ожидать выполнения этого запроса.
Проблему решает хинт OPTION (RECOMPILE) или указание константы в фильтре.

Есть ли другой способ заставить движок отсечь ненужные БД?
Секционирование не предлагать ;)

i-one · Accepted Answer

Операторы сканирования в плане запроса располагаются за фильтрами, в свойствах которых определены startup expression, например:

Это означает, что в run-time сканирование будет выполняться только если соответствующий фильтр его пропустит (startup-предикат будет иметь значение True).
Из четырёх веток, объединяемых в Concatenation, фактически лишь одна (выделенная) поставляет данные при указанном значении переменной

Только у выделенного Table Scan число выполнений ненулевое

у остальных операторов сканирования оно равно нулю

Есть, однако, нюанс. Перед началом выполнения запроса execution engine установит Sch-S блокировки (schema stability) на все таблицы, участвующие в запросе (независимо от того, будет ли происходить чтение данных из конкретной таблицы, или не будет). Соответственно ALTER, TRUNCATE и всё, для чего требуется Sch-M блокировка (schema modification), становятся невозможны на таблицах до окончания запроса.
Если нужно этого по-возможности избегать, то вариант с константой, в данном случае, видимо самый лучший (либо то же самое с помощью динамического запроса). Вариант с RECOMPILE менее желателен, но он может быть не таким уж и злом, если компиляция запроса малозатратна относительно выполнения.
Следует отметить, что Sch-S блокировки устанавливаются также и на время компиляции. С константой или RECOMPILE, когда ненужная таблица полностью выпадает из плана, время нежелательной блокировки будет естественно меньше.
Порой невозможно угодить всем требованиям, приходится выбирать разумный компромисс, исходя из того, что важнее.

Правильный план для Cross DB views

One Answer

Add your own answers!

Ask a Question