Задача 

В данном контексте, подчеркивается важность умения настраивать ("в нужный режим") SQL-запросы для обеспечения их оптимальной производительности. Этот навык полезен для всех, кто работает с базой данных SQL Server - от обычных пользователей до администраторов и разработчиков, которые пишут собственные SQL-запросы.

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

Решение

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

Эти советы могут быть применены к SQL Server, работающей как на собственном сервере, так и в облаке.

Всегда проверяйте план выполнения запроса

Не важно, используете ли вы SQL Server Management Studio, Azure Data Studio или сторонний инструмент, всегда следует анализировать план выполнения запроса, чтобы выявить проблемы и определить, на чем следует сосредоточиться для улучшения производительности.

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

• Обзор плана выполнения SQL Server

• Как читать графические планы выполнения запросов SQL Server

• Сравнение графического плана выполнения SQL Server с текстовым планом

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

Находим операторы с наибольшей стоимостью

В данном примере мы можем рассмотреть стоимость операции Index Scan (сканирования индекса) и увидеть, что она составляет 94 процента от общей стоимости запроса. Исходя из этой стоимости, мы получаем представление о том, с чего начать, чтобы улучшить производительность.

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

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

Если вы работаете с Azure Data Studio, то в нем присутствует функциональный компонент - Operations grid (операционная сетка). Этот инструмент облегчает администрирование баз данных и отладку запросов, предоставляя детальную информацию о том, как база данных взаимодействует с запросами и операциями.

Посмотрите на предупреждения

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

В приведенном ниже примере речь идет о предупреждении "implicit conversion" (неявное преобразование).

Предупреждение "implicit conversion" относится к ситуации, когда система управления базами данных (СУБД) автоматически выполняет преобразование одного типа данных в другой без явного указания программиста или пользователя. Это может произойти, когда в SQL-запросе сравниваются или выполняются операции с данными разных типов.

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

Вот еще один пример, который показывает предупреждение о "high memory grant" (высоком выделении памяти). Такая ситуация возникает, когда запрос требует выполнения сложных операций со значительным объемом информации или используется большое количество временных структур данных в памяти.

Стрелки между операторами предоставляют ценную информацию

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

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

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

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

Окно свойств плана выполнения

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

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

Открыть план выполнения запроса в инструменте управления базой данных, таком как SQL Server Management Studio или Azure Data Studio.

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

В контекстном меню выбрать опцию "Properties " (Свойства) или аналогичную команду.

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

Рекомендации по индексам

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

Если вы кликните правой кнопкой мыши и выберете "Missing Index Details" (Сведения о недостающем индексе), то получите информацию и скрипт для создания нового индекса. Я рекомендую сначала оценить предложения и просмотреть уже существующие индексы перед созданием, чтобы избежать их дублирования. 

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

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

SARGABLE запросы

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

Для эффективного использования индексов запрос должен быть SARGable (search +‎ argument +‎ able). Это означает, что аргумент поиска (например, в предложении WHERE) может успешно использовать индекс. Здесь очень важна структура запроса, и для решения данной проблемы чаще всего требуется изменение кода.

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

Даже если используется правильный индекс, выполняется index scan (сканирование индекса), как показано ниже, поскольку аргумент в предложении WHERE не является sargable. Система не может гарантировать, что функция substring (подстроки) вернет верное значение. Также она не может "угадать", что именно вернется, поэтому и используется сканирование, чтобы проверить каждую строку индекса. 

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

Это будет выглядеть следующим образом:

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

Index seek (индексный поиск) - это метод доступа к данным в системе управления базами данных (СУБД), который позволяет непосредственно находить нужные значения в индексе, так как теперь запрос стал SARGable и использует точное значение из переменной @StartChar. Системе не нужно сканировать весь индекс, она может точно найти соответствующие записи.

Давайте разберемся более подробно:

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

SARGable запросы: Термин "SARGable" означает, что запрос можно оптимизировать с использованием индексов. Это возможно, когда запрос использует точные значения, а не сложные вычисления или функции, которые затрудняют использование индексов.

Переменная @StartChar: В нашем контексте, @StartChar является параметром, который содержит точное значение, и запрос использует это значение при фильтрации данных.

Когда запрос становится SARGable и использует точное значение из переменной @StartChar, СУБД может выполнить индексный поиск следующим образом:

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

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

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

Итак, модификация запроса таким образом, чтобы он стал SARGable, позволяет использовать index seek, что может значительно улучшить производительность запроса и сократить количество ресурсов, затрачиваемых на его выполнение.

Распределение данных

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

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

Когда статистика покажет, что данные сосредоточены в определенных сегментах и частях таблицы, это и станет местом для улучшений.

Получить доступ к такой информации можно, развернув таблицу в SQL Server Management Studio (SSMS) и в разделе Statistics (Статистика) выбрав один из элементов, кликнув правой кнопкой мыши и выбрав Properties (Свойства). Затем смотрите страницу Details (Детали).

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

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

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

Метрики производительности

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

Существует несколько бесплатных инструментов, которые можно использовать для сбора текущей статистики и данных, например sp_whoisactive, SQL Server First Responder Kit или wait stats query (запрос по статистике ожидания). С их помощью можно выявить запросы, выполняющиеся в течение длительного времени, высокую загрузку процессора, статистику ожидания и многое другое.

Также можно использовать такие средства, как SQL Performance Counters, Extended Events и Query Store. Чтобы отслеживать прошлое и иметь доступ к историческим данным, можно настроить процессы сбора данных на периодической основе. Такой процесс называется регистрацией данных или аудитом.

SQL Performance Counters (Счетчики производительности SQL):

• SQL Performance Counters - это собранные и доступные для мониторинга системные метрики и статистика, связанные с производительностью SQL Server.

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

• Счетчики производительности могут быть использованы администраторами для мониторинга и настройки производительности SQL Server.

Extended Events (Расширенные события):

• Extended Events - это механизм SQL Server для сбора и анализа информации о событиях, происходящих в базе данных и сервере.

• Они предоставляют более гибкий и детальный способ мониторинга и трассировки запросов и событий в SQL Server.

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

Query Store (Хранилище запросов):

• Query Store - это интегрированный компонент SQL Server, предназначенный для управления и мониторинга производительности запросов.

• Он сохраняет историю выполнения запросов, планов выполнения и статистику запросов, что позволяет анализировать и оптимизировать запросы с течением времени.

• Query Store позволяет обнаруживать изменения в производительности запросов, исследовать проблемы и принимать меры для их решения.

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

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

Новые возможности SQL Server

С каждой версией SQL Server добавляются новые возможности для повышения эффективности выполнения запросов. Ниже приведены некоторые из усовершенствований, появившихся в SQL Server 2017 и SQL Server 2019.

источник изображения

Вот их краткие объяснения:

Adaptive QP (Query Processing): Адаптивная обработка запросов позволяет SQL Server адаптироваться к изменяющимся условиям выполнения запросов, выбирая различные стратегии выполнения.

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

Interleaved Execution: Метод выполнения запросов, при котором несколько операторов запроса выполняются параллельно, что может улучшить производительность.

Memory Grant Feedback (Обратная связь о выделении памяти): Оптимизация, предназначенная для улучшения производительности запросов, которые выполняются в среде с ограниченным объемом оперативной памяти. Обратная связь - механизм, который позволяет SQL Server адаптироваться к производительности запросов на основе предыдущих выполнений.

Batch Mode: Режим выполнения запросов, при котором данные обрабатываются пакетами, что может улучшить производительность в операциях сканирования и агрегации больших объемов данных.

Intelligent QP (Query Processing): Интеллектуальная обработка запросов - набор функций и оптимизаций, которые делают выполнение запросов более эффективным и адаптивным.

Table Variable Deferred Compilation: Оптимизация, связанная с использованием временных переменных типа таблицы (table variable) в SQL-запросах и отложенной компиляцией (deferred compilation). Table variable используется для временного хранения данных в пределах одной процедуры, функции или пакета SQL. Она представляет собой таблицу, определенную как переменная и может использоваться для хранения и манипулирования данными, аналогично обычным таблицам в базе данных. Отложенная компиляция - метод оптимизации, при котором компиляция SQL-запроса происходит при первом выполнении, а не при создании запроса.

Batch Mode on Rowstore: Оптимизация, которая позволяет использовать режим пакетной обработки (Batch Mode) для операций обработки данных на обычных (rowstore) таблицах. Ранее режим пакетной обработки использовался преимущественно для работы с колоночными хранилищами данных (columnstore), но с внедрением Batch Mode on Rowstore, он стал доступен и для обычных строковых таблиц. Row Store - хранение данных в виде строк, что является одним из способов организации данных в таблицах.

T-SQL Scalar UDF (User-Defined Function) Inlining: Оптимизация, которая позволяет встроить (inline) код скалярных пользовательских функций (scalar UDF) непосредственно в текст SQL-запроса во время компиляции. Она позволяет улучшить производительность запросов, содержащих скалярные функции, и избежать накладных расходов, связанных с вызовами функций.

Approximate QP (Query Processing): Обработка запросов с использованием приближенных алгоритмов, которые могут быть менее точными, но более быстрыми.

Approximate Count Distinct: Приближенное вычисление уникальных значений в столбце, что может улучшить производительность при анализе больших объемов данных.

Row Mode: Режим выполнения запросов, при котором операции обработки данных выполняются на уровне отдельных строк данных в таблицах. Этот режим предназначен для операций, которые обрабатывают данные по одной строке за раз.

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

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

• Automatic plan correction (Автоматическая коррекция плана) - при возникновении регрессии плана (частой причиной является parameter sniffing). Parameter sniffing (прослушивание параметров) - это процесс, при котором SQL Server оптимизирует план выполнения на основе конкретных значений параметров при первом выполнении запроса. Когда система обнаруживает, что текущий план выполнения стал неэффективным из-за регрессии, она может принудительно вернуть выполнение к последнему известному хорошему плану. Это происходит автоматически без необходимости вмешательства администратора базы данных или разработчика

• Automatic index management (Автоматическое управление индексами) - (только для Azure SQL), если эта функция включена, она автоматически добавляет или удаляет индексы в зависимости от их использования.

Следующие шаги

• Вы можете прочитать другие советы по настройке запросов

• Вы можете узнать больше об intelligent query processing memory grant feedback (обратная связь по выделению памяти для интеллектуальной обработки запросов)

• Вы можете скачать Azure Data Studio

А в завершение хочу порекомендовать вам бесплатный урок курса MS SQL Server про наполнение БД тестовыми данными.