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

Диагностика по устранению неполадок производительности в SQL

  • Статья

Применимо к: База данных SQL Azure

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

Уменьшение времени ожидания при работе с журналами

Каждая база данных и эластичные пулы в Базе данных SQL Azure управляют скоростью создания журналов с помощью регулирования скорости генерации журналов. В Hyperscale ограничение скорости журналирования установлено на уровне 105 МБ/с независимо от размера вычислительных ресурсов. Это значение предоставляется в столбце primary_max_log_rate в sys.dm_user_db_resource_governance.

Иногда скорость создания журналов на первичной реплике вычислений должна быть сокращена для поддержания соглашения об уровне обслуживания восстановления. Например, это может произойти, если сервер страницы или другая вычислительная реплика значительно отстает в применении новых записей журнала из службы журналов. Если за компонентами гипермасштабирования ничего нет, механизм управления скоростью создания журналов позволяет этой скорости достигать 100 МБ/с. Это эффективная максимальная скорость создания журналов во всех размерах вычислений с гипермасштабированием.

Примечание.

Скорость создания журналов составляет 150 МБ/с, доступна как предварительная версия функции. Дополнительные сведения и согласие на 150 МБ/с см . в блоге: усовершенствования гипермасштабирования за ноябрь 2024 г.

Следующие типы ожидания отображаются в sys.dm_os_wait_stats при снижении скорости записи в журнал:

Тип ожидания Причина
RBIO_RG_STORAGE Задержка потребления журналов сервером страницы
RBIO_RG_DESTAGE Задержка потребления данных долгосрочным хранилищем логов
RBIO_RG_REPLICA Задержка потребления журналов вторичной репликой высокой доступности или именованной репликой
RBIO_RG_GEOREPLICA Задержка потребления логов вторичной гео-репликой
RBIO_RG_DESTAGE Задержка обработки журналов службой журналов
RBIO_RG_LOCALDESTAGE Задержка в обработке логов журналирующей службой
RBIO_RG_STORAGE_CHECKPOINT Задержка в потреблении логов сервером страниц из-за медленной проверки контрольной точки базы данных.
RBIO_RG_MIGRATION_TARGET Задержка потребления журнала базой данных без гипермасштабирования во время обратной миграции

Функция динамического управления (DMF) sys.dm_hs_database_log_rate() предоставляет дополнительные сведения, которые помогут вам понять снижение скорости журналов, если таковые есть. Например, он может указать, какая конкретная вторичная реплика отстает в применении записей журналов, и каков общий размер журнала транзакций, который еще не применен.

Операции чтения сервера страниц

Реплики вычислений не кэшируют полную копию базы данных локально. Данные, локальные для реплики вычислений, хранятся в буферном пуле (в памяти) и в кэше локального расширения буферного пула (RBPEX), который содержит подмножество наиболее часто доступных страниц данных. Этот локальный кэш SSD имеет размер пропорционально размеру вычислительных ресурсов. С другой стороны, каждый сервер страницы имеет полный кэш SSD для части поддерживаемой базы данных.

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

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

  • Столбцы для чтения сервера страниц отчетов доступны в динамических представлениях выполнения и представлениях каталога.
  • Поля чтения сервера страницы присутствуют в следующих расширенных событиях:
    • sql_statement_completed
    • sp_statement_completed
    • sql_batch_completed
    • rpc_completed
    • scan_stopped
    • query_store_begin_persist_runtime_stat
    • query_store_execution_runtime_info
  • ActualPageServerReads / ActualPageServerReadAheads атрибуты присутствуют в XML-коде плана запроса для планов, включающих статистику среды выполнения. Например: 
    <RunTimeCountersPerThread Thread="8" ActualRows="90466461" [...] ActualPageServerReads="0" ActualPageServerReadAheads="5687297" ActualLobPageServerReads="0" ActualLobPageServerReadAheads="0" />

    Совет

    Чтобы просмотреть эти атрибуты в окне "Свойства плана запроса", требуется SSMS 18.3 или более поздней версии.

Статистика виртуальных файлов и учет операций ввода-вывода

В Azure SQL Database sys.dm_io_virtual_file_stats() DMF — это один из способов мониторинга статистики операций ввода-вывода, таких как IOPS, пропускная способность и задержка. Характеристики ввода-вывода в гипермасштабируемых системах отличаются из-за их распределенной архитектуры . В этом разделе мы сосредоточимся на чтении и записи операций ввода-вывода, как показано в этом DMF. В гипермасштабировании каждый файл данных, отображаемый в этом DMF, соответствует серверу страницы. DmF также предоставляет статистику ввода-вывода для локального кэша SSD на реплике вычислений и журнала транзакций.

Использование локального кэша SSD

Так как локальный кэш SSD существует на той же вычислительной реплике, где ядро СУБД обрабатывает запросы, операции ввода-вывода для этого кэша быстрее, чем операции ввода-вывода на серверах страниц. В базе данных гипермасштабирования или эластичном пуле у sys.dm_io_virtual_file_stats() есть специальная строка, содержащая статистику ввода-вывода для локального кэша SSD. Эта строка имеет значение 0 для столбцов database_id и file_id. Например, приведенный ниже запрос возвращает статистику ввода-вывода локального кэша SSD с момента запуска базы данных.

SELECT *
FROM sys.dm_io_virtual_file_stats(0, NULL);

Соотношение операций чтения из локального кэша SSD к агрегированным считываниям из всех других файлов данных — это соотношение попаданий локального кэша SSD. Эта метрика предоставляется счетчиками производительности RBPEX cache hit ratio и RBPEX cache hit ratio base, доступными в sys.dm_os_performance_counters DMV.

Считываний данных

  • При выполнении операций чтения ядром СУБД на реплике вычислений они могут обслуживаться либо локальным кэшем SSD, либо серверами страниц, либо сочетанием двух при чтении нескольких страниц.
  • Когда реплика вычислений считывает некоторые страницы из определенного файла данных, например файл с file_id 1, если эти данные находятся исключительно в локальном кэше SSD, все операции ввода-вывода для этого чтения учитываются в file_id 0. Если часть этих данных находится в локальном кэше SSD, а часть находится на страничных серверах, то операции ввода-вывода засчитываются на file_id 0 для части, обслуживаемой из локального кэша SSD, а часть, обслуживаемая с страничных серверов, засчитывается в их соответствующие файлы.
  • Когда реплика вычислений запрашивает страницу на определенном LSN с сервера страницы, если сервер страницы еще не достиг запрашиваемого LSN, запрос на чтение с реплики вычислений ожидает, пока сервер страницы не догонит, прежде чем страница будет возвращена. Для любого чтения с сервера страницы на вычислительной реплике отображается тип ожидания PAGEIOLATCH_*, если она ожидает операции ввода-вывода. В режиме масштабирования это время ожидания включает как время, необходимое для получения запрошенной страницы на сервере страниц, так и номер LSN, а также время, необходимое для перемещения страницы с сервера страниц в реплику вычислений.
  • Большие операции чтения, такие как предварительное чтение, часто выполняются с помощью чтения с разбросом-сбором. Это позволяет считывать до 4 МБ в виде одной операции ввода-вывода. Однако при чтении данных в локальном кэше SSD эти операции чтения учитываются как несколько отдельных операций чтения 8 КБ, так как буферный пул и локальный кэш SSD всегда используют 8-КБ страниц. В результате число операций чтения, замеченных в локальном кэше SSD, может быть больше фактического количества операций чтения, выполняемых механизмом.

Операции записи данных

  • Первичная расчетная реплика не записывается непосредственно на серверы страниц. Вместо этого записи журналов из службы журналов воспроизводятся на соответствующих серверах страниц.
  • Записи в реплике вычислений преимущественно записываются в локальный кэш SSD (file_id 0). Для операций записи, размер которых превышает 8 КБ, то есть выполненных с использованием механизма gather-write, каждая операция записи преобразуется в несколько отдельных записей по 8 КБ в локальный кэш SSD, поскольку буферный пул и локальный кэш SSD всегда используют страницы размером 8 КБ. В результате количество операций ввода-вывода, замеченных в локальном кэше SSD, может быть больше фактического количества операций ввода-вывода, выполняемых подсистемой.
  • Файлы данных, отличные от file_id 0, которые соответствуют серверам страниц, также могут отображать записи. В Hyperscale эти записи имитируются, так как вычислительные реплики никогда не записывают непосредственно на серверы страниц. Статистика ввода-вывода учитывается по мере их регистрации на вычислительной реплике. Операции ввода-вывода в секунду, пропускная способность и задержка, видимые на реплике вычислений для файлов данных, отличных от file_id 0, не отражают фактические статистические данные операций ввода-вывода, происходящих на серверах страниц.

Операции записи в журнал

  • На первичной реплике вычислений запись журнала учитывается в sys.dm_io_virtual_file_stats() в file_id 2.
  • В отличие от групп доступности AlwaysOn, когда транзакция фиксируется на первичной реплике вычислений, записи журналов не ужесточаются на вторичной реплике. В гипермасштабировании журнал закреплен в службе журналов и применяется к вторичным репликам асинхронно. Так как записи журналов на самом деле не происходят на вторичных репликах, учет операций ввода-вывода журнала в sys.dm_io_virtual_file_stats() на вторичных репликах не следует использовать в качестве статистики журнала транзакций.

Ввод-вывод данных в статистику использования ресурсов

В базе данных, отличной от для масштабирования, объединенные операции чтения и записи операций ввода-вывода в файлы данных, относящиеся к ограничению операций ввода-вывода данных управления ресурсами, отображаются в представлениях sys.dm_db_resource_stats и sys.resource_stats в столбце avg_data_io_percent. Соответствующими динамическими административными представлениями для эластичных пулов являются sys.dm_elastic_pool_resource_stats и sys.elastic_pool_resource_stats. Те же значения отображаются в метриках Azure Monitor как процент операций ввода-вывода данных для баз данных и эластичных пулов.

В гипермасштабируемой базе данных эти столбцы и метрики сообщают об использовании операций IOPS относительно ограничения локального хранилища SSD только на реплике вычислений, включая операции ввода-вывода в локальном кэше SSD и в базе данных tempdb. Значение 100 % в этом столбце указывает, что управление ресурсами ограничивает число операций ввода-вывода в локальном хранилище. Если это связано с проблемой производительности, оптимизируйте рабочую нагрузку, чтобы уменьшить количество операций ввода-вывода, или увеличьте объём вычислительных ресурсов, чтобы увеличить максимальное количество операций ввода-вывода в секунду. Для управления ресурсами считывания и записи данных локального кэша SSD система подсчитывает отдельные 8-КБ IO, а не более крупные IO, которые могут быть выданы движком базы данных.

Данные ввода-вывода на серверах страниц не сообщаются в представлениях использования ресурсов или с помощью метрик Azure Monitor, но сообщаются в sys.dm_io_virtual_file_stats(), как описано ранее.

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