Новое в SQL Server 2022: CDC Extended Events

Автор: Mukesh Kumar - MICROSOFT Monitoring CDC using Extended Events in SQL Server 2022

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

• repl_logscan_session
• cdc_cleanup_job_status

Введение в Change Data Capture

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

Что отслеживают новые расширенные события MSSQL 2022 для CDC?

Задания Агента для CDC включают в себя:

• Задание сбора изменений — записывает изменения в системную таблицу сбора изменений.
• Задание по очистке старых данных — удаляет старые данные из таблицы сбора изменений.

Каждое событие будет охватывать разные аспекты активности заданий CDC.

• repl_logscan_session - сообщает о ходе работы задания отслеживания изменений.
• cdc_cleanup_job_status - сообщает о ходе работы задания очистки системных таблиц.

Описание расширенного события repl_logscan_session

Это событие сообщает о транзакциях изменения данных по мере их поступления в таблицу изменений. Ниже описано, какая информация возвращается в колонках repl_logscan_session с подробным описанием ключевых метрик, которые поступают в таблицу изменений:

• Идентификатор сеанса CDC
• Текущая фаза сеанса***

1. чтение конфигурации
2. создание хэш-таблицы
3. частичный откат
4. EOR,
5. частичный откат и EOR
6. версия схемы
7. последнее сканирование

• Фазовое состояние отражает начальную фазу = 0 и конечную фазу = 1
• Максимальное количество транзакций для обработки в каждом цикле сканирования max_trans (int), по умолчанию равно 500. Если указано иное, это значение должно быть положительным целым числом.
• Количество команд, обработанных в фазе 7 (последнее сканирование за сеанс)
• Количество обработанных (реплицированных или отслеживаемых) транзакций, установленное на этапе 2, после чего остаётся неизменным.
• Количество просмотренных записей журнала, доступных на этапе 2, после чего остается неизменным.
• Количество обнаруженных операций DDL для изменений в схемах RE, SE и статьи.
• Время начала сканирования журнала.
• Начальный LSN для текущего сеанса, доступен после начала фазы 2
• Конечный LSN для сеанса, доступен после окончания фазы 2
• LSN, который был отсканирован, доступный после окончания фазы 2, затем сохраняется без изменений.
• Самый ранний номер LSN в этом сеансе CDC, доступен на этапе 7, в противном случае 00000000:00000000:0000
• Последний обработанный номер LSN этого сеанса CDC, доступен на этапе 7, в противном случае 00000000:00000000:0000.

Фазы репликации и CDC

Процесс CDC сканирует журнал транзакций используя для этого 7 фаз:

1. Чтение конфигурации
2. Первое сканирование, построение хэш-таблицы
3. Второе сканирование (частичный откат)
4. Второе сканирование (ошибки)
5. Второе сканирование (ошибки частичного отката)
6. Управление версиями схемы
7. Последнее сканирование
8. Завершение

Когда значение session_id равно нулю, это всегда подразумевает «агрегат» этих фаз.

Описание расширенного события CDC_cleanup_job_status

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

• Количество строк, удаленных из таблицы изменений.
• Идентификатор объекта таблицы изменений
• Номер обнаруженного сообщения об ошибке.
• Номер low_water_mark LSN (который идентифицирует строки, подлежащие очистке)
• Количество строк, удаленных из таблицы LSN_Time_Mapping.
• Время хранения до очистки (время в минутах для хранения в CT-таблице)
• Пороговое значение задания на очистку (размер пачки удаления)

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

После того, как будет очищена последняя таблица изменений CDC, процесс очистки перейдет к очистке записей из таблицы LSN_Time_Mapping. Будет использован тот же 5-минутный цикл очистки и генерации строк-событий пока вся таблица не будет очищена. Эта конструкция была предназначена для генерации «сигналов жизни» процесса, а также для отчета о ходе выполнения заданий очистки.

Мониторинг CDC с помощью событий

Давайте рассмотрим, как настроить CDC и обеспечить мониторинг, будем придерживаться следующего плана изложения:

• Настройка CDC.
• CDC с простыми вставками
• Процесс очистки в CDC
• CDC во время частичного отката
• CDC во время длительной очистки

Настройка CDC

Проверьте, что Агент SQL Server настроен правильно: Configure SQL Server Agent.

Для мониторинга CDC потребуется включить CLR. Для этого выполните следующий сценарий:

USE master;
GO
--Enable CLR
EXEC sp_configure 'clr enabled' , '1'; 
RECONFIGURE; 
 
--Ensure CLR was enabled:

EXEC sp_configure 'clr enabled'; 

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

use [master]
GO
Create database MyDB;
GO
--Size the database for testing
USE [master]
GO
ALTER DATABASE [MyDB] MODIFY FILE ( NAME = N'MyDB', SIZE = 102400KB , FILEGROWTH = 256000KB )
GO
ALTER DATABASE [MyDB] MODIFY FILE ( NAME = N'MyDB_log', SIZE = 307200KB , FILEGROWTH = 256000KB )

GO

CDC с простыми вставками

Создадим простую таблицу:

USE MyDB;

GO

CREATE TABLE T1(

     C1 BIGINT PRIMARY KEY,

     C2 NVARCHAR(22));

 

--Ensure tables were generated

SELECT * from sysobjects where type = 'U';

Для того, чтобы настроить сбор изменений для этой таблицы средствами CDC, владелец схемы в базе данных должен иметь возможность видеть все объекты CDC. Простой способ обойти это — установить хотя бы временно владельцем 'SA' (его можно будет переключить обратно на фактического владельца схемы, как только будут созданы все таблицы CDC).

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

-- Enable Database for CDC template

exec sp_changedbowner [sa]

-- ==== 

USE MyDB 

GO 

EXEC sys.sp_cdc_enable_db  ;

GO

-- ========= 

-- Enable sample Tables for CDC

-- ========= 

EXEC sys.sp_cdc_enable_table 

@source_schema = N'dbo', 

@source_name   = N'T1', 

@role_name     = NULL, 

@supports_net_changes = 1 

GO

--verify cdc tables have been generated.

use myDB;

select * from cdc.dbo_T1_CT;

select * from sysobjects where type = 'U';

--check target table

select * from T1;

Новая таблица пока пуста и CDC не обнаружил ни одного изменения ни в одной строке, как показано на рисунке ниже.

Создадим сборщик событий xEvents для repl_logscan_session, запустив следующий сценарий (предварительно изменив путь к файлу):

USE master;

GO

--Update filename and path as appropriate

CREATE EVENT SESSION [cdc_test] ON SERVER

ADD EVENT sqlserver.repl_logscan_session

ADD TARGET package0.event_file(SET filename = N'C:\output\xevents\cdc_test_event.xel')

WITH (STARTUP_STATE=OFF)

GO

На этом этапе мы готовы выполнить тестовые транзакции в контексте нашей таблицы и можем наблюдать, как CDC будет проходить все фазу отслеживания изменений. Для того, чтобы убедится, что сеанс событий был успешно создан, откроем обозреватель объектов SSMS, раскроем дерево экземпляра базы данных, папку «Management», а в ней «Extended Events», где будут видны созданные сессии:

Щелкнув правую кнопку мыши по имени созданного ранее сеанса выберите «Start Session»:

Затем выберите «Watch Live Data» (просмотр данных в реальном времени).

Примечание: мы еще не запустили очистку и не вносили никаких изменений, поэтому мы просто увидим события сканирования журнала с поиском изменений. 

Теперь правой кнопкой мыши щёлкните, как показано ниже, чтобы выбрать столбцы для отображения в таблице событий, и укажите для нужных «Show Column in Table» (показать в таблице).

Добавляем столбцы в таблицу:

Обратите внимание, что каждое событие показывает запущенный сеанс сканирования журнала, которое проверяет наличие каких-либо изменений в таблице, и это без какой-либо дополнительной настройки!

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

Создадим для CDC транзакции (откройте новое окно запроса SSMS) и выполните следующий сценарий:

Примечание: измените ограничение cnt1 на желаемое количество строк (в примере ниже установлено значение 255).

 --Start with the next row count and load table

--with ascii character rows

--Replace 255 with desired number of rows.

--Note: arithmetic overflow at 1000000000 for

--int conversion so limit max rows.

--

use myDB;

DECLARE @cnt INT,

        @cnt1 INT

select @cnt= (select count(*) from T1)+1;

select @cnt1= (select count(*) from T1)+255;

WHILE @cnt < @cnt1

BEGIN

  INSERT INTO T1

   VALUES (@cnt, char(@cnt));

    SET @cnt = @cnt + 1;

После этого в SSMS перейдите на вкладку «Watch Live Data», чтобы просмотреть события в repl_logscan_session.

Событие repl_logscan_session показывает:

• LSN 0000004A:00008CB8:0001 в начале транзакции (first_begin_cdc_lsn)
• Выполнено 254 команды (изменения DML) (транзакция завершена)
• LSN 0000004A:000094A0:0003 после завершения транзакции (last_commit_lsn)
• В фазе 2 (сборка с использованием хэш-таблицы) было использовано 760 записей журнала без откатов, ошибок или частичных фиксаций, а также без изменений версии схемы.

Отлично! Все идет хорошо! 

Процесс очистки CDC

После завершения тестовых транзакций, можно проверите, что все изменения, внесенные в таблицу «T1» и были зафиксированы в таблице изменений «cdc.dbo_T1_CT», выполнив следующий сценарий:

select * from cdc.dbo_T1_CT;

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

EXEC sys.sp_cdc_help_jobs 

Задание сбора изменений настроено на непрерывную работу с интервалом опроса 5 секунд. Срок хранения для задания очистки установлен для нашего тестирования большим (4320 минут).

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

EXECUTE sys.sp_cdc_change_job  

    _type = N'cleanup', 

    @retention = 3 --three-minute retention 

GO

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

После трёхминутного ожидания запустите задание очистки, щелкнув правой кнопкой мыши по заданию и выбрав «Start Job at Step…»:

После завершения задания в окне статуса должно появиться сообщение «Success», как показано ниже.

Повторно выполните сценарий из начала этой части статьи, чтобы убедиться, что зафиксированные изменения были удалены из таблицы изменений:

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

CDC с частичным откатом

Используя это-же событие, можно отслеживать не завершённые транзакции. Команды управления транзакциями в SQL Server (иногда их называют Transaction Control Language):

• Commit
• Rollback
• Save

repl_logscan_session будет содержать событие третьей фазы для любых частичных откатов.

Для создания частичного отката применим следующий сценарий:

• Начнём транзакцию с помощью begin trans.
• Запустите команду DML для обновления тестовой таблицы.
• Создадим точку сохранения.
• Выполним ещё команды DML.
• Откатим все несохраненные транзакции
• Начните другую транзакцию с другим begin trans.
• Выполним ещё команды.
• Выполним фиксацию транзакции.
• Запустите еще одну транзакцию и зафиксируйте её, чтобы очистить первоначальную транзакцию.

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

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

--Check last row and beyond

select *from T1 where C1 > 819;

select * from cdc.dbo_T1_CT;

-------

BEGIN TRAN;

INSERT INTO T1 VALUES (820, 'A');

 

PRINT 'A';

SAVE TRANSACTION FirstInsert

select @@TRANCOUNT

 

INSERT INTO T1 VALUES (821, 'B');

PRINT 'B';

INSERT INTO T1 VALUES (822, 'C');

PRINT 'C';

GO

--Roll them back

ROLLBACK TRANSACTION FirstInsert

 select @@TRANCOUNT

BEGIN TRAN; --2

INSERT INTO T1 VALUES (823, 'D');

PRINT 'D';

 

COMMIT --2

select @@TRANCOUNT

PRINT 'Commit 2';

 

INSERT INTO T1 VALUES (824, 'E');

PRINT 'E';

PRINT 'Commit E';

--Note this would also work if we just executed another

--commit, but the demo just looks better ending with

--a command.

select @@TRANCOUNT

select * from T1 where C1 > 819;

select * from cdc.dbo_T1_CT;

В SQL Server Management Studio (SSMS) проверьте вкладку «Watch Live Data» с событиями:

Давайте подробнее рассмотрим полученные сведения об этих событиях.

Сканирование CDC переходит в фазу 3: repl_logscan_session показывает сканирование в фазе 3, что указывает на частичный откат. Точка сохранения, сделанная после команды отката, указывает на 1 запись в журнале, но при этом не выполнено ни одной команды.

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

select *from T1 where C1 > 819;

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

select * from cdc.dbo_T1_CT;

В последнем примере были выявлены события для двух изменений: одно с двумя командами и одно только с одной командой. То есть A было сохранено как частичное изменение для следующей за ним фиксации, в то время как B и C откатились. После этого, A и D были зафиксированы, и уже потом была зафиксирована E, закрывая открытую в начале запроса транзакцию.

Частично зафиксированная вставка A прошла процесс отслеживания изменений CDC и попала в таблицу изменений.

CDC во время длительной работы задания очистки

Ниже показан сценарий, который создаёт xEvents сессию для отслеживания работы cdc_clean_up_job (при необходимости замените имя файла и путь к нему):

CREATE EVENT SESSION [cdc_clean__up_job_test] ON SERVER

ADD EVENT sqlserver.cdc_cleanup_job_status

ADD TARGET package0.event_file(SET filename=N'C:\output\xevents\cdc_clean__up_job_test_event.xel')

WITH (STARTUP_STATE=OFF)

GO

Используя SSMS, перейдите к созданной сессии и щелкните на её имени правой кнопкой мыши, выбрав «Start Session»:

Примечание: эта сессия начнёт показывать собранные данные только после того, как будет в очередной раз запущено задание по очистке CDC.

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

Выполните следующий ниже запрос для создания новой таблицы, которая понадобится для того, чтобы протестировать очистку:

CREATE TABLE T3(

    C1 BIGINT PRIMARY KEY,

    C2 NVARCHAR(100) NULL);

Следующий сценарий включит CDC для таблицы T3:

EXEC sys.sp_cdc_enable_table 

@source_schema = N'dbo', 

@source_name   = N'T3', 

@role_name     = NULL, 

@supports_net_changes = 1 

GO

Выполним достаточно много транзакций, чтобы задание очистки выполнялось не менее 10 минут. Вот пример подходящего для этого запроса:

USE myDB;

--Start with the next row count and load table with ascii character rows

--Note: arithmetic overflow at 1000000000 for int conversion --so don't go that far.

DECLARE @cnt INT,

                 @cnt1 INT

select @cnt= (select count(*) from T3)+1;

select @cnt1= (select count(*) from T3)+5500000;

WHILE @cnt < @cnt1

BEGIN

  INSERT INTO T3

   VALUES (@cnt, '500000');

    SET @cnt = @cnt + 1;

END;

Следующий скрипт демонстрирует, что мы имеем в качестве отправной точки перед запуском задания по очистке:

select * from T3;

select * from cdc.dbo_T3_CT;

В SSMS запустите задание очистки, выбрав «Start Job at Step».

Вернитесь на вкладку «Watch Live Data» в SSMS, и через 5 минут задание cdc_cleanup_job_status добавит строку в таблицу на этой вкладке. Щелкните правой кнопкой мыши по полю детализации, чтобы отобразить их в виде таблицы.

Полученная через 5 минут строка, которая отражает детали выполнения задания очистки, не содержит ошибок, как видно на предыдущем скриншоте.

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

Помните, что retention level составлял 3 минуты, и мы ждали 3 минуты перед запуском очистки, поэтому таблица CDC должна быть пустой.

Проверим результаты работы задания по очистке, выполнив запрос:

select count(*) from T3;

select count(*) from cdc.dbo_T3_CT;

Задание очистки успешно удалило все строки из таблицы CDC, которые появились там около трёх минут назад.

Дополнительные материалы

• Система отслеживания измененных данных (CDC)
• Включение и отключение отслеживания измененных данных
• Таблицы системы отслеживания измененных данных (Transact-SQL)
• Sys.sp_cdc_enable_table (Transact-SQL)
• Работа с информацией об изменениях

Более реалистичный пример того, как CDC позволяет легко и систематически собирать данные об изменениях, проиллюстрирован ниже и описан здесь.