Напомню, в предыдущей статье мы начали разговор об интеллектуальных возможностях по настройке производительности SQL Server 2019. В этой статье мы разовьем тему и подробно поговорим об интеллектуальной настройке запросов. Готовясь к выпуску SQL Server 2014, наша команда инженеров приняла смелое решение написать новый программный код для обработчика запросов в ядре, который будет принимать решения при оценке кардинальности (cardinality estimation, CE). Новая «модель CE» вступит в силу, если для базы данных будет использоваться значение уровня совместимости (compatibility level) 120 или более высокое (120 является значением по умолчанию для SQL Server 2014). Вы можете прочитать о том, как это работает и почему мы внесли это изменение, в нашей документации, доступной по ссылке.
Это решение породило множество споров о том, было ли оно правильным. Одна из проблем данного подхода заключается в том, что это было масштабное, негибкое изменение. Когда команда заканчивала работу над SQL Server 2016 и планировала SQL Server 2017, все согласились с тем, что нужна новая функция обработки запросов. Как говорит Джо Сэк (Joe Sack), один из ведущих менеджеров программ, работающий над Query Processor (QP): «Команда поняла, что попытка отделаться локальными изменениями, которые работали бы во всем многообразии встречающихся ситуаций, - это не наш путь, если мы хотим продвигаться вперед. Скорее, нам нужно тратить силы и время на функции, позволяющие адаптироваться к широкому спектру рабочих нагрузок клиентов в экосистеме SQL Server (высокие нагрузки, низкие нагрузки, OLTP, гибридные варианты, хранилища данных...)».
Так родилось новое семейство функций в SQL Server 2017, которое называется адаптивной обработкой запросов (Adaptive Query Processing, AQP). Идея заключалась в том, чтобы встроить в обработчик запросов способность самостоятельно адаптироваться после выполнения запроса (или до его повторного выполнения), чтобы ускорить выполнение без какого-либо вмешательства пользователя SQL Server или изменений приложения.
Примечание. Примеры AOP для SOL Server 2017 можно найти по адресу.
Когда мы готовились к выпуску SQL Server 2017 с поддержкой AQP, планировалось множество новых функций, которые команда хотела добавить в AQP, однако на реализацию всех планов в полном объеме не хватило времени. Команда начала внедрять новые функции для улучшения AQP в базе данных SQL Server Azure, планируя реализовать их в SQL Server 2019. Кроме того, слово «адаптивный» на самом деле не отражало суть работы, которую проделала команда. В течение многих лет обработчик запросов SQL Server был достаточно умным - он использовал для принятия решений сложный набор алгоритмов, в основе которых лежали вычисления затрат на выполнение запроса. Но команда хотела большего; они хотели, чтобы обработчик запросов стал еще более интеллектуальным. Таким образом, название «интеллектуальная обработка запросов» (Intelligent Query Processing, IQP) прижилось.
На рис. 1 показано «родословное древо» возможностей обработчика запросов, где представлены и SQL Server 2017, и SQL Server 2019.
Рис. 1. «Родословное древо» интеллектуальной обработки запросов
Давайте рассмотрим каждую из этих новых возможностей (новые возможности выделены на рисунке серым цветом), приведя наглядные примеры того, как работает каждая из них. При чтении этого раздела очень важно помнить, что мы создали эти возможности специально, чтобы вы не думали о них. Если мы хорошо справились со своей работой, то со временем интеллектуальная обработка запросов станет «всего лишь» обработчиком запросов, а вы как разработчик приложений, администратор баз данных или специалист по данным просто привыкнете к гибкому, интеллектуальному и адаптирующемуся к нагрузкам вашей системы механизму. Вы можете ознакомиться со всеми возможностями адаптивной обработки запросов в новой документации по IQP по ссылке.
Примечание. Во всех сценариях, за исключением функции приблизительного подсчета количества уникальных значений Approximate Count Distinct, можно включить возможности интеллектуальной обработки запросов, установив значение уровня совместимости базы данных, равное 150. Approximate Count Distinct - это новая функция T-SQL для SQL Server 2019, для которой не требуется устанавливать уровень совместимости базы данных, равный 150.
Подготовительные шаги для использования примеров, иллюстрирующих интеллектуальную обработку запросов
Хотя преимущества интеллектуальной обработки запросов (IQP) очевидны для многих стандартных случаев, выигрыш в производительности при использовании IQP становится заметным при работе с большими наборами данных и базами данных, предназначенными для аналитических запросов. Поэтому для примеров, приведенных в этой статье, вам нужно использовать базу данных WideWorldImportersDW (более подробную информацию об этой базе данных и ее схеме можно получить по ссылке).
Приведенные в данной статье примеры будут работать на SQL Server 2019, установленном на Windows, Linux и в контейнерах. Учитывая большой объем данных, SQL Server потребуется как минимум 12 ГБ ОЗУ, чтобы различия в производительности для приведенных примеров стали заметны. Кроме того, в некоторых приведенных примерах запросов используется параллельная обработка, поэтому для демонстрации всех возможностей в полном объеме лучше установить SQL Server в многопроцессорной системе.
Все сценарии, использованные в этой статье, можно найти в репозитории GitHub.
Мы благодарны сотруднику Microsoft Джо Саку (Joe Sack) за подготовленные примеры, в том числе за дополнения, внесенные в базу данных WideWorldImportersDW. Эти примеры были созданы на основе материалов, хранящихся в репозитории Джо на GitHub по ссылке.
Чтобы использовать на практике примеры из этой статьи, выполните следующие действия:
- Загрузите резервную копию базы данных WideWorldImportersDW с сайта GitHub.
- Восстановите эту базу данных на вашем экземпляре SQL Server Вы можете использовать для этого готовый сценарий restorewwidw. sql. Возможно, вам придется изменить путь к каталогу, где находится ваша резервная копия, и путь к месту для восстановления файлов базы данных.
- Для запуска некоторых сценариев, приводимых в качестве примера, вам понадобятся таблицы большего размера, нежели те, которые добавлены по умолчанию в WideWorldImportersDW и которые не используют столбцовые индексы. Поэтому запустите сценарий extendwwidw.sql, чтобы создать две большие таблицы. Расширение этой базы данных увеличит ее размер, включая журнал транзакций, примерно до 8 Гб. Одна из этих таблиц называется Fact.OrderHistory. Взяв за основу таблицу Orders, мы намного увеличим ее размер и не будем использовать столбцовый индекс. Также мы создадим еще одну таблицу с именем Fact.OrderHistoryExtended. В ее основе будет таблица Fact.OrderHistory, однако новая таблица будет содержать большее количество строк.
Почти во всех примерах используются два метода:
- набор сценариев T-SQL, которые можно применять с любым инструментом для запуска сценариев, таким как SQL Server Management Studio, Azure Data Studio или sqlcmd;
- записная книжка T-SQL (T-SQL Notebook) в Azure Data Studio. Информацию о том, как пользоваться записной книжкой T-SQL в Azure Data Studio, вы найдете по ссылке.
Для выполнения одного из примеров потребуется клиент Windows, поскольку он использует известную утилиту нагрузочного тестирования ostress.exe. Подробная информация о том, как установить и использовать утилиту ostress.exe, приведена в разделе «Обратная связь по временно предоставляемому буферу памяти» этого блога. Я собрал все сценарии, предполагая, что вы будете запускать их под учетной записью системного администратора (я использовал учетную запись sa). В обычной практике вам потребуется создать другие учетные записи для использования SQL Server, но я хотел упростить примеры - поэтому просто используйте учетную запись с разрешениями sysadmin
.
Обратная связь по временно предоставляемому буферу памяти (Memory Grant Feedback Row Mode)
До прихода в команду разработчиков SQL Server я долгое время работал в технической поддержке Microsoft. Одна из самых сложных проблем, с которыми я сталкиваюсь, когда имею дело с производительностью, - это проблемы с предоставлением памяти (memory grant). Что же такое предоставление памяти?
SQL Server выделяет память по разным причинам. Когда SQL Server выполняет запрос, память может использоваться для кеширования буферов, связанных со страницами, принадлежащими индексам или таблицам в запросе. В большинстве запущенных экземпляров SQL Server буферный пул может уже находиться в выделенной памяти, поэтому для помещения в нее страниц не потребуется дополнительная память.
Некоторые запросы интенсивно используют память, и для них требуется определенный тип временной области для хранения данных. Двумя разновидностями таких запросов являются хеш-соединения (hash joins) (или даже просто хеш-операторы) и сортировки (sorts) . Чтобы выполнить хеш-соединение, SQL Server фактически должен построить мини-таблицу в памяти. Для любого типа сортировки данных может потребоваться создание массива или структуры определенного типа. В SQL Server должно иметься место для выполнения этих операций, поэтому он выделяет память вне буферного пула. Процесс выделения этой памяти механизмом выполнения запросов называется предоставлением памяти.
Пока все выглядит достаточно просто. Однако существует проблема: предоставление памяти основано на том, что оптимизатору запроса известен план выполнения запроса даже тогда, когда этот запрос выполняется в первый раз. Решение такой задачи обычно сводится к оценке кардинальности, или уникального количества строк, затрагиваемых выполняемой операцией, для ее выполнения. Если SQL Server оценивает, что операция сортировки данных как часть плана выполнения запроса будет выполняться для столбцов данных, суммарный размер которых составляет 100 байт, и при этом число строк, на которых должен выполняться запрос, составит 1 миллиард, то он должен предоставить достаточно памяти, чтобы выделить память для сортировки такого количества строк данных указанного размера. Аналогичная концепция применяется для хеш-оператора.
Совет. Существует блог команды разработчиков SQL Server, в котором подробно разъясняется функция предоставления памяти. Я рекомендую вам остановиться и обратиться к данному блогу, чтобы погрузиться в эту тему. Блог размещен по адресу.
Во многих случаях описанный подход работает просто отлично, и никаких заметных проблем не возникает. Однако что происходит, если предоставление памяти основано на неточных оценках кардинальности?
Может возникнуть два вида проблем:
- объем предоставляемой памяти может оказаться слишком мал по сравнению с действительно необходимым для выполнения операции объемом памяти, что может привести к печально известной и болезненной проблеме «утечки данных в tempdb» (tempdb spill). SQL Server не позволяет оператору хеш-соединения или сортировки получить необходимый объем памяти. Если запрошено очень много памяти (мы не указываем, какие конкретные цифры подразумеваются под «очень много», потому что можем изменить объем запрашиваемой памяти и не хотим, чтобы вы полагались на него как на абсолютную цифру), текущая выделенная память должна быть где-то сохранена. Но где? Вы наверняка догадались - в tempdb. Думайте об этом как о системе подкачки страниц, как о том, каким образом операционная система распределяет память, когда физическая память исчерпана;
- объем предоставляемой памяти может оказаться слишком велик по сравнению с действительно необходимым для выполнения операции объемом памяти. Это может снизить нагрузку на память для других элементов ядра SQL Server, однако более вероятна ситуация, когда несколько пользователей запускают запросы с чрезмерным объемом предоставляемой памяти, а SQL Server будет управлять выполнением запросов. В результате для некоторых пользователей «узким местом» окажется ожидание (wait_type) с именем
RESOURCE_ SEMAPHORE
.
Обе эти ситуации могут привести к снижению производительности. В SQL Server 2017 мы представили концепцию, называемую обратной связью по временно предоставляемому буферу памяти для пакетного режима. Эта функция является прекрасным примером адаптации. После завершения выполнения запроса SQL Server обладает информацией о том, сколько памяти было в действительности использовано по сравнению с первоначально запрошенным объемом памяти. Если использованная память была намного меньше предоставленной, зачем продолжать запрашивать слишком много памяти при следующем выполнении того же кешированного плана выполнения запроса? То же самое происходит, если объем использованной памяти был намного больше, чем первоначально запрошенный объем памяти. Зачем продолжать сохранять текущую выделенную память в базе данных tempdb для кешированных запросов раз за разом?
Обратная связь по временно предоставляемому буферу памяти решает эту проблему путем сохранения в кешированном плане выполнения запросов информации о том, каким должен быть корректный объем предоставляемой памяти для будущих выполнений запроса. Пользователю при этом кажется, что SQL Server «исправился сам». Эта функция была очень полезной для SQL Server 2017, однако она поддерживалась только для операций в пакетном режиме, что означало, что она работала лишь для операций со столбцовыми индексами. Как вы узнаете из одного из следующих разделов этой статьи под названием «Пакетный режим для хранилища строк», SQL Server поддерживает операции пакетного режима не только для столбцовых индексов. Однако почему бы не поддерживать обратную связь по временно предоставляемому буферу памяти, даже если пакетный режим не используется?
В результате появился адаптивный механизм SQL Server для сценариев предоставления памяти независимо от используемых типов таблиц или индексов.
Чтобы включить режим обратной связи по временно предоставляемому буферу памяти, достаточно просто изменить параметр уровня совместимости базы данных (dbcompat
), установив для него значение, равное 150
.
Если для параметра dbcompat
установлено значение 150
, вы можете также отключить или включить режим обратной связи по временно предоставляемому буферу памяти, не изменяя значение уровня совместимости базы данных. Для этого вам нужно использовать команду ROW_MODE_MEMORY_GRANT_FEEDBACK
оператора ALTER DATABASE SCOPED CONFIGURATION
. Вы также можете отключить эту функцию на уровне запроса, используя параметр запроса DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK
. Вы можете посмотреть примеры того, как нужно использовать эти возможности включения и отключения режима обратной связи по временно предоставляемому буферу памяти, по ссылке.
Выделение слишком малого объема памяти
Рассмотрим несколько примеров. Давайте сначала рассмотрим сценарий, в котором SQL Server предоставляет слишком мало памяти по сравнению с фактически используемой памятью, что приводит к «утечке данных в tempdb». Все сценарии, используемые в этих примерах, можно найти в этом каталоге. Есть два способа запустить примеры кода для этого сценария:
- использовать сценарий T-SQL iqp_rowmodemfg.sql ;
- использовать записную книжку T-SQL в Azure Data Studio с именем iqp_rowmodemfg.ipynb.
Давайте выполним сценарий T-SQL iqp_rowmodemfg.sql шаг за шагом. Я буду применять для этого SQL Server Management Studio, попутно объясняя различия в плане выполнения запросов, но вы можете воспользоваться любым инструментом, отображающим план выполнения запроса. В приведенном в качестве примера сценарии T-SQL имеются комментарии для каждого шага рассматриваемого примера.
- На шаге 1 мы изменяем уровень совместимости базы данных, устанавливая для него значение 150, очищаем кеш процедур и заполняем буферный пул страницами из таблицы с именем Fact.OrderHistory, размещенной в базе данных WideWorldImportersDW, «разогревая» буферный пул. Для того чтобы включить обратную связь по временно предоставляемому буферу памяти для хранилища строк, необходимо установить значение параметра dbcompat, равное 150. Очистка кеша процедур - это всего лишь шаг, необходимый, чтобы убедиться, что мы «начинаем чистить». (Обратите внимание на использование опции
ALTER DATABASE
для очистки кеша процедур только для этой базы данных. Это очень хороший вариант!) Выгрузка страниц с диска для таблицы Fact.OrderHistory выполняется только для обеспечения сравнения производительности запросов с и без предоставления обратной связи по временно предоставляемому буферу памяти - это «честный бой».-- Шаг 1. Убедитесь, что для текущей базы данных установлено значение -- уровня совместимости 150, и очистите кеш процедур для этой базы данных. -- Также поместите таблицу в кеш, чтобы сравнить «теплые» запросы кеша USE [WideWorldImportersDW] GO ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 150 GO ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE GO SELECT COUNT(*) FROM [Fact].[OrderHistory] GO
- На шаге 2 создаются условия для предоставления недостаточного объема памяти. Я покажу вам один прием, позволяющий смоделировать такую ситуацию. Команда T-SQL
UPDATE STATISTICS
имеет специальный необязательный параметр для принудительного задания указанного количества строк или страниц, которые хранятся в статистике запроса. Вы вряд ли когда-либо будете использовать этот параметр при обычной работе с SQL Server. В документации, описывающей командуUPDATE STATISTICS
(см. статью), об этом параметре говорится следующее: «Используется только в ознакомительных целях. Не поддерживается. Будущая совместимость не гарантируется». Так что рассматриваемый здесь вариант предназначен только для этой демонстрации. Для наших целей давайте принудительно увеличим кардинальность, хранящуюся в статистике выполнения запроса, для этой таблицы до 1000 строк:-- Шаг 2. Имитация устаревшей статистики UPDATE STATISTICS Fact.OrderHistory WITH ROWCOUNT = 1000 GO
На самом деле в этой таблице 3 702 592 строки; принудительно заданный параметр статистики, предполагающий, что в таблице всего 1000 строк, воспроизводит сценарий, когда данные статистики не синхронизированы с фактическими данными о количестве строк в таблице.
- Перейдем к шагу 3. Теперь выполним наш запрос, используя таблицу
OrderHistory
.-- Шаг 3. Запустите запрос, чтобы получить данные о заказе и наличии товара -- При выполнении запроса НЕ выделяйте эти комментарии! SELECT fo.[Order Key], fo.Description, si.[Lead Time Days] FROM Fact.OrderHistory AS fo INNER HASH JOIN Dimension.[Stock Item] AS si ON fo.[Stock Item Key] = si.[Stock Item Key] WHERE fo.[Lineage Key] = 9 AND si.[Lead Time Days] > 19 GO
При выполнении этого запроса SQL Server пытается получить данные заказа и позиции товара. Обратите внимание на использование
HASH JOIN
в синтаксисе T-SQL - это необходимо, чтобы оптимизатор применял хеш-соединение. Это простой способ принудительно использовать хеш-соединение в запросе с недооцененным количеством строк с целью продемонстрировать, что при этом произойдет. Я сопроводил код запроса комментариями, однако предупреждаю вас: очень важно, чтобы при выполнении этого фрагмента кода T-SQL вы не включали в код комментарии. Я обжегся на этом, когда впервые начал создавать свои демоверсии. Комментарии учитываются, когда речь идет об уникальной идентификации запроса, для которого существует кешированный план. Если при следующем выполнении запроса в нем не будет точно таких же комментариев, запросы не будут использованы повторно. В SSMS включите флаг Include Actual Execution Plan (Включить фактический план выполнения) (вы можете использовать сочетание клавиш Ctrl+M для его включения) перед выполнением запроса. О том, как включить этот флаг, рассказывается на следующей странице документации.Этот запрос должен выполняться не менее чем за 30 секунд; он должен вернуть около 66 тыс. строк (ваши результаты могут отличаться от тех, которые приводятся здесь). Используйте режим SSMS для просмотра плана выполнения запроса. План выполнения запроса должен выглядеть примерно так, как показано на рис. 2.
В этом плане есть несколько мест, на которые нужно обратить внимание. Если в SSMS вы наведете курсор мыши на оператор Table Scan (Сканирование таблицы), он должен выглядеть примерно так, как показано на рис. 3. Обратите внимание, что значение параметра Estimated Number of Rows
(Количество строк при оценке) сильно отличается от фактического числа строк, считанных при сканировании (т. е. при последовательном просмотре строк) таблицы.Рис. 3. Оценка и фактическое значение числа строк при сканировании таблицы Fact.OrderHistory
В рассмотренном нами случае таблица Fact.OrderHistory представляет собой входные данные сборки хеш-соединения. SQL Server будет запрашивать предоставление памяти для хеш-соединения на основании этих данных. В данном случае мы столкнемся с проблемой, поскольку предоставление памяти основано на оценке, составляющей всего 1000 строк. Перемещайте курсор мыши по строчкам данных о выполнении хеш-соединения, и вы обнаружите маленький значок предупреждения; обратите внимание на предупреждение об «утечке данных в tempdb», как показано на рис. 4.
Рис. 4. «Утечка данных в tempdb» в хеш-соединении
Обратите внимание на цифры, приведенные в предупреждении. 52 008 страниц (8 Кб на страницу) -это ~ 426 Мб данных, используемых при выполнении операций ввода/вывода для файлов tempdb. Размеры «утечки» действительно ужасны, поскольку это не те данные, которые размещаются на странице буферного пула, связанного с tempdb. Файлы данных tempdb становятся «файлом подкачки» при предоставлении памяти для хеш-соединений (это не страницы tempdb для хранения временных таблиц, и потому я часто называю tempdb «мусорной свалкой» SQL Server).
Совет. Хотите узнать, как работает хеш-соединение? Прочитайте эту старую, но классическую статью в блоге от одного из наших ведущих инженеров группы Query Processor, Крейга Фридмана (Craig Freedman).
Перемещаясь в левую часть плана выполнения запроса, наведите курсор на оператор
SELECT
. В этом операторе указаны сведения о количестве выделенной памяти согласно плану. На рис. 5 показано, что для выполнения этого запроса было запрошено ~ 1,4 Мб памяти.Рис. 5. В разделе с информацией об операторе SELECT показано запрошенное количество памяти
Запрашиваемого объема памяти 1,4 Мб недостаточно, чтобы разместить там все необходимые данные (в соответствии с информацией об «утечке данных в tempdb» необходимый объем памяти составляет ~ 400 Мб).
Еще одни интересные данные, представленные в плане выполнения запроса в виде XML-файла, находятся в разделе свойств плана. Чтобы просмотреть их, щелкните правой кнопкой мыши оператор SELECT и выберите Properties (Свойства). Раскройте раздел MemoryGrant, который будет выглядеть, как показано на рис. 6.
Рис. 6. Данные о выделении памяти приводятся в свойствах плана выполнения запроса
Наиболее важная информация в обратной связи по временно предоставляемому буферу памяти содержится в поле IsMemoryGrantFeedbackAdjusted. Значение NoFirstExecution означает, что это только первое выполнение запроса, поэтому никакой обратной связи не было получено.
Список возможных значений в этом поле приведен в документации по SQL Server (см. https://docs.microsoft.com/en-us/sql/relational-databases/ performance/intelligent-query-processing?view=sql-server-ver15#row-mode-memory- grant-feedback).
Поскольку обратная связь по временно предоставляемому буферу памяти включена, если тот же самый кешируемый запрос будет выполняться повторно, SQL Server адаптирует план выполнения запроса и изменит объем предоставляемой памяти с учетом недооценки, имевшей место в прошлый раз.
- Перейдем к шагу 4 нашего сценария и снова выполним тот же самый запрос.
ВАЖНО: не включайте комментарии в текст сценария при выполнении запроса. Комментарии учитываются при сравнении повторно выполняемого запроса с первоначальным запросом в кеше плана. Обязательно сохраните включенным флаг Include Actual Execution Plan (Включить фактический план выполнения) в SSMS.
-- Шаг 4. Давайте попробуем снова -- При выполнении запроса НЕ выделяейте эти комментарии! SELECT fo.[Order Key], fo.Description, si.[Lead Time Days] FROM Fact.OrderHistory AS fo INNER HASH JOIN Dimension.[Stock Item] AS si ON fo.[Stock Item Key] = si.[Stock Item Key] WHERE fo.[Lineage Key] = 9 AND si.[Lead Time Days] > 19 GO
Если в прошлый раз выполнение запроса занимало 30 или более секунд, то на этот раз запрос должен выполняться за 3 секунды или менее. Помните, концепция заключается в том, что план не меняется; поэтому, когда вы смотрите на фактический план выполнения запроса, он должен выглядеть так же, как и в прошлый раз, за исключением того, что отсутствует значок предупреждения в разделе Hash Match Join, а также нет предупреждения об «утечке данных в tempdb» . Перемещая курсор в раздел оператора SELECT, вы увидите, насколько в данном случае объем предоставленной памяти (Memory Grant) отличается от предыдущего случая, как показано на рис. 7.
Как мы видим, для корректного выделения памяти для размещения хеш-соединения на самом деле требуется ~ 625 Мб.
Щелкните правой кнопкой мыши оператор SELECT и выберите Properties (Свойства). Раскройте раздел MemoryGrantInfo, который теперь будет выглядеть, как показано на рис. 8. В этом разделе содержится обратная связь по временно предоставляемому буферу памяти.
Рис. 7. В разделе с информацией об операторе SELECT приведен скорректированный показатель объема предоставляемой памяти
Рис. 8. Обратная связь по временно предоставляемому буферу памяти после коррекции предоставляемого объема памяти
- Нам нужно убедиться, что данные статистики запроса вернулись в исходное состояние. Для этого нужно запустить код T-SQL шага 5 в сценарии T-SQL:
-- Шаг 5. Восстановление таблицы и кластеризованного индекса в -- исходное состояние. UPDATE STATISTICS Fact.OrderHistory WITH ROWCOUNT = 3702592; GO ALTER TABLE [Fact].[OrderHistory] DROP CONSTRAINT [PK_Fact_OrderHistory] GO ALTER TABLE [Fact].[OrderHistory] ADD CONSTRAINT [PK_Fact_OrderHistory] PRIMARY KEY NONCLUSTERED ( [Order Key] ASC, [Order Date Key] ASC ) GO
Выделение слишком большого объема памяти
Давайте рассмотрим пример, где объем предоставленной памяти слишком велик, гораздо больше того, который требуется в действительности . Как я уже упоминал ранее, если объем выделенной памяти чрезмерно велик, то, возможно, это не приведет ни к каким отрицательным последствиям, но в ряде случаев может привести к неожиданному дефициту памяти или ухудшению производительности.
Пример, иллюстрирующий выделение слишком большого объема памяти, немного сложнее; для него потребуется эмуляция одновременной работы нескольких пользователей. Поэтому для выполнения практических заданий этого примера вам понадобится бесплатная утилита для нагрузочного тестирования под названием ostress, которую можно загрузить, перейдя по ссылке. В настоящее время для работы с этой утилитой требуется клиентский компьютер с установленной ОС Windows.
Чтобы увидеть, как выделение слишком большого объема памяти может привести к неожиданному ухудшению производительности и появлению режима ожидания (wait_type) RESOURCE_SEMAPHORE
, выполните описанные ниже действия. Все используемые сценарии находятся в этом каталоге. Я создал сценарии, запускаемые из командной оболочки с использованием учетной записи администратора sa.
- Во-первых, нам необходимо настроить регулятор ресурсов (resource governor), чтобы использовать максимальный объем выделенной памяти для сервера, запустив исполняемый файл adjustrg.cmd (который запускает сценарий T-SQL adjustrg.sql). В этом сценарии используются следующие допущения: имя сервера, для которого выполняются настройки bwsql2019, поэтому вам будет нужно отредактировать сценарий, указав вместо bwsql2019 имя своего сервера. Я выполняю эту настройку, чтобы позволить SQL Server получить очень большой объем избыточной памяти, необходимый для данного примера.
ALTER WORKLOAD GROUP [default] WITH (REQUEST_MAX_MEMORY_GRANT_PERCENT = 50) GO ALTER RESOURCE GOVERNOR RECONFIGURE GO
- Теперь запустите исполняемый файл cmd (который выполняет сценарий T-SQL turn_off_mgf.sql).
-- Отключаем обратную связь по временно предоставляемому буферу памяти USE [WideWorldImportersDW] GO -- Шаг 2: Эмуляция устаревших данных статистики UPDATE STATISTICS Fact.OrderHistory WITH ROWCOUNT = 5000000000 GO ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE; GO ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_ FEEDBACK = OFF GO ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_ FEEDBACK = OFF GO
В этом примере я буду использовать прием, аналогичный предыдущему примеру с выделением слишком малого объема памяти, на этот раз изменяя данные статистики таким образом, чтобы число строк в статистике запроса намного превышало число строк в таблице.
Примечание. За прошедшие годы я видел несколько примеров, когда оценка кардинальности оказалась выше, чем реальное значение. Один из примеров такой ситуации - выполнение запросов к связанному серверу, когда отсутствует доступ к статистике для удаленного источника данных. В этих случаях оценка кардинальности может быть неточной и неоправданно завышенной.
- Теперь запустите исполняемый файл rowmode_mgf.cmd, который выполняет сценарий T-SQL rowmode_mgf.sql.
SELECT fo.[Order Key], fo.Description, si.[Lead Time Days] FROM Fact.OrderHistory AS fo INNER JOIN Dimension.[Stock Item] AS si ON fo.[Stock Item Key] = si.[Stock Item Key] WHERE fo.[Lineage Key] = 9 AND si.[Lead Time Days] > 19 ORDER BY fo.[Order Key], fo.Description, si.[Lead Time Days] OPTION (MAXDOP 1) GO
Этот запрос похож на пример для выделения слишком малого объема памяти, но он содержит дополнительный оператор
ORDER BY
для добавления сортировки.Командный файл, запускаемый из командной оболочки, будет использовать утилиту ostress для эмуляции выполнения этого запроса T-SQL десятью пользователями одновременно; запрос будет повторяться десять раз для каждого пользователя. Во время работы этого сценария используйте другой сеанс SQL для запуска сценария dm_ exec_requests.sql, чтобы узнать, с какими типами ожидания (wait_ type) могут столкнуться выполняемые пользователями запросы. Вы заметите значительное число элементов очереди ожидания освобождения ресурсов на
RESOURCE_SEMAPHORE
. Вы можете запускать этот файл несколько раз, пока работает сценарий ostress. То, что сценарий ostress выполняется долго, объясняется режимом ожидания освобождения ресурсов.Общее время выполнения сценария ostress должно составлять более 40 секунд. Результат выполнения сценария (после его успешного завершения) должен быть таким:
<datetime> [0x000046CC] OSTRESS exiting normally, elapsed time: 00:00:43.833 <datetime> [0x000046CC] RsFx I/O completion thread ended.
- Выполните один запрос, используя сценарий rowmode_mgf.sql, и просмотрите данные обратной связи по временно предоставляемому буферу памяти в SQL Server Management Studio. Используйте те же приемы, что и в предыдущем разделе этой статьи, чтобы просмотреть свойства оператора
SELECT
в плане. Раскройте раздел MemoryGrantInfo. Результаты должны выглядеть так, как показано на рис. 9.Ниже приведено описание ключевых параметров обратной связи. DesiredMemory - это идеальный объем предоставляемой памяти, основанный на оценке кардинальности. В данном случае это число составляет около 56 Гб. Это сумасшедшее количество памяти!
- GrantedMemory - мы не можем предоставить 56 Гб памяти для этого запроса, поэтому предоставляем только около 5 Гб. Это все еще значительный объем предоставляемой памяти.
- MaxUsedMemory - этот объем памяти фактически используется для предоставления памяти во время выполнения запроса. Как вы видите, он составляет всего 3 Мб. Это, безусловно, пример предоставления чрезмерно большого объема памяти по сравнению с необходимым объемом.
Рис. 9. Обратная связь по временно предоставляемому буферу памяти при чрезмерного большом объеме предоставляемой памяти
- Теперь давайте включим обратную связь по временно предоставляемому буферу памяти, запустив исполняемый файл turn_on_mgf.cmd (который выполняет сценарий T-SQL turn_on_mgf.sql).
- Давайте снова сэмулируем рабочую нагрузку, запустив исполняемый файл rowmode_mgf.cmd. Работа этого файла должна завершиться за время, вдвое меньшее предыдущего (обычно около 20 секунд). Если вы запустите сценарий dm_exec_requests.sql во время работы сценария ostress, то увидите кратковременное увеличение числа элементов очереди ожиданий
RESOURCE_SEMAPHORE
, а затем эта очередь исчезнет, потому что сработала обратная связь по временно предоставляемому буферу памяти, уменьшив размер предоставляемой памяти в соответствии с объемом памяти, необходимым для выполнения запроса.Совет. Попробуйте запустить исполняемый файл rowmode_mgf.cmd во второй раз. Будет ли он в этот раз работать быстрее? Да, сейчас он может работать немного быстрее. Это связано с тем, что при первом запуске rowmode_mgf.cmd первые выполнения запроса происходили очень быстро, и кешированный план не обновлялся при новом предоставлении памяти. Но по мере выполнения дальнейших запусков командного файла память каждый раз выделялась заново. Когда вы запустили rowmode_mgf.cmd во второй раз, во всех запросах использовалось новое выделение памяти.
- Если вы посмотрите на свойства оператора
SELECT
, относящиеся к временно предоставляемому буферу памяти для rowmode_mgf.sql, то увидите, что объемы предоставляемой памяти изменились, и их обновленные значения гораздо ближе к значению объема памяти, необходимого для выполнения запроса. - Восстановите состояние базы данных, данные статистики для таблиц и регулятора ресурсов, запустив исполняемые файлы adjustrgback. cmd и restore_orderhistory_state.cmd.
Примечание. Даже при использовании функции обратной связи в некоторых случаях объем фактически необходимой памяти может быть очень большим. Достаточно большим, чтобы пользователи, работающие одновременно, сталкивались с режимом ожидания
RESOURCE_SEMAPHORE
, что, в свою очередь, приведет к ситуации дефицита памяти в SQL Server. В этих случаях вы можете использовать регулятор ресурсов (resource governor), чтобы ограничить объем выделяемой памяти. Документация по данной теме находится по ссылке. В ней разъясняется, как изменить соответствующие параметры. В SQL Server 2019 это значение настраиваемого параметра теперь может быть числом с плавающей запятой, поэтому допустимы значения < 1 %. Это может быть важно для систем с большим объемом памяти. Кроме того, вы можете установить эти значения на уровне отдельного запроса. См. документацию.
Эта функция хорошо продумана и может реально помочь вам сэкономить время при настройке параметров SQL Server для рабочих нагрузок, требующих выделения памяти.
Существует несколько сценариев, когда обратная связь по временно предоставляемому буферу памяти не должна использоваться или же будет бесполезна:
- не обнаружено «утечки данных в tempdb» или используется 50 % выделенной памяти;
- имеют место флуктуации объема памяти, когда объем предоставляемой памяти постоянно то уменьшается, то увеличивается.
Отложенная компиляция табличных переменных
Если вы работаете в Microsoft уже 26 лет, то за это время вы, несомненно, встречались со многими людьми. Я видел очень много людей, которые умнее меня и, честно говоря, обладают лучшим характером, нежели я. Один из таких людей - Джек Ли (Jack Li). Джек много лет проработал в службе технической поддержки CSS в нашем офисе в Ирвинге (штат Техас). Несколько лет назад у Джека появилась возможность поработать в команде SQL Engineering - это случилось после того, как я присоединился к данной команде. Однажды он с присущей ему скромностью спросил меня, считаю ли я, что он пригоден для работы в этой команде. Я не колебался. Я сказал ему, что у него есть все необходимые навыки, чтобы стать первоклассным разработчиком, и, кроме того, он обладает уникальным и обширным опытом по улучшению производительности SQL Server. Несмотря на то что команда CSS потеряла одного из лучших ее членов, наша команда, безусловно, выиграла от того, что к ней присоединился Джек Ли.
Первым проектом, над которым Джек работал в своем новом статусе, было решение известной проблемы оценки кардинальности для табличных переменных. Когда используются табличные переменные, у нас наблюдается проблема, состоящая в том, что при оценке кардинальности оптимизатор SQL Server всегда добавляет в оценку одну строку, независимо от того, сколько строк заполняется в табличной переменной. Это происходит потому, что оптимизатор не обладает информацией о том, сколько строк на самом деле содержится в табличной переменной, поскольку они определяются и обычно заполняются как часть пакета или хранимой процедуры. Когда Джек работал в техподдержке, он писал об этой проблеме и предлагал решение с использованием флага трассировки. Описание предложенного Джеком решения можно найти по ссылке https://blogs.msdn.microsoft.com/psssql/2014/08/11/having-performance-issues-with-table-variables-sql-server- 2012-sp2-can-help/.
Таким образом, когда Джек присоединился к команде, он уже был глубоко погружен в эту проблему. У руководства группы Query Processor была идея, которую они хотели реализовать в SQL Server 2019 в рамках интеллектуальной обработки запросов, называемой отложенной компиляцией табличных переменных. Они прислушались к Джеку при реализации этой возможности.
Приведем цитату из документации по SQL Server по ссылке https:// docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query- processing?view=sql-server-ver15#table-variable-deferred-compilation: «Отложенная компиляция табличной переменной откладывает компиляцию оператора, который ссылается на табличную переменную, до первого фактического выполнения оператора. Отложенная компиляция в данном случае выполняется так же, как для временных таблиц. Это изменение приводит к тому, что используется фактическое количество строк, а не одна строка, как предполагалось первоначально».
Вы можете прочитать и просмотреть примеры того, как включить и отключить отложенную компиляцию табличных переменных, а также параметры базы данных и указания запросов, по ссылке.
Все примеры сценариев, приведенные в этом разделе, размещены в каталоге.
Давайте рассмотрим пример этой концепции, используя записную книжку T-SQL (T-SQL Notebook) (вы также можете просмотреть этот сценарий T-SQL, открыв файл iqp_tablevariable.sql).
- Откройте в Azure Data Studio записную книжку T-SQL с названием iqp_tablevariable.ipynb.
- Выполните каждый шаг в записной книжке T-SQL, чтобы сравнить производительность при использовании табличных переменных с отложенной компиляцией и без нее.
- Чтобы сравнить планы выполнения запросов для этих двух сценариев, мы можем использовать функцию под названием Query Store, которая впервые была представлена в SQL Server Возможно, вы этого не знали, но при восстановлении резервной копии WideWorldImportersDW в базе данных уже использовалось хранилище запросов Query Store.
- Вот как использовать Query Store для сравнения двух запросов: один с использованием отложенной компиляции табличной переменной, а другой - без нее.
- Откройте SSMS, подключитесь к SQL Server, на котором вы запустили примеры из записных книжек T-SQL, и найдите отчет Top Resource Consuming Queries (Самые ресурсоемкие запросы), как показано на рис. 10.
- В отчете, приведенном на рис. 10, показаны данные Query Store после выполнения предыдущего примера использования обратной связи по временно предоставляемому буферу памяти в режиме строк и примеры табличных переменных из этого раздела. Ваше окно программного интерфейса может немного отличаться от приведенного на рисунке в зависимости от того, каким инструментом вы пользуетесь и на каких данных выполняется запущенный вами запрос. Каждый столбец диаграммы представляет уникальный запрос, поэтому вам нужно найти запрос, в котором используется табличная переменная. Щелкните мышью каждую строку - ниже будет отображен текст запроса. Если вы просмотрите запрос в хранимой процедуре из этого примера в записной книжке T-SQL, то обнаружите, что этот запрос содержит следующий код:
SELECT top 10 oh.[Order Key], oh.[Order Date Key],oh.[Unit Price], o.Quantity FROM Fact.OrderHistoryExtended AS oh INNER JOIN @Order AS o ON o.[Order Key] = oh.[Order Key] WHERE oh.[Unit Price] > 0.10 ORDER BY oh.[Unit Price] DESC
Рис. 10. Отчет Top Resource Consuming Queries (Самые ресурсоемкие запросы) в Query Store
- Поочередно щелкайте мышью каждый столбец на диаграмме, пока не увидите этот запрос. Обратите внимание на две точки справа, которые представляют два плана выполнения для данного запроса. После завершения выполнения отчет должен выглядеть примерно так, как показано на рис. 11.
Чем «выше» расположена точка на диаграмме, тем больше средний период времени для данного плана выполнения запроса. Если щелкнуть каждую точку, вы увидите, как изменяется план выполнения запроса в нижнем окне.
Рис. 11. Планы выполнения запросов для использования табличных переменных
- Наведите курсор на верхнюю точку, чтобы увидеть данные статистики для плана выполнения запроса. Просмотрите данные статистики, например среднюю продолжительность, как показано на рис. 12.
Рис. 12. Средний период времени для более медленного плана выполнения запроса
После того как вы щелкнули мышью точку и просмотрели план выполнения запроса в нижней области окна, наведите указатель мыши на оператор сканирования таблицы (Table Scan). Обратите внимание на оценку числа обрабатываемых строк, равную 1, как показано на рис. 13.
Рис. 13. Оценка в одну строку для табличной переменной
Обратите внимание на операцию соединения табличной переменной и таблицы OrderHistoryExtended. Здесь используется алгоритм соединения с применением вложенных циклов (Nested Loops Join). Это вполне разумный выбор для оптимизатора, поскольку он предполагает, что табличная переменная содержит только одну строку. Проблема в том, что в данном случае табличная переменная содержит около 3 млн строк! Использование подобного типа соединения (с вложенными циклами) для такого числа строк будет очень дорогостоящей операцией и в данном случае не имеет смысла.
- Теперь щелкните «нижнюю» точку в окне с планами выполнения запросов. Наведите указатель мыши на точку, чтобы увидеть среднюю продолжительность запроса. Это должно выглядеть примерно так, как показано на рис. 14.
Рис. 14. Средняя продолжительность более быстрого плана выполнения запроса
Теперь просмотрите план выполнения запроса. Наведите указатель мыши на оператор сканирования таблицы (Table Scan). Обратите внимание, что оценки теперь точны, и, поскольку требуется выполнить сканирование таблицы, использование пакетного режима вполне оправдано. Это пример одновременного использования нескольких функций интеллектуальной обработки запросов. Информация, выводимая для этого оператора, должна выглядеть, как показано на рис. 15.
Рис. 15. Лучшая оценка при использовании табличной переменной
Теперь посмотрите на соединение табличной переменной и таблицы OrderHistoryExtended. Для этой цели используется хеш-соединение, а также выполняется сканирование таблицы OrderHistoryExtended.
Пакетный режим для хранилища строк
В SQL Server 2012 была добавлена отличная (какое преуменьшение!) возможность, называемая столбцовыми индексами. Это было сделано в рамках проекта Apollo (см. оригинальный блог по ссылке). При разработке этой новой функции был усовершенствован обработчик запросов - в нем появилась возможность выполнять обработку строк в пакетном режиме с использованием столбцовых индексов. До этих пор операторы, включаемые в план выполнения запросов, такие как сканирование таблицы (scan), выполняли операции и обрабатывали данные построчно, выбирая по одной строке таблицы целиком. Пакетный режим представляет новую парадигму обработки данных, позволяющую операторам обрабатывать пакеты строк, упорядоченных по столбцам и содержащих векторы для идентификации подходящих строк. Эта концепция очень хорошо согласуется со столбцовыми индексами, которые упорядочены по столбцам, а не по строкам.
Хотя столбцовые индексы очень полезны для выполнения аналитических запросов, для которых характерны сканирование и обработка большого количества строк, все же столбцовые индексы могут не соответствовать вашим потребностям в обработке данных; также могут иметься ограничения, препятствующие их использованию. Кроме того, у вас могут иметься запросы, соответствующие сценарию «аналитическая нагрузка». Другими словами, ваши потребности могут заключаться не в выполнении запросов к одной или нескольким строкам (что многие считают стандартным «сценарием OLTP»). Любая таблица или индекс, которые не организованы с помощью столбцового индекса, называется хранилищем строк.
В SQL Server 2019 обработчик запросов может автоматически определять, соответствует ли ваш запрос пакетному режиму для хранилища строк. Использование пакетного режима, опять же, может не иметь смысла для всех запросов, поэтому для его применения важно понимать и использовать некоторые основные принципы. Например, ваш запрос должен обрабатывать большое количество строк и включать операции, которые требуют агрегации данных (такие как count(*)
или sum()
, а также соединение или сортировку). Другими словами, пакетную обработку имеет смысл использовать, когда существует поток данных между несколькими операторами над большим числом строк для выполнения запроса. Насколько большим? В документации не приводится конкретная цифра (потому что в будущем она может измениться), но пороговое значение обычно составляет 128 тыс. строк.
Подробная информация о пакетном режиме для хранилища строк, в том числе о включении и отключении этой возможности, находится по адресу. Эта документация содержит сведения об истории возникновения данной возможности, а также о том, при каких рабочих нагрузках она принесет наибольшее преимущество, и информацию о существующих ограничениях.
Совет. Вы действительно хотите углубиться в эту тему? Тогда я рекомендую вам статью в блоге эксперта SQL-сообщества Димы Пилюгина, который определил «роковой» предел 128 тыс. Этот блог расположен по адресу.
Мы будем использовать базу данных WideWorldImportersDW, которую восстановили и расширили в начале этой статьи. Давайте рассмотрим пример, в котором пакетный режим для строкового хранилища может ускорить выполнение запросов. В каталоге размещены все примеры сценариев.
Вы можете выполнить следующие запросы, используя сценарий iqp_ batchmoderow.sql или записную книжку T-SQL iqp_batchmoderow.ipynb.
Какой бы способ вы ни выбрали, давайте продвигаться вперед шаг за шагом. В этом разделе я рекомендую вам использовать записную книжку T-SQL, выполняя пример сценария. Вы можете также выполнить сценарий iqp_batchmoderow.sql, используя любой инструмент SQL, однако вам нужно будет проанализировать планы выполнения запросов в графическом инструменте, таком как SSMS или Azure Data Studio (или же иметь возможность прочитать и подробно разобрать XML-план).
Откройте Azure Data Studio, подключенную к вашему экземпляру SQL Server 2019, и затем откройте записную книжку iqp_batchmoderow.ipynb.
Одним из удобств записных книжек является то, что документация для каждого шага и ячеек находится в самой записной книжке. Записные книжки, сохраненные в репозитории GitHub для этой статьи, уже содержат все ответы, так что вы знаете, чего ожидать!
Я даже визуально проиллюстрировал различия в планах выполнения запросов, используя Azure Data Studio и приводя ожидаемые результаты.
Прочитайте текст и выполните каждый шаг из записной книжки. Вы увидите, что использование пакетного режима для хранилища строк может дать значительный прирост производительности, особенно при работе с таблицами, содержащими большие наборы данных. Кроме того, пакетный режим теперь работает как для столбцового хранилища данных (реализовано в SQL Server 2017), так и для хранилища строк, поэтому вам не нужно об этом беспокоиться. Обработчик запросов знает, когда его использовать и как он может повысить производительность вашего запроса.
Чтобы вы могли выполнить самопроверку, на рис. 16 показано, как выглядит верхняя область записной книжки.
Рис. 16. Записная книжка T-SQL для демонстрации пакетного режима обработки для хранилища строк
Встраивание скалярных UDF
В SQL Server уже давно существует такая концепция, как пользовательская функция (user-defined function, UDF). Концепция заключается в том, что вы размещаете код T-SQL внутри оператора FUNCTION
, который принимает на вход один или несколько параметров, и функция возвращает результирующее значение. Затем вы можете использовать функцию в любом операторе SELECT
T-SQL. Существуют и иные популярные способы повторного использования кода, например хранимые процедуры, но функция обладает одним преимуществом: она может быть частью оператора SELECT
.
Примечание. Есть и другие способы использования пользовательских функций, о которых вы можете узнать больше, перейдя по ссылке.
Существует два типа пользовательских функций:
- скаляр, который возвращает одно значение;
- табличное значение, которое возвращает результирующий набор данных в виде таблицы (тип
TABLE
).
Несмотря на популярность и преимущества UDF, их использование может привести к проблемам с производительностью из-за существующих ограничений. Эти ограничения связаны с тем, как UDF компилируются и интегрируются в общий план выполнения запросов. Например, всякий раз, когда скалярная UDF используется для того, чтобы вернуть значение как часть списка столбцов, каждая строка таблицы, к которой выполняется запрос, используется в UDF по одной строке за один раз. Существуют и другие ограничения, связанные с тем, как обработчик запросов обрабатывает пользовательские функции, что в некоторых ситуациях просто делает их весьма неэффективными с точки зрения производительности.
Теперь перейдем к встраиванию скалярных UDF. Обработчик запросов может взять код UDF (UDF может состоять из нескольких операторов T-SQL) и интегрировать эти операторы в общий запрос, отсюда и возник термин «встраивание».
Вы можете прочитать, как включить встраивание скалярных UDF, используя параметр dbcompat
, в документации, размещенной по ссылке.
О том, как отключить и включить встраивание скалярных UDF без изменения параметра dbcompat
, рассказывается в документации, размещенной по ссылке.
Как и в любом примере интеллектуальной обработки запросов, для понимания встраивания скалярных UDF лучше всего рассмотреть пример. В каталоге scalarinlineudf размещены все сценарии, которые приводятся здесь в качестве примеров.
Аналогично другим примерам из этой статьи, у вас есть два способа прохождения сценариев, иллюстрирующих встраивание скалярных UDF. Вы можете использовать записную книжку T-SQL iqp_scalarudfinlining.pynb или набор сценариев T-SQL.
Примечание. В основу приведенного мной примера были положены примеры кода, приведенные в следующей публикации в блоге Microsoft; в этом блоге также есть некоторые важные и интересные подробности о предыдущих ограничениях скалярных UDF и о том, как интеллектуальная обработка запросов позволила значительно повысить производительность.
Для выполнения примеров из этого раздела мы будем использовать сценарии T-SQL и проверку фактического плана выполнения запроса в SSMS.
- Откройте сценарий T-SQL get_customer_spend.sql.
Код этого сценария выглядит так:
USE WideWorldImportersDW GO SELECT c.[Customer Key], SUM(oh.[Total Including Tax]) as total_spend FROM [Fact].[OrderHistory] oh JOIN [Dimension].[Customer] c ON oh.[Customer Key] = c.[Customer Key] GROUP BY c.[Customer Key] ORDER BY total_spend DESC GO
Этот сценарий определяет суммартные затраты на приобретение продукции на одного клиента, основываясь на данных из таблицы OrderHistory. Посмотрев на полученные результаты, вы увидите, что диапазон затрат клиентов составляет от 2 до 7 млн. Согласно условиям поставленной задачи, мы должны создать пользовательскую функцию, которая будет принимать в качестве входных данных идентификатор клиента и классифицировать клиента на основании его суммарных расходов на приобретение продукции.
Клиенты, суммарные расходы которых составляют 3M, будут относиться к категории «ПРОСТЫЕ». Клиенты, потратившие от 3 до 4,5 млн, будут относиться к категории «ЗОЛОТЫЕ». Любой, кто потратит сумму более 4,5 млн, будет считаться «ПЛАТИНОВЫМ» клиентом. Преимущество использования функции в данном случае заключается в том, что мы можем изменять правила для определения категории клиента («ПРОСТОЙ», «ЗОЛОТОЙ» и «ПЛАТИНОВЫЙ»), не оказывая влияния на весь остальной код, использующий эту функцию.
- Откройте сценарий T-SQL iqp_scalarudfinlining.sql и выполните все шаги в соответствии с комментариями, приведенными в сценарии.
- Выполните фрагмент сценария шаг 1, который создаст скалярную UDF:
-- Шаг 1. Создайте новую функцию, чтобы получить категорию -- клиентов на основе их суммарных расходов на заказы USE WideWorldImportersDW GO CREATE OR ALTER FUNCTION [Dimension].[customer_category] (@CustomerKey INT) RETURNS CHAR(10) AS BEGIN DECLARE @total_amount DECIMAL(18,2) DECLARE @category CHAR(10) SELECT @total_amount = SUM([Total Including Tax]) FROM [Fact].[OrderHistory] WHERE [Customer Key] = @CustomerKey IF @total_amount <= 3000000 SET @category = 'REGULAR' ELSE IF @total_amount < 4500000 SET @category = 'GOLD' ELSE SET @category = 'PLATINUM' RETURN @category END GO
- Задайте нужное значение переменной dbcompat, очистите кеш процедур и «разогрейте» кеш буферного пула, выполнив шаг 2.
-- Шаг 2. Установите для базы данных значение db compat 150, -- очистите кеш процедур от предыдущих выполнений и добейтесь того, -- чтобы сравнение было корректным, «разогрев» кеш ALTER DATABASE WideWorldImportersDW SET COMPATIBILITY_LEVEL = 150 GO ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE; GO SELECT COUNT(*) FROM [Fact].[OrderHistory] GO
- Давайте запустим запрос с использованием UDF, но применим указание запроса, чтобы временно отключить встраивание скалярной UDF. Включите фактический план выполнения (Actual Execution Plan) в SSMS и выполните шаг 3 сценария.
-- Шаг 3. Запустите запрос, но отключите использование встраивания -- скалярной функции, используя указание запроса. SELECT [Customer Key], [Customer], [Dimension].[customer_category] ([Customer Key]) AS [Discount Price] FROM [Dimension].[Customer] ORDER BY [Customer Key] OPTION (USE HINT(‘DISABLE_TSQL_SCALAR_UDF_INLINING')) GO
Время выполнения запроса составляет не менее 30 секунд. Фактический план выполнения запроса должен выглядеть примерно так, как показано на рис. 17.
Если вы наведете указатель мыши на каждый оператор, то увидите, что он затрагивает 403 строки. Число строк не так уж велико; так почему же выполнение запроса занимает так много времени? Это происходит потому, что вы не видите, что скалярная функция обращается к таблице OrderHistory, которая содержит более 3 млн строк; для каждой строки в таблице Dimension.Customer она обращается ко всему множеству строк в таблице OrderHistory (а их более 3 млн). Это крайне неэффективно.
Рис. 17. План выполнения запроса для отключенной возможности встраивания скалярной UDF
- Запустите шаг 4 сценария, который будет выполнять тот же самый запрос, не используя указание, тем самым включив встраивание скалярной UDF.
-- Шаг 4: Запустите запрос снова, но не используйте указание SELECT [Customer Key], [Customer], [Dimension].[customer_category] ([Customer Key]) AS [Discount Price] FROM [Dimension].[Customer] ORDER BY [Customer Key] GO
Запрос должен выполняться значительно быстрее. Если вы посмотрите на фактический план выполнения, то увидите, что в плане представлены операторы, необходимые для выполнения функции, а также новые операторы, оптимизирующие запрос к таблице OrderHistory, выполняющийся в функции. План будет выглядеть примерно так, как показано на рис. 18.
Рис. 18. План выполнения встраиваемой скалярной UDF
Теперь, когда вы смогли убедиться в мощи встраивания скалярных UDF, вы должны быть более мотивированы использовать скалярные UDF.
Узнать больше о встраивании скалярных UDF, в том числе обо всех связанных с ними требованиях и ограничениях, можно по ссылке.
Функция приблизительного подсчета числа уникальных значений Approximate Count Distinct
Есть сценарии, в которых вам нужно подсчитать количество строк в любой таблице. Это сделать легко. Просто используйте оператор SELECT COUNT(*) FROM <table>
- и вы получите готовый ответ. Но также иногда бывает необходимо знать количество различных значений в определенном столбце по всем строкам таблицы. Эта задача не намного сложнее. Просто используйте SELECT COUNT(DISTINCT <col>) FROM <table>
. Это кажется достаточно простым. Единственная проблема заключается в том, как должен работать обработчик запросов, чтобы найти все различные значения.
В SQL Server для этого часто используется оператор Hash Match. Этот оператор похож на Hash Join в том, что «хеш-таблица» используется для построения списка всех различных значений, чтобы дальше использовать этот список для подсчета. Как вы помните, ранее в этой статье рассказывалось, что для Hash Join необходимо предоставить память; таким образом, при использовании этого оператора могут возникнуть все проблемы, характерные для предоставления памяти. Кроме того, использование хеш-таблицы для подсчета всех различных значений - очень ресурсоемкая операция, и для нее может потребоваться много вычислительных ресурсов.
Есть ли лучший способ решения этой задачи? Да, есть другой способ, который может работать быстрее, при этом давая чуть менее точный ответ. Это новая функция T-SQL APPROX_COUNT_DISTINCT(). Это встроенная функция, подсчитывающая различные значения в столбце на основе выборочного приближения. Это не расширение функции COUNT(). Это совершенно новая функция, поэтому для ее использования не требуется устанавливать значение параметра dbcompat = 150. Данная функция использует концепцию HyperLogLog (вы можете прочитать больше об этой концепции, перейдя по ссылке). Использование приблизительного подсчета числа уникальных значений дает вероятность ошибки 2 % с вероятностью 97 %. Это означает, что если вас устроит ответ, который будет весьма близок к реальности, вы можете использовать эту функцию.
Давайте рассмотрим пример использования данной функции и сравним ее с применением COUNT
и DISTINCT
.
Все примеры сценариев размещены в каталоге approxcount. Вы можете просмотреть примеры с помощью записной книжки T-SQL iqp_approxcountdistinct.ipynb. Я также создал сценарий T-SQL с названием iqp_approxcountdistinct.sql. Давайте воспользуемся сценарием T-SQL и пройдемся по его шагам, изучая различия между запросами и их планами выполнения.
- Откройте сценарий iqp_approxcountdistinct.sql в SSMS.
- Выполните операторы шага 1. При этом выполняется очистка кеша процедур, изменение значения уровня dbcompat (
dbcompat = 130
) и «разогревается» буферный пул (это «честный бой»).-- Шаг 1. Очистите кеш, установите значение dbcompat, равное 130, -- чтобы доказать, что сценарий работает, и «разогрейте» кеш USE WideWorldImportersDW GO ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE GO ALTER DATABASE WideWorldImportersDW SET COMPATIBILITY_LEVEL = 130 GO SELECT COUNT(*) FROM Fact.OrderHistoryExtended GO
Вы можете удивиться, почему я установил значение переменной dbcompat, равное 130. Это сделано, чтобы показать вам, что вам не нужно использовать значение
dbcompat = 150
для применения этой новой возможности. Это связано с тем, что новая функция T-SQLAPPROX_COUNT_DISTINCT()
просто поставляется с ядром SQL Server 2019, но для нее не требуется устанавливать значениеdbcompat = 150
, как для других функций интеллектуальной обработки запросов. - Включите фактический план выполнения запроса (Actual Execution Plan) в SSMS и выполните шаг 2:
-- Шаг 2. Сначала используйте COUNT и DISTINCT SELECT COUNT(DISTINCT [WWI Order ID]) FROM [Fact].[OrderHistoryExtended] GO
Это займет не так много времени - в зависимости от мощности вашего компьютера, возможно, от 4 до 5 секунд. В результате у вас должно получиться число 29 620 736. Пять секунд для подсчета различных значений - не так уж и плохо. Однако что произойдет, если в этой таблице будет 100 млн строк или больше? Это не так уж необычно для больших баз данных.
Если вы посмотрите на план выполнения запроса, то увидите нечто похожее на то, что показано на рис. 19.
Рис. 19. План выполнения запроса для
COUNT
иDISTINCT
Обратите внимание на оператор
Hash Match
. Если вы наведете указатель мыши на этот оператор, то увидите, что он использует построчный режим (Row Mode) и должен обработать все 29 млн строк в хеш-операторе. - Теперь выполните шаг 3 сценария следующим образом:
-- Шаг 3. Используйте новую функцию APPROX_COUNT_DISTINCT для -- сравнения вычисленных значений и производительность запроса -- Разность между результатами приблизительного и точного подсчета -- не должна превышать 2 % от точного значения (с вероятностью 97 %) SELECT APPROX_COUNT_DISTINCT([WWI Order ID]) FROM [Fact].[OrderHistoryExtended] GO
На этот раз выполнение запроса должно занять всего секунду или две - это примерно на 50 % быстрее, чем раньше. Опять же, это может быть важно для очень больших наборов данных.
Если вы посмотрите на план выполнения запроса, он будет выглядеть аналогично, но с меньшим числом операторов, как показано на рис. 20.
Рис. 20. План выполнения запроса для
APPROX_COUNT_DISTINCT
Обратите внимание, что у оператора
Hash Match
отсутствует «толстая линия» в качестве иллюстрации выходных данных, потому что операция приближения применяется именно в этом операторе, что дает только одну строку для остальной части плана.Как вы можете заметить, использование приблизительного подсчета числа уникальных значений может улучшить производительность, если вам нужно лишь «достаточно точное» значение.
Восстановите предыдущее значение уровня совместимости базы данных (
dbcompat
), равное 150, выполнив шаг 4 сценария.-- Шаг 4. Восстановление предыдущего значения уровня совместимости -- базы данных. ALTER DATABASE WideWorldImportersDW SET COMPATIBILITY_LEVEL = 150 GO
Подробнее о функции APPROX_COUNT_DISTINCT
можно прочитать по ссылке.
Интеллектуальная обработка запросов - это более интеллектуальный обработчик запросов, отвечающий вашим потребностям и избавляющий от необходимости внесения серьезных изменений в имеющиеся приложения. Большая часть его функциональности доступна, если вы просто измените знчение уровня совместимости вашей базы данных, установив для него значение 150.
Я с нетерпением жду появления новых возможностей в будущем, поскольку обработчик запросов расширяет поддержку сценариев, включая новые сценарии, в основу которых положены ваши отзывы.
Упрощенное профилирование запросов
Прежде чем я присоединился к команде разработчиков SQL Server в 2016 году, я работал в технической поддержке, решая самые сложные проблемы, с которыми когда-либо сталкивались наши клиенты. И проблемы с производительностью оказывались самыми трудными не только для меня, но и для всей команды CSS. Все проблемы, касающиеся производительности SQL Server, весьма сложны - в них изначально много неясного, они критичны по времени, и редко когда у вас под рукой есть вся нужная информация, чтобы их решить.
В SQL Server существуют великолепные средства диагностики проблем с производительностью, в число которых входят динамические административные представления (Dynamic Management Views, DMVs) и расширенные события (Extended Events). Мы создали DMV как механизм, позволяющий видеть, что именно выполняет SQL Server в любой момент времени. Это отличный способ узнать об активных подключениях и о том, какие запросы они выполняют. Однако часто, чтобы решить сложную проблему с производительностью, информации об активных подключениях и выполняемых ими запросах недостаточно - вам нужна подробная информация из плана выполнения запроса.
Итак, разрыв углублялся. Вы можете видеть, какой запрос выполняется, но вы не можете углубиться в план выполняемого запроса. Кроме того, если вам необходимы данные из плана выполнения для уже завершенного запроса, вам необходимо использовать то, что может оказаться сложной диагностикой, - расширенные события (Extended Events). Или же вам нужно найти конкретный запрос и запустить его в автономном режиме (то есть вне приложения), используя отдельный инструмент, при этом включив диагностику плана выполнения запроса, чтобы получить всю необходимую информацию.
Когда я присоединился к команде инженеров, работающих над выпуском новых версий SQL Server, то обнаружил, что известная команда проекта Tiger ведет работу над решением подобных проблем. Педро Лопес (Pedro Lopes), Алексей Эксаревский и Джей Чоу (Jay Choe) уже работали над инфраструктурой профилирования запросов. Если вы спросите любого разработчика о том, как выполняется трассировка кода, он будет использовать термин профилирование. Итак, как мы профилируем запрос в SQL Server? Обычно это сводится к получению информации о плане выполнения запроса. Необходимо получить информацию о плане во время выполнения запроса и получить фактический план выполнения запроса после его завершения.
Эта команда уже создала концепцию статистики запросов в реальном времени (вы можете получить дополнительную информацию на данную тему по ссылке). Было очевидно, что команда могла сделать больше. Как говорит Алексей: «Я хотел реализовать эту функцию в продукте еще в 2009 году... я потратил столько времени на разглядывание планов выполнения запросов и хотел, чтобы они ожили, чтобы легче было видеть, что происходит. Итак, идея живой статистики запросов. Эти два слова прекрасно дополняют друг друга, хотя, конечно, упрощенное профилирование позволяет сделать гораздо больше».
На самом деле команда создала инфраструктуру профиля статистики выполнения запросов, или стандартное профилирование. Эта возможность выдает вам фактическую статистику плана выполнения для каждого оператора по строкам, ЦП и вводу-выводу. Это ключевая информация для профилирования запроса; однако есть один нюанс. Вы должны включить профилирование перед выполнением запроса либо включить расширенные события для всех запросов, что, в свою очередь, может повлиять на производительность. Дополнительная информация о стандартном профилировании доступна по ссылке.
Я люблю работать с такими коллегами, как Педро, Алексей и Джей. Они всегда спрашивают: «Можем ли мы улучшить эту функцию?» И конечно же, все они очень умны. По своему опыту они знают, как сложно и неудобно использовать стандартное профилирование. Они создали инфраструктуру упрощенного профилирования статистики выполнения запросов, или упрощенное профилирование. Концепция упрощенного профилирования состоит в том, чтобы получить данные профилирования для запросов без дополнительных затрат, которые непременно возникают при стандартном профилировании. Чтобы «облегчить» и «упростить» профилирование, нам пришлось исключить из него статистику ЦП; однако при этом вы все равно будете получать данные статистики по количеству строк «на оператор» и статистику ввода-вывода. Дополнительная информация об упрощенном профилировании доступна по ссылке.
Это замечательно, но... нам все равно придется включить упрощенное профилирование, чтобы оно заработало. Как узнать, когда нам следует включать упрощенное профилирование? Ну, большинство пользователей SQL Server этого не знает. Никто этого не делает. Правильный ответ - просто запустить упрощенное профилирование по умолчанию. И это то, что можно сделать в SQL Server 2019. Педро называет это «инсайты относительно производительности в любое время и в любом месте». Есть ли в этом какие-то нюансы? Да. Вы получаете информацию о количестве строк только для активно выполняющихся запросов, но часто этого достаточно, чтобы помочь разобраться с проблемами производительности. Однако здесь есть и небольшой бонус. Мы добавили в упрощенное профилирование возможность получить последний фактический план выполнения для большинства кешированных запросов.
Давайте рассмотрим два сценария, чтобы вы могли понять преимущества использования упрощенного профилирования запросов по умолчанию в SQL Server 2019.
Подготовительные шаги для использования примеров, иллюстрирующих упрощенное профилирование запросов
Итак, во-первых, вам нужно выполнить некоторые настройки, чтобы использовать примеры для двух сценариев. Для примеров в этой статье вы будете использовать базу данных WideWorldImporters (подробную информацию об этой базе данных и ее схеме можно получить по ссылке).
Приведенные здесь примеры будут работать на SQL Server 2019, установленном на Windows, Linux, а также в контейнерах. Учитывая большой размер набора данных, SQL Server потребуется как минимум 12 Гб ОЗУ, чтобы вы смогли заметить различия в производительности. Кроме того, в некоторых примерах запросов используется параллельная обработка, поэтому для полной демонстрации возможностей лучше установить SQL Server в многопроцессорной системе.
Все сценарии, применяемые в этой статье, можно найти в репозитории GitHub в каталоге lwp.
Чтобы использовать на практике примеры из этой статьи, выполните следующие действия:
- Загрузите резервную копию базы данных WideWorldImporters, доступную по ссылке.
- Восстановите эту базу данных на вашем экземпляре SQL Server Вы можете использовать для этого готовый сценарий restorewwidw. sql. Возможно, вам придется изменить путь к каталогу, где находятся ваша резервная копия, и путь к месту для восстановления файлов базы данных.
- Для запуска некоторых примеров вам понадобятся таблицы большего размера, чем те, которые добавлены по умолчанию в WideWorldImporters. Поэтому запустите сценарий extendwwi.sql, чтобы создать таблицы большего размера. Расширение этой базы данных увеличит ее размер, включая журнал транзакций, примерно до 5 Гб. Одна из таблиц называется Sales.InvoiceLinesExtended. Взяв за основу таблицу InvoiceLines, мы намного увеличим ее размер и не будем использовать столбцовый индекс.
Нужно ли мне прерывать активный запрос?
Рассмотрим следующий сценарий. Вам говорят, что на SQL Server запущен запрос, который занимает много ресурсов ЦП на сервере. Вы используете DMV, например sys.dm_exec_requests, чтобы идентифицировать запрос и пользователя. Пользователь - ваш вице-президент, которому необходимо получить отчет, и при выполнении этого запроса используется кешированный план. Вы используете стандартные DMV, sys.dm_exec_ requests и sys.dm_exec_query_stats, чтобы узнать, какой запрос выполняется. Как вы узнаете, завершится ли этот запрос в ближайшее время или же его следует прервать и исправить?
Давайте рассмотрим следующий пример и узнаем, как включенное упрощенное профилирование запросов может помочь вам найти ответ.
Вы можете запускать приведенные ниже сценарии T-SQL, применяя любой инструмент, который сможет подключиться к SQL Server, но лучше всего использовать SQL Server Management Studio (SSMS), чтобы увидеть все подробности.
- Откройте сценарий T-SQL mysmartquery.sql (возможно, этот сценарий не столь «умен», как его название) и запустите выполнение пакета.
- В новом соединении откройте сценарий T-SQL show_active_queries. sql.
- Запустите пакет из шага 1 сценария:
-- Шаг 1. Показывать только источники запросов с активными запросами, -- кроме данного запроса SELECT er.session_id, er.command, er.status, er.wait_type, er.cpu_time, er.logical_reads, eqsx.query_plan, t.text FROM sys.dm_exec_requests er CROSS APPLY sys.dm_exec_query_statistics_xml(er.session_id) eqsx CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) t WHERE er.session_id <> @@SPID GO
Этот код находит любые активные запросы (кроме текущего соединения). Если вы несколько раз повторно выполните данный запрос, то увидите, что значения cpu и
logic_reads
будут увеличиваться, и значение переменнойwait_type
будетwait_type = ASYNC_NETWORK_ IO
. Такая картина указывает на две вещи:- запрос отнимает большое количество ресурсов процессора и, вероятно, выполняет сканирование большой таблицы (значение
logical_reads
велико и продолжает увеличиваться); - большой объем данных (результатов) отправляется обратно клиенту (т. е. имеет место ожидание
ASYNC_NETWORK_IO
).
По моему опыту, это не «хороший» запрос, а тот, который имеет «шансы на улучшение». Но вопрос в том, стоит ли прерывать его сейчас или он уже «почти выполнен».
- запрос отнимает большое количество ресурсов процессора и, вероятно, выполняет сканирование большой таблицы (значение
- Что было бы полезно узнать, когда запрос активен, - так это увидеть ход выполнения операторов из плана выполнения запроса, например статистики активных запросов (Live Query Statistics). Запустите шаг 2 сценария:
-- Шаг 2. Как выглядит профиль плана выполнения для активного запроса SELECT session_id, physical_operator_name, node_id, thread_id, row_count, estimate_row_count FROM sys.dm_exec_query_profiles WHERE session_id <> @@SPID ORDER BY session_id, node_id DESC GO
Результаты должны выглядеть примерно так, как показано на рис. 21.
Рис. 21. Профиль плана выполнения для активного запроса
Заметьте, что значение
estimate_row_count
для операторов Nested Loops и Table Spool очень велико. И обратите внимание, чтоrow_count
(это количество строк, обрабатываемых в данный момент) намного меньше этого оценочного значения. Может быть, оценка является неточной; однако если она верна, этот запрос еще далек от завершения. Запустите этот запрос еще раз, чтобы увидеть, как изменяется значениеrow_count
для этих операторов.Примечание. Когда в SQL Server 2019 по умолчанию включено упрощенное профилирование запросов, то единственный параметр статистики, который собирается и сохраняется, - это
row_count
. Сбор данных, таких как загрузка процессора и объем ввода-вывода, включенный по умолчанию, может оказаться весьма дорогостоящим. Вы можете собрать эти данные, используя стандартное профилирование. - Давайте посмотрим на сам план выполнения запроса. Это предполагаемый план, но он может дать представление об этих больших оценочных числах
row_count
. Выполните шаг 3 сценария:-- Шаг 3. Вернитесь и просмотрите план выполнения запроса и текст -- запроса для лучшего понимания SELECT er.session_id, er.command, er.status, er.wait_type, er.cpu_time, er.logical_reads, eqsx.query_plan, t.text FROM sys.dm_exec_requests er CROSS APPLY sys.dm_exec_query_statistics_xml(er.session_id) eqsx CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) t WHERE er.session_id <> @@SPID GO
В SSMS щелкните значение
query_plan
, после чего должно открыться новое окно с визуальным планом выполнения запроса.План выполнения должен выглядеть так, как показано на рис. 22.
Рис. 22. План выполнения для активного запроса
Обратите внимание на значок с символом X на операторе соединения с использованием вложенных циклов (Nested Loops Join). Если вы наведете указатель мыши на оператор Nested Loops Join, то отображаемая информация будет выглядеть, как показано на рис. 23.
Рис. 23. Предупреждение, выведенное для оператора Nested Loops Join
Что означает текст «No Join Predicate» (отсутствует условие отбора для соединения)? Это означает, что существует серьезная проблема с оператором
JOIN
в запросе. Это означает, что соединение на основе равенства («equi» join) действительно не работает.В результатах, возвращенных после выполнения шага 3, посмотрите на значение в текстовом столбце диагностики. Это выглядит так:
SELECT si.CustomerID, sil.InvoiceID, sil.LineProfit FROM Sales.Invoices si INNER JOIN Sales.InvoiceLines sil ON si.InvoiceID = si.InvoiceID OPTION (MAXDOP 1)
Поскольку проблема заключается в операторе
JOIN
, давайте сосредоточимся на оператореINNER JOIN
:INNER JOIN Sales.InvoiceLines sil ON si.InvoiceID = si.InvoiceID
Вы видите, что этот запрос просто соединяет таблицу с самой собой. Простая опечатка: написано
si
, а неsil
- вот в чем проблема. Этот запрос «зависнет», т. е. почти никогда не завершится. Его можно прервать или исправить, и тогда ваш вице-президент будет намного счастливее. - Отмените запрос из mysmartquery.sql, если он все еще выполняется.
Упрощенное профилирование запросов также включает в себя расширенные события и поддержку указаний запросов, и эти функции можно включить. Подробнее о том, как их включить, а также о том, как отключить эту функцию для каждой базы данных, можно узнать по ссылке.
Я не могу его «поймать»!
Рассмотрим другой сценарий. Вы наблюдали увеличение загрузки ЦП SQL Server и не уверены, что нечто подобное должно происходить в нормальном режиме работы (потому что это не похоже на обычное поведение). Используя DMV, вы можете видеть значение различных параметров, например использовать sys.dm_exec_query_stats, чтобы узнать, где именно запросы больше всего загружают ЦП; однако при помощи этого динамического административного представления (DMV) вы получаете только предполагаемый план. Вы можете попытаться выполнить запрос самостоятельно в автономном режиме и получить фактический план, но вы хотите увидеть фактический план выполнения реального запроса из приложения, чтобы убедиться, что вы получаете корректные данные. Этот запрос постоянно выполняется многими пользователями, но время его выполнения составляет всего несколько секунд (отсюда и постоянное увеличение загрузки ЦП), поэтому трудно использовать новые инструменты для того, чтобы «поймать» выполняемый запрос. Вы можете включить стандартное профилирование запросов, однако для этого требуется немало ресурсов, и использование стандартного профилирования может вызвать проблемы с производительностью приложений в момент выполнения запроса.
Вместе с упрощенным профилированием запросов в SQL Server 2019 появилась новая возможность. В SQL Server 2019 теперь есть новая функция динамического управления (Dynamic Management Function, DMF) с названием sys.dm_exec_query_plan_stats. Идея состоит в том, чтобы зафиксировать последний фактический план выполнения кешированного запроса. Вы можете прочитать об использовании этой DMF по ссылке.
Давайте посмотрим, как применять эту DMF для поиска фактического плана выполнения запроса, который осуществляется постоянно, не применяя какие-либо специальные средства диагностики, не включая переключатели и флаги и не запуская запрос вручную. Единственный тонкий момент здесь заключается в том, что эту функцию облегченного профилирования запросов необходимо включить для каждой базы данных, где вы хотите ее использовать. Вы можете сделать это с помощью следующего оператора T-SQL, который будет использован в приведенном ниже примере:
ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON
Все сценарии для этого примера размещены в каталоге lwp. Чтобы было проще увидеть планы выполнения в визуальном режиме, я рекомендую вам запустить этот пример с использованием SSMS.
- Откройте сценарий T-SQL mysmartquery_top.sql.
- Выполните предварительные настройки параметров для данного примера. Для этого выполните шаг 1 сценария:
-- Шаг 1. Очистите кеш процедур и установите значение параметра -- dbcompat = 130, чтобы показать, что вам не нужно значение -- dbcompat = 150 для вывода данных статистики последнего плана выполнения. USE WideWorldImporters GO ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE GO ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 130 GO ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON GO SELECT COUNT(*) FROM Sales.InvoiceLinesExtended GO
Для параметра
dbcompat
установлено значение130
, чтобы показать, что вам не нужно устанавливатьdbcompat=150
для использования этой функции. - Теперь давайте сэмулируем неверные данные статистики, выполнив шаг 2 сценария:
-- Шаг 2. Имитация устаревших данных статистики: очень низкие значения UPDATE STATISTICS Sales.InvoiceLinesExtended WITH ROWCOUNT = 1 GO
- Запустите запрос. Запрос будет выполняться всего несколько секунд, но он отнимет ресурсы ЦП. Выполните шаг 3.
Примечание. Вам не нужно выбирать фактический план выполнения, поскольку мы эмулируем ситуацию, когда вы будете просматривать планы независимо от приложения.
-- Шаг 3. Запустите запрос. Запрос будет выполняться всего несколько -- секунд, но он отнимет ресурсы ЦП SELECT si.InvoiceID, sil.StockItemID FROM Sales.InvoiceLinesExtended sil JOIN Sales.Invoices si ON si.InvoiceID = sil.InvoiceID AND sil.StockItemID >= 225 GO
- Теперь давайте посмотрим на предполагаемый план выполнения для этого запроса, используя стандартные DMV. Выполните шаг 4, чтобы просмотреть предполагаемый план. Обратите внимание на то, что это позволяет вам увидеть план выполнения запроса из другого пользовательского подключения.
-- Шаг 4. Что говорит предполагаемый план выполнения? Выглядит -- как корректный план, основанный на оценках SELECT st.text, cp.plan_handle, qp.query_plan FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp WHERE qp.dbid = db_id('WideWorldImporters') GO
В выходных данных вам нужно найти строку, в которой значения текстового столбца начинаются с «Шаг 3». Щелкните значение
query_ plan
для этой строки. Ваш план должен выглядеть подобно тому, что показано на рис. 24.Рис. 24. Предполагаемый план выполнения для проблемного запроса
Обратите внимание, какая тонкая линия выходит из оператора сканирования кластеризованного индекса (Clustered Index Scan). Это связано с тем, что оптимизатор оценивает таблицу InvoiceLinesExtended как одну строку. Но это только предполагаемый план, и потому вы не знаете, что это не так (вы просто сэмулировали ошибочные строки предполагаемого плана, но притворились, что не знали, что эта оценка ошибочна).
- Теперь давайте используем новую DMV, чтобы получить последний фактический план выполнения для этого запроса и посмотреть, являются ли строки предполагаемого плана неправильными. Выполните шаг 5.
-- Шаг 5. Что говорит последний фактический план выполнения? Ой-ой. -- Расхождения между предполагаемым и фактическим планом очень велики. SELECT st.text, cp.plan_handle, qps.query_plan, qps.* FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st CROSS APPLY sys.dm_exec_query_plan_stats(cp.plan_handle) AS qps WHERE qps.dbid = db_id('WideWorldImporters') GO
В этом примере мы используем функцию dm_exec_query_plan_stats вместо
dm_exec_query_plan
. Снова найдите запрос в списке и щелкните значениеquery_plan
. План выполнения запроса должен выглядеть так, как показано на рис. 25.Рис. 25. Фактический план выполнения для проблемного запроса
Обратите внимание на «более толстые» линии. Они выглядят так по - тому, что фактическое количество обрабатываемых строк намного больше, чем оценочное значение. В этом заключается проблема, и это объясняет, почему оптимизатор принял решение использовать соединение таблиц с применением вложенных циклов (Nested Loops Join) и сделать таблицу InvoiceLinesExtended «внешней» таблицей: потому что он предполагал, что это только одна строка.
- Обновите данные статистики, чтобы исправить их. После этого вы сможете увидеть, что на самом деле должен делать запрос. Выполните шаг 6 сценария.
-- Шаг 6. Обновите статистику до правильного значения и очистите -- кеш процедур. UPDATE STATISTICS Sales.InvoiceLinesExtended WITH ROWCOUNT = 3652240 GO ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE GO
- Запустите запрос еще раз, используя шаг 7 сценария, и просмотрите новый фактический план выполнения. Вы заметите, что запрос работает немного быстрее:
-- Шаг 7. Запустите запрос еще раз. Он работает быстрее. SELECT si.InvoiceID, sil.StockItemID FROM Sales.InvoiceLinesExtended sil JOIN Sales.Invoices si ON si.InvoiceID = sil.InvoiceID AND sil.StockItemID >= 225 GO
- Выполните шаг 8, чтобы посмотреть, улучшился ли новый фактический план.
-- Шаг 8. Как выглядит фактический план на этот раз? -- Он отличается от предыдущего, потому что статистика актуальна SELECT st.text, cp.plan_handle, qps.query_plan FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st CROSS APPLY sys.dm_exec_query_plan_stats(cp.plan_handle) AS qps WHERE qps.dbid = db_id(‘WideWorldImporters’) GO
Щелкнув мышью query_plan для строки, которая соответствует тексту «Шаг 7. Запустите запрос еще раз...», вы увидите план, аналогичный представленному на рис. 26.
Рис. 26. Фактический план выполнения для улучшенного запроса
План выполнения запроса кардинально отличается от предыдущего. Вы можете видеть, что в этом случае оптимизатор создает план, в котором сначала выполняется поиск по индексу для таблицы InvoiceLinesExtended, а затем операция соединения таблицы с самой собой с помощью поиска по ключу кластеризованного индекса (Key Lookup). Для запроса, использующего доступные индексы, это гораздо более эффективный способ выполнить соединение промежуточного результата запроса с другими таблицами и отфильтровать полученные результаты.
Теперь, когда у вас есть возможность в любой момент увидеть фактический план выполнения запроса и вам не нужно включать специальные флаги, которые могут выполнять дорогостоящие операции, или запускать запрос в автономном режиме, вы видите, что эта возможность дополняет мощный арсенал средств настройки производительности запросов и устранения неполадок, имеющихся в вашем распоряжении.
Упрощенное профилирование запросов - это просто здорово! Если вы потратили какое-то время на поддержку SQL-серверов в промышленной среде, решая проблемы с производительностью, то согласитесь, что встроенная диагностика, доступная в любое время и в любом месте, - это поистине глоток свежего воздуха.