От переводчика:

Предлагаю перевод фундаментальной статьи Remus Rusanu (CC-BY), где кратко, но обстоятельно рассказывается об основных механизмах MS SQL Server. В процессе перевода я дополнительно выделил некоторые вещи, плюс обновил ссылки, но старался держаться максимально близко к оригиналу.

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

Статья большая, поэтому перевод я буду выкладывать частями. Обо всех замечаниях к переводу – просьба писать в личку. Поехали!

UPD: Вторая часть, где разбираются принципы хранения данных и механика считывания строк во время выполнения запроса - по ссылке

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

Если вы разработчик, работаете над приложением, которое взаимодействует с SQL Server, и вам интересно, что в действительности происходит, когда вы «выполняете» запрос из своего приложения – что ж, я надеюсь, эта статья поможет лучше писать код запросов, а также может стать отправной точкой для расследования проблем производительности.

Оригинал изображения

SQL Server — это клиент-серверная платформа. Единственный способ как-то взаимодействовать с базой данных - отправить ей запрос, который будет содержать команды для базы данных. Протокол, используемый для общения между приложением и БД называется TDS (Tabular Data Stream) и описан в технической документации на портале MSDN. Приложение может использовать одну из клиентский реализаций протокола: SqlClient, управляемый код из CLR, OleDB, ODBC, JDBC, PHP Driver for SQL Server или реализацию с открытым кодом – FreeTDS. Суть в том, что когда ваше приложение хочет что-то сделать с базой данных – в любом случае, оно работает с протоколом TDS. Сам запрос может принимать несколько форм:

Пакетный запрос (Batch Request)

Запрос этого типа содержит в себе только текст выполняемого запроса (или скрипта/пакета запросов). Этот тип запроса сам по себе не содержит параметров, но, очевидно, внутри скрипта могут быть объявлены локальные переменные.

Именно этот тип запроса выполняется из SqlClient когда вызываете методы SqlCommand.ExecuteReader(), ExecuteNonQuery(), ExecuteScalar(), ExecuteXmlReader() (или их асинхронные эквиваленты) с пустым списком параметров. Во время выполнения таких запросов в профайлере можно увидеть событие SQL: BatchStarting.

Запрос с удаленным вызовом процедуры (RPC Request)

Этот запрос содержит идентификатор процедуры, которую необходимо выполнить, а также параметры, перечисленные через запятую. Особенно интересна тут будет процедура с id 12 – то есть, sp_execute. В этом случае первым параметром будет текст TSQL-запроса.

Именно такой тип запроса будет вызван если ваше приложение будет использовать объект SqlCommand хотя бы с одним непустым параметром. В профайлере вы увидите событие RPC:StartingEvent.

Запрос массовой вставки (Bulk Load Request)

Bulk Load – это особый тип запросов, используемый (внезапно) операциями массовой вставки: утилитой bcp.exe, интерфейсом IRowsetFastLoad из OleDB или из класса SqlBulkcopy.

Запрос массовой загрузки отличается от других типов запросов, так как это единственный тип запроса, выполнение которого начинается ещё до того, как будет завершён вызов по протоколу TDS. Это позволяет начать выполнение запроса и затем передать ему поток данных для вставки – не дожидаясь окончания передачи этого потока серверу СУБД.

--

После того, как весь TDS-запрос попадёт на сервер СУБД, SQL Server создаст задачу (Task) для выполнения запроса. Список текущих запросов можно увидеть в представлении sys.dm_exec_requests.

Задачи (Tasks)

«Задачи», упомянутые выше, создаются для выполнения запроса – и будут представлять запрос от начала и до конца выполнения. Например, если мы выполняем пакетный запрос (Batch request), задача будет представлять собой не отдельные команды, но весь скрипт. Отдельные команды из этого скрипта не будут создавать новые задачи. Некоторые команды из пакета могут выполняться с учётом параллелизма (тут надо вспомнить настройки, касающиеся степени параллелизма, Degree of Parallelism). Так вот, в этом случае Задача создаст Подзадачи для выполнения в несколько потоков.

Если запрос возвращает какой-то результат, задача (в оригинале: batch – пакет запросов, прим. переводчика) будет завершена только тогда, когда результат полностью получен на клиенте (например, когда вы уничтожите экземпляр SqlDataReader).

Список задач можно увидеть, обратившись к sys.dm_os_tasks.

Когда новый запрос попадает на сервер и создаётся новая задача для выполнения этого запроса, она получает статус «ожидание» (PENDING). В этот момент сервер не имеет никакого понятия о том, что вообще делает новый запрос. В первую очередь задача должна начать выполняться – и для этого ей должны назначить рабочий поток (worker).

Рабочие потоки (Workers)

Рабочие потоки – это потоки (нити, threads), относящиеся к SQL Server. Несколько таких потоков создаётся сразу при старте службы сервера, дальше, по необходимости, могут быть созданы дополнительные - пока общее число рабочих потоков не достигнет значения параметра max worker threads.

Только рабочие потоки могут выполнять код. Рабочие потоки ждут, пока «ожидающая» (PENDING) задача не станет доступна, затем каждый рабочий поток забирает ровно одну задачу и выполняет её. Рабочий поток занят (работает) пока задача не будет выполнена полностью. Ожидающие (pending) задачи, для которых не нашлось свободного рабочего потока вынуждены висеть, пока какая-нибудь из выполняемых задач не завершится – и не освободится рабочий поток, который сможет взять новую задачу.

Если рабочий поток выбирает пакетный запрос (batch), он один будет выполнять весь пакет, т.е. каждую его команду. Тут должно стать понятно, могут ли разные команды в одном скрипте (=> задаче => рабочем процессе) выполняться параллельно: нет, не могут. Поскольку они все выполняются в одном потоке, каждый запрос из пакета не начнёт выполняться, пока не закончится предыдущий.

Для команд, которые используют внутренний параллелизм (помним про DOP, здесь он будет >1) и создают подзадачи, каждая такая подзадача пройдёт ровно такой же цикл: будет создана со статусом «ожидание» (PENDING), её должен будет подхватить и выполнить рабочий поток (не тот поток, который выполняет «родительский» пакетный запрос! – тот поток по определению уже занят).

Список рабочих потоков можно увидеть в представлении sys.dm_os_workers.

Парсинг и компиляция запроса

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

SQL и TSQL – это высокоуровневые декларативные языки с крайне сложными выражениями (представьте SELECT с несколькими JOIN’ами). Компиляция запроса не приводит к появлению ассемблерных инструкций – или даже чего-то похожего на байт-код для JVM. Вместо этого строится план обращения к данным (или план запроса). План описывает, как именно обращаться к таблицам и индексам, как искать и находить нужные строки и выполнять дальнейшие манипуляции с полученными данными. Условно, план может звучать так:

«открыть индекс ndx1 таблицы T, затем найти строку с ключом «k» и венуть колонки A и B»

Тут же сразу важное замечание:

Многие разработчики пытаются сделать из запроса швейцарский перочинный нож, который пригодится сразу в 10 разных ситуациях. Для этого обычно используются хитрые условия в секции WHERE, часто содержащие несколько вариантов, соединённых через OR (COLUMN = @parameter OR @parameter IS NULL). Это хорошая практика для разработчиков, придерживающихся принципа DRY - и старающихся избегать дублирования кода. Но в случае SQL-запросов это выливается в большие проблемы. Компиляция должна завершиться с планом, который будет приемлемым для каждой из 10 ситуаций, для которых предназначен такой запрос. Это значит, что план скорее всего будет хуже, чем 10 планов, подобранных для каждой из ситуаций по отдельности. Если вы следите за чистотой клиентского кода, рекомендую обратить внимание на приёмы динамического SQL

Оптимизация

Продолжим разговор о выборе оптимального способа получения данных. Следующая стадия жизни запроса: оптимизация.

В T-SQL, так же, как и в SQL, оптимизация означает выбор лучшего способа получения данных среди всех возможных альтернатив. Предположим, у нас есть простой запрос с объединением 2х таблиц, у каждой таблицы есть по одному некластерному индексу - и вот у нас уже 4 способа выполнить запрос! Количество возможных вариантов растёт экспоненциально вместе с ростом сложности запросов и количеством способов обращения к данным (т.е., с числом индексов в нужных таблицах). Добавьте к этому, что соединение (JOIN) может быть выполнено при помощи разных стратегий (в общем случае: вложенные циклы, соединение хэшированием или объединением). Становится понятно, почему оптимизация настолько важна в работе SQL Server.

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

Стоимость, по большей части, зависит от объёма данных, которые необходимо будет прочитать в рамках выбранного способа выполнения. Для того, чтобы получить эту «стоимость», SQL Server должен знать размер каждой таблицы и распределение значений в колонках этой таблицы. Информация об этом распределении хранится в статистиках.

Кроме объёма читаемых данных, стоимость также зависит от предполагаемой нагрузки на CPU и объёма памяти, необходимого для выполнения запроса.

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

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

Выполнение

Как только оптимизатор выбрал план выполнения, запрос начинает выполнение. План выполнения транслируется в «дерево выполнения». Каждый узел дерева – отдельный оператор. Все операторы реализуют абстрактный интерфейс минимум с 3 методами:

Open()
Next()
Close()

Цикл выполнения выглядит так: в самом начале выполняется open() у оператора, который находится в корне дерева. Затем вызывается его же метод next() – и повторяется, пока не вернёт False. После этого вызывается close(). Оператор из корня дерева, в свою очередь, выполнит те же операции для своих «дочерних» операторов, они для своих – и так до самого конца дерева.

В конце, на «листовом» уровне дерева, обычно находятся операторы физического доступа, которые, собственно, и читают данные из таблиц и индексов. На среднем уровне находятся операторы, выполняющие различные действия с прочитанными данными: отбор, соединение источников, сортировка. У запросов, использующих параллелизм, появляется специальный оператор, называемый оператором обмена (Exchange operator). Он запускает несколько потоков выполнения (=>задач =>рабочих потоков); каждый такой поток выполняет под-дерево основного плана выполнения. Оператор обмена затем собирает результаты выполнения этих под-деревьев, используя обычный паттерн «много производителей – один получатель». Великолепное объяснение этой модели выполнения можно найти в статье «Volcano-An Extensible and Parallel Query Evaluation System».

Эта модель выполнения применяется не только к «читающим» запросам, но и к запросам изменения данных (insert, update, delete). Есть отдельные операторы для обработки вставки данных, операторы, которые удаляют строки и операторы для изменения существующих значений. Какие-то запросы создают примитивные планы (например, INSERT INTO ... VALUES ...), тогда как другие могут порождать крайне сложные планы – но стадия выполнения одинакова для всех и происходит строго как было описано: для дерева последовательно вызывается next(), пока оно не закончится.

Некоторые операторы очень просты: например, оператор TOP (N). Каждый раз, когда для него вызывается next(), он просто передаёт управление дальше по дереву, увеличивая значение внутреннего счётчика. После того, как счётчик достигнет N, оператор просто вернёт False без вызова дочерних операторов. Тем самым, перебор текущего поддерева будет завершён.

Другие операторы имеют более сложное поведение. Представим, что нужно сделать оператору Nested Loops: ему требуется отслеживать состояние как внешнего, так и внутреннего циклов. Он вызывает next() для «внешнего» поддерева, а после этого сбрасывает счётчик для внутреннего цикла и выполняет next() для внутреннего поддерева, пока не будет удовлетворено условие соединения.

Некоторые операторы имеют внутренний ограничитель (их ещё называют блокирующими): они не могут вернуть ни одного значения, пока не получат вывод всего поддерева, расположенного за ними. Например, оператор SORT: самый первый вызов next() для этого оператора не вернёт ни одной строки, пока все дочерние операторы не будут выполнены, а их результат – отсортирован.

Оператор HASH JOIN отличается одновременно и сложным поведением, и является частично блокирующим. Для того, чтобы построить хэш-таблицу ему требуется прочитать все данные, возвращаемые «внешней» стороной: он вынужден вызывать next() для «строящей» стороны, пока не получит False. Только после этого он может вызвать next() у проверяемой (внутренней) стороны и вызывает его, пока не будет найдено совпадение в хэш-таблице. Как только совпадение найдено, нужная строка возвращается вышестоящему оператору. Следующие вызовы next() у Hash Match приведут к вызовам next() у проверяемой стороны (хэш-таблицу ведь мы уже собрали) – и так, пока вторая сторона не вернёт False.

Результаты

Результаты возвращаются обратно в клиентскую программу по мере выполнения запроса. Как только строка «всплывёт» в дереве выполнения, самый первый оператор обычно сразу записывает ее в сетевой буфер и отправляет клиенту. Результат не «подготавливается» в каком-то промежуточном хранилище (в памяти или на диске) – вместо этого он отправляется сразу по мере получения (то есть, по мере выполнения запроса). На этом этапе, конечно, свою роль играют сетевые протоколы управления потоком. Если клиент не выбирает результат (например, не выполняет SqlDataReaded.Read()), то сетевой поток заблокирует передающую сторону, а это, в свою очередь, приостановит выполнение запроса. Запрос продолжит выполнение и вернёт следующие строки результата (то есть, продолжит выполнение плана запроса) как только сетевой стек восстановит необходимые ресурсы.

Здесь интересно упомянуть возвращаемые (OUTPUT) параметры, связанные с запросом. Для того, чтобы вернуть возвращаемое значение клиенту, это значение должно быть вставлено в сетевой поток, передаваемый от запроса обратно клиенту. Значение может быть записано в поток только по окончании выполнения, как только запрос завершится. Именно поэтому возвращаемые параметры могут быть проверены только после получения всех результатов запроса или пакета запросов.

Грант выделения памяти

Некоторым операторам требуется значительный объём памяти для выполнения. Оператор сортировки должен хранить все полученные данные для того, что бы отсортировать их. Hash join и hash aggregate приходится строить большие хэш-таблицы в процессе своего выполнения.

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

В ситуации, когда несколько тяжелых запросов выполняются одновременно, они могут исчерпать всю память, доступную системе. Для предотвращения таких ситуаций SQL Server использует так называемый «семафор ресурсов». Такой механизм гарантирует, что сумма всех грантов памяти, выделенных для выполняемых запросов, не превысит общий объем памяти, доступный серверу. Если сумма всех грантов памяти все же оказывается больше объема памяти сервера, тогда запросы, запрашивающие последующие гранты, вынуждены ждать, пока текущие запросы не завершатся и не отпустят свои гранты. Текущий статус грантов памяти (запрошен, выделен, и т.д.) можно увидеть в представлении sys.dm_exec_query_memory_grants. Когда запрос вынужден ждать выделения гранта памяти, создается событие Execution Warning.

Как обычно, дьявол кроется в деталях. Запросам не всегда выделяют в точности тот объём, который они запрашивали. Они могут начать выполнение с меньшим объёмом, чем запрашивали – и это нормально. Оператор уведомляется о том, что ему выдали меньше памяти и он подстраивает своё выполнение чтобы уложиться в выделенный грант. Может случиться и так, что полного запрошенного гранта оказывается недостаточно (обычно в этом виновата устаревшая статистика). В этом случае оператор вынужден «выливать» (spill) данные в tempdb. Конечно же, перенос данных на диск (сначала запись, потом чтение) гораздо медленнее работы исключительно в RAM – и для таких ситуаций есть специальные события-предупреждения:

Строго говоря, для хэшей всё еще немного сложнее: они «сбрасываются» (bail out), а не «сливаются» (spill). Подробнее это разобрано в статье с описанием события.

Больше подробностей о грантах памяти – в статье Understanding SQL server memory grant.

Память, положенная запросам, резервируется, а не выделяется. Во время выполнения запрос обращается за выделением памяти из объёма гранта – и только в этот момент память действительно потребляется запросом. Запрос может использовать меньше памяти, чем запросил (оценки памяти обычно пессимистичны и ориентируются на худшие сценарии). Память, которая была запрошена, но не была использована, отдается под нужды кэширования данных (этим занимается Buffer pool). Тем не менее, большие гранты, которые в итоге не были использованы, опасны тем, что мешают выполнению других запросов – им не остается памяти для своих грантов из-за ограничений ресурсного семафора.

В какой-то степени схожая проблема может возникнуть из-за семафора ресурсов для компиляции запросов. Эта «калитка» (gate) схожа с «калиткой» при выполнении запроса, но относится к этапу компиляции. Обычно здесь не должно быть проблем, потому что компиляция происходит относительно редко. Большое число запросов, заблокированных на этапе компиляции сигнализирует о проблемах с переиспользованием планов. См. Diagnosing Plan Cache Related Performance Problems and Suggested Solutions.

Ну и последнее замечание про гранты памяти. Помните, что не всем запросам требуется грант памяти. Гранты нужны только тяжелым запросам, в которых используются сортировки, широкие сканирования индексов (параллелизм) и соединения хэшем или агрегация. Если вы видите проблемы с грантами памяти в системах, где предполагается короткое время отклика (например, веб-сайты) – самое время пересмотреть архитектуру ваших данных. Большие гранты памяти допустимы в аналитических сценариях (большие запросы, где долгое время ответа ожидаемо и устраивает пользователей).

Продолжение - в следующих частях: второй и третьей

Перевод: Как MS SQL Server выполняет запросы. Часть 1