Администрирование и мониторинг отслеживания измененных данных

Область применения: SQL Server Управляемый экземпляр SQL Azure

В этом разделе описывается, как администрировать и отслеживать запись измененных данных для SQL Server и Управляемый экземпляр SQL Azure.

Сведения о База данных SQL Azure, использующего другой механизм задания, см. в разделе CDC с База данных SQL Azure.

Задание отслеживания

Задание отслеживания инициируется путем запуска хранимой процедуры без параметров sp_MScdc_capture_job. Эта хранимая процедура начинается с извлечения настроенных значений для maxtrans, maxscanscontinuousа также 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 и Управляемый экземпляр SQL Azure механизм очистки состоит из задания агент SQL Server Transact-SQL, созданного при включении первой таблицы базы данных. Одно задание очистки управляет очисткой всех таблиц изменений базы данных; оно применяет одно значение срока хранения ко всем определенным экземплярам системы отслеживания.

Задание очистки инициируется путем запуска хранимой процедуры без параметров sp_MScdc_cleanup_job. После запуска данная хранимая процедура получает значение срока хранения и пороговое значение, установленные для задания очистки из системной таблицы msdb.dbo.cdc_jobs. Значение срока хранения используется для вычисления нижнего предела таблиц изменений. Указанное число минут вычисляется из максимального значения tran_end_time таблицы cdc.lsn_time_mapping для получения нового значения нижнего предела в виде значения datetime. Затем таблица «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, для столбца задержки устанавливается значение 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 представления управления.

настройка сбора данных;

1. Включите сборщик данных и настройте хранилище данных управления. Дополнительные сведения см. в разделе Управление сбором данных.

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

   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  
   

3. В СРЕДЕ SQL Server Management Studio разверните узел "Управление", а затем разверните коллекцию данных. Щелкните правой кнопкой мыши пункт Сборщик данных о производительности CDC, затем пункт Запустить набор сбора данных.

4. В хранилище данных, которое было настроено в шаге 1, найдите таблицу custom_snapshots.cdc_log_scan_data. В данной таблице предоставлен архивный моментальный снимок данных из сеансов просмотра журнала. Эти данные могут быть использованы для анализа задержки, пропускной способности и других показателей производительности во времени.

Режим обновления скрипта

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

Связанный контент

• CDC с База данных SQL Azure
• Отслеживание изменений данных (SQL Server)
• Сведения об отслеживании измененных данных (SQL Server)
• Включение и отключение отслеживания измененных данных (SQL Server)
• Работа с измененными данными (SQL Server)