Понимание планов запросов

  • 11 мин

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

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

Когда пользователь отправляет запрос в ядро СУБД, происходит следующий процесс.

  1. Запрос анализируется для правильного синтаксиса и, если правильно, создается дерево синтаксического анализа объектов базы данных.
  2. Затем дерево синтаксического анализа вводится в компонент ядра СУБД с именем Algebrizer для привязки. На этом шаге проверяется, существуют ли столбцы и объекты в запросе, идентифицирует обрабатываемые типы данных. Выходные данные — это дерево обработчика запросов, которое служит входными данными для следующего шага.
  3. Оптимизация запросов является ресурсоемкой для ЦП, поэтому ядро СУБД кэширует планы выполнения в специальной области памяти, называемой кэшем планов. Если план запроса уже существует, он извлекается из кэша. Каждый запрос в кэше имеет хэш-значение, созданное на основе T-SQL в запросе, известного как query_hash. Модуль создает query_hash для текущего запроса и проверяет совпадения в кэше планов.
  4. Если план не существует, оптимизатор запросов использует оптимизатор на основе затрат для создания нескольких вариантов плана выполнения на основе статистики о столбцах, таблицах и индексах, используемых в запросе. Выходные данные — это план выполнения запросов.
  5. Запрос выполняется с помощью плана выполнения из кэша планов или нового плана, созданного на предыдущем шаге. Выходные данные — это результаты запроса.

Примечание.

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

Рассмотрим пример. Обратите внимание на следующий запрос:

SELECT orderdate,
        AVG(salesAmount)
FROM FactResellerSales
WHERE ShipDate = '2013-07-07'
GROUP BY orderdate;

В этом примере SQL Server проверяет наличие столбцов OrderDate, ShipDate и SalesAmount в таблице FactResellerSales . Если эти столбцы существуют, SQL Server создает хэш-значение для запроса и проверяет кэш плана для соответствующего хэш-значения. Если найдено соответствующее хэш-значение, подсистема пытается повторно использовать план. Если не найдено соответствующее хэш-значение, SQL Server проверяет доступную статистику по столбцам OrderDate и ShipDate .

Предложение WHERE , ссылающееся на столбец ShipDate , называется предикатом в этом запросе. Если есть некластеризованный индекс, включающий столбец ShipDate , SQL Server, скорее всего, будет включать его в план, если затраты ниже, чем получение данных из кластеризованного индекса. Затем оптимизатор выбирает самый низкий план затрат из доступных параметров и выполняет запрос.

Планы запросов объединяют ряд реляционных операторов для получения данных и сбора данных, таких как предполагаемое количество строк. Другим элементом плана выполнения является память, необходимая для операций, таких как присоединение или сортировка данных, известных как предоставление памяти. Предоставление памяти подчеркивает важность статистики. Если SQL Server оценивает, что оператор возвращает 10 000 000 строк, когда он фактически возвращает 100, то для запроса выделяется более крупный объем памяти. Чрезмерно большое предоставление памяти может привести к двум проблемам. Во-первых, запрос может столкнуться с RESOURCE_SEMAPHORE ожиданием, указывающим на то, что он ждет, когда SQL Server выделит большой объем памяти. SQL Server по умолчанию ожидает в 25 раз больше, чем затрачивается на запрос (в секундах) до выполнения, это может продолжаться до 24 часов. Во-вторых, если при выполнении запроса недостаточно памяти, данные переносятся в базу данных tempdb, что медленнее, чем работа в памяти.

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

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

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

Существует три различных типа планов выполнения.

Предполагаемый план выполнения

Этот тип плана выполнения создается оптимизатором запросов. Метаданные и размер предоставления памяти запроса основаны на оценках статистики, присутствующих в базе данных во время компиляции запросов. Чтобы просмотреть текстовый предполагаемый план, выполните команду SET SHOWPLAN_ALL ON перед выполнением запроса. При выполнении запроса вы увидите шаги плана выполнения, но запрос не будет выполнен, и вы не увидите никаких результатов. Параметр SET остается действующим, пока вы не выключите его.

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

Этот тип плана совпадает с предполагаемым планом; однако он также включает контекст выполнения для запроса. Этот контекст содержит предполагаемые и фактические числа строк, предупреждения о выполнении, фактическую степень параллелизма (количество используемых процессоров), а также время, прошедшее и время ЦП, используемое во время выполнения. Чтобы просмотреть фактический план на основе текста, выполните команду SET STATISTICS PROFILE ON перед выполнением запроса. Запрос выполняется, и вы получаете как план, так и результаты.

Динамическая статистика запросов

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