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

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

Задание записи

Задание отслеживания инициируется путем запуска хранимой процедуры без параметров 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) или выполнением операции ожидания (continuous = 1).

непрерывный параметр

Непрерывный параметр определяет, удаляется ли 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, что поможет завершить дневную обработку и подготовиться к еженощным выполнениям. Ход выполнения процесса отслеживания также можно отслеживать, чтобы определить, когда все транзакции, зафиксированные в середине ночи, были сканированы и отложены в таблицы изменений. Это позволит завершить задание захвата, чтобы его можно было перезапустить при запланированном ежедневном перезапуске. Заменив заданный шаг sp_cdc_scan вызовом оболочки, написанной пользователем, для sp_cdc_scan, можно получить настраиваемое поведение с небольшими дополнительными усилиями.

Задание очистки

В этом разделе представлена информация о том, как работает задание очистки данных при захвате изменений.

Структура задания очистки

Для управления размером таблицы изменений используется стратегия очистки, основанная на удержании данных. Механизм очистки состоит из задания агента 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 для удаления записей из таблиц изменений базы данных.

Если выполняется очистка, то значение нижнего предела всех экземпляров системы отслеживания изначально обновляется в одной транзакции. Затем производится попытка удаления устаревших записей из таблиц изменений и таблицы 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.

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

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)
Отслеживание измененных данных (SQL Server)
Включение и отключение отслеживания измененных данных (SQL Server)
Работа с измененными данными (SQL Server)