Очень медленное обновление таблиц

Так что это оказалось довольно прямолинейным. Мы не воспроизводили существующее поведение.

В миграции насчитывается ок. 1800 таблиц ODS, все они имеют флаг ROW_SENT_TO_EDW, но в огромном количестве из них этот флаг никогда не устанавливался, поэтому все строки равны 0. «Медленные» таблицы были теми, в которых все строки были равны 0, поэтому обновление было против многих сотни миллионов миллиардов строк. Это подтверждает, что ведение журнала ТРАНЗАКЦИИ для ОБНОВЛЕНИЯ является основной причиной замедления, а случайность связана с тем, сколько строк нам нужно было обновить. (Я до сих пор не понимаю, почему установка всего столбца = 0 для таблицы COLUMNSTORE INDEX не очень быстрая)

Выполнение некоторого анализа и рассмотрение использования HEAPS вместо таблиц CLUSTERED COLUMN INDEX. Это все было при 3000C DWU

Влияние HEAP на CCI

Таблица CCI (обновить все 1,8 млрд строк)

-- (staticrc40) 1hr 43m 47s
-- (largerc)   1hr  4m 11s 

Таблица HEAP (обновить все 1,8 млрд строк)

-- (staticrc40) 2hr 13m 5s
-- (largerc)        48m 47s

CTAS выводит всю таблицу, просто переключая значение столбца

CTAS — ответ Microsoft на все

ТПП -- (staticrc40) 56м

КУЧА -- (staticrc40) 1ч 35м

Да, это быстрее, чем обновлять всю таблицу, но все же слишком медленно для нас.

Влияние класса ресурсов

Таблица CCI обновляет 14 млн строк из 1,8 млрд (типичный пользователь smallrc)

-- (staticrc40)  1m 30s
-- (smallrc)     1m 40s

Таблица CCI со свежей STATS в столбце ROW_SENT_TO_EDW до переключения 0 на 1

-- (staticrc40) 1m 6s
-- (smallrc)    1m 8s

Таблица HEAP обновляет 14 млн строк из 1,8 млрд (типичный пользователь smallrc)

-- (staticrc40)  30s
-- (smallrc)     37s

Таблица HEAP со свежей STATS в столбце ROW_SENT_TO_EDW перед переключением 0 на 1

-- (staticrc40) 25s
-- (smallrc)    25s

Таким образом, похоже, что HEAPS работает лучше, чем CCI в этом конкретном случае, и классы ресурсов имеют значение, но не так сильно, как вы думаете (операции хранения и ведение журнала транзакций, очевидно, являются критическими факторами).

Sneaky Полный стол Обновление

Наконец, изменения полной таблицы явно огромны и не оптимальны. Поэтому мы решили полностью удалить столбец и добавить его обратно со значением по умолчанию.

Вы должны найти любую статистику и удалить ее, прежде чем вы сможете удалить столбец. Удаление столбца занимает 15 секунд для CCI и 20 секунд для HEAP.

Добавление его обратно со значением по умолчанию на CCI занимает 29 секунд.

В HEAP это занимает 20 секунд (шокирован этим, так как я ожидал, что запись страницы займет огромное количество времени, но очевидно, что под прикрытием происходит что-то умное)

Вы должны сразу же удалить ограничение по умолчанию, иначе вы не сможете снова удалить столбец, а также снова включить статистику. (это часто занимает 20 секунд)

Но для обоих типов таблиц этот метод очень быстрый по сравнению с полным обновлением таблицы.

SQL для этого удаления и добавления выглядит следующим образом

-- get the name of the stat to drop
SELECT
              t.[name]                            AS [table_name]
              , s.[name]                          AS [table_schema_name]
              , c.[name]                          AS [column_name]
              , c.[column_id]                     AS [column_id]
              , t.[object_id]                     AS [object_id]
              ,st.[name]    As stats_name
              , ROW_NUMBER()
                     OVER(ORDER BY (SELECT NULL))    AS [seq_nmbr]
       FROM
              sys.tables t
              JOIN sys.schemas s ON  t.[schema_id] = s.[schema_id]
              JOIN sys.columns c ON  t.[object_id] = c.[object_id]
              INNER JOIN sys.stats_columns l ON  l.[object_id] = c.[object_id]
                     AND l.[column_id] = c.[column_id]
                     AND l.[stats_column_id] = 1
              INNER JOIN sys.stats st
                     ON  t.[object_id] = st.[object_id]
                     and l.stats_id = st.stats_id
WHERE t.[object_id] = OBJECT_ID('BKP.SRC_xxx')

DROP STATISTICS BKP.SRC_xxx._WA_Sys_0000000E_2CEA8251

-- now alter the actual table
ALTER TABLE BKP.SRC_xxx 
DROP COLUMN ROW_SENT_TO_EDW
-- 13s

ALTER TABLE BKP.SRC_xxx 
       ADD ROW_SENT_TO_EDW INT NOT NULL DEFAULT 1
-- 1s  


-- find the constraint
SELECT t.[name] AS Table_Name, 
              c.[name] AS Column_Name,
              dc.[name] as DefaultConstraintName
FROM   sys.tables t 
                     INNER JOIN sys.columns c 
                           ON t.[object_id] = c.[object_id] 
                     LEFT OUTER JOIN sys.default_constraints dc
                           ON  t.[object_id] = dc.parent_object_id
                           AND c.column_id = dc.parent_column_id
WHERE  t.[object_id] = OBJECT_ID('BKP.SRC_xxx')
       AND c.[name] = 'ROW_SENT_TO_EDW'

-- drop the constraint
ALTER TABLE BKP.SRC_xxx DROP CONSTRAINT [Cnstr_7183c5bec657448da3475af85110123a]
--  18s

-- create stats
CREATE STATISTICS [thingy] ON BKP.SRC_xxx([ROW_SENT_TO_EDW])
--  9s

Я использовал тот же шаблон с столбцом BIT для идентификации измененных/вставленных записей ODS, которые должны инициировать обновление измерения.

Я специально не тестировал CCI на исходных таблицах, я использовал кучу, так как все они были на 10 миллионов строк меньше. Однако я обнаружил, что важно иметь некластеризованный индекс в столбце BIT и использовать CONVERT(BIT..., чтобы индекс можно было использовать. 0 или 1 в предложении where без CONVERT/CAST приходит как INT и может заблокировать возможность использования NCI. Это оказало огромное влияние на производительность как при обновлении размеров на основе столбца флагов, так и при операции обновления.

ОБНОВЛЕНИЕ src_table УСТАНОВИТЬ обработанный_флаг = 1 где обрабатываемый_флаг = ПРЕОБРАЗОВАТЬ (БИТ, 0)

Кстати, полностью согласен с мнением, что CTAS слишком часто является ответом на все вопросы, когда это не всегда практично, и часто это более медленная операция просто из-за необходимости копировать так много данных.