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

  • Статья

Область применения:База данных SQL Azure

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

Ожиданий регулирования скорости записи в журнал

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

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

Следующие типы ожидания (в sys.dm_os_wait_stats) описывают причины, по которым скорость ведения журнала может регулироваться в основной реплике вычислений:

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

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

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

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

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

  • Столбцы, доступные для чтения сервера страниц отчетов, имеются в представлениях DMV выполнения и в представлении каталога, например:

  • Операции чтения сервера страниц добавляются к следующим расширенным событиям:

    • sql_statement_completed
    • sp_statement_completed
    • sql_batch_completed
    • rpc_completed
    • scan_stopped
    • query_store_begin_persist_runtime_stat
    • запрос — store_execution_runtime_info

  • ActualPageServerReads/ActualPageServerReadAheads добавляются в XML плана запроса для реальных планов. Пример:

<RunTimeCountersPerThread Thread="8" ActualRows="90466461" ActualRowsRead="90466461" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="133645" ActualCPUms="85105" ActualScans="1" ActualLogicalReads="6032256" ActualPhysicalReads="0" ActualPageServerReads="0" ActualReadAheads="6027814" ActualPageServerReadAheads="5687297" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobPageServerReads="0" ActualLobReadAheads="0" ActualLobPageServerReadAheads="0" />

Примечание.

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

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

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

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

Локальный кэш RBPEX существует в реплике вычислений в локальном хранилище SSD. Поэтому операции ввода-вывода в этом кэше выполняются быстрее, чем операции ввода-вывода на серверах удаленных страниц. В настоящее время sys.dm_io_virtual_file_stats () в базе данных для масштабирования имеет специальную строку, сообщающую о вводе-выводе в локальном кэше RBPEX в реплике вычислений. Эта строка имеет значение 0 для обоих database_idfile_id столбцов и. Например, приведенный ниже запрос возвращает статистику использования RBPEX с момента запуска базы данных.

select * from sys.dm_io_virtual_file_stats(0,NULL);

Отношение операций чтения к RBPEX к агрегированным операциям чтения, выполненным для всех остальных файлов данных, обеспечивает коэффициент попаданий в кэш RBPEX. Счетчик RBPEX cache hit ratio также предоставляется в динамическом административном представлении счетчиков производительности sys.dm_os_performance_counters.

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

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

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

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

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

  • На основном вычислении запись журнала учитывается в file_id 2 sys.dm_io_virtual_file_stats. Запись в журнал в основном вычислений — это запись в основную зону журнала.
  • Записи журнала не зафиксированы на вторичной реплике при фиксации. В режиме "линейка" журнал применяется службой журналов ко вторичным репликам асинхронно. Поскольку записи журнала в действительности не происходят во вторичных репликах, любые учетные данные операций ввода-вывода в журнале на вторичных репликах предназначены только для отслеживания.

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

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

В этом столбце в базе данных для масштабирования в этой статье задается отчет об использовании операций ввода-вывода в секунду относительно ограничения локального хранилища только для реплик вычислений, а именно для операций ввода-вывода в RBPEX и tempdb. Значение 100 % в этом столбце указывает, что управление ресурсами ограничивает число операций ввода-вывода в локальном хранилище. Если это связано с проблемой с производительностью, настройте рабочую нагрузку для создания меньшего количества операций ввода-вывода или увеличьте цель службы базы данных, чтобы увеличить ограничение максимального количестваопераций ввода-вывода в управлении ресурсами. Для управления ресурсами при операциях чтения и записи RBPEX система подсчитывает количество отдельных 8-килобайтных IOs, а не более крупных IOs, которые могут выдаваться ядром СУБД SQL Server.

Операция ввода-вывода данных на серверах удаленных страниц не отображается в представлениях использования ресурсов или на портале, но указывается в sys.dm_io_virtual_file_stats () DMF (), как отмечалось ранее.

Дополнительные ресурсы