Настройка производительности CDC

Автор: Штеффен Краузе (Steffen Krause)

Соавторы: Санджай Мишра (Sanjay Mishra), Гопал Ашок (Gopal Ashok), Грег Ивкофф (Greg Yvkoff), Жуй Ван (Rui Wang)

Технические рецензенты: Бурзин Патель (Burzin Patel), Денни Ли (Denny Lee), Гленн Берри (Glenn Berry, MVP SQL Server), Джозеф Сак (Joseph Sack), Линдси Аллен (Lindsey Allen), Майкл Редман (Michael Redman), Майк Рутрафф (Mike Ruthruff), Пол С. Рэндал (Paul S. Randal, SQLskills.com), Tuning the Performance of Change Data Capture in SQL Server 2008

Краткое содержание: Отслеживание изменений данных (Change Data Capture, CDC) — это новая функция в SQL Server, которая предоставляет простой способ отслеживания изменений данных в наборе таблиц базы данных для последующей передачи этих изменений во вторую систему, например, в хранилище данных. В этом документе содержатся рекомендации по настройке параметров отслеживания изменений данных для максимальной производительности захвата данных при минимальном влиянии на производительность производственной нагрузки. Область действия этого документа ограничена захватом изменяемых данных и процессом очистки. Запрос изменённых данных не входит в область действия данного технического документа.

Введение

Для получения вводной информации о функции отслеживания изменений данных см. раздел «Change Data Capture» в электронной документации SQL Server.

Обратите внимание, что в SQL Server есть ещё одна новая функция — отслеживание изменений (Change Tracking), которая также позволяет отслеживать строки таблиц, изменённые командами DML (insert, update, delete, merge). Отслеживание изменений предназначено для синхронизации слабо связанных приложений баз данных и отслеживает только первичные ключи изменённых строк и, при необходимости, какие столбцы изменились, но не сами изменённые данные. Отслеживание изменений также не входит в область действия этого документа. Для получения дополнительной информации об этих двух функциях см. раздел «Comparing Change Data Capture and Change Tracking» в электронной документации SQL Server.

Преимущества отслеживания изменений данных

До появления CDC обнаружение изменений в исходной базе данных для передачи этих изменений в хранилище данных требовало либо специальных столбцов в исходных таблицах (метки времени, версии строк), либо триггеров, фиксирующих изменения, либо сравнения исходной и целевой систем. Эти методы могут иметь существенные недостатки: специальные столбцы требуют изменения схемы исходной базы данных и во многих случаях изменения логики приложения. Триггеры необходимо реализовывать вручную, и они могут создавать значительную дополнительную нагрузку на команды DML исходной системы. Сравнение исходной и целевой баз данных может создавать высокую нагрузку на обе системы.

Отслеживание изменений данных позволяет фиксировать изменения в исходной системе путём асинхронного чтения журнала транзакций исходной базы данных. Для этого отслеживание изменений данных использует тот же модуль чтения журнала, который используется в транзакционной репликации. Поскольку отслеживание изменений данных работает с существующими схемами таблиц, исходную базу данных или приложение не нужно изменять для включения отслеживания изменений данных. Поскольку задание чтения журнала работает асинхронно, транзакции DML подвергаются гораздо меньшему влиянию, чем при синхронных решениях, таких как триггеры. Все изменения исходных таблиц записываются в специальные таблицы изменений, поэтому сравнение исходной и целевой систем на предмет изменений не требуется.

Основной метод работы отслеживания изменений данных

Когда отслеживание изменений данных включено для базы данных, создаётся схема cdc и набор таблиц метаданных в этой схеме. Большинство этих таблиц отслеживания изменений данных содержат метаданные и имеют очень небольшой объём данных и транзакционную нагрузку. Единственная таблица метаданных, которая часто записывается во время работы отслеживания изменений данных и может достигать значительного размера, — это таблица cdc.lsn_time_mapping, которая хранит сопоставление между номерами последовательности журнала (LSN) и датой и временем совершения транзакции.

Когда впоследствии таблица включается для отслеживания изменений данных, создаётся экземпляр захвата (capture instance). Экземпляр захвата состоит из таблицы изменений (cdc.<имя_экземпляра_захвата>_CT) и до двух табличных функций для запроса этой таблицы изменений. Для каждой таблицы может быть до двух экземпляров захвата. Если в базе данных нет задания сканирования журнала для транзакционной репликации или другого экземпляра захвата, в агенте SQL Server создаются задание захвата и задание очистки с параметрами по умолчанию. Задание захвата запускается автоматически.

После включения задания захвата оно будет записывать одну строку в таблицу изменений для каждой строки, вставленной или удалённой в исходной таблице, и две строки для каждой строки, обновлённой в исходной таблице (одна строка, содержащая значения до обновления, и одна строка, содержащая значения после обновления). Таблица изменений всегда содержит значения для всех захваченных столбцов в исходной таблице, а не только для столбцов, которые изменили своё значение во время обновления. Поэтому для распространения изменений в хранилище данных достаточно данных только из таблицы изменений. В дополнение к данным из исходной таблицы таблица изменений содержит как минимум 37 байт дополнительных данных для каждой строки изменённых данных (или больше, в зависимости от количества захваченных столбцов).

Настройка параметров sys.sp_cdc_enable_table

Хранимая процедура sys.sp_cdc_enable_table имеет несколько параметров, которые, как мы обнаружили, важны для производительности отслеживания изменений данных. Параметры, не влияющие на производительность, здесь не обсуждаются.

Параметр @capture_instance определяет имя экземпляра захвата для этой таблицы. Само имя не влияет на производительность. Однако этот параметр можно использовать для создания второго экземпляра захвата для той же таблицы. Функция наличия второго экземпляра захвата предназначена только для сценариев обновления схемы, когда выполняется изменение схемы исходной таблицы. Старый экземпляр захвата следует отключить, как только он перестанет быть нужен, потому что наличие двух активных экземпляров захвата для одной таблицы означает, что необходимо записывать вдвое больше изменённых данных. Это может существенно повлиять на производительность.

Когда в исходной таблице происходит изменение схемы, следует сделать следующее:

1. Изменить схему исходной таблицы.
2. Создать новый экземпляр захвата. Этот новый экземпляр захвата создаётся с новой (изменённой) схемой исходной таблицы.
3. Дождаться изменения, а затем прочитать минимальный LSN из нового экземпляра захвата (используя sys.fn_cdc_get_min_lsn(имя_нового_экземпляра_захвата)).
4. Прочитать и обработать все изменения из старого экземпляра захвата до, но не включая, первого LSN из нового экземпляра захвата.
5. Отключить старый экземпляр захвата.
6. Обновить все ссылки на старое имя экземпляра захвата новым именем.
7. Продолжить чтение из нового экземпляра захвата.

Параметр @captured_column_list определяет, какие столбцы исходной таблицы включаются в таблицу изменений и захватываются отслеживанием изменений данных. Если этот параметр не указан или равен NULL, в таблицу изменений включаются все столбцы исходной таблицы. Мы обнаружили, что количество и размер захваченных столбцов существенно влияют на производительность отслеживания изменений данных и требуемое дисковое пространство. Производительность отслеживания изменений данных в целом лучше, когда количество захваченных столбцов меньше, потому что объём данных, которые необходимо записать в таблицы изменений, меньше.

Параметр @supports_net_changes определяет, возможны ли запросы к таблице изменений с «чистыми» изменениями (с использованием cdc.fn_cdc_get_net_changes_<capture_instance>). Запросы с «чистыми» изменениями возвращают только одну строку изменений для каждой изменённой исходной строки, независимо от количества изменений. Когда @supports_net_changes установлен в 1, в таблице изменений создаётся дополнительный некластерный индекс и создаётся функция запроса «чистых» изменений. Поскольку этот индекс необходимо обслуживать, мы обнаружили, что включение «чистых» изменений может отрицательно сказаться на производительности отслеживания изменений данных.

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

Настройка задания захвата

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

Задание захвата, созданное в агенте SQL Server для отслеживания изменений данных (обычно с именем cdc.<имя_базы_данных>_capture), содержит только вызов процедуры sys.sp_MScdc_capture_job без параметров. Эта процедура определяет параметры задания сканирования и вызывает sys.sp_cdc_scan с этими параметрами. Процедура sys.sp_cdc_scan выполняет фактическую работу, сканируя журнал и вставляя данные, которые необходимо захватить, в таблицы изменений.

Существует четыре параметра в sys.sp_cdc_scan, определяющих поведение задания захвата.

Параметр continuous (значение по умолчанию 1) определяет, работает ли задание захвата непрерывно (значение 1) или завершается после одного цикла сканирования (one shot mode, значение 0). Режим «один цикл» рекомендуется только для тестирования, а не для промышленного использования. Основная причина в том, что записи журнала остаются активными, пока отслеживание изменений данных не обработает их. Таким образом, журнал будет продолжать расти, когда задание сканирования не работает.

Остальные три параметра определяют, как часто и сколько транзакций читается из журнала и вставляется в таблицы изменений.

Параметр maxtrans (значение по умолчанию 500) определяет, сколько транзакций читается из журнала и записывается в таблицы изменений. Эта запись выполняется в одной транзакции. Параметр maxscans (значение по умолчанию 10) определяет, сколько таких циклов сканирования выполняется до завершения задания (continuous = 0) или до паузы на интервал (continuous = 1). Длина этого интервала паузы задаётся параметром pollinginterval (в секундах, значение по умолчанию 5 секунд). WAITFOR выполняется, когда цикл сканирования полностью считывает журнал или когда завершено maxscans циклов сканирования.

Параметры задания сканирования можно изменить с помощью sys.sp_cdc_change_job. Поскольку эти параметры считываются только во время инициализации задания захвата, задание захвата необходимо остановить с помощью EXEC sys.sp_cdc_stop_job @job_type = 'capture', а затем перезапустить с помощью EXEC sys.sp_cdc_start_job @job_type = 'capture', чтобы изменённые параметры были применены.

Тестовая нагрузка и тестовая среда

Для тестирования производительности отслеживания изменений данных и его влияния на рабочие нагрузки мы тестировали нагрузку приложения независимого поставщика (ISV). Тесты проводились на HP DL 580 (4 сокета по 2 ядра, 32 ГБ ОЗУ, x64), подключённом к HP EVA SAN с отдельными дисковыми томами для данных, журнала, резервных копий и файловых групп отслеживания изменений данных. Сервер приложений — HP BL460 (2 сокета по 4 ядра, 32 ГБ ОЗУ, 32-разрядный), подключённый через Gigabit Ethernet.

В основной базе данных приложения семь таблиц с общим количеством 603 столбцов были включены для отслеживания изменений данных. Следует отметить, что в этой рабочей нагрузке отслеживание изменений данных использовалось для захвата транзакционных данных (таких как продажи, отгрузки, аудиторские данные), а не мастер-справочных данных (таких как клиенты, продукты, данные о площадках). Многие клиенты будут использовать отслеживание изменений данных только для захвата изменений в мастер-справочных данных, а не в транзакционных данных. Это приведёт к значительно более низкой частоте транзакций, которые необходимо захватывать отслеживанием изменений данных, чем в этой рабочей нагрузке.

Приложение состояло из нагрузок «пакетного» типа с множеством дискретных фаз. За исключением фазы 3, почти все транзакции в базе данных приложения записывали данные в таблицы, отслеживаемые отслеживанием изменений данных.

Фаза	Продолжительность (мин)	Среднее количество транзакций базы данных приложения в секунду (tps)	Среднее количество транзакций tempdb в секунду (tps)	Среднее использование ЦП % (сервер базы данных)
Begin	Несколько секунд	2 большие транзакции (обновлено 200 000 и 20 000 строк)	Не применимо	Не применимо
1	7	5	780	6
2	10	10, всплески до 200	40	13
3	17	3000, постепенно снижаясь до 1800. Из них около 1000 записей в таблицы, охваченные отслеживанием изменений данных	5000, снижаясь до 3200	60
4	3	1800–2500	1000	37
5	7	850	2000	62
6	6	3000–4000	12 000 – 1 200	45
7	3	Переменная, всплески до 14 500	Переменная, от низкой до средней	Переменная, от низкой до средней

В дополнение к нагрузке приложения мы также хотели протестировать синтетические рабочие нагрузки (более простые, искусственно масштабируемые нагрузки для тестирования конкретных типов DML на постоянных уровнях), которые доводят отслеживание изменений данных до предела и определяют поведение отслеживания изменений данных с различными наборами параметров при экстремально высокой нагрузке. Каждая из этих нагрузок была разработана для создания максимально возможной нагрузки на нашей тестовой системе. Все нагрузки запускались с тестового клиента с 100 параллельными потоками и выполнялись в течение 500 секунд в той же базе данных, которую использовала нагрузка приложения.

Особенности синтетических нагрузок:

• Тест вставки 1: Вставка в широкую таблицу (82 столбца) с заполнением 8 столбцов и 390 000 строк, предварительно заполненных в таблице, одна строка за транзакцию.
• Тест вставки 2: Вставка в более узкую таблицу (21 столбец) с заполнением 10 столбцов и 666 000 строк, предварительно заполненных в таблице, одна строка за транзакцию.
• Тест малого обновления: Обновление 3 столбцов в 82-столбцовой таблице, одна строка за транзакцию.
• Вставка с триггером: Та же вставка, что и в тесте 1. Единственное отличие в том, что оператор INSERT запускает триггер, который обновляет один столбец той же таблицы после вставки. Это приводит к трём строкам в таблице изменений для каждой вставки (одна строка вставки, одна строка, содержащая значения до обновления, и одна строка, содержащая значения после обновления).
• Смесь операторов вставки, обновления и выборки.
• Крупное обновление: Обновление большого количества строк (случайное количество от 10 до 2000 строк) за транзакцию.
• Крупная вставка: 1000 строк за транзакцию вставляются в широкую таблицу (82 столбца) с заполнением 8 столбцов и 390 000 строк, предварительно заполненных в таблице. Эта нагрузка выполнялась в течение 300 секунд.

Определение производительности и характеристик отслеживания изменений данных

Для определения характеристик производительности отслеживания изменений данных необходимо было ответить на три основных вопроса:

1. Какова производительность самого отслеживания изменений данных?
2. Как включение отслеживания изменений данных влияет на производительность исходной рабочей нагрузки?
3. Какие части системы (ЦП, ввод-вывод и т. д.) и в каком объёме затрагиваются отслеживанием изменений данных?

Производительность самого отслеживания изменений данных определяется разницей между временем выполнения исходной транзакции в базе данных и временем появления записи об изменении в таблице изменений. Это время называется задержкой (latency). В системе, где отслеживание изменений данных полностью успевает за нагрузкой, задержка не должна быть значительно выше интервала опроса и не должна увеличиваться со временем.

Задержку можно определить с помощью динамического административного представления sys.dm_cdc_log_scan_sessions. В этом техническом документе это представление использовалось для определения продолжительности сеансов сканирования, количества обработанных команд и транзакций, а также величины задержки. Поскольку sys.dm_cdc_log_scan_sessions отслеживает только последние несколько сеансов сканирования, мы фиксировали расширенное событие sqlserver.cdc_session в файл, вместо того чтобы запрашивать sys.dm_cdc_log_scan_sessions.

Расширенные события (Extended Events) — это высокопроизводительный механизм захвата событий в SQL Server, который позволяет регистрировать целевые объекты (в данном случае файл) для событий (в данном случае события sqlserver.cdc_session). Всякий раз, когда событие срабатывает (в данном случае для каждого сеанса сканирования журнала), данные о событии записываются в целевой объект. Большим преимуществом расширенных событий является то, что они создают очень низкую нагрузку на сервер.

Расширенное событие sqlserver.cdc_session содержит ту же информацию, что и sys.dm_cdc_log_scan_sessions, но позволяет записывать каждый сеанс сканирования, произошедший во время рабочей нагрузки.

Для захвата события сначала необходимо создать сеанс событий, определяющий событие(я) для захвата и целевой объект, в который будет записано событие:

CREATE EVENT SESSION cdc_session ON SERVER
ADD EVENT sqlserver.cdc_session
ADD TARGET package0.asynchronous_file_target
(SET filename='c:\cdc_session.xel',
 metadatafile='c:\cdc_session.xem', max_file_size=10)

Затем перед каждым запуском рабочей нагрузки включите сеанс событий:

ALTER EVENT SESSION cdc_session ON SERVER STATE = start

Чтобы записать время, необходимое отслеживанию изменений данных для обработки всех транзакций данной рабочей нагрузки, и, таким образом, найти максимальную задержку, нам также нужен был способ определить, закончило ли отслеживание изменений данных чтение журнала после завершения рабочей нагрузки. Это можно определить, периодически запрашивая динамическое административное представление, чтобы увидеть, было ли последнее сканирование пустым (то есть сканированием, которое не нашло записей журнала, которые нужно было вставить в таблицы изменений). Динамическое административное представление не перечисляет пустые сканирования по отдельности; вместо этого оно суммирует их в одной строке со столбцом empty_scan_count, заполненным количеством пустых сканирований.

SELECT empty_scan_count FROM appdb.sys.dm_cdc_log_scan_sessions WHERE start_time =
(select MAX(start_time) from appdb.sys.dm_cdc_log_scan_sessions)

После завершения как рабочей нагрузки, так и отслеживания изменений данных, сеанс событий отключается, а результаты записываются в таблицу. Поскольку расширенные события записываются в формате XML, XML-результат необходимо разобрать:

ALTER EVENT SESSION cdc_session ON SERVER STATE = stop

SELECT
CAST (event_data as xml).value('(/event/data[@name="start_time"]/text/text())[1]','datetime2(2)') start_time,
CAST (event_data as xml).value('(/event/data[@name="end_time"]/text/text())[1]','datetime2(2)') end_time,
CAST (event_data as xml).value('(/event/data[@name="last_commit_cdc_time"]/text/text())[1]','datetime2(2)') last_commit_cdc_time,
CAST (event_data as xml).value('(/event/data[@name="duration"]/value/text())[1]','int' ) duration,
CAST (event_data as xml).value('(/event/data[@name="tran_count"]/value/text())[1]','int' ) tran_count,
CAST (event_data as xml).value('(/event/data[@name="command_count"]/value/text())[1]','int' ) command_count,
CAST (event_data as xml).value('(/event/data[@name="latency"]/value/text())[1]','int' ) latency
FROM sys.fn_xe_file_target_read_file
('C:\cdc_session_*.xel','C:\cdc_session_*.xem', null, null)

Этот захват приводит к полной таблице всех сеансов сканирования журнала, произошедших во время рабочей нагрузки. Она также содержит максимальную задержку. Мы также рассчитали максимальную пропускную способность из этих чисел. Для расчёта пропускной способности мы измеряли количество команд в секунду, а не транзакций в секунду. Чтобы рассчитать пропускную способность отслеживания изменений данных, мы учитывали только период времени, когда отслеживание изменений данных работало под полной нагрузкой. Это период, когда задержка составляла > 5 секунд. Мы рассчитали количество обработанных команд и разделили его на время от начала первого сканирования с задержкой > 5 секунд до конца последнего сканирования.

Чтобы определить влияние отслеживания изменений данных на производительность рабочей нагрузки, мы просто использовали время выполнения рабочей нагрузки для приложения ISV (которое записывается самим приложением в таблицу). Для синтетических нагрузок с фиксированным временем выполнения 500 секунд мы записывали количество завершённых транзакций в нагрузке.

Чтобы определить дополнительную нагрузку, которую отслеживание изменений данных создаёт на систему, мы записывали ряд счётчиков производительности в Windows и динамические административные представления SQL Server. Следующие счётчики производительности оказались полезными для измерения влияния отслеживания изменений данных на систему:

• Logical Disk: Average Disk Queue Length для диска данных, диска журнала и диска файловой группы CDC.
• Databases: Transactions/second для базы данных приложения и tempdb.
• Processor: % Processor time (Total).

Для сбора данных о том, насколько велика дополнительная нагрузка на файлы данных и журналов, мы использовали динамическое административное представление sys.dm_io_virtual_file_stats. Для получения информации о том, добавляет ли отслеживание изменений данных дополнительные ожидания в систему, мы использовали динамическое административное представление sys.dm_os_wait_stats, которое сбрасывалось перед каждым запуском рабочей нагрузки с помощью команды DBCC SQLPERF("sys.dm_os_wait_stats", CLEAR). Мы также восстанавливали базу данных приложения и использовали DBCC FREEPROCCACHE и DBCC DROPCLEANBUFFERS, чтобы начать каждый тестовый запуск с одной и той же базовой линии.

Рекомендации по производительности отслеживания изменений данных

Влияние параметров задания сканирования на производительность отслеживания изменений данных

Как объяснено в разделе «Настройка задания захвата», задание захвата с параметрами по умолчанию (maxscans=10, maxtrans=500, pollinginterval=5) даже теоретически не может обрабатывать более 1000 транзакций в секунду в среднем.

На практике с нагрузкой ISV-приложения обрабатывалось около 670 транзакций в секунду с падениями при обработке очень больших транзакций. В результате общая задержка до обработки всех транзакций отслеживанием изменений данных составила 3123 секунды. Задержка начала расти, как только нагрузка достигала более 670 транзакций в секунду.

Чтобы увеличить количество транзакций в секунду, которое может обрабатывать отслеживание изменений данных, и уменьшить задержку, можно сделать одно из следующих действий:

1. Увеличить количество транзакций за сканирование, увеличив maxtrans.
2. Увеличить количество сканирований до паузы, увеличив maxscans.
3. Уменьшить паузу между циклами сканирования, уменьшив pollinginterval.

Все результаты были получены с использованием ISV-нагрузки приложения со всеми 603 столбцами в семи таблицах, захваченными отслеживанием изменений данных, чистыми изменениями отключёнными, а таблицы изменений находились в той же файловой группе, что и данные приложения. Мы также измеряли, когда таблицы изменений находились в другой файловой группе на другом LUN, и не обнаружили существенной разницы при этой нагрузке и оборудовании.

Путём модификации только параметров задания сканирования мы смогли сократить задержку с более чем 52 минут до примерно 9 минут и пропускную способность до 1900 команд в секунду. Влияние на время выполнения рабочей нагрузки составило менее 10%.

На рисунке 3 показаны количество транзакций в секунду и задержка при тестировании с maxtrans=5000, maxscans=100 и pollinginterval=5. Обратите внимание, что масштаб оси времени (внизу) и оси задержки (справа) отличается от масштаба на предыдущем графике. При таких измененных параметрах задержка значительно меньше и начинает расти только после 35 минут выполнения рабочей нагрузки, поскольку сбор данных об изменениях теперь может справляться с более высокой нагрузкой транзакций.

Наши результаты показывают, что отслеживание изменений данных имеет очень низкое влияние (менее 10% изменения времени выполнения нагрузки) на рабочую нагрузку приложения и поэтому может использоваться без отрицательного влияния на производительность приложения в системе с достаточной пропускной способностью ввода-вывода. В системах с менее оптимальной подсистемой ввода-вывода, особенно для диска журнала, результаты могут быть другими. В наших тестах средняя длина очереди диска для диска журнала утроилась для большинства тестов, количество записанных байтов журнала выросло до 250% от нормы, и диск журнала имел значительную активность чтения при включённом отслеживании изменений данных.

При синтетических рабочих нагрузках нагрузка 2 (небольшие вставки в более узкую таблицу) показала, что слишком высокое значение maxtrans (50 000 и 500 000) или слишком низкое значение pollinginterval (0) могут снизить производительность (уменьшить количество транзакций в секунду для данной рабочей нагрузки и увеличить задержку при сборе измененных данных).

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

Рекомендация: Если отслеживание изменений данных с параметрами по умолчанию не успевает за нагрузкой и задержка становится слишком высокой, можно увеличить maxscans и/или maxtrans в 10 раз, или уменьшить pollinginterval до 1. Если задержка уменьшается, но всё ещё слишком высока, можно дополнительно увеличить maxtrans, но внимательно следите за производительностью вашей нагрузки, задержкой и производительностью запросов к таблицам изменений.

Влияние характеристик рабочей нагрузки на производительность отслеживания изменений данных

Для планирования решения отслеживания изменений данных характеристики рабочей нагрузки очень важны. Основные факторы, которые следует учитывать: INSERT/DELETE против UPDATE, а также влияние операций DML на одну строку за транзакцию против многих. Для сравнения операций INSERT и UPDATE мы сравнили нагрузку 1 (вставка одной строки за транзакцию в таблицу) с нагрузкой 3 (обновление одной строки за транзакцию в той же таблице).

SQL Server может поддерживать гораздо более высокую частоту транзакций UPDATE, чем INSERT (примерно в 5 раз больше транзакций в секунду). Для отслеживания изменений данных одна вставка создаёт только одну строку в таблице изменений. Однако одно обновление вставляет две строки в таблицу изменений. В общей сложности это означает, что нагрузка UPDATE должна вставлять примерно в 10 раз больше строк в таблицу изменений, чем нагрузка INSERT, что приводит к значительной задержке. Снижение производительности нагрузки (количество транзакций за 500 секунд) при включённом отслеживании изменений данных составляло от 3% до 14% для нагрузки INSERT и от 10% до 17% для нагрузки UPDATE. Это очень низкая нагрузка для объёма работы, который необходимо выполнить из-за отслеживания изменений данных.

Распространённый сценарий в приложениях — когда строка вставляется в таблицу и сразу же обновляется для заполнения недостающих полей. Это может происходить в приложении или с помощью триггера INSERT, который ищет недостающие поля и затем обновляет их при необходимости. Мы протестировали отслеживание изменений данных в этом сценарии. В таблице, в которую вставляются рабочие нагрузки 1 и 4, есть триггер INSERT, который проверяет, заполнен ли один столбец, и обновляет его значением из другого столбца, если оно равно NULL.

В синтетической рабочей нагрузке 1 мы заполнили этот столбец в команде INSERT, чтобы в триггере не выполнялось обновление. В синтетической рабочей нагрузке 4 значение столбца не было заполнено, поэтому триггер обновлял строку сразу после вставки (в рамках той же транзакции). В результате в таблицу изменений для каждой вставки в исходную таблицу записывалось три строки вместо одной. Результаты показывают, что производительность как рабочей нагрузки, так и сбора измененных данных выше, если обновление не происходит сразу после вставки.

Рекомендация: Старайтесь избегать сценариев, где строку необходимо обновлять сразу после вставки.

Нагрузка 6 (крупные обновления, 10–2000 строк за транзакцию) не показала существенной разницы в производительности или задержке независимо от параметров отслеживания изменений данных. Причина такого поведения в том, что при небольшом количестве больших транзакций обновления отслеживание изменений данных должно вставлять две строки в таблицу изменений для каждой строки, обновлённой в исходной таблице. Таким образом, при этой нагрузке одна транзакция приводит в среднем к 2000 вставок в таблицу изменений. Время, которое занимает эта большая вставка, настолько велико, что параметры задания сканирования больше не имеют существенного влияния. Даже с параметрами по умолчанию отслеживание изменений данных должно вставлять 1 миллион строк в таблицу изменений за один цикл сканирования. Задержка здесь определялась только общим объёмом данных, которые необходимо вставить в таблицу изменений.

Рекомендация: Старайтесь избегать использования отслеживания изменений данных для захвата изменений в таблицах, которые часто подвергаются большим транзакциям обновления.

Нагрузка 7 (крупные вставки, 1000 строк за транзакцию, около 1,7 миллиона строк за 300 секунд) также не показала существенной разницы в производительности или задержке независимо от параметров отслеживания изменений данных. Задержка составляла около 250–399 секунд в зависимости от поддержки «чистых» изменений, количества захваченных столбцов и параметров задания сканирования. Это означает, что отслеживание изменений данных заняло примерно столько же времени для вставки строк в таблицу изменений после завершения рабочей нагрузки, сколько заняла исходная нагрузка.

Влияние параметров sys.sp_cdc_enable_table на производительность отслеживания изменений данных

В нагрузке приложения отслеживание изменений данных включено для семи таблиц с общим количеством 603 столбцов. При изучении таблиц изменений мы обнаружили, что только 171 столбец имел более одного уникального значения. Другие столбцы были либо NULL, либо всегда имели одно и то же значение. Во втором тестовом запуске мы решили захватывать только 171 столбец, имеющих разные значения. Мы сделали это, указав параметр @captured_column_list в sys.sp_cdc_enable_table. Мы обнаружили, что уменьшение количества захваченных столбцов значительно повышает общую производительность отслеживания изменений данных, особенно при указании нестандартных параметров задания сканирования.

Сократив количество собираемых столбцов до тех, в которых происходят соответствующие вставки или обновления, мы смогли снизить задержку до менее чем 8 минут для всей рабочей нагрузки, а также сократить время выполнения рабочей нагрузки и нагрузку на подсистему ввода-вывода, вызванную сбором измененных данных. Для синтетических рабочих нагрузок мы наблюдали аналогичные улучшения в показателях количества транзакций в секунду и снижение задержки при уменьшении количества собираемых столбцов.

Увеличение задержки при уменьшении количества захватываемых столбцов (maxscans=100, maxtrans=5000 и pollinginterval=5)

Рекомендация: Всегда ограничивайте список столбцов, захватываемых отслеживанием изменений данных, только теми столбцами, которые вам действительно нужно отслеживать, указывая параметр @captured_column_list в sys.sp_cdc_enable_table.

Параметр @supports_net_changes может иметь существенное влияние на производительность отслеживания изменений данных. Особенно если отслеживание изменений данных едва успевает за нагрузкой, дополнительная нагрузка, создаваемая поддержкой дополнительного индекса для запросов «чистых» изменений, может быть достаточной, чтобы предотвратить успевание отслеживания изменений данных за нагрузкой.

Рекомендация: Если вам не требуется поддержка «чистых» изменений, установите @supports_net_changes = 0. Если вам требуется запрос «чистых» изменений, но задержка отслеживания изменений данных становится слишком большой, стоит отключить поддержку «чистых» изменений и выполнять обнаружение «чистых» изменений позже в промежуточной базе данных.

Рекомендации по заданию очистки

Для тестирования производительности очистки она выполнялась вручную после завершения нагрузки приложения. В тесте одна таблица изменений, содержащая 4 147 855 строк, была полностью очищена с различными значениями параметра порога (threshold) путём выполнения sys.sp_cdc_cleanup_change_table. Настраиваемый параметр порога ограничивает количество записей, удаляемых в одном операторе. Тест проводился после запроса таблицы изменений (путем выполнения SELECT count(*) FROM имя_таблицы_изменений), чтобы имитировать сценарий, когда таблица изменений сначала читается, а затем очищается.

Когда задание очистки выполнялось без дополнительной нагрузки, было показано, что увеличение порога до 500 000 может улучшить общую производительность очистки. Дальнейшее увеличение до 5 000 000 (чтобы вся таблица очищалась за одну транзакцию) снижало производительность.

Когда задание очистки выполнялось параллельно с нагрузкой (нагрузка 5: смесь вставок, обновлений и выборок), время выполнения очистки значительно возрастало. Этот тест проводился со следующими параметрами задания отслеживания изменений данных: maxtrans=5000, maxscans=100, pollinginterval=5.

Опять же, порог 500 000 оказался оптимальным для этой нагрузки. Также было замечено, что особенно при больших порогах может происходить эскалация блокировок на таблицах изменений. Это может ухудшить время отклика приложения и увеличить задержку задания сканирования отслеживания изменений данных.

Рекомендация: Если возможно, выполняйте очистку, когда нет другой активной нагрузки. Тестируйте увеличение параметра порога, пока не найдёте оптимальное значение для вашей нагрузки.

Рекомендации по файлу журнала транзакций

Одна из самых важных вещей, на которые следует обратить внимание при отслеживании изменений данных, — это подсистема ввода-вывода журнала транзакций. Как было сказано ранее, операции ввода-вывода журнала значительно возрастают, когда отслеживание изменений данных включено в базе данных. Кроме того, записи журнала остаются активными, пока отслеживание изменений данных их не обработает. Это означает, что особенно в средах, где накапливается большая задержка, файл журнала может значительно вырасти, потому что пространство журнала не может быть повторно использовано, пока задание сканирования отслеживания изменений данных не обработает записи журнала, даже в простой модели восстановления или даже после резервного копирования журнала в полной модели восстановления.

Следует отметить, что отслеживание изменений данных работает со всеми моделями восстановления. Но когда отслеживание изменений данных включено, операции, которые обычно минимально регистрируются в простой или модели восстановления с неполным протоколированием (bulk-logged), полностью протоколируются, чтобы отслеживание изменений данных могло зафиксировать все изменения.

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

Рекомендация: При планировании архитектуры отслеживания изменений данных учитывайте значительное увеличение размера журнала и объёма операций ввода-вывода журнала. В зависимости от объёма данных, захватываемых отслеживанием изменений данных, и времени, необходимого отслеживанию изменений данных для обработки изменений, размер файла журнала может вырасти до 200–300% от исходного размера, а в некоторых случаях даже больше. Соответственно настройте размер файла журнала. Убедитесь, что рост размера файла журнала не приведёт к полному заполнению диска журнала.

Рекомендации по файловым группам

Мы не обнаружили разницы в наших тестах между размещением таблицы изменений в той же файловой группе, что и таблица приложения, и размещением их в разных файловых группах. Это может быть иначе, когда файлы данных расположены на подсистеме ввода-вывода, которая уже находится под высокой нагрузкой. В этом случае размещение таблиц изменений в файловой группе, расположенной на другом наборе физических дисков, может улучшить производительность отслеживания изменений данных.

Рекомендация: Чтобы поддерживать файловую группу PRIMARY небольшой и иметь чёткое различие между данными приложения и изменёнными данными, следует указывать @filegroup_name в sys.sp_cdc_enable_table.

В дополнение к таблицам изменений таблица cdc.lsn_time_mapping также может достигать значительного размера и становиться объектом многих операций ввода-вывода. Эта таблица создаётся в файловой группе по умолчанию при выполнении sys.sp_cdc_enable_db для базы данных.

Рекомендация: Рассмотрите возможность изменения файловой группы по умолчанию для базы данных перед выполнением sys.sp_cdc_enable_db, чтобы метаданные отслеживания изменений данных и особенно cdc.lsn_time_mapping находились в другой файловой группе, чем PRIMARY. Вы можете изменить файловую группу по умолчанию обратно после создания таблиц метаданных отслеживания изменений данных.

Заключение

Отслеживание изменений данных предоставляет простой и высокопроизводительный способ захвата изменений в наборе таблиц. Существует множество способов настройки производительности отслеживания изменений данных. Понимание характеристик рабочей нагрузки, использования системы ввода-вывода и допустимой задержки является ключом к настройке производительности отслеживания изменений данных без негативного влияния на основную рабочую нагрузку. Настройка параметров задания сканирования, параметров sys.sp_cdc_enable_table и, если возможно, запросов в рабочей нагрузке может значительно улучшить производительность отслеживания изменений данных под нагрузкой.

Сводка рекомендаций

Хранилище:

• При планировании архитектуры отслеживания изменений данных учитывайте значительное увеличение размера журнала и объёма операций ввода-вывода журнала.
• Рассмотрите возможность указания файловой группы в sys.sp_cdc_enable_table.
• Рассмотрите возможность изменения файловой группы по умолчанию для базы данных перед выполнением sys.sp_cdc_enable_db, чтобы метаданные отслеживания изменений данных и особенно cdc.lsn_time_mapping находились в другой файловой группе, чем PRIMARY.

Поведение рабочей нагрузки:

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

Параметры отслеживания изменений данных:

• Всегда ограничивайте список столбцов, захватываемых отслеживанием изменений данных, только теми столбцами, которые вам действительно нужно отслеживать.
• Если вам не требуется поддержка «чистых» изменений, установите @supports_net_changes = 0.
• Используйте sys.dm_cdc_log_scan_sessions, чтобы видеть, успевает ли отслеживание изменений данных за вашей нагрузкой.
• Если отслеживание изменений данных не успевает за вашей нагрузкой, измените параметры задания сканирования и перезапустите задание сканирования.

Очистка:

• Если возможно, выполняйте очистку, когда нет другой активной нагрузки.
• Тестируйте увеличение параметра порога, пока не найдёте оптимальное значение для вашей нагрузки.