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

Создание таблицы отслеживания

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

CREATE TABLE LatestDate (LogDate datetime2)
GO
CREATE CLUSTERED INDEX cx_LogDate on LatestDate(LogDate)
GO
-- Запускайте этот скрипт так часто, как требуется вашей среде.
INSERT INTO LatestDate (LogDate)
SELECT GETDATE()

Запуская этот скрипт периодически на основной реплике, вы можете отслеживать последнюю дату и убедиться, что ваши приложения имеют доступ к самым свежим данным.

Мониторинг производительности запросов

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

-- Часть 1: Захват запросов на создание отсутствующих индексов
SELECT
    migs.avg_total_user_cost,
    migs.avg_user_impact,
    migs.user_seeks,
    migs.user_scans,
    migs.last_user_seek,
    migs.last_user_scan,
    migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) AS improvement_measure,
    'CREATE INDEX [missing_index_' + CONVERT(varchar, mig.index_group_handle) + '_' + CONVERT(varchar, mid.index_handle) + '_' + LEFT(PARSENAME(mid.statement, 1), 32) + ']'
        + ' ON ' + mid.statement
        + ' (' + ISNULL(mid.equality_columns, '')
        + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ', ' ELSE '' END
        + ISNULL(mid.inequality_columns, '')
        + ')'
        + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact DESC

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

Уровень изоляции и использование TempDB

Важно отметить, что уровень изоляции изменяется на читаемых репликах. Все запросы, выполняемые во вторичных базах данных, автоматически отображаются на уровне изоляции снимка, даже если явно установлены другие уровни изоляции транзакций. Это означает, что все подсказки NOLOCK не имеют никакого эффекта. Хотя это предотвращает блокировку, оно увеличивает использование TempDB в терминах хранилища версий. Вы можете отслеживать использование TempDB с помощью DMV dm_tran_version_store_usage.

Заключение

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

Спасибо за чтение! Следите за новыми советами и трюками по SQL Server.