SQL-Ex blog

Босс: оптимизатор запросов

В общих чертах при отправлении запроса на SQL Server выполняется 3 основных фазы:

• Разбор запроса: На этой фазе проверяется правильность синтаксиса запроса. Построенное дерево синтаксического анализа посылается на следующую фазу.


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


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

Результаты выполнения следующего запроса дают нам все возможные причины перекомпиляции запросов.

SELECT dxmv.name,

dxmv.map_key,

dxmv.map_value

FROM sys.dm_xe_map_values AS dxmv

WHERE dxmv.name = N'statement_recompile_cause'

ORDER BY dxmv.map_key

• Изменилась схема


• Изменилась статистика


• Отложенная компиляция


• Изменение опции Set


• Изменилась временная таблица


• Изменился удаленный набор строк


• Изменились разрешения на просмотр


• Изменилась среда уведомления запроса


• Изменился PartitionView


• Изменились опции курсора


• Запрошена опция (перекомпиляция)


• Удален параметризованный план


• Линеаризация плана тестирования


• Изменилась версия базы данных, влияющая на план


• Изменилась политика принудительного использования плана из хранилища запросов


• Не удалось принудительно установить план в хранилище запросов


• В хранилище запросов отсутствует план

Мониторинг перекомпиляции запросов

Расширенные события (Extended Event) в SQL Server представляют собой инструмент мониторинга, который позволяет отслеживать метрики производительности базы данных и осуществлять сбор различных событий. Поэтому мы можем уверенно использовать расширенные события для решения проблем производительности запросов. Использование расширенного события для мониторинга перекомпиляций представляется подходящим решением. Событие sql_statement_recompile может захватить и выдать отчет в ситуации, когда происходит перекомпиляция уровня оператора. Следующий запрос создаст и запустит расширенное событие, которое даст знать, когда запрос перекомпилируется.

CREATE EVENT SESSION CaptureQuery_Recompilations  ON SERVER 

ADD EVENT sqlserver.sql_statement_recompile(

    ACTION(sqlserver.database_name,sqlserver.query_hash_signed,

            sqlserver.query_plan_hash_signed,sqlserver.sql_text,

            sqlserver.username))

ADD TARGET package0.ring_buffer

WITH (STARTUP_STATE=ON);

GO

С другой стороны, есть SQL Profiler - другой инструмент для мониторинга перекомпиляций, и класс событий SQL: StmtRecompile выводит сообщения, когда происходит перекомпиляция. Чтобы включить этот класс событий в SQL Profiler, необходимо выбрать их в Trace Properties (свойства трассировки). Итак, мы можем использовать SQL Profiler для мониторинга производительности запросов, но отметим, что этот SQL устарел. Мы выполним в базе данных Adventureworks следующий запрос, который будет содержать хинт запроса OPTION(RECOMPILE). По причине этого хинта выполняемый запрос будет перекомпилироваться оптимизатором.

SELECT 

    p.[ProductID] 

    ,p.[Name] 

    ,pm.[Name] AS [ProductModel] 

    ,pmx.[CultureID] 

    ,pd.[Description] 

FROM [Production].[Product] p 

    INNER JOIN [Production].[ProductModel] pm 

    ON p.[ProductModelID] = pm.[ProductModelID] 

    INNER JOIN [Production].[ProductModelProductDescriptionCulture] pmx 

    ON pm.[ProductModelID] = pmx.[ProductModelID] 

    INNER JOIN [Production].[ProductDescription] pd 

    ON pmx.[ProductDescriptionID] = pd.[ProductDescriptionID]

WHERE p.ProductID =994

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



В то же время SQL Profiler после выполнения запроса покажет отчет, который включает причину перекомпиляции запроса.

Изменения схемы и перекомпиляция запросов

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

ALTER TABLE Production.ProductDescription

ALTER COLUMN Description nvarchar(600);

GO

SELECT 

    p.[ProductID] 

    ,p.[Name] 

    ,pm.[Name] AS [ProductModel] 

    ,pmx.[CultureID] 

    ,pd.[Description] 

FROM [Production].[Product] p 

    INNER JOIN [Production].[ProductModel] pm 

    ON p.[ProductModelID] = pm.[ProductModelID] 

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

Перестройка индекса и перекомпиляция запросов

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

ALTER INDEX ALL ON Production.Product REBUILD

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

Статистика SQL Server и перекомпиляция запросов

Статистика SQL Server играет ключевую роль в производительности запросов, поскольку оптимизатор запросов использует статистику для оценки числа строк, которые вернет запрос. Оптимизатор запросов может создать статистику для отдельного столбца во время выполнения запроса, если мы включим опцию базы данных Auto Create Statistics (автоматическое создание статистики). После создания статистики оптимизатор запросов перекомпилирует план выполнения, предполагая, что появятся более свежие статистические данные. Давайте теперь более глубоко изучим эту концепцию на примере. Сначала мы создадим таблицу, а затем выполним очень простой запрос.

CREATE TABLE TestNewProduction

(PID  INT  PRIMARY KEY  IDENTITY(1,1),PModelID  INT,

Name VARCHAR(50),ProductNumber VARCHAR(50),SafetyStockLevel INT ,ReorderPoint INT)

После выполнения этого запроса, SQL Server сохраняет его план выполнения в кэше планов.

SELECT P.Name FROM TestNewProduction P

INNER JOIN Production.ProductModel PM

ON P.PModelID= PM.ProductModelID

WHERE P.Name LIKE  'A%'

Теперь мы вставим несколько строк в таблицу TestNewProduction.

INSERT INTO  TestNewProduction ( Name,PModelID,ProductNumber,SafetyStockLevel,ReorderPoint)  

SELECT  TOP 100   Name,ProductModelID,ProductNumber,SafetyStockLevel,ReorderPoint FROM Production.Product

WHERE ProductModelID IS NOT NULL

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

SELECT P.Name FROM TestNewProduction P

INNER JOIN Production.ProductModel PM

ON P.PModelID= PM.ProductModelID

WHERE P.Name LIKE  'A%'

Расширенное событие захватывает событие перекомпиляции оптимизатора запросов и показывает причину перекомпиляции.



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



После выполнения запроса производятся следующие шаги:

1. Оптимизатор запросов перекомпилирует запрос по причине изменения статистики


2. Новая статистика создается для столбца Name, поскольку этот столбец используется в условии WHERE.


3. Новая статистика создается для столбца PModelID, поскольку этот столбец используется в операторе JOIN.


4. Наконец, запрос завершен.

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

Другой момент, связанный со статистикой, состоит в том, что модификации данных приводят к устареванию статистики. Статистика в SQL Server хранится в виде диаграммы распределения данных, но после модификаций данных данные гистаграммы могут устареть. Ручное или автоматическое обновление статистики вызывает перекомпиляцию запросов. Например, давайте добавим 500 новых строк с таблицу TestNewProduction, чтобы понять как работает этот сценарий.

INSERT INTO  TestNewProduction ( Name,PModelID,ProductNumber,SafetyStockLevel,ReorderPoint)  

SELECT  TOP 100   Name,ProductModelID,ProductNumber,SafetyStockLevel,ReorderPoint FROM Production.Product

WHERE ProductModelID IS NOT NULL

GO 5

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

DROP EVENT SESSION [CaptureQuery_Recompilations] ON SERVER 

GO

CREATE EVENT SESSION [CaptureQuery_Recompilations] ON SERVER 

ADD EVENT sqlserver.auto_stats,

ADD EVENT sqlserver.sql_statement_recompile(

ACTION(sqlserver.database_name,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,sqlserver.sql_text,sqlserver.username))

ADD TARGET package0.ring_buffer

WITH (STARTUP_STATE=ON)

GO

После пересоздания расширенного события снова выполним наш тестовый запрос и проверим захваченные события.

SELECT P.Name FROM TestNewProduction P

INNER JOIN Production.ProductModel PM

ON P.PModelID= PM.ProductModelID

WHERE P.Name LIKE  'A%'

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



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

Опции SET и перекомпиляция запросов

Опции SET позволяют нам изменть поведение SQL Server на уровне сессии, поэтому мы можем изменять значения этих опций. Важно иметь в виду, что опции SET могут вызвать перекомпиляцию. Так, например, опции SQL Server Management Studio (SSMS) и опции подключения приложения могут различаться, что приводит к различным планам выполнения. Например, если изменить опции подключения ARITHABORT и NUMERIC_ROUNDABORT, оптимизатор запросов решит перекомпилировать запрос.

SET ARITHABORT ON

SET NUMERIC_ROUNDABORT OFF

SELECT P.Name FROM TestNewProduction P

INNER JOIN Production.ProductModel PM

ON P.PModelID= PM.ProductModelID

WHERE P.Name LIKE  'A%'

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

Заключение

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