Поделиться через

  • Статья

Наблюдение за процессом отслеживания измененных данных

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

  • sys.dm_cdc_log_scan_sessions

    Административное представление sys.dm_cdc_log_scan_sessions содержит по строке для каждого сеанса просмотра журнала в текущей базе данных. Последняя строка соответствует текущему сеансу. В представлении также содержится строка с идентификатором сеанса 0, в которой содержатся статистические данные обо всех сеансах с момента последнего запуска экземпляра SQL Server.

  • sys.dm_cdc_errors

    В административном представлении sys.dm_cdc_errors содержится по одной строке для каждой ошибки, обнаруженной во время сеанса просмотра журнала системы отслеживания измененных данных.

Нахождение сеансов с пустыми результирующими наборами

Каждая строка в административном представлении sys.dm_cdc_log_scan_sessions представляет сеанс просмотра журнала (за исключением строки с идентификатором 1). Сеанс просмотра журнала является эквивалентом одного выполнения хранимой процедуры sp_cdc_scan. Во время сеанса просмотр может возвратить изменения или пустой результат. Если результирующий набор пуст, то для столбца empty_scan_count column в представлении 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 column, у которых присутствует значение большее, чем 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. Включите сборщик данных и настройте хранилище данных управления. Дополнительные сведения см. в разделе Управление сбором данных в среде SQL Server Management Studio.

  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 @paramters xml;
DECLARE @collection_item_id int;

SELECT @paramters = 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 = @paramters,
@collection_item_id = @collection_item_id output;

GO

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

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