Советы по настройке производительности SQL-запросов
Пересказ статьи Eduardo Pivaral. SQL Query Performance Tuning Tips
Пользователи баз данных SQL Server, администраторы и разработчики, все, кто пишет запросы, должны иметь некоторые знания по настройке запросов, чтобы последние, которые они пишут, были эффективными и выполнялись в оптимальном режиме. Есть несколько вещей, которые можно использовать для проверки производительности запросов и которые могут помочь в их настройке. В этой статье обсуждаются некоторые из этих вещей, о которых вам следует знать.
Здесь будут обсуждаться основные направления проверки и исправления проблемных запросов SQL. Следует отметить, что это не исчерпывающий перечень, а, скорее, начальное руководство и рассмотрение наиболее общих проблем. Существуют "общие" вещи, которые нужно проверять при настройке запросов, и после обнаружения общей проблемы вы можете погрузиться глубже, найти причину, а затем исправить её.
Изложенное здесь может применяться к SQL Server, работающему как локально, так и в облаке.
Все равно будете ли вы использовать SQL Server Management Studio, Azure Data Studio, или инструменты третьих фирм, всегда нужно рассматривать план запроса, чтобы обнаружить проблемы и то, на чем сфокусироваться для улучшения запроса.
Получив план выполнения (я предпочитаю его визуальную форму), мы можем проверить несколько следующих вещей.
В этом примере мы можем посмотреть на стоимость операции Index Scan (сканирование индекса), которая составляет 94 процента от общей стоимости запроса. Эта стоимость дает нам направление, в котором следует искать улучшение производительности.
Для более объемных запросов, это сделать сложнее, поскольку там может быть множество операторов, как на плане ниже. Но мы можем увидеть операцию Sort (сортировка) со стоимостью 91%.
Если вы используете Azure Data Studio, то там имеется сетка Operations, которая позволяет обнаружить это легче.
Для некоторых операций генерируются предупреждения, и вы сможете обнаружить их по желтому восклицательному знаку, как показано ниже. Если навести курсор на значок операции, вы увидите подробности.
На примере ниже предупреждение относится к "неявному преобразованию".
Вот другой пример, который показывает предупреждение "высокого выделения памяти".
Стрелки дают информацию о предварительном и фактической числе строк и размере данных, передаваемых между операторами. Чем толще стрелка, тем больше данных и строк передается между операторами.
Для одного и того же запроса размер стрелки может различаться в зависимости от используемого значения параметра.
Тут показана более толстая стрелка, если навести курсор на стрелку, мы увидим детали и высокое число строк.
Если снова выполнить запрос с другим значением параметра, можно увидеть, что стрелка стала тоньше и показана только одна строка.
Вы можете проверить любой оператор или весь план, получив подробную информацию щелчком правой кнопкой на нем и выбором пункта Properties (свойства).
Окно свойств покажет намного больше информации, что видно на рисунке ниже.
В некоторых случаях план выполнения предлагает индексы, которые могут быть созданы для улучшения плана. Ниже показано такое предложение зеленым цветом.
Если выполнить щелчок правой кнопкой и выбрать "Missing Index Details" (подробности отсутствующего индекса) вы получите информацию и скрипт для создания нового индекса. Я предлагаю сначала рассмотреть это предложение и существующие индексы, прежде чем его создавать, чтобы избежать дублирующих индексов.
Большинство производственных запросов содержат те или иные фильтры, чтобы уменьшить число возвращаемых строк. Вот почему так важно наличие правильных индексов; но не только наличие индексов имеет значение, вам также необходимо, чтобы запрос использовал эти индексы.
Чтобы сделать использование индексов эффективным, запрос должен быть SARGable, что означает: search (поиск) + argument (аргумент) + able (возможность). Этот термин указывает, когда поисковый аргумент (например в предложении WHERE) может эффективно использовать индекс. Это в значительной степени связано с проектированием запросов, и для устранения такого рода проблем в большинстве случаев требуются изменения кода.
Например, этот простой запрос должен выдать информацию о лицах, фамилия которых начинается с A.
Даже если используется правильный индекс, будет выполняться его сканирование (index scan), как показано ниже, поскольку аргумент в предложении WHERE не является поисковым. Движок не может гарантировать, что функция substring будет возвращать правильное значение, и не может догадаться, что будет возвращено, поэтому используется сканирование.
Если изменить запрос, чтобы убрать эту функцию и выполнить манипуляцию на стороне параметра, таким образом:
то движок может непосредственно извлечь требуемые значения, используя поиск по индексу (index seek), т.к. здесь не нужно угадывать, какие значения содержит столбец:
Если иногда запрос выполняется быстро, а иногда существенно медленней, это может быть связано с распределением данных и статистикой.
Вы можете проверить таблицы, которые являются частью запроса, и если обнаружите некий шаг запроса, который возвращает большой результирующий набор, вы можете проверить статистическое распределение, чтобы оценить распределение ваших данных. Доступ к этим данным можно получить в SSMS, развернув узел Statistics под таблицей, выбрав один из элементов и пункт Properties (свойства) в контекстном меню. Затем обратитесь к странице Details.
Ниже можно увидеть, что данные распределены достаточно хорошо, когда нет одного сегмента, содержащего бОльшую часть строк.
Вы можете получить ту же информацию, выполнив следующую команду и указав в качестве параметров имя таблицы и имя статистики:
Хороший проект базы данных должен включать очистку данных и перенос исторических данных в некоторую систему архивации. Поддержание небольших объемов данных поможет при соединениях, фильтрации, а также в задачах обслуживания.
Наряду с обсуждавшимися выше вещами, имеется достаточно много других инструментов, которые могут помочь определить, почему запросы выполняются медленно. Это, возможно, вообще не связано с вашим запросом, а виноват какой-то другой параллельно выполняющийся процесс.
Есть несколько бесплатных инструментов, которые вы можете использовать для захвата текущей статистики и данных, таких как sp_whoisactive, SQL Server First Responder Kit или wait stats query. С их помощью вы можете обнаружить долго выполняющиеся запросы, высокую степень использования ЦП, статистику ожиданий и многое другое.
Другие вещи, которые вы могли бы использовать, включают Счетчики производительности SQL, расширенные события (Extended Events) и хранилище запросов (Query Store). Если вам нужно заглянуть назад во времени, вы могли бы настроить процессы для сбора данных на периодической основе, поскольку вы имеете некоторые исторические данные.
Мне бы не хотелось тратить много времени на построение своих собственных инструментов, поскольку есть довольно много инструментов третьих фирм, которые могут помочь в настройке производительности запросов.
Если вы пользуетесь Azure SQL Database, там имеются встроенные метрики производительности, которые вы можете использовать на портале Azure, как показано ниже.
С каждой новой версией SQL Server появляются новые функции, которые позволяют выполнять запросы более эффективно. Ниже представлены некоторые улучшения, введенные в SQL Server 2017 и SQL Server 2019.
Microsoft внедрила некоторый ИИ и функции машинного обучения, поэтому на базе рабочей нагрузки сервер может подстраиваться под ваши нужды и улучшать производительность без всяких усилий с вашей стороны. Мы не можем рассказать о каждой функции, но вы можете почитать об этом, например, здесь.
Начиная с SQL Server 2017, доступна автоматическая настройка, и эта особенность может обнаружить проблемы производительности запросов и автоматически внести такие изменения в конфигурацию:
Всегда проверяйте план выполнения
Все равно будете ли вы использовать SQL Server Management Studio, Azure Data Studio, или инструменты третьих фирм, всегда нужно рассматривать план запроса, чтобы обнаружить проблемы и то, на чем сфокусироваться для улучшения запроса.
Получив план выполнения (я предпочитаю его визуальную форму), мы можем проверить несколько следующих вещей.
Найти операторы, имеющие наивысшую стоимость
В этом примере мы можем посмотреть на стоимость операции Index Scan (сканирование индекса), которая составляет 94 процента от общей стоимости запроса. Эта стоимость дает нам направление, в котором следует искать улучшение производительности.
Для более объемных запросов, это сделать сложнее, поскольку там может быть множество операторов, как на плане ниже. Но мы можем увидеть операцию Sort (сортировка) со стоимостью 91%.
Если вы используете Azure Data Studio, то там имеется сетка Operations, которая позволяет обнаружить это легче.
Искать предупреждения
Для некоторых операций генерируются предупреждения, и вы сможете обнаружить их по желтому восклицательному знаку, как показано ниже. Если навести курсор на значок операции, вы увидите подробности.
На примере ниже предупреждение относится к "неявному преобразованию".
Вот другой пример, который показывает предупреждение "высокого выделения памяти".
Стрелки между операторами дают хорошее понимание
Стрелки дают информацию о предварительном и фактической числе строк и размере данных, передаваемых между операторами. Чем толще стрелка, тем больше данных и строк передается между операторами.
Для одного и того же запроса размер стрелки может различаться в зависимости от используемого значения параметра.
Тут показана более толстая стрелка, если навести курсор на стрелку, мы увидим детали и высокое число строк.
Если снова выполнить запрос с другим значением параметра, можно увидеть, что стрелка стала тоньше и показана только одна строка.
Окно свойств плана выполнения
Вы можете проверить любой оператор или весь план, получив подробную информацию щелчком правой кнопкой на нем и выбором пункта Properties (свойства).
Окно свойств покажет намного больше информации, что видно на рисунке ниже.
Предложение индекса
В некоторых случаях план выполнения предлагает индексы, которые могут быть созданы для улучшения плана. Ниже показано такое предложение зеленым цветом.
Если выполнить щелчок правой кнопкой и выбрать "Missing Index Details" (подробности отсутствующего индекса) вы получите информацию и скрипт для создания нового индекса. Я предлагаю сначала рассмотреть это предложение и существующие индексы, прежде чем его создавать, чтобы избежать дублирующих индексов.
Поисковые запросы (SARGABLE)
Большинство производственных запросов содержат те или иные фильтры, чтобы уменьшить число возвращаемых строк. Вот почему так важно наличие правильных индексов; но не только наличие индексов имеет значение, вам также необходимо, чтобы запрос использовал эти индексы.
Чтобы сделать использование индексов эффективным, запрос должен быть SARGable, что означает: search (поиск) + argument (аргумент) + able (возможность). Этот термин указывает, когда поисковый аргумент (например в предложении WHERE) может эффективно использовать индекс. Это в значительной степени связано с проектированием запросов, и для устранения такого рода проблем в большинстве случаев требуются изменения кода.
Например, этот простой запрос должен выдать информацию о лицах, фамилия которых начинается с A.
SELECT [Title]
, [FirstName]
, [MiddleName]
, [LastName]
FROM [AdventureWorks2017].[Person].[Person]
WHERE SUBSTRING(LastName,1,1) = 'A'Даже если используется правильный индекс, будет выполняться его сканирование (index scan), как показано ниже, поскольку аргумент в предложении WHERE не является поисковым. Движок не может гарантировать, что функция substring будет возвращать правильное значение, и не может догадаться, что будет возвращено, поэтому используется сканирование.
Если изменить запрос, чтобы убрать эту функцию и выполнить манипуляцию на стороне параметра, таким образом:
SELECT [Title]
, [FirstName]
, [MiddleName]
, [LastName]
FROM [AdventureWorks2017].[Person].[Person]
WHERE LastName LIKE 'A%'то движок может непосредственно извлечь требуемые значения, используя поиск по индексу (index seek), т.к. здесь не нужно угадывать, какие значения содержит столбец:
Распределение данных
Если иногда запрос выполняется быстро, а иногда существенно медленней, это может быть связано с распределением данных и статистикой.
Вы можете проверить таблицы, которые являются частью запроса, и если обнаружите некий шаг запроса, который возвращает большой результирующий набор, вы можете проверить статистическое распределение, чтобы оценить распределение ваших данных. Доступ к этим данным можно получить в SSMS, развернув узел Statistics под таблицей, выбрав один из элементов и пункт Properties (свойства) в контекстном меню. Затем обратитесь к странице Details.
Ниже можно увидеть, что данные распределены достаточно хорошо, когда нет одного сегмента, содержащего бОльшую часть строк.
Вы можете получить ту же информацию, выполнив следующую команду и указав в качестве параметров имя таблицы и имя статистики:
DBCC SHOW_STATISTICS ("Production.WorkOrder", _WA_Sys_00000007_59904A2C);
GO Хороший проект базы данных должен включать очистку данных и перенос исторических данных в некоторую систему архивации. Поддержание небольших объемов данных поможет при соединениях, фильтрации, а также в задачах обслуживания.
Метрики производительности
Наряду с обсуждавшимися выше вещами, имеется достаточно много других инструментов, которые могут помочь определить, почему запросы выполняются медленно. Это, возможно, вообще не связано с вашим запросом, а виноват какой-то другой параллельно выполняющийся процесс.
Есть несколько бесплатных инструментов, которые вы можете использовать для захвата текущей статистики и данных, таких как sp_whoisactive, SQL Server First Responder Kit или wait stats query. С их помощью вы можете обнаружить долго выполняющиеся запросы, высокую степень использования ЦП, статистику ожиданий и многое другое.
Другие вещи, которые вы могли бы использовать, включают Счетчики производительности SQL, расширенные события (Extended Events) и хранилище запросов (Query Store). Если вам нужно заглянуть назад во времени, вы могли бы настроить процессы для сбора данных на периодической основе, поскольку вы имеете некоторые исторические данные.
Мне бы не хотелось тратить много времени на построение своих собственных инструментов, поскольку есть довольно много инструментов третьих фирм, которые могут помочь в настройке производительности запросов.
Если вы пользуетесь Azure SQL Database, там имеются встроенные метрики производительности, которые вы можете использовать на портале Azure, как показано ниже.
Новые возможности SQL Server
С каждой новой версией SQL Server появляются новые функции, которые позволяют выполнять запросы более эффективно. Ниже представлены некоторые улучшения, введенные в SQL Server 2017 и SQL Server 2019.
Microsoft внедрила некоторый ИИ и функции машинного обучения, поэтому на базе рабочей нагрузки сервер может подстраиваться под ваши нужды и улучшать производительность без всяких усилий с вашей стороны. Мы не можем рассказать о каждой функции, но вы можете почитать об этом, например, здесь.
Начиная с SQL Server 2017, доступна автоматическая настройка, и эта особенность может обнаружить проблемы производительности запросов и автоматически внести такие изменения в конфигурацию:
- Автоматическая корректировка плана - навязывание последнего известного хорошего плана, когда имеет место ухудшение плана (обычно вызывается прослушиванием параметра)
- Автоматическое обслуживание индексов - (только для Azure SQL) если включена, эта функция автоматически добавляет и удаляет индексы на основе их использования.
Обратные ссылки
Нет обратных ссылок
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой