Заметки Дмитрия Пилюгина о Microsoft SQL Server 

PDF

Введние

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

План выполнения состоит из операторов и их свойств, связанных между собой в древовидную структуру, каждый из которых отвечает за определенную логическую и физическую операцию. Все вместе они обеспечивают получения того результата, который описан текстом запроса. Внутри сервера операторы представляют собой объекты классов в памяти SQL Server. Пользователи сервера, т.е. мы с вами, видим их описание, сформированное в виде документа XML с определенной схемой, и отображаемое средой SQL Server Management Studio (SSMS) в графическом виде.

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

Версия сервера

Иногда встречаются ситуации, когда на форумах просят уточнить версию сервера, даже если выложен план запроса в правильном sqlplan (XML) формате. Вместо этого, можно сэкономить время и открыть план как XML, в самом первом элементе, который описывает план, вы увидите версию сервера в свойстве Build.

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

Число строк в таблицах

Второй частый вопрос, это «сколько строк у вас в таблице». Эту информацию можно также узнать из плана запроса (начиная с 2008 версии сервера). Для этого необходимо выбрать оператор доступа к данным (Scan или Seek) интересующей таблицы и посмотреть свойство TableCardinality для уточнения числа строк. Также может быть интересно свойство Estimated Row Size для уточнения размера строки и приблизительной оценки размеров таблицы или индекса (при условии, что таблица не сжата).

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

Контекст

План запроса сохраняет значимые настройки SET, для которых был построен, для того, чтобы их посмотреть, нужно выбрать в плане корневой элемент и открыть свойство Set Options. Например, так можно узнать, был ли план построен при включенной опции ARITHABORT (различие в этой настройке часто приводит к двум разным планам и ситуации с плохим прослушиванием параметров, описанной в статье Медленно в приложении, быстро в SSMS).

Количество CPU

Можно узнать число процессоров, доступных оптимизатору, для этого, в том же корневом элементе нужно открыть свойство OptimizerHardwareDependentProperties -> EstimatedAvailableDegreeOfParallelism и умножить его на 2. Исключение, если доступен всего один процессор, умножать на 2 не нужно.
Например, в моем случае:

2*2 = 4, доступно 4 CPU, действительно у меня на машине 4 ядерный процессор и все 4 ядра доступны серверу. Эта информация может дать понимание о том, на какой машине получен план.

Версия Cardinality Estimator

Начиная с SQL Server 2014 доступны несколько версий механизма оценки числа строк (Cardinality Estimator) оптимизатором, от этого механизма зависит стоимость операторов и большинство решений оптимизатора при выборе плана. Версию Cardinality Estimator можно посмотреть в свойстве корневого оператора CardinalityEstimationModelVersion.

• 70 – SQL Server <= 2012
• 120 – SQL Server 2014
• 130 – SQL Server 2016
• 140 – SQL Server vNext

Время выполнения запроса и ожидания

Начиная с SQL Server 2016 SP1, в действительном плане запроса доступна информация о времени выполнения и времени процессора, для этого в корневом элементе нужно раскрыть свойство QueryTimeStats и посмотреть значения CpuTime и ElapsedTime. Теперь не будет необходимости отдельно включать сбор времени выполнения или спрашивать «сколько выполнялся запрос» — все уже есть в плане.

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

Типы параметров

Свойство Parameter List, в котором перечислялись используемые в запросе параметры, присутствовало в плане давно, однако, начиная с SQL Server 2016 SP1, в определение параметров было добавлено свойство Parameter Data Type, в котором хранится тип данных параметра. Это может быть полезно для понимания проблем с преобразованиями типов.

Число прочитанных строк и Оценочное число строк к прочтению

В плане запроса есть два очень важных свойства Actual Number Of Rows и Estimated Number of Rows, действительное и оценочное число строк. Эти свойства, содержат информацию только о том, сколько строк вернул оператор чтения данных, но не сколько строк он действительно прочитал. Свойства Number of Rows Read и Estimated Number of Rows to be Read отвечают как раз на этот вопрос и позволяют понять, сколько строк в действительности прочитал или собирается прочитать сервер. Свойство ActualRowsRead (Number of Rows Read в SSMS) доступно начиная с SQL Server 2012 SP3, 2014 SP2, 2016 SP1. Свойство EstimatedRowsRead (Estimated Number of Rows to be Read в SSMS) доступно начиная с SQL Server 2016 SP1.

Статистика IO и времени выполнения по операторам

Еще несколько очень полезных свойств, которые появились в SQL Server 2016 , 2014 SP2 и доступны в действительном плане запроса, это метрики IO (если у оператора есть IO) – Actual IO Statistics, CPU и времени выполнения – Actual Time Statistics, а также памяти (начиная с 2016 SP1, если оператор требует память).
Свойства включают в себя следующие новые метрики, которые могут быть разделены по потокам, в случае параллельного плана:

Как видно из этого списка, теперь можно получить исчерпывающую информацию о времени выполнения того или иного оператора, затратах IO и памяти. В последних версиях SSMS эти метрики отражаются в окне свойств, если вы используете старую версию SSMS – вы можете посмотреть их, открыв план как XML. На мой взгляд, теперь открыты все пути к тому, чтобы показывать проценты в плане запроса не по оценочной стоимости, а по реальным затратам ресурсов (я создал предложение на Connect, если вам нравится эта идея, голосуйте).

Сведения о включенных флагах трассировки

Флаги трассировки в SQL Server являются своеобразными «переключателями» поведения сервера с поведения по умолчанию, на какое-либо другое, отличающееся. Начиная с SQL Server 2014 SP2 и 2016 SP1, информация о включенных флагах трассировки доступна в свойстве TraceFlags корневого элемента, она может включать до 100 одновременно включенных флагов в момент построения плана.

Информация о сливе данных в tempdb

Некоторые операторы плана, например, такие как Sort или Hash Match, требуют память во время выполнения запроса, однако, количество памяти рассчитывается в момент компиляции. В силу разных причин (например, неверной оценки числа предполагаемых строк), количество памяти может быть посчитано неверно. Если памяти было выделено меньше, чем нужно для выполнения, сервер вынужден сливать данные в tempdb (spill to tempdb), что замедляет выполнение запроса. Предупреждение о такой ситуации появилось в 2012 сервере, но начиная с SQL Server 2012 SP3, 2014 SP2, 2016 диагностическая информация была расширена и теперь включает в себя объем слитых и прочитанных данных, так что можно оценить степень бедствия и принять адекватные меры.

Заключение

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

PDF