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

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

  • Статья

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

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

Исследуйте размер PVS

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

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

SELECT pvss.database_id,
       DB_NAME(pvss.database_id) AS database_name,
       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_percent_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,
       pvss.oldest_aborted_transaction_id,
       pvss.oldest_active_transaction_id,
       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
FROM sys.dm_tran_persistent_version_store_stats AS pvss
CROSS APPLY (
            SELECT SUM(size * 8.) AS total_db_size_kb
            FROM sys.master_files AS mf
            WHERE mf.database_id = pvss.database_id
                  AND
                  mf.state = 0
                  AND
                  mf.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.persistent_version_store_size_kb > 0
ORDER BY persistent_version_store_size_kb DESC;

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

Если размер PVS не уменьшается, выполните следующие действия по устранению неполадок, чтобы найти и устранить причину большого размера PVS.

Подсказка

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

Большой размер PVS может быть вызван любой из следующих причин:

Проверка на наличие длительных активных транзакций.

Длительные активные транзакции могут предотвратить очистку PVS в базах данных с включенным ADR. Проверьте время начала самой старой активной транзакции с помощью столбца oldest_transaction_begin_time. Чтобы найти длительные транзакции, используйте следующий пример запроса. Пороговые значения для длительности транзакции и объема созданного журнала транзакций можно задать:

DECLARE @LongTxThreshold int = 900;  /* The number of seconds to use as a duration threshold for long-running transactions */
DECLARE @LongTransactionLogBytes bigint = 1073741824; /* The number of bytes to use as the generated log threshold for long-running transactions */

SELECT  dbtr.database_id,
        DB_NAME(dbtr.database_id) AS database_name,
        st.session_id,
        st.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 st
ON tr.transaction_id = st.transaction_id
INNER JOIN sys.dm_exec_sessions AS sess
ON st.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 = st.transaction_id
WHERE GETDATE() >= DATEADD(second, @LongTxThreshold, tr.transaction_begin_time)
      OR
      dbtr.database_transaction_log_bytes_used >= @LongTransactionLogBytes;

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

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

Проверьте длительные активные сканирования моментальных снимков

Длительные проверки активных моментальных снимков могут предотвратить очистку PVS в базах данных с включенным ADR. Инструкции, использующие изоляцию моментальных снимков READ COMMITTED (RCSI) или уровни изоляции SNAPSHOT, получают метки времени на уровне экземпляра. Сканирование моментального снимка использует метку времени для определения видимости строки версии для транзакции RCSI или SNAPSHOT. Каждая инструкция с использованием RCSI имеет собственную метку времени, в то время как изоляция SNAPSHOT имеет метку времени уровня транзакции.

Эти метки времени транзакций уровня экземпляра используются даже в транзакциях с одной базой данных, так как любая транзакция может быть преобразована в транзакцию между базами данных. Таким образом, сканирование моментальных снимков может предотвратить очистку PVS в любой базе данных в одном экземпляре ядра СУБД. Аналогичным образом, даже если ADR не включен, сканирование моментальных снимков может предотвратить очистку хранилища версий в tempdb. В результате PVS может увеличиваться в размере, если существуют длительные транзакции, использующие SNAPSHOT или RCSI.

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

Используйте следующий пример запроса, чтобы найти сеансы с длительной SNAPSHOT или транзакцией RCSI:

SELECT sdt.transaction_id,
       sdt.transaction_sequence_num,
       s.database_id,
       s.session_id,
       s.login_time,
       GETDATE() AS query_time,
       s.host_name,
       s.program_name,
       s.login_name,
       s.last_request_start_time
FROM sys.dm_tran_active_snapshot_database_transactions AS sdt
INNER JOIN sys.dm_exec_sessions AS s
ON sdt.session_id = s.session_id;

Чтобы предотвратить задержки в процессе очистки PVS:

  • Если это возможно, рассмотрите возможность убийства длительного активного сеанса транзакций, который задерживает очистку PVS.
  • Настройте длительно выполняющиеся запросы, чтобы уменьшить время их выполнения.
  • Просмотрите приложение, чтобы определить характер проблемного активного сканирования моментальных снимков. Рассмотрим другой уровень изоляции, например READ COMMITTED, вместо SNAPSHOT или RCSI для длительных запросов, которые задерживают очистку PVS. Эта проблема часто возникает с уровнем изоляции SNAPSHOT.
  • В эластичных пулах базы данных SQL Azure рассмотрите возможность перемещения баз данных с длительными транзакциями с помощью изоляции SNAPSHOT или RCSI из эластичного пула, чтобы избежать задержки очистки PVS в других базах данных в том же пуле.

Проверка запросов, выполняющихся долго, на вторичных репликах

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

Большое значение в столбце pvs_off_row_page_skipped_low_water_mark может быть признаком задержки очистки из-за запроса, выполняющегося долгое время на вторичной реплике. Помимо задержки очистки PVS, длительный запрос на вторичную реплику также может задерживать очистку призраков.

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

SELECT database_id,
       DB_NAME(database_id) AS database_name,
       low_water_mark_for_ghosts,
       synchronization_state_desc,
       synchronization_health_desc,
       is_suspended,
       suspend_reason_desc,
       secondary_lag_seconds
FROM sys.dm_hadr_database_replica_states
WHERE is_local = 1
      AND
      is_primary_replica = 1;

Дополнительные сведения см. в описании столбца low_water_mark_for_ghosts в sys.dm_hadr_database_replica_states .

Подключитесь к каждой читаемой вторичной реплике, найдите сеанс с длительным запросом и рассмотрите возможность завершения сеанса, если это разрешено. Дополнительные сведения см. в статье Поиск медленных запросов.

Проверка большого количества прерванных транзакций

Проверьте столбцы aborted_version_cleaner_start_time и aborted_version_cleaner_end_time, чтобы узнать, завершена ли последняя прерванная очистка транзакций. После очистки аварийно завершенных транзакций значение oldest_aborted_transaction_id должно быть больше. Если oldest_aborted_transaction_id гораздо ниже oldest_active_transaction_id, а значение current_abort_transaction_count большое, скорее всего, старая прерванная транзакция предотвращает очистку PVS.

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

  • Если вы используете SQL Server 2022 (16.x), увеличьте значение конфигурации сервера ADR Cleaner Thread Count. Дополнительные сведения см. в разделе Конфигурация сервера: количество потоков очистки ADR.
  • Если это возможно, остановите рабочую нагрузку, чтобы позволить более чистой версии добиться прогресса.
  • Просмотрите приложение, чтобы определить и устранить проблему с высокой скоростью прерывания транзакций. Прерывания могут возникать из высокой частоты взаимоблокировок, повторяющихся ключей, нарушений ограничений или времени ожидания запроса.
  • Оптимизируйте рабочую нагрузку, чтобы уменьшить блокировки, несовместимые с блокировками уровня объектов или секционирования IX, необходимых для очистки PVS. Подробную информацию смотрите в разделе о совместимости блокировки.
  • Если используется SQL Server, отключите ADR как экстренную меру для управления размером PVS. См. Отключение ADR.
  • Если используется SQL Server и если очистка прерванной транзакции не завершилась успешно, проверьте журнал ошибок для сообщений, сообщавших о проблемах VersionCleaner.
  • Если размер PVS не уменьшается, как ожидалось даже после завершения очистки, проверьте столбец pvs_off_row_page_skipped_oldest_aborted_xdesid. Большие значения указывают, что пространство по-прежнему используется версиями строк из прерванных транзакций.

Контроль размера PVS

Если у вас есть рабочая нагрузка с большим объемом инструкций DML (INSERT, UPDATE, DELETE, MERGE), например с большим объемом OLTP, и обратите внимание, что размер PVS велик, может потребоваться увеличить значение конфигурации сервера ADR Cleaner Thread Count, чтобы сохранить размер PVS под контролем. Дополнительные сведения см. в разделе Конфигурация сервера: число потоков очистки ADR, который доступен, начиная с SQL Server 2022 (16.x).

В SQL Server 2019 (15.x), или если увеличение значения конфигурации ADR Cleaner Thread Count не помогает достаточно сократить размер PVS, может потребоваться период отключения или восстановления рабочей нагрузки, чтобы процесс очистки PVS освободил пространство.

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

Например:

EXEC sys.sp_persistent_version_cleanup [WideWorldImporters];

Сбои очистки записи

Начиная с SQL Server 2022 (16.x), в журнал ошибок записываются важные сообщения очистки PVS. Статистика очистки также сообщается расширенным событием tx_mtvc2_sweep_stats.

Связанное содержимое