Перенести нагрузку, предназначенную только для чтения, на вторичную реплику группы доступности Always On

Область применения:SQL Server

Возможности активных вторичных реплик в группах доступности Always On включают поддержку доступа в режиме только для чтения к одной или нескольким вторичным репликам (вторичные реплики, доступные для чтения). Читаемая вторичная реплика может находиться либо в режиме доступности с синхронной фиксацией, либо в режиме доступности с асинхронной фиксацией. Вторичная реплика, доступная для чтения, обеспечивает доступ только для чтения ко всем её вторичным базам данных. Однако вторичные базы данных, доступные для чтения, не устанавливаются в режим «только для чтения». Они являются динамическими. Данная вторичная база данных изменяется по мере применения к ней изменений, происходящих в соответствующей первичной базе данных. Для типичной вторичной реплики данные, включая таблицы, оптимизированные для памяти и поддерживающие устойчивое хранение, во вторичных базах данных доступны практически в реальном времени. Более того, полнотекстовые индексы синхронизируются со вторичными базами данных. Во многих случаях задержка данных между базой данных-источником и соответствующей базой данных-получателем находится в пределах нескольких секунд.

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

Примечание.

Хотя во вторичные базы данных нельзя записывать данные, их можно записывать в базы данных с доступом на чтение и запись на экземпляре сервера, на котором размещена вторичная реплика, включая пользовательские базы данных и системные базы данных, такие как tempdb.

Группы доступности Always On также поддерживают перенаправление запросов на подключение с намерением чтения к доступной для чтения вторичной реплике (маршрутизация только для чтения). Дополнительные сведения о маршрутизации только для чтения см. в статье Использование прослушивателя для подключения к вторичной реплике только для чтения (маршрутизация только для чтения).

Льготы

Направление подключений «только для чтения» к доступным для чтения вторичным репликам обладает следующими преимуществами:

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

  • Повышает окупаемость инвестиций в системы, размещающие вторичные реплики, доступные для чтения.

Кроме того, вторичные реплики, доступные для чтения, обеспечивают надежную поддержку операций только для чтения, как указано ниже:

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

  • Для нагрузок, предназначенных только для чтения, в дисковых таблицах используется управление версиями строк, чтобы устранить конфликты из-за блокировок во вторичных базах данных. Все запросы, выполняемые на вторичных базах данных, автоматически сопоставляются с уровнем изоляции транзакций «моментальный снимок», даже если явно заданы другие уровни изоляции транзакций. Кроме того, все подсказки блокировки игнорируются. Это позволяет исключить конфликт между чтением и записью.

  • Рабочие нагрузки только на чтение для устойчивых таблиц, оптимизированных для памяти, обращаются к данным точно таким же способом, как и при доступе к базе данных-источнику, используя скомпилированные в собственном коде хранимые процедуры или совместимость SQL с теми же ограничениями уровня изоляции транзакций (см. статью Уровни изоляции в компоненте ядро СУБД). Нагрузку отчетности или запросы только на чтение, выполняемые на первичной реплике, можно без каких-либо изменений выполнять на вторичной реплике. Точно так же, рабочая нагрузка отчетов или запросов только на чтение, выполняющаяся на вторичной реплике, может быть запущена на главной реплике без внесения каких-либо изменений. Подобно таблицам на диске, все запросы, выполняемые к вторичным базам данных, автоматически переводятся на уровень изоляции транзакций snapshot, даже если явно заданы другие уровни изоляции транзакций.

  • Операции DML допустимы для табличных переменных как в дисковых, так и в оптимизированных для памяти типах таблиц на вторичной реплике.

Предварительные условия для использования группы доступности

  • Доступные для чтения вторичные реплики (необходимое условие)

    Администратор базы данных должен настроить одну или несколько реплик, чтобы они, при выполнении во вторичной роли, разрешали либо все подключения (для доступа только для чтения), либо только подключения с намерением чтения данных.

    Примечание.

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

    Дополнительные сведения см. в статье Сведения о доступе клиентских подключений к репликам доступности (SQL Server).

    Предупреждение

    Доступны для чтения будут только реплики, находящиеся в одной основной сборке SQL Server. Дополнительные сведения см. в статье Основные сведения о поэтапном обновлении.

  • Прослушиватель группы доступности

    Для поддержки маршрутизации только для чтения группа доступности должна иметь прослушиватель группы доступности. Клиент, запрашивающий данные в режиме только для чтения, должен направлять свои запросы к данному прослушивателю, и в строке подключения клиента должно быть задано намерение приложения read-only, то есть это должны быть запросы только для чтения.

  • Маршрутизация только для чтения

    Маршрутизация только для чтения означает возможность SQL Server направлять входящие запросы на подключение с намерением чтения, адресованные прослушивателю группы доступности, на доступную вторичную реплику, допускающую чтение. Необходимые условия для использования маршрутизации только для чтения:

    • Для поддержки маршрутизации только для чтения доступная для чтения вторичная реплика должна иметь URL-адрес для маршрутизации только для чтения. Этот URL-адрес вступает в силу, только если локальная реплика работает во вторичной роли. URL-адрес маршрутизации только для чтения должен быть указан для каждой реплики отдельно (если для реплики требуется подобная маршрутизация). Все URL-адреса маршрутизации только для чтения используются для направления запросов на соединение с намерением чтения к определенной доступной для чтения вторичной реплике. Как правило, каждой доступной для чтения вторичной реплике назначается URL-адрес маршрутизации только для чтения.

    • Каждая реплика доступности, поддерживающая маршрутизацию только для чтения и при этом являющаяся первичной репликой, требует наличия списка маршрутизации только для чтения. Указанный список маршрутизации для доступа только на чтение применяется только тогда, когда локальная реплика работает в роли первичной. Этот список должен указываться отдельно для каждой реплики, по мере необходимости. Как правило, каждый список маршрутизации только для чтения будет содержать все URL-адреса маршрутизации только для чтения, причем URL-адрес локальной реплики будет идти в конце списка.

      Примечание.

      Для запросов на соединение с намерением чтения может выполняться балансировка нагрузки на нескольких репликах. Дополнительные сведения см. в разделе Настройка балансировки нагрузки между репликами только для чтения.

    Дополнительные сведения см. в статье Настройка маршрутизации только для чтения в группе доступности (SQL Server).

Примечание.

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

Ограничения и запреты

Некоторые операции поддерживаются не в полной мере, а именно:

  • Как только реплика для чтения будет включена, она сможет принимать подключения к своим вторичным базам данных. Однако при наличии любых активных транзакций в базе данных-источнике версии строк не будут полностью доступны в базе данных-получателе. Все активные транзакции, существовавшие на основной реплике на момент настройки вторичной реплики, должны быть зафиксированы или отменены. Пока этот процесс не завершится, сопоставление уровней изоляции транзакций во вторичной базе данных остается неполным, а выполнение запросов временно блокируется.

    Предупреждение

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

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

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

    • Отслеживание изменений явно отключено во вторичных базах данных.

    • Отслеживание измененных данных нельзя включить только для базы данных вторичной реплики. Функцию Change Data Capture можно включить в базе данных первичной реплики, а изменения можно считывать из таблиц CDC с помощью функций в базе данных вторичной реплики.

  • Поскольку операции чтения выполняются на уровне изоляции snapshot, очистка призрачных записей на первичной реплике может быть заблокирована транзакциями на одной или нескольких вторичных репликах. Задача очистки фантомных записей автоматически очищает фантомные записи дисковых таблиц в основной реплике, когда они более не нужны в любой из вторичных реплик. Это похоже на то, что происходит, когда вы выполняете транзакции на первичной реплике. В крайнем случае во вторичной базе данных потребуется принудительно завершить долго выполняющийся запрос на чтение, который блокирует процесс очистки фантомных записей. Обратите внимание, что очистка фантомных записей может быть заблокирована, если вторичная реплика отключается или когда перемещение данных во вторичной базе данных приостановлено. Фантомные записи используют физическое пространство в файле данных, что может вызвать проблемы с повторным использованием этого пространства. Дополнительные сведения см. в разделе об очистке фантомных записей. Это состояние также предотвращает усечение журнала, поэтому, если это состояние сохраняется, рекомендуется удалить эту вторичную базу данных из группы доступности. С таблицами, оптимизированными для памяти, не возникает проблем, связанных с очисткой фантомных записей, поскольку версии строк хранятся в памяти и не зависят от версий строк в первичной реплике.

  • Операция DBCC SHRINKFILE для файлов, содержащих таблицы на диске, может завершиться с ошибкой на первичной реплике, если файл содержит фантомные записи, которые все еще необходимы на вторичной реплике.

  • Начиная с SQL Server 2014 (12.x), вторичные реплики, доступные для чтения, могут оставаться в сети, даже если первичная реплика находится вне сети из-за действий пользователя или сбоя; например, если синхронизация была приостановлена по команде пользователя или из-за сбоя, либо если реплика находится в состоянии разрешения из-за того, что WSFC находится вне сети. Однако в этой ситуации маршрутизация, доступная только для чтения, не работает, так как прослушиватель группы доступности также находится вне сети. Клиенты могут подключаться непосредственно к вторичным репликам, доступным только для чтения, для рабочих нагрузок, доступных только для чтения.

Примечание.

Если вы выполняете запрос к динамическому административному представлению sys.dm_db_index_physical_stats на экземпляре сервера, где размещена доступная для чтения вторичная реплика, может возникнуть проблема блокировки REDO. Это связано с тем, что данное динамическое административное представление устанавливает блокировку (IS) на указанную пользовательскую таблицу или представление, что может блокировать запросы потока REDO на получение монопольной блокировки (X) этой пользовательской таблицы или представления.

Рекомендации по производительности

В этом разделе рассматриваются несколько аспектов производительности доступных для чтения вторичных баз данных

В этом разделе.

Задержка данных

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

Основная реплика отправляет вторичным репликам записи журнала изменений в основной базе данных. На каждой вторичной базе данных выделенный поток повтора применяет записи журнала. Во вторичной базе данных, доступной для чтения, определённое изменение данных не отображается в результатах запроса до тех пор, пока запись журнала, содержащая это изменение, не будет применена к вторичной базе данных, а транзакция не будет зафиксирована в первичной базе данных.

Это означает, что между первичной и вторичной репликами возникает некоторая задержка, обычно порядка нескольких секунд. В нетипичных случаях, например в ситуации, когда проблемы с сетью ухудшают пропускную способность, задержка может стать значительной. Задержка увеличивается из-за наличия узких мест в системе ввода-вывода и в результате приостановки движения данных. Для отслеживания приостановок перемещения данных можно использовать Панель управления AlwaysOn или динамическое административное представление sys.dm_hadr_database_replica_states.

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

В SQL Server 2014 (12.x) существовали особенности, которые следовало учитывать в отношении задержки данных на активных вторичных репликах; см. статью SQL Server 2014 (12.x) Active Secondaries: Readable Secondary Replicas. Начиная с SQL Server 2016 (13.x) нет особых соображений по задержке данных для оптимизированных для памяти таблиц. Ожидаемая задержка данных в таблицах, оптимизированных для памяти, сопоставима с задержкой в таблицах на диске.

Влияние на рабочую нагрузку только для чтения

При настройке вторичной реплики для доступа в режиме только для чтения рабочие нагрузки только для чтения на вторичных базах данных потребляют системные ресурсы, такие как ресурсы ЦП и ввода-вывода (для дисковых таблиц), отнимая их у потоков повтора, особенно если рабочие нагрузки только для чтения на дисковых таблицах характеризуются высокой интенсивностью операций ввода-вывода. При доступе к таблицам, оптимизированным для памяти, дополнительная нагрузка на систему ввода-вывода отсутствует, поскольку все строки находятся в памяти.

Кроме того, рабочие нагрузки, предназначенные только для чтения, на вторичных репликах могут блокировать изменения языка определения данных (DDL), применяемые через записи журнала.

  • Даже несмотря на то, что операции чтения не вызывают совмещаемых блокировок в связи с управлением версиями строк, эти операции вызывают блокировки стабильности схемы (Sch-S), что может приводить к блокировке операций повтора, в которых применяются изменения с помощью DDL. Операции DDL включают операции ALTER/DROP таблиц и представлений, но не DROP или ALTER хранимых процедур. Например, рассмотрим случай удаления дисковой или оптимизированной для памяти таблицы на первичной реплике. Если поток REDO обрабатывает записи журнала, чтобы удалить таблицу, он должен получить блокировку SCH_M для таблицы и может быть заблокирован запущенным запросом к таблице. Точно так же происходит и в первичной реплике, за исключением того, что удаление таблицы выполняется в составе пользовательского сеанса, а не потоком REDO.

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

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

Примечание.

Если поток повтора блокируется запросами на вторичной реплике, возникает событие XEvent sqlserver.lock_redo_blocked .

Индексирование

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

Для отслеживания действий использования индекса на вторичной реплике можно создавать запросы к столбцам user_seeks, user_scansи user_lookups динамического административного представления sys.dm_db_index_usage_stats .

Статистика для баз данных с доступом только для чтения

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

Чтобы решить эту проблему, вторичная реплика создает и поддерживает временную статистику для вторичных баз данных в tempdb. Суффикс _readonly_database_statistic добавляется к имени временной статистики. Он позволяет отличить временную статистику от постоянной, которая сохраняется в основной базе данных.

Только SQL Server может создавать и обновлять временную статистику. Тем не менее можно удалять временную статистику и наблюдать за ее свойствами с помощью тех же средств, которые используются для работы с постоянной статистикой.

  • Удалите временную статистику с помощью инструкции DROP STATISTICS Transact-SQL.

  • Мониторинг статистики ведется с помощью представлений каталога sys.stats и sys.stats_columns. sys_stats включает столбец is_temporaryдля указания того, какая статистика является постоянной, а какая — временной.

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

Дополнительные сведения о статистике SQL Server см. в статье Статистика.

В этом разделе.

Устаревшая постоянная статистика на вторичных базах данных

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

Когда постоянная статистика обновляется в основной базе данных, она автоматически сохраняется во вторичной базе данных. Затем SQL Server использует обновленную постоянную статистику, которая является более текущей, чем временная статистика.

При аварийном переключении группы доступности временная статистика удаляется на всех вторичных репликах.

Ограничения и запреты

  • Так как временная статистика хранится в tempdb, перезапуск службы SQL Server приводит к исчезновению всех временных статистических данных.

  • Суффикс _readonly_database_statistic зарезервирован для статистики, создаваемой SQL Server. Этот суффикс нельзя использовать при создании статистики в базе данных-источнике. Дополнительные сведения см. в статье Managing statistics on tables in SQL Data Warehouse (Управление статистикой таблиц в хранилище данных SQL).

Доступ к таблицам, оптимизированным для памяти, на вторичной реплике

С таблицами, оптимизированными для памяти, во вторичной реплике используются те же уровни изоляции транзакций, что и в первичной реплике. Рекомендуется выбрать изоляцию на уровне сеанса READ COMMITTED и установить параметр на уровне базы данных MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT на значение ON. Например:

ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON  
GO  
SET TRANSACTION ISOLATION LEVEL READ COMMITTED  
GO  
SELECT SUM(UnitPrice*OrderQty)   
FROM Sales.SalesOrderDetail_inmem  
GO

Рекомендации по планированию загрузки

  • В случае если таблицы хранятся на дисках, доступные для чтения вторичные реплики могут потребовать наличия свободного места в базе данных tempdb по двум причинам:

    • Применение уровня изоляции моментальных снимков приводит к копированию версий строк в базу данных tempdb.

    • Временная статистика для вторичных баз данных создается и поддерживается в базе данных tempdb. Временная статистика может привести к небольшому увеличению размера базы данных tempdb. Дополнительные сведения см. в пункте Статистика баз данных, предназначенных только для чтениядалее в этом разделе.

  • При настройке доступа на чтение для одной или нескольких вторичных реплик первичные базы данных добавляют 14 байт служебных данных к удаляемым, изменяемым или вставляемым строкам данных для хранения указателей на версии строк во вторичных базах данных для таблиц на диске. Эти дополнительные служебные 14 байт также переносятся во вторичные базы данных. Так как к строкам данных добавляются 14 байт, может происходить разбиение страниц.

    Данные версий строк не формируются в базах данных-источниках. Вместо этого вторичные базы данных создают версии строк. Тем не менее управление версиями строк увеличивает объем хранения данных как в базах данных-источниках, так и в базах данных-получателях.

    Добавление данных управления версиями строк зависит от настройки уровня изоляции моментальных снимков или уровня изоляции моментальных снимков с чтением зафиксированных данных (RCSI) в базе данных-источнике. В таблице ниже описывается поведение версионирования во вторичной базе данных, доступной для чтения, при различных параметрах для таблиц на основе диска.

    Доступна ли для чтения вторичная реплика? Включен ли уровень изоляции моментальных снимков или RCSI? База данных-источник Вторичная база данных
    нет нет Нет ни версий строк, ни 14-байтовых накладных расходов Отсутствуют версии строки, либо 14-байтовые издержки
    нет Да Версии строк и 14 байт служебных данных Нет версий строк, но есть 14 дополнительных байт
    Да нет Нет версий строк, но есть 14 дополнительных байт Версии строк и 14 байт служебных данных
    Да Да Версии строк и 14 байт служебных данных Версии строк и 14 байт служебных данных

Связанные задачи

Связанные материалы

См. также

Обзор групп доступности Always On (SQL Server)
Сведения о доступе клиента к репликам доступности (SQL Server)
Прослушиватели групп доступности, подключение клиентов и переключение приложений при отказе (SQL Server)
Статистика

  • Last updated on