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


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

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