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

Область применения: SQL Server 2019 (15.x) База данных SQL Azure Управляемый экземпляр SQL Azure

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

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

Используйте sys.dm_tran_persistent_version_store_stats DMV, чтобы определить, увеличивается ли размер быстрой очистки хранилища версий (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 );
   

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

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

   • Устранение неполадок с медленными выполнением запросов в SQL Server
   • Определение проблем с производительностью запросов в База данных SQL Azure
   • Определение проблем с производительностью запросов в Управляемый экземпляр SQL Azure

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

   В исходном запросе по устранению неполадок в верхней части этой статьи отображается количество страниц, pvs_off_row_page_skipped_min_useful_xts пропущенных для освобождения из-за длительной проверки моментального снимка. Если значение 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 или "Понимание" и "Устранение проблем" База данных SQL Azure.
   3. Просмотрите приложение, чтобы определить характер проблемного активного сканирования моментальных снимков. Рассмотрим другой уровень изоляции, например READ COMMITTED, а не SNAPSHOT или READ COMMITTED SNAPSHOT для длительных запросов, которые задерживают очистку ADR PVS. Эта проблема часто возникает с уровнем изоляции SNAPSHOT.
   4. Эта проблема может возникать в SQL Server, Управляемый экземпляр SQL Azure и эластичных пулах База данных SQL Azure, но не в отдельных базах данных SQL Azure. В База данных SQL Azure эластичных пулов рассмотрите возможность перемещения баз данных из эластичного пула, имеющих длительные запросы с использованием уровней изоляции READ COMMIT SNAPSHOT или SNAPSHOT.

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

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

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

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

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

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

9. Отслеживайте журнал ошибок 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 минут.

См. также

• sys.sp_persistent_version_cleanup
• sys.dm_tran_persistent_version_store_stats
• sys.dm_tran_aborted_transactions

Следующие шаги

• Концепции ускоренного восстановления баз данных
• Управление ускоренным восстановлением баз данных
• Ускорение восстановления базы данных в SQL Azure