Настройка приложений и баз данных для повышения производительности в Базе данных SQL Azure

  • Статья

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

После выявления проблемы с производительностью, с которой вы столкнулись с База данных SQL Azure, эта статья поможет вам:

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

В этой статье предполагается, что вы уже работали с рекомендациями помощника по База данных SQL Azure базы данных и рекомендациями по автоматической настройке, если это применимо. Кроме того, предполагается, что вы ознакомились с обзором мониторинга и настройки, мониторингом производительности с помощью хранилище запросов и связанных статей, связанных с устранением проблем с производительностью. Кроме того, в этой статье предполагается, что у вас нет проблем с производительностью, связанной с использованием ресурсов ЦП, которые можно устранить, увеличив размер вычислительных ресурсов или уровень служб, чтобы предоставить дополнительные ресурсы для базы данных.

Примечание.

Аналогичные рекомендации в Управляемый экземпляр SQL Azure см. в разделе "Настройка приложений и баз данных для производительности в Управляемый экземпляр SQL Azure".

Настройка приложения

В традиционной локальной среде SQL Server процесс изначального планирования загрузки часто отделен от процесса запуска приложения в рабочей среде. Сначала приобретаются лицензии на оборудование и продукт, а затем настраивается производительность. При использовании SQL Azure рекомендуется объединить процессы настройки и запуска приложения. Оплата только за используемые ресурсы позволяет настраивать приложение на использование минимального объема ресурсов, которые нужны в данный момент, а не выполнять избыточную подготовку согласно прогнозам будущего роста нагрузки на приложение, так как такие прогнозы часто ошибочны.

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

Рекомендации и антипаттерны в разработке приложений для База данных SQL Azure

Уровни служб Базы данных SQL Azure предназначены для оптимизации стабильности и предсказуемости производительности приложения. Вместе с тем с помощью некоторых рекомендаций по настройке приложения можно настроить приложение для максимально эффективного использования ресурсов при определенном объеме вычислительных ресурсов. Многие приложения получают значительный прирост производительности после простого перехода на более высокий объем вычислительных ресурсов или уровень служб, в то время как для лучшей работы других приложений на новом уровне требуется дополнительная настройка. Для повышения производительности приложений со следующими характеристиками необходима дополнительная настройка:

  • Приложения с низкой производительностью из-за "нестабильной" работы.

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

  • Базы данных с интенсивной рабочей нагрузкой, которые не могут работать на одном компьютере.

    Базам данных, которые превышают использование ресурсов при использовании наивысшего объема вычислительных ресурсов уровня служб "Премиум", может быть полезно масштабировать рабочие нагрузки. Дополнительные сведения см. в разделах Сегментирование баз данных и Функциональное секционирование.

  • Приложения с неоптимальными запросами

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

  • Приложения с неоптимальной схемой доступа к данным

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

    Сведения о том, как предотвратить повторную взаимоблокировку в Базе данных SQL Azure, см. в статье Анализ и предотвращение взаимоблокировок в Базе данных SQL Azure.

Настройка базы данных

В этом разделе рассматриваются некоторые методы, которые можно использовать для настройки базы данных с целью повышения производительности приложения и использования наименьшего объема вычислительных ресурсов. Некоторые из этих методов совпадают с традиционными рекомендациями по настройке SQL Server, однако есть и специфические методы настройки базы данных SQL Azure. В некоторых случаях можно изучить использование ресурсов в базе данных, чтобы настроить и улучшить традиционные методы SQL Server для работы в Базе данных SQL Azure.

Выявление и добавление отсутствующих индексов

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

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

DROP TABLE dbo.missingindex;
CREATE TABLE dbo.missingindex (col1 INT IDENTITY PRIMARY KEY, col2 INT);
DECLARE @a int = 0;
SET NOCOUNT ON;
BEGIN TRANSACTION
    WHILE @a < 20000
    BEGIN
        INSERT INTO dbo.missingindex(col2) VALUES (@a);
        SET @a += 1;
    END
    COMMIT TRANSACTION;
    GO
SELECT m1.col1
    FROM dbo.missingindex m1 INNER JOIN dbo.missingindex m2 ON(m1.col1=m2.col1)
    WHERE m1.col2 = 4;

Снимок экрана: план запроса с по крайней мере одним отсутствующим индексом с изображением сканирования индекса.

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

Этот запрос можно использовать для оценки потенциально отсутствующих индексов:

SELECT
   CONVERT (varchar, getdate(), 126) AS runtime
   , mig.index_group_handle
   , mid.index_handle
   , CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact *
        (migs.user_seeks + migs.user_scans)) AS improvement_measure
   , 'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' +
        CONVERT (varchar, mid.index_handle) + ' 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
   , migs.*
   , mid.database_id
   , mid.[object_id]
FROM sys.dm_db_missing_index_groups AS mig
   INNER JOIN sys.dm_db_missing_index_group_stats AS migs
      ON migs.group_handle = mig.index_group_handle
   INNER JOIN sys.dm_db_missing_index_details AS mid
      ON mig.index_handle = mid.index_handle
 ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

В этом примере при выполнении запроса возникло такое предложение:

CREATE INDEX missing_index_5006_5005 ON [dbo].[missingindex] ([col2])

После его создания инструкция SELECT выбирает другой план, в котором используется поиск вместо сканирования, а затем выполняет план более эффективно.

Снимок экрана: графический план выполнения, показывающий план запроса с исправленными индексами.

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

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

Настройка запросов и указания на них

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

Примером является то, как оптимизатор запросов вносит параметры "на лету". Это распространено в SQL Server и также применяется к Базе данных SQL Azure. Во время компиляции оптимизатор запросов вычисляет текущее значение параметра с целью создания более оптимального плана запроса. Хотя эта стратегия часто может привести к плану запросов, который значительно быстрее, чем план, скомпилированный без известных значений параметров, в настоящее время он работает несовершенно как в База данных SQL Azure. (Новая функция интеллектуальной производительности запросов, представленная с именем SQL Server 2022Оптимизация плана конфиденциальности параметров устраняет сценарий, в котором один кэшированный план для параметризованного запроса не является оптимальным для всех возможных входящих значений параметров. В настоящее время оптимизация плана конфиденциальности параметров недоступна в База данных SQL Azure.)

Ядро СУБД поддерживает указания запросов (директивы), чтобы можно было указать намерение более намеренно и переопределить поведение по умолчанию для sniffing параметров. Вы можете использовать подсказки, если поведение по умолчанию несовершенно для определенной рабочей нагрузки.

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

DROP TABLE psptest1;
CREATE TABLE psptest1(col1 int primary key identity, col2 int, col3 binary(200));
DECLARE @a int = 0;
SET NOCOUNT ON;
BEGIN TRANSACTION
   WHILE @a < 20000
   BEGIN
     INSERT INTO psptest1(col2) values (1);
     INSERT INTO psptest1(col2) values (@a);
     SET @a += 1;
   END
   COMMIT TRANSACTION
   CREATE INDEX i1 on psptest1(col2);
GO

CREATE PROCEDURE psp1 (@param1 int)
   AS
   BEGIN
      INSERT INTO t1 SELECT * FROM psptest1
      WHERE col2 = @param1
      ORDER BY col2;
    END
    GO

CREATE PROCEDURE psp2 (@param2 int)
   AS
   BEGIN
      INSERT INTO t1 SELECT * FROM psptest1 WHERE col2 = @param2
      ORDER BY col2
      OPTION (OPTIMIZE FOR (@param2 UNKNOWN))
   END
   GO

CREATE TABLE t1 (col1 int primary key, col2 int, col3 binary(200));
GO

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

-- Prime Procedure Cache with scan plan
EXEC psp1 @param1=1;
TRUNCATE TABLE t1;

-- Iterate multiple times to show the performance difference
DECLARE @i int = 0;
WHILE @i < 1000
   BEGIN
      EXEC psp1 @param1=2;
      TRUNCATE TABLE t1;
      SET @i += 1;
    END

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

EXEC psp2 @param2=1;
TRUNCATE TABLE t1;

DECLARE @i int = 0;
    WHILE @i < 1000
    BEGIN
        EXEC psp2 @param2=2;
        TRUNCATE TABLE t1;
        SET @i += 1;
    END

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

Снимок экрана: графический план выполнения, показывающий настройку запроса с помощью плана сканирования.

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

При выполнении теста с параметром SET STATISTICS IO со значением ON логическое сканирование в этом примере будет проходить в фоновом режиме. Вы можете увидеть, что план выполнил 1148 операций чтения (что является неэффективным, если обычно нужно вернуть только одну строку).

Снимок экрана: графический план выполнения, показывающий настройку запроса с помощью логической проверки.

Во второй части примера используется указание запроса, которое позволяет дать оптимизатору инструкцию использовать конкретное значение во время компиляции. В этом случае обработчик запросов принудительно игнорирует значение, которое передается в качестве параметра, и вместо этого использует значение UNKNOWN. Это значение со средней частотой в таблице (независимо от искажения распределения). В результате мы получим план на основе поиска, который работает быстрее и использует меньше ресурсов, чем план из части 1 этого примера.

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

В системном представлении sys.resource_stats можно увидеть эффект, характерный для База данных SQL Azure. Существует задержка с момента выполнения теста и при заполнении таблицы данными. В этом примере первая часть выполнялась в рамках временного окна 22:25:00, а вторая часть — в 22:35:00. В первой части использовано больше ресурсов, чем во второй (в силу повышения эффективности).

SELECT TOP 1000 *
FROM sys.resource_stats
WHERE database_name = 'resource1'
ORDER BY start_time DESC

Снимок экрана: таблица sys.resource_stats с разницей в avg_cpu_percent после улучшения индексов.

Примечание.

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

Вы можете проверить sys.resource_stats , использует ли ресурс для теста больше или меньше ресурсов, чем другой тест. При сравнении данных разделите время тестов, чтобы они не были в одном 5-минутном окне в представлении sys.resource_stats . Цель расчетов — минимизировать общее использование ресурсов, а не пиковую нагрузку. Обычно оптимизация части кода с целью уменьшения задержки выполнения также приводит к сокращению использования ресурсов. Убедитесь, что изменения, внесенные в приложение, необходимы и что они не оказывают негативное влияние на работу приложения при использовании указаний запроса.

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

Рекомендации по архитектуре очень больших баз данных в База данных SQL Azure

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

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

Примечание.

Эластичные пулы недоступны для Управляемый экземпляр SQL Azure, экземпляров SQL Server в локальной среде, SQL Server на виртуальных машинах Azure или Azure Synapse Analytics.

Сегментирование баз данных

Так как База данных SQL Azure выполняется на стандартном оборудовании, ограничения емкости для отдельной базы данных будут ниже, чем в традиционной локальной среде SQL Server. Некоторые пользователи используют техники сегментирования, чтобы расширить операции базы данных среди нескольких баз данных, если размеры в пределах границы отдельной базы данных не соответствуют в Базе данных SQL Azure. Большинство клиентов, использующих сегментирование в Базе данных SQL Azure, разбивают данные одного измерения на несколько баз данных. При использовании этого подхода необходимо понимать, что часто приложения OLTP выполняют транзакции, которые применимы только к одной строке или небольшой группе строк внутри одной схемы.

Примечание.

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

Например, если в базе данных есть имя клиента, заказ и сведения о заказе (например, в AdventureWorks базе данных), эти данные можно разделить на несколько баз данных, группируя клиента с соответствующими сведениями о заказе и заказе. Это гарантирует, что данные клиента останутся в пределах отдельной базы данных. Приложение должно разбивать заказчиков по разным базам данных и эффективно распределять нагрузку. С сегментированием клиенты не только могут избежать максимального ограничения размера базы данных, но и База данных SQL Azure также могут обрабатывать рабочие нагрузки, которые значительно больше ограничений различных размеров вычислительных ресурсов, если каждая отдельная база данных вписывается в ограничения уровня обслуживания.

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

Функциональное секционирование

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

Если в Базе данных SQL Azure используется масштабируемая архитектура, лучше распределить функции приложения по разным базам данных. При использовании этого метода каждое приложение масштабируется независимо. По мере повышения нагрузки на приложение (и нагрузки на базу данных) администратор сможет определить объемы вычислительных ресурсов отдельно для каждой функции одного приложения. Такая архитектура позволит приложению расширяться за пределы одного сервера за счет распределения нагрузки по разным серверам.

Пакетные запросы

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

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

Кэширование на уровне приложения

Некоторые приложения базы данных содержат рабочие нагрузки, выполняющие много операций чтения. Уровни кэширования могут снизить нагрузку на базу данных, а вслед за ней и объем вычислительных ресурсов, необходимый для поддержки базы данных с помощью Базы данных SQL Azure. Кэш Azure для Redis Если у вас есть рабочая нагрузка с большим объемом чтения, данные можно считывать один раз (или, возможно, один раз на компьютер уровня приложений в зависимости от того, как он настроен), а затем хранить эти данные за пределами базы данных. Это способ уменьшения нагрузки базы данных (ЦП и операций ввода-вывода), но существует влияние на согласованность транзакций, так как данные, считываемые из кэша, могут быть не синхронизированы с данными в базе данных. Для многих приложений определенный уровень несогласованности приемлем, однако это подходит не для всех рабочих нагрузок. Мы советуем внимательно изучить требования к приложению, прежде чем использовать стратегию кэширования на уровне приложения.

Получите советы по настройке и проектированию

При использовании База данных SQL Azure можно выполнить скрипт T-SQL с открытым исходным кодом для улучшения конфигурации базы данных и проектирования в База данных SQL Azure. Скрипт анализирует базу данных по запросу и предоставляет советы по повышению производительности и работоспособности базы данных. Одни советы касаются настройки и операционных изменений в соответствии с рекомендациями, а другие — рекомендуют изменить структуру в соответствии с вашей рабочей нагрузкой, например, включить расширенные функции ядра СУБД.

Чтобы узнать больше о сценарии и о том, как приступить к работе с ним, посетите эту страницу вики-сайта с советами по Azure SQL.

Сведения о новых возможностях и обновлениях База данных SQL Azure см. в База данных SQL Azure новых возможностях?

Связанный контент