Администрирование и мониторинг отслеживания измененных данных
Область применения:
SQL Server Управляемый экземпляр SQL Azure
В этом разделе описывается, как администрировать и отслеживать функцию отслеживания изменений данных для SQL Server и управляемого экземпляра SQL Azure.
Сведения о Базе данных Azure SQL, имеющей другой механизм выполнения заданий, см. в разделе CDC с Базой данных Azure SQL.
Задание отслеживания
Задание отслеживания инициируется путем запуска хранимой процедуры без параметров sp_MScdc_capture_job. Эта хранимая процедура начинается с извлечения настроенных значений для maxtrans, maxscans, continuous и pollinginterval для задачи захвата из msdb.dbo.cdc_jobs. Затем данные настроенные значения передаются в качестве параметров для хранимой процедуры sp_cdc_scan. Используется для вызова процедуры sp_replcmds для выполнения просмотра журнала.
Параметры задания захвата
Чтобы понять поведение задания захвата, необходимо понять, как sp_cdc_scan использует настраиваемые параметры.
maxtrans параметр
Параметр maxtrans указывает максимальное количество транзакций, которое может быть обработано в одном цикле просмотра журнала. Если во время сканирования количество обрабатываемых транзакций достигает этого ограничения, в текущий скан не будут включены дополнительные транзакции. После завершения цикла просмотра число обработанных транзакций всегда будет меньше или равным значению параметра maxtrans.
maxscans параметр
Параметр maxscans задает максимальное число циклов сканирования, которые предпринимаются для опустошения журнала до возврата (continuous = 0) или выполнения инструкции waitfor (continuous = 1).
continuous параметр
Параметр continuous определяет, передает ли sp_cdc_scan управление после очистки журнала или выполнения максимального количества сканирующих циклов (режим однократного выполнения). Этот параметр также управляет тем, продолжает ли sp_cdc_scan выполняться до её явной остановки в непрерывном режиме.
Режим однократного снимка
В однократном режиме задание захвата запрашивает sp_cdc_scan для выполнения до maxtrans сканирований, чтобы попытаться очистить журнал и вернуть управление. Любые присутствующие в журнале транзакции, вышедшие за пределы значения maxtrans, будут обработаны в последующих циклах просмотра.
Режим однократного выполнения используется в управляемых проверках, когда количество обрабатываемых транзакций известно, а преимуществом является тот факт, что задание автоматически закрывается при завершении работы. Не рекомендуется использовать режим однократного выполнения в производственной среде. Это обусловлено тем, что данный режим полагается на расписание заданий для управления частотой выполнения циклов просмотра.
При работе в режиме однократного выполнения можно вычислить верхнюю границу ожидаемой пропускной способности задания отслеживания, выраженную в транзакциях в секунду, использовав следующее вычисление:
(maxtrans * maxscans) / number of seconds between scans
Даже если количество времени, необходимое для просмотра журнала и заполнения таблиц изменений, не сильно отличается от нуля, средняя пропускная способность задания не может превысить значение, которое можно получить, разделив произведение максимального числа допустимых транзакций для одного просмотра и максимального числа допустимых просмотров на число секунд, которое проходит между отдельными обработками журнала.
Если бы для управления просмотрами журнала использовался режим однократного выполнения, то число секунд, проходящее между обработками журнала, должно было бы управляться расписанием задания. Если требуется такое поведение, выполнение задания записи в непрерывном режиме является лучшим способом перепланировать проверку журнала.
Непрерывный режим и интервал опроса
В непрерывном режиме задание захвата запрашивает, чтобы sp_cdc_scan выполнялась непрерывно. Это позволяет хранимой процедуре управлять собственным циклом ожидания, предоставляя не только maxtrans и maxscans, но и значение для интервала опроса, то есть количества секунд между обработкой журнала. В непрерывном режиме задание записи остается активным, выполняя WAITFOR между сканированием журналов.
Примечание.
Если значение интервала опроса больше 0, то верхняя граница пропускной способности для повторяющихся заданий в режиме однократного выполнения также будет применяться и к функционированию задания в непрерывном режиме. То есть значение (maxtrans * maxscans), разделенное на ненулевой интервал опроса, будет служить верхней границей для среднего количества транзакций, которое может быть обработано заданием отслеживания.
Настройка задачи захвата
К заданию отслеживания можно применить дополнительную логику для определения того, будет ли новый просмотр выполняться немедленно или перед запуском нового просмотра будет реализовываться период бездействия, вместо того чтобы полагаться на фиксированный интервал опроса. Выбор может быть основан на времени суток: например, во время пиковой активности можно установить очень большие периоды бездействия, а в конце дня можно установить значение интервала опроса, близкое к 0, что поможет завершить дневную обработку и подготовиться к еженощным выполнениям. Процесс выполнения может быть отслежен, чтобы определить, когда все транзакции, зафиксированные к полуночи, были отсканированы и добавлены в таблицы изменений. Это позволит завершить задание захвата, чтобы его можно было перезапустить при запланированном ежедневном перезапуске. Чтобы настроить поведение, можно заменить шаг задания, который вызывает sp_cdc_scan, на вызов оболочки, написанной пользователем, для sp_cdc_scan.
Задание очистки
В этом разделе представлена информация о том, как работает задание очистки данных при захвате изменений.
Структура задания очистки
В системе фиксации изменений данных используется стратегия очистки на основе хранения для управления размером таблиц изменений. В SQL Server и Управляемом экземпляре Azure SQL механизм очистки состоит из задания агента SQL Server, использующего Transact-SQL, созданного при включении первой таблицы базы данных. Одно задание очистки управляет очисткой всех таблиц изменений базы данных и применяет одно значение срока хранения ко всем определенным экземплярам захвата.
Задание очистки инициируется путем запуска хранимой процедуры без параметров sp_MScdc_cleanup_job. Эта хранимая процедура начинается с извлечения настроенных значений срока хранения и порогового значения для задания очистки из msdb.dbo.cdc_jobs. Значение хранения используется для вычисления нового минимального порога для таблиц изменений. Указанное число минут вычитается из максимального значения tran_end_time из таблицы cdc.lsn_time_mapping для получения нового значения нижнего предела в формате даты и времени. Затем таблица «CDC.lsn_time_mapping» используется для преобразования значения datetime в соответствующее значение lsn. Если одно и то же время фиксации задано для нескольких значений в таблице, то номер lsn, соответствующий записи с наименьшим номером lsn выбирается в качестве нового значения нижнего предела. Значение lsn передается в sp_cdc_cleanup_change_tables для удаления записей из таблиц изменений базы данных.
Примечание.
Преимуществом использования времени фиксации недавней транзакции в качестве основы для вычисления нового значения нижнего предела является то, что это позволяет хранить сведения об изменениях в таблицах изменений в течение определенного времени. Это происходит, даже если процесс захвата задерживается. Все изменения, имеющие то же время фиксации, что и значение нижнего предела, и далее представляются в таблицах изменений методом выбора наименьшего номера lsn, имеющего то же время фиксации, что и реальное значение нижнего предела.
Если выполняется очистка, то значение нижнего предела всех экземпляров системы отслеживания изначально обновляется в одной транзакции. Затем производится попытка удаления устаревших записей из таблиц изменений и таблицы cdc.lsn_time_mapping. Настраиваемое пороговое значение ограничивает количество записей, удаляемое в любой одиночной инструкции. Неуспешное выполнение удаления в любой отдельной таблице не повлияет на выполнение операции в остальных таблицах.
Настройка задания очистки
В задании очистки присутствует возможность настройки стратегии, определяющей, какие из записей таблиц изменений подлежат удалению. В передаваемом задании очистки поддерживается только основанная на времени стратегия. В данной ситуации новое значение нижнего предела вычисляется методом вычитания допустимого срока хранения из времени фиксации последней обработанной транзакции. Поскольку лежащие в основе процедуры очистки основаны на lsn вместо времени, можно использовать различные стратегии для определения наименьшего lsn, который следует сохранять в таблицах изменений. Только часть из этих стратегий являются полностью основанными на времени. Сведения о клиентах, например, могут быть использованы для обеспечения предохранительных мер на тот случай, если не удастся запустить последующие процессы, которым необходим доступ к таблицам изменений. Хотя в стратегии по умолчанию для очистки таблиц изменений всех баз данных используется один и тот же lsn, процедуру очистки можно также вызвать для очистки на уровне экземпляра захвата.
Мониторинг процесса
Наблюдение за процессом отслеживания измененных данных позволяет определить, правильно ли записываются изменения и насколько приемлема задержка при записи в таблицы изменений. Наблюдение также помогает выявить возможные ошибки. SQL Server включает два динамических административных представления для мониторинга отслеживания изменений данных: sys.dm_cdc_log_scan_sessions и sys.dm_cdc_errors.
Определение сеансов с пустыми результирующих наборами
Каждая строка в sys.dm_cdc_log_scan_sessions представляет сеанс сканирования журнала (за исключением строки с идентификатором 0). Сеанс сканирования журнала является эквивалентом одного выполнения хранимой процедуры sp_cdc_scan. Во время сеанса сканирование может возвратить изменения или пустой результат. Если результирующий набор пуст, столбец empty_scan_count имеет sys.dm_cdc_log_scan_sessions значение 1. Если пустые результирующие наборы встречаются последовательно (например, при непрерывном выполнении задания отслеживания), то счетчик empty_scan_count в последней существующей строке увеличивается. Например, если sys.dm_cdc_log_scan_sessions уже содержит 10 строк для проверок, возвращающих изменения, и есть пять пустых результатов в строке, представление содержит 11 строк. В последней строке в столбце empty_scan_count указано значение 5. Чтобы определить сеансы с пустым сканированием, выполните следующий запрос.
SELECT * from sys.dm_cdc_log_scan_sessions where empty_scan_count <> 0
Определение задержки
В административное представление sys.dm_cdc_log_scan_sessions включен столбец, записывающий задержку для каждого сеанса захвата. Задержка представляет собой время, прошедшее между фиксацией транзакции в исходной таблице и фиксацией последней отслеженной транзакции в таблицу изменений. Столбец задержки заполняется только для активных сеансов. Для сеансов, у которых значение в столбце empty_scan_count больше 0, в столбце latency устанавливается значение 0. Следующий запрос возвращает среднее время задержки для наиболее новых сеансов.
SELECT latency FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0
Данные о задержках можно использовать для определения того, насколько быстро или медленно процесс отслеживания обрабатывает транзакции. Эти данные наиболее полезны в том случае, если процесс отслеживания выполняется непрерывно. Если процесс отслеживания выполняется по расписанию, то задержка может быть высокой, ввиду запаздывания между фиксацией транзакций в исходной таблице и выполнением процесса отслеживания по его расписанию.
Еще одним важным показателем эффективности процесса отслеживания является пропускная способность. Это среднее число команд в секунду, обрабатываемых в каждом сеансе. Для определения пропускной способности сеанса следует разделить значение в столбце command_count column на значение в столбце продолжительности. Следующий запрос возвращает среднюю пропускную способность для наиболее новых сеансов.
SELECT command_count/duration AS [Throughput] FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0
Использование сборщика данных для сбора данных выборки
Сборщик данных SQL Server позволяет собирать моментальные снимки данных из любой таблицы или динамического административного представления и создавать хранилище данных производительности. Если захват изменений данных включён в базе данных, полезно регулярно делать моментальные снимки представления sys.dm_cdc_log_scan_sessions и представления sys.dm_cdc_errors для последующего анализа. Следующая процедура настраивает сборщик данных для сбора примерных данных из представления управления sys.dm_cdc_log_scan_sessions.
настройка сбора данных;
Включите сборщик данных и настройте хранилище данных управления. Дополнительные сведения см. в разделе Управление сбором данных.
Выполните следующий код для создания пользовательского сборщика для фиксации изменений данных.
USE msdb; DECLARE @schedule_uid uniqueidentifier; -- Collect and upload data every 5 minutes SELECT @schedule_uid = ( SELECT schedule_uid from sysschedules_localserver_view WHERE name = N'CollectorSchedule_Every_5min') DECLARE @collection_set_id int; EXEC dbo.sp_syscollector_create_collection_set @name = N' CDC Performance Data Collector', @schedule_uid = @schedule_uid, @collection_mode = 0, @days_until_expiration = 30, @description = N'This collection set collects CDC metadata', @collection_set_id = @collection_set_id output; -- Create a collection item using statistics from -- the change data capture dynamic management view. DECLARE @parameters xml; DECLARE @collection_item_id int; SELECT @parameters = CONVERT(xml, N'<TSQLQueryCollector> <Query> <Value>SELECT * FROM sys.dm_cdc_log_scan_sessions</Value> <OutputTable>cdc_log_scan_data</OutputTable> </Query> </TSQLQueryCollector>'); EXEC dbo.sp_syscollector_create_collection_item @collection_set_id = @collection_set_id, @collector_type_uid = N'302E93D1-3424-4BE7-AA8E-84813ECF2419', @name = ' CDC Performance Data Collector', @frequency = 5, @parameters = @parameters, @collection_item_id = @collection_item_id output; GOВ среде SQL Server Management Studio разверните узел "Управление", а затем разверните Коллекция данных. Щелкните правой кнопкой мыши пункт Сборщик данных о производительности CDC, затем пункт Запустить набор сбора данных.
В хранилище данных, которое было настроено в шаге 1, найдите таблицу custom_snapshots.cdc_log_scan_data. В данной таблице представлена историческая сводка данных из сессий сканирования журналов. Эти данные могут быть использованы для анализа задержки, пропускной способности и других показателей производительности во времени.
Режим обновления скрипта
При установке накопительных обновлений или пакетов обновления для экземпляра при перезагрузке экземпляр может войти в режим обновления скрипта. В этом режиме SQL Server может выполнить шаг для анализа и обновления внутренних таблиц CDC, что может привести к повторному созданию таких объектов, как индексы на таблицах записи. В зависимости от объема данных этот шаг может занять некоторое время или вызвать большое использование журнала транзакций для баз данных с включенной функцией CDC.