SQL Server 2019: Интеллектуальная обработка запросов на примерах

Интеллектуальная обработка запросов SQL Server 2019
Doc

Doc

АйТишник со стажем... Профиль автора.

Напомню, в предыдущей статье мы начали разговор об интеллектуальных возможностях по настройке производительности 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 по ссылке.

Чтобы использовать на практике примеры из этой статьи, выполните сле­дующие действия:

  1. Загрузите резервную копию базы данных WideWorldImportersDW с сайта GitHub.
  2. Восстановите эту базу данных на вашем экземпляре SQL Server Вы можете использовать для этого готовый сценарий restorewwidw. sql. Возможно, вам придется изменить путь к каталогу, где находит­ся ваша резервная копия, и путь к месту для восстановления файлов базы данных.
  3. Для запуска некоторых сценариев, приводимых в качестве приме­ра, вам понадобятся таблицы большего размера, нежели те, кото­рые добавлены по умолчанию в 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 CONFIGURA­TION. Вы также можете отключить эту функцию на уровне запроса, исполь­зуя параметр запроса 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. На шаге 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
  1. На шаге 2 создаются условия для предоставления недостаточного объема памяти. Я покажу вам один прием, позволяющий смодели­ровать такую ситуацию. Команда T-SQL UPDATE STATISTICS имеет специальный необязательный параметр для принудительного зада­ния указанного количества строк или страниц, которые хранятся в статистике запроса. Вы вряд ли когда-либо будете использовать этот параметр при обычной работе с SQL Server. В документации, опи­сывающей команду UPDATE STATISTICS (см. статью), об этом параметре гово­рится следующее: «Используется только в ознакомительных целях. Не поддерживается. Будущая совместимость не гарантируется». Так что рассматриваемый здесь вариант предназначен только для этой демонстрации. Для наших целей давайте принудительно увеличим кардинальность, хранящуюся в статистике выполнения запроса, для этой таблицы до 1000 строк: 
    -- Шаг 2. Имитация устаревшей статистики
    UPDATE STATISTICS Fact.OrderHistory
    WITH ROWCOUNT = 1000
    GO

    На самом деле в этой таблице 3 702 592 строки; принудительно задан­ный параметр статистики, предполагающий, что в таблице всего 1000 строк, воспроизводит сценарий, когда данные статистики не синхронизированы с фактическими данными о количестве строк в таблице.

  2. Перейдем к шагу 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.

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

    Рис. 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 Мб памяти.

    SELECT показано запрошенное количество памяти

    Рис. 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 адаптирует план выполнения запро­са и изменит объем предоставляемой памяти с учетом недооценки, имевшей место в прошлый раз.

  3. Перейдем к шагу 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. Обратная связь по временно предоставляемому буферу памяти после коррекции предоставляемого объема памяти

  4. Нам нужно убедиться, что данные статистики запроса вернулись в исходное состояние. Для этого нужно запустить код 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.

  1. Во-первых, нам необходимо настроить регулятор ресурсов (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
  2. Теперь запустите исполняемый файл 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

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

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

  3. Теперь запустите исполняемый файл 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.
  4. Выполните один запрос, используя сценарий rowmode_mgf.sql, и просмотрите данные обратной связи по временно предоставля­емому буферу памяти в SQL Server Management Studio. Исполь­зуйте те же приемы, что и в предыдущем разделе этой статьи, чтобы просмотреть свойства оператора SELECT в плане. Раскройте раздел MemoryGrantInfo. Результаты должны выглядеть так, как показано на рис. 9.

    Ниже приведено описание ключевых параметров обратной связи. DesiredMemory - это идеальный объем предоставляемой памяти, основанный на оценке кардинальности. В данном случае это число составляет около 56 Гб. Это сумасшедшее количество памяти!

    • GrantedMemory - мы не можем предоставить 56 Гб памяти для это­го запроса, поэтому предоставляем только около 5 Гб. Это все еще значительный объем предоставляемой памяти.
    • MaxUsedMemory - этот объем памяти фактически используется для предоставления памяти во время выполнения запроса. Как вы види­те, он составляет всего 3 Мб. Это, безусловно, пример предоставле­ния чрезмерно большого объема памяти по сравнению с необходи­мым объемом.

    Рис. 9. Обратная связь по временно предоставляемому буферу памяти при чрезмерного большом объеме предоставляемой памяти

  5. Теперь давайте включим обратную связь по временно предоставляе­мому буферу памяти, запустив исполняемый файл turn_on_mgf.cmd (который выполняет сценарий T-SQL turn_on_mgf.sql).
  6. Давайте снова сэмулируем рабочую нагрузку, запустив исполняемый файл rowmode_mgf.cmd. Работа этого файла должна завершиться за время, вдвое меньшее предыдущего (обычно около 20 секунд). Если вы запустите сценарий dm_exec_requests.sql во время рабо­ты сценария ostress, то увидите кратковременное увеличение числа элементов очереди ожиданий RESOURCE_SEMAPHORE, а затем эта очередь исчезнет, потому что сработала обратная связь по временно предоставляемому буферу памяти, уменьшив размер предоставля­емой памяти в соответствии с объемом памяти, необходимым для выполнения запроса.

    Совет. Попробуйте запустить исполняемый файл rowmode_mgf.cmd во второй раз. Бу­дет ли он в этот раз работать быстрее? Да, сейчас он может работать немного быстрее. Это связано с тем, что при первом запуске rowmode_mgf.cmd первые выполнения запроса происходили очень быстро, и кешированный план не обновлялся при новом предоставлении памяти. Но по мере выполнения дальнейших запусков командного файла память каждый раз выделялась заново. Когда вы запустили rowmode_mgf.cmd во второй раз, во всех запросах использовалось новое выделение памяти.

  7. Если вы посмотрите на свойства оператора SELECT, относящиеся к временно предоставляемому буферу памяти для rowmode_mgf.sql, то увидите, что объемы предоставляемой памяти изменились, и их обновленные значения гораздо ближе к значению объема памяти, необходимого для выполнения запроса.
  8. Восстановите состояние базы данных, данные статистики для таблиц и регулятора ресурсов, запустив исполняемые файлы 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).

  1. Откройте в Azure Data Studio записную книжку T-SQL с названием iqp_tablevariable.ipynb.
  2. Выполните каждый шаг в записной книжке T-SQL, чтобы сравнить производительность при использовании табличных переменных с отложенной компиляцией и без нее.
  3. Чтобы сравнить планы выполнения запросов для этих двух сце­нариев, мы можем использовать функцию под названием Query Store, которая впервые была представлена в SQL Server Воз­можно, вы этого не знали, но при восстановлении резервной копии WideWorldImportersDW в базе данных уже использовалось хранили­ще запросов Query Store.
  4. Вот как использовать Query Store для сравнения двух запросов: один с использованием отложенной компиляции табличной переменной, а другой - без нее.
  5. Откройте SSMS, подключитесь к SQL Server, на котором вы запустили примеры из записных книжек T-SQL, и найдите отчет Top Resource Consuming Queries (Самые ресурсоемкие запросы), как показано на рис. 10.
  6. В отчете, приведенном на рис. 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

    Отчет Top Resource Consuming Queries (Самые ресурсоемкие запросы)

     Рис. 10. Отчет Top Resource Consuming Queries (Самые ресурсоемкие запросы) в Query Store

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

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

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

    Рис. 11. Планы выполнения запросов для использования табличных переменных

  8. Наведите курсор на верхнюю точку, чтобы увидеть данные статисти­ки для плана выполнения запроса. Просмотрите данные статистики, например среднюю продолжительность, как показано на рис. 12.

    Средний период времени для более медленного плана выполнения запроса 

    Рис. 12. Средний период времени для более медленного плана выполнения запроса

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

    Оценка в одну строку для табличной переменной

    Рис. 13. Оценка в одну строку для табличной переменной

    Обратите внимание на операцию соединения табличной перемен­ной и таблицы OrderHistoryExtended. Здесь используется алгоритм соединения с применением вложенных циклов (Nested Loops Join). Это вполне разумный выбор для оптимизатора, поскольку он пред­полагает, что табличная переменная содержит только одну строку. Проблема в том, что в данном случае табличная переменная содер­жит около 3 млн строк! Использование подобного типа соединения (с вложенными циклами) для такого числа строк будет очень дорого­стоящей операцией и в данном случае не имеет смысла.

  9. Теперь щелкните «нижнюю» точку в окне с планами выполнения за­просов. Наведите указатель мыши на точку, чтобы увидеть среднюю продолжительность запроса. Это должно выглядеть примерно так, как показано на рис. 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 показано, как выглядит верхняя область записной книжки.

Записная книжка T-SQL для демонстрации пакетного режима обработки 

Рис. 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.

  1. Откройте сценарий 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 млн, будет считаться «ПЛАТИНОВЫМ» кли­ентом. Преимущество использования функции в данном случае за­ключается в том, что мы можем изменять правила для определения категории клиента («ПРОСТОЙ», «ЗОЛОТОЙ» и «ПЛАТИНОВЫЙ»), не оказывая влияния на весь остальной код, использующий эту функ­цию.

  2. Откройте сценарий T-SQL iqp_scalarudfinlining.sql и выполните все шаги в соответствии с комментариями, приведенными в сценарии.
  3. Выполните фрагмент сценария шаг 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
  4. Задайте нужное значение переменной 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
  5. Давайте запустим запрос с использованием 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 млн). Это крайне неэффективно.

     План выполнения запроса для отключенной возможности встраивания скалярной UDF

    Рис. 17. План выполнения запроса для отключенной возможности встраивания скалярной UDF

  6. Запустите шаг 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.

    План выполнения встраиваемой скалярной UDF

    Рис. 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 и пройдемся по его шагам, изучая разли­чия между запросами и их планами выполнения.

      1. Откройте сценарий iqp_approxcountdistinct.sql в SSMS.
      2. Выполните операторы шага 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-SQL APPROX_COUNT_DISTINCT() просто поставляется с ядром SQL Server 2019, но для нее не требуется устанавливать значение dbcompat = 150, как для других функций интеллектуальной обработ­ки запросов.

      3. Включите фактический план выполнения запроса (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.

        План выполнения запроса для COUNT и DISTINCT

        Рис. 19. План выполнения запроса для COUNT и DISTINCT

        Обратите внимание на оператор Hash Match. Если вы наведете указатель мыши на этот оператор, то увидите, что он использует построчный режим (Row Mode) и должен обработать все 29 млн строк в хеш-операторе.

      4. Теперь выполните шаг 3 сценария следующим образом:
        -- Шаг 3. Используйте новую функцию APPROX_COUNT_DISTINCT для
        -- сравнения вычисленных значений и производительность запроса
        -- Разность между результатами приблизительного и точного подсчета
        -- не должна превышать 2 % от точного значения (с вероятностью 97 %)
        SELECT APPROX_COUNT_DISTINCT([WWI Order ID])
        FROM [Fact].[OrderHistoryExtended]
        GO

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

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

        План выполнения запроса для APPROX_COUNT_DISTINCT

        Рис. 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.

Чтобы использовать на практике примеры из этой статьи, выполните сле­дующие действия:

      1. Загрузите резервную копию базы данных WideWorldImporters, до­ступную по ссылке.
      2. Восстановите эту базу данных на вашем экземпляре SQL Server Вы можете использовать для этого готовый сценарий restorewwidw. sql. Возможно, вам придется изменить путь к каталогу, где находятся ваша резервная копия, и путь к месту для восстановления файлов базы данных.
      3. Для запуска некоторых примеров вам понадобятся таблицы большего размера, чем те, которые добавлены по умолчанию в 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), чтобы увидеть все подробности.

  1. Откройте сценарий T-SQL mysmartquery.sql (возможно, этот сце­нарий не столь «умен», как его название) и запустите выполнение пакета.
  2. В новом соединении откройте сценарий T-SQL show_active_queries. sql.
  3. Запустите пакет из шага 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).

    По моему опыту, это не «хороший» запрос, а тот, который имеет «шансы на улучшение». Но вопрос в том, стоит ли прерывать его сей­час или он уже «почти выполнен».

  4. Что было бы полезно узнать, когда запрос активен, - так это увидеть ход выполнения операторов из плана выполнения запроса, напри­мер статистики активных запросов (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. Сбор данных, таких как загрузка процессора и объем ввода-вывода, включенный по умолчанию, может оказаться весьма дорогостоящим. Вы можете собрать эти данные, используя стандартное профилирование.

  5. Давайте посмотрим на сам план выполнения запроса. Это предпо­лагаемый план, но он может дать представление об этих больших оценочных числах 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.

    Предупреждение, выведенное для оператора Nested Loops Join

    Рис. 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 - вот в чем проблема. Этот запрос «зависнет», т. е. почти никогда не завершится. Его можно прервать или исправить, и тогда ваш вице-президент будет намного счастливее.

  6. Отмените запрос из 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.

  1. Откройте сценарий T-SQL mysmartquery_top.sql.
  2. Выполните предварительные настройки параметров для данного примера. Для этого выполните шаг 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 для использования этой функции.

  3. Теперь давайте сэмулируем неверные данные статистики, выполнив шаг 2 сценария:
    -- Шаг 2. Имитация устаревших данных статистики: очень низкие значения
    UPDATE STATISTICS Sales.InvoiceLinesExtended
    WITH ROWCOUNT = 1
    GO
  4. Запустите запрос. Запрос будет выполняться всего несколько секунд, но он отнимет ресурсы ЦП. Выполните шаг 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
  5. Теперь давайте посмотрим на предполагаемый план выполнения для этого запроса, используя стандартные 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 как одну строку. Но это только предполагаемый план, и потому вы не знаете, что это не так (вы просто сэмулировали ошибочные стро­ки предполагаемого плана, но притворились, что не знали, что эта оценка ошибочна).

  6. Теперь давайте используем новую 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 «внешней» таблицей: потому что он предполагал, что это только одна строка.

  7. Обновите данные статистики, чтобы исправить их. После этого вы сможете увидеть, что на самом деле должен делать запрос. Выпол­ните шаг 6 сценария.
    -- Шаг 6. Обновите статистику до правильного значения и очистите
    -- кеш процедур.
    UPDATE STATISTICS Sales.InvoiceLinesExtended
    WITH ROWCOUNT = 3652240
    GO
    ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE
    GO
  8. Запустите запрос еще раз, используя шаг 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
  9. Выполните шаг 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-серверов в про­мышленной среде, решая проблемы с производительностью, то со­гласитесь, что встроенная диагностика, доступная в любое время и в любом месте, - это поистине глоток свежего воздуха.

Вас заинтересует / Intresting for you:

Что нового в SQL Server 2019? ...
Что нового в SQL Server 2019? ... 2689 просмотров Doctor Mon, 15 Feb 2021, 19:46:52
База данных в памяти - улучшае...
База данных в памяти - улучшае... 2748 просмотров Doctor Mon, 01 Mar 2021, 17:46:50
Как найти столбец по имени в б...
Как найти столбец по имени в б... 2926 просмотров Андрей Васенин Wed, 09 Jun 2021, 18:38:14
MS SQL: поиск ключевого слова ...
MS SQL: поиск ключевого слова ... 2802 просмотров Андрей Васенин Wed, 09 Jun 2021, 06:02:30
Войдите чтобы комментировать

apv аватар
apv ответил в теме #9926 3 года 3 нед. назад
Упрощённой профилирование запросов действительно крутая фишка Sql server 2019.
Офигенная статья. Спасибо!