Устранение неполадок ускоренного восстановления баз данных

  • Статья

Применимо к: SQL Server 2019 (15.x) Azure SQL DatabaseУправляемый экземпляр SQL Azure

Эта статья поможет администраторам диагностировать проблемы с ускоренным восстановлением базы данных (ADR) в SQL Server 2019 (15.x) и более поздних версиях, Управляемый экземпляр SQL Azure и Azure SQL базы данных.

Проверка постоянного хранилища версий (PVS)

Используйте sys.dm_tran_persistent_version_store_stats динамического административного представления, чтобы определить, увеличивается ли размер PVS для ускоренного восстановления базы данных (ADR), а затем определить, какой фактор препятствует очистке постоянного хранилища версий (PVS).

В следующем примере скрипта содержится столбец sys.dm_tran_persistent_version_store_stats.pvs_off_row_page_skipped_oldest_aborted_xdesid, добавленный в SQL Server 2022 г. (16.x) и содержащий количество страниц, пропущенных для освобождения из-за самых старых прерванных транзакций. Если средство очистки версий медленно работает или если оно недопустимо, этот пункт покажет количество страниц, которые необходимо хранить для прерванных транзакций.

Образец запроса отображает все сведения о процессах очистки и показывает текущий размер PVS, самую старую прерванную транзакцию и другие сведения:

SELECT
 db_name(pvss.database_id) AS DBName,
 pvss.persistent_version_store_size_kb / 1024. / 1024 AS persistent_version_store_size_gb,
 100 * pvss.persistent_version_store_size_kb / df.total_db_size_kb AS pvs_pct_of_database_size,
 df.total_db_size_kb/1024./1024 AS total_db_size_gb,
 pvss.online_index_version_store_size_kb / 1024. / 1024 AS online_index_version_store_size_gb,
 pvss.current_aborted_transaction_count,
 pvss.aborted_version_cleaner_start_time,
 pvss.aborted_version_cleaner_end_time,
 dt.database_transaction_begin_time AS oldest_transaction_begin_time,
 asdt.session_id AS active_transaction_session_id,
 asdt.elapsed_time_seconds AS active_transaction_elapsed_time_seconds,
 pvss.pvs_off_row_page_skipped_low_water_mark,
 pvss.pvs_off_row_page_skipped_min_useful_xts,
 pvss.pvs_off_row_page_skipped_oldest_aborted_xdesid -- SQL Server 2022 only
FROM sys.dm_tran_persistent_version_store_stats AS pvss
CROSS APPLY (SELECT SUM(size*8.) AS total_db_size_kb FROM sys.database_files WHERE [state] = 0 and [type] = 0 ) AS df 
LEFT JOIN sys.dm_tran_database_transactions AS dt
ON pvss.oldest_active_transaction_id = dt.transaction_id
   AND
   pvss.database_id = dt.database_id
LEFT JOIN sys.dm_tran_active_snapshot_database_transactions AS asdt
ON pvss.min_transaction_timestamp = asdt.transaction_sequence_num
   OR
   pvss.online_index_min_transaction_timestamp = asdt.transaction_sequence_num
WHERE pvss.database_id = DB_ID();

  1. Проверьте размер pvs_pct_of_database_size, обратите внимание на отличия от типичного по сравнению с базовыми показателями во время других периодов активности приложения. Хранилище PVS считается большим, если его размер значительно больше базового уровня или близок к 50 % от размера базы данных. Чтобы устранить неполадки для PVS большого размера, выполните следующие действия.

  2. Активные, длительные транзакции в любой базе данных, где включен ADR, могут препятствовать очистке PVS. Получите oldest_active_transaction_id и проверка, была ли эта транзакция sys.dm_tran_database_transactions активной в течение длительного времени, выполнив запрос на основе идентификатора транзакции. Проверьте наличие длительных активных транзакций с запросом, аналогичным приведенному ниже примеру, который объявляет переменные для установки пороговых значений для длительности или объема журнала:

    DECLARE @longTxThreshold int = 1800; --number of seconds to use as a duration threshold for long-running transactions
DECLARE @longTransactionLogBytes bigint = 2147483648; --number of bytes to use as a log amount threshold for long-running transactions

SELECT
    dbtr.database_id, 
    transess.session_id,  
    transess.transaction_id, 
    atr.name, 
    sess.login_time,  
    dbtr.database_transaction_log_bytes_used, 
    CASE
       WHEN getdate() >= dateadd(second, @longTxThreshold, tr.transaction_begin_time) then 'DurationThresholdExceeded' 
       WHEN dbtr.database_transaction_log_bytes_used >= @longTransactionLogBytes then 'LogThresholdExceeded' 
       ELSE 'unknown' END AS Reason 
  FROM
    sys.dm_tran_active_transactions AS tr  
    INNER JOIN sys.dm_tran_session_transactions AS transess on tr.transaction_id = transess.transaction_id  
    INNER JOIN sys.dm_exec_sessions AS sess on transess.session_id = sess.session_id 
    INNER JOIN sys.dm_tran_database_transactions AS dbtr on tr.transaction_id = dbtr.transaction_id 
    INNER JOIN sys.dm_tran_active_transactions AS atr on atr.transaction_id = transess.transaction_id 
WHERE transess.session_id <> @@spid AND 
    ( getdate() >= dateadd(second, @longTxThreshold, tr.transaction_begin_time) OR
      dbtr.database_transaction_log_bytes_used >= @longTransactionLogBytes );

    Определив сеансы, попробуйте завершить сеанс, если это разрешено. Кроме того, выполните проверку приложения, чтобы определить природу проблемных активных транзакций.

    Дополнительные сведения об устранении неполадок с длительными запросами см. в разделе:

  1. Очистка постоянной версии может быть вызвана длительными проверками активных моментальных снимков. Инструкции, использующие уровни изоляции snapshot для чтения и фиксации snapshot (RCSI) или SNAPSHOT, получают метки времени на уровне экземпляра. Проверка snapshot использует метку времени для определения видимости строки для транзакции RCSI или SNAPSHOT в PVS, где включено ускоренное восстановление базы данных. Каждая инструкция, использующей RCSI, имеет собственную метку времени, тогда как изоляция SNAPSHOT имеет метку времени уровня транзакции. Эти метки времени транзакции уровня экземпляра используются даже в транзакциях с одной базой данных, так как транзакция может быть повышена до транзакции между базами данных. Таким образом, сканирование моментальных снимков может препятствовать очистке записей в ADR PVS или, если ADR отсутствует, в хранилище версий tempdb . Таким образом, из-за этого отслеживания версий длительные транзакции с использованием SNAPSHOT или RCSI могут привести к задержке очистки в базе данных в экземпляре ADR PVS, что приведет к росту размера ADR PVS.

    В исходном запросе на устранение неполадок в верхней части этой статьи отображается число страниц, pvs_off_row_page_skipped_min_useful_xts пропущенных для освобождения из-за длительного сканирования snapshot. Если значение pvs_off_row_page_skipped_min_useful_xts больше обычного, это означает, что происходит длительное сканирование моментального снимка, препятствующее очистке PVS.

    Этот пример запроса можно использовать для выбора проблемного сеанса:

    SELECT 
    snap.transaction_id, snap.transaction_sequence_num, session.session_id, session.login_time, 
    GETUTCDATE() as [now], session.host_name, session.program_name, session.login_name, session.last_request_start_time
FROM sys.dm_tran_active_snapshot_database_transactions AS snap
INNER JOIN sys.dm_exec_sessions AS session ON snap.session_id = session.session_id  
ORDER BY snap.transaction_sequence_num asc;

    Чтобы избежать задержек при очистке PVS, выполните приведенные далее действия.

    1. По возможности рекомендуется завершить длительный активный сеанс транзакций, который задерживает очистку PVS. Длительные транзакции в любой базе данных, где включен ADR, могут отложить очистку ADR PVS.
    2. Настройте длительные запросы, чтобы сократить длительность запросов и требуемые блокировки. Дополнительные сведения и рекомендации см. в разделах Общие сведения и устранение блокировки в SQL Server или Общие сведения и устранение проблем блокировки базы данных Azure SQL.
    3. Просмотрите приложение, чтобы определить характер проблемного активного snapshot сканирования. Рассмотрим другой уровень изоляции, например READ COMMITTED, вместо SNAPSHOT или READ COMMITTED SNAPSHOT для длительных запросов, которые задерживают очистку ADR PVS. Эта проблема возникает чаще при уровне изоляции SNAPSHOT.
    4. Эта проблема может возникать в SQL Server, Управляемый экземпляр SQL Azure и эластичных пулах базы данных Azure SQL, но не в отдельных базах данных Azure SQL. В Azure SQL эластичных пулов баз данных рассмотрите возможность перемещения баз данных из эластичного пула с длительными запросами с помощью уровней изоляции READ COMMIT SNAPSHOT или SNAPSHOT.

  2. Если размер PVS увеличивается из-за длительных транзакций на первичной или вторичной репликах, следует исследовать длительные запросы и устранить узкие места. DMV sys.dm_tran_aborted_transactions отображает все прерванные транзакции. Дополнительные сведения см. здесь. В столбце nest_aborted указано, что транзакция зафиксирована, но там есть прерванные части (точки сохранения или вложенные транзакции), которые могут блокировать процесс очистки PVS.

  3. Если база данных входит в группу доступности, проверьте значение secondary_low_water_mark. Оно аналогично значению low_water_mark_for_ghosts, сообщаемому sys.dm_hadr_database_replica_states. Выполните запрос к sys.dm_hadr_database_replica_states, чтобы определить, блокирует ли одна из реплик это значение, так как это также предотвратит очистку PVS. Очистка версий блокируется из-за запросов чтения на доступных для чтения вторичных репликах. Как SQL Server локальная база данных, так и база данных Azure SQL поддерживают удобочитаемые вторичные базы данных. В DMV sys.dm_tran_persistent_version_store_statspvs_off_row_page_skipped_low_water_mark также может предоставить указания по задержке вторичной реплики. Дополнительные сведения см. здесь.

    Это решение аналогично блокировке сканирования моментальных снимков. Перейдите к вторичным файлам, найдите сеанс, который выдает длинный запрос, и рассмотрите возможность его уничтожения, если это разрешено. Обратите внимание, что вторичная блокировка не только влияет на очистку версии ADR, но также может препятствовать очистке фантомных записей.

  4. Проверьте значение min_transaction_timestamp (или online_index_min_transaction_timestamp, если блокировка производится активным хранилищем PVS) и в соответствии с результатом проверьте значение sys.dm_tran_active_snapshot_database_transactions для столбца transaction_sequence_num, чтобы определить сеанс, в котором содержится старая транзакция моментального снимка, блокирующая очистку PVS.

  5. Если ни одна из указанных выше проверок не дала результата, значит, очистка блокируется аварийно завершенными транзакциями. Проверьте время последнего aborted_version_cleaner_last_start_time выполнения и aborted_version_cleaner_last_end_time , чтобы узнать, завершена ли очистка прерванной транзакции. После очистки аварийно завершенных транзакций значение oldest_aborted_transaction_id должно быть больше. oldest_aborted_transaction_id Если значение гораздо меньше oldest_active_transaction_idи current_abort_transaction_count имеет большее значение, существует старая прерванная транзакция, препятствующая очистке PVS. Чтобы решить проблему:

    • Если возможно, остановите рабочую нагрузку, чтобы очиститель версий смог продолжать работу.
    • Оптимизируйте рабочую нагрузку, чтобы уменьшить потребление блокировки на уровне объектов.
    • Проверьте приложение, чтобы обнаружить любые проблемы с прерыванием транзакций. Взаимоблокировка, дублирование ключа и другие нарушения ограничений могут вызвать высокую скорость прерывания.
    • На SQL Server в качестве экстренной меры отключите ADR, чтобы управлять размером PVS и количеством прерываний транзакций. См. статью о прерывании функции ADR.

  6. Если прерванная транзакция не была недавно успешно выполнена, проверьте наличие сообщений о проблемах VersionCleaner в журнале ошибок.

  7. Отслеживайте журнал ошибок SQL Server для записей "PreallocatePVS". При наличии записей "PreallocatePVS" может потребоваться увеличить возможность ADR для предварительного выделения страниц для фоновых задач, так как производительность может быть увеличена, когда фоновый поток ADR предварительно выделяет достаточно страниц, а процент выделений PVS переднего плана близок к 0. Для увеличения этого числа можно использовать sp_configure 'ADR Preallocation Factor'. Дополнительные сведения см. в статье о параметре конфигурации сервера для коэффициента предварительного распределения ADR.

Запуск процесса очистки PVS вручную

Не рекомендуется для освобождения места использовать ADR в средах баз данных с большим числом транзакций обновления/удаления, таких как крупномасштабная OLTP, без периода ожидания или восстановления для процесса очистки PVS.

Для активации процесса очистки PVS вручную между рабочими нагрузками или периодами обслуживания используйте системную хранимую процедуру sys.sp_persistent_version_cleanup.

EXEC sys.sp_persistent_version_cleanup [database_name];

Например, примененная к объекту директива

EXEC sys.sp_persistent_version_cleanup [WideWorldImporters];

Запись ошибок очистки

Начиная с SQL Server 2022 (16.x) SQL Server записывает поведение очистки ADR PVS в журнал ошибок SQL Server. Как правило, это приводит к тому, что новое событие журнала записывается каждые 10 минут.

См. также

Дальнейшие действия