Как проектировать запросы на изменение данных sql

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

Сделайте резервную копию данных перед выполнением запроса на изменение данных. Это поможет избежать потери данных в случае ошибки.

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

Используйте оператор SET для задания новых значений столбцов в таблице. Например:

UPDATE orders SET order_status = 'Completed' WHERE order_id = 12345;

Здесь мы обновляем столбец order_status таблицы orders, устанавливая значение Completed для заказа с order_id равным 12345.

Обратите внимание на условие WHERE , которое определяет, какие строки будут изменены. Если условие не указано, запрос обновит все строки в таблице.

Если вы хотите изменить несколько столбцов в таблице, используйте оператор SET для каждого столбца, например:

UPDATE orders SET order_status = 'Completed', order_total = 100.00 WHERE order_id = 12345;

Здесь мы обновляем значения столбцов order_status и order_total таблицы orders для заказа с order_id равным 12345.

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

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

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

Ранее в материале «Основы индексов в Microsoft SQL Server» мы с Вами узнали, что же такое индексы и какие они бывают, а сейчас мы с Вами научимся правильно проектировать эти индексы, чтобы достичь максимальной производительности SQL запросов.

Картинка 1 - Microsoft SQL Server проектирование индексов

Общие рекомендации по проектированию индексов в Microsoft SQL Server

Одним из самых эффективных индексов является индекс для целочисленных столбцов, которые имеют уникальные значения, поэтому по возможности создавайте индексы для таких столбцов;
Если таблица очень интенсивно обновляется, то не рекомендуется создавать большое количество индексов, так как это снижает производительность инструкций INSERT, UPDATE, DELETE и MERGE. Потому что после изменений данных в таблице SQL сервер автоматически вносит соответствующие изменения во все индексы;
Если таблица с большим объемом данных обновляется редко, при этом она активно используется в инструкциях SELECT, т.е. на выборку данных, то большое количество индексов может улучшить производительность, так как у оптимизатора запросов будет больший выбор индексов при определении наиболее эффективного и быстрого способа доступа к данным;
Если создавать некластеризованный индекс в файловой группе, которая расположена не на том диске, на котором расположены файловые группы таблицы, то это может повысить производительность для больших таблиц и индексов, так как это позволяет одновременно обращаться к нескольким дискам;
Для таблиц с небольшим объемом данных создание индексов в частности некластеризованных индексов с целью повышения производительности может оказаться абсолютно бесполезно, да еще и затратами на их поддержание. Так как оптимизатору может потребоваться больше времени на поиск данных в индексе, чем просмотр данных в самой таблице. Поэтому не создавайте индексы для таблиц, в которых очень мало данных;
Кластеризованный индекс необходимо создавать для столбца, который является уникальным и не принимает значения NULL, также длина ключа должна быть небольшой, другими словами ключ индекса не нужно составлять из нескольких столбцов;
Если представление содержит агрегаты и объединения таблиц, то индексы для таких представлений могут дать неплохое улучшение производительности.

Руководство по проектированию реляционных баз данных (1-3 часть из 15) [перевод]

Перевод цикла из 15 статей о проектировании баз данных.
Информация предназначена для новичков.
Помогло мне. Возможно, что поможет еще кому-то восполнить пробелы.

Руководство по проектированию баз данных.

1. Вступление.

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

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

Структурированный язык запросов (SQL).

База данных создается для хранения в ней информации и получения этой информации при необходимости. Это значит, что мы должны иметь возможность помещать, вставлять (INSERT) информацию в базу данных и мы хотим иметь возможность делать выборку информации из базы данных (SELECT).
Язык запросов к базам данных был придуман для этих целей и был назван Структурированный язык запросов или SQL. Операции вставки данных (INSERT) и их выборки (SELECT) – части этого самого языка. Ниже приведен пример запроса на выборку данных и его результат.

SQL – большая тема для повествования и его рассмотрение выходит за рамки данного руководства. Данная статья строго сфокусирована на изложении процесса проектирования баз данных. Позднее, в отдельном руководстве, я расскажу об основах SQL.

Реляционная модель.

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

Правила реляционной модели диктуют, как информация должна быть организована в таблицах и как таблицы связаны друг с другом. В конечном счете результат можно предоставить в виде диаграммы базы данных или, если точнее, диаграммы «сущность-связь», как на рисунке (Пример взят из MySQL Workbench).

Примеры.

В качестве примеров в руководстве я использовал ряд приложений.

РСУБД, которую я использовал для создания таблиц примеров – MySQL. MySQL – наиболее популярная РСУБД и она бесплатна.

Утилита для администрирования БД.

После установки MySQL вы получаете только интерфейс командной строки для взаимодействия с MySQL. Лично я предпочитаю графический интерфейс для управления моими базами данных. Я часто использую SQLyog. Это бесплатная утилита с графическим интерфейсом. Изображения таблиц в данном руководстве взяты оттуда.

Визуальное моделирование.

Существует отличное бесплатное приложение MySQL Workbench. Оно позволяет спроектировать вашу базу данных графически. Изображения диаграмм в руководстве сделаны в этой программе.

Проектирование независимо от РСУБД.

Важно знать, что хотя в данном руководстве и приведены примеры для MySQL, проектирование баз данных независимо от РСУБД. Это значит, что информация применима к реляционным базам данных в общем, не только к MySQL. Вы можете применить знания из этого руководства к любым реляционным базам данных, подобным Mysql, Postgresql, Microsoft Access, Microsoft Sql or Oracle.

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

2. История.

В 70-х – 80-х годах, когда компьютерные ученые все еще носили коричневые смокинги и очки с большими, квадратными оправами, данные хранились бесструктурно в файлах, которые представляли собой текстовый документ с данными, разделенными (обычно) запятыми или табуляциями.

Так выглядели профессионалы в сфере информационных технологий в 70-е. (Слева внизу находится Билл Гейтс).

Текстовые файлы и сегодня все еще используются для хранения малых объемов простой информации. Comma-Separated Values (CSV) — значения, разделённые запятыми, очень популярны и широко поддерживаются сегодня различным программным обеспечением и операционными системами. Microsoft Excel – один из примеров программ, которые могут работать с CSV–файлами. Данные, сохраненные в таком файле могут быть считаны компьютерной программой.

Выше приведен пример того, как такой файл мог бы выглядеть. Программа, производящая чтение данного файла, должна быть уведомлена о том, что данные разделены запятыми. Если программа хочет выбрать и вывести категорию, в которой находится урок ‘Database Design Tutorial’, то она должна строчка за строчкой производить чтение до тех пор, пока не будут найдены слова ‘Database Design Tutorial’ и затем ей нужно будет прочитать следующее за запятой слово для того, чтобы вывести категорию Software.

Таблицы баз данных.

Чтение файла строчка за строчкой не является очень эффективным. В реляционной базе данных данные хранятся в таблицах. Таблица ниже содержит те же самые данные, что и файл. Каждая строка или “запись” содержит один урок. Каждый столбец содержит какое-то свойство урока. В данном случае это заголовок (title) и его категория (category).

Компьютерная программа могла бы осуществить поиск в столбце tutorial_id данной таблицы по специфическому идентификатору tutorial_id для того, чтобы быстро найти соответствующие ему заголовок и категорию. Это намного быстрее, чем поиск по файлу строка за строкой, подобно тому, как это делает программа в текстовом файле.

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

История реляционной модели.

Реляционная модель баз данных была изобретена в 70-х Эдгаром Коддом (Ted Codd), британским ученым. Он хотел преодолеть недостатки сетевой модели баз данных и иерархической модели. И он очень в этом преуспел. Реляционная модель баз данных сегодня всеобще принята и считается мощной моделью для эффективной организации данных.

Сегодня доступен широкий выбор систем управления базами данных: от небольших десктопных приложений до многофункциональных серверных систем с высокооптимизированными методами поиска. Вот некоторые из наиболее известных систем управления реляционными базами данных (РСУБД):

— Oracle – используется преимущественно для профессиональных, больших приложений.
— Microsoft SQL server – РСУБД компании Microsoft. Доступна только для операционной системы Windows.
— Mysql – очень популярная РСУБД с открытым исходным кодом. Широко используется как профессионалами, так и новичками. Что еще нужно?! Она бесплатна.
— IBM – имеет ряд РСУБД, наиболее известна DB2.
— Microsoft Access – РСУБД, которая используется в офисе и дома. На самом деле – это больше, чем просто база данных. MS Access позволяет создавать базы данных с пользовательским интерфейсом.
В следующей части я расскажу кое-что о характеристиках реляционных баз данных.

3. Характеристики реляционных баз данных.

Реляционные базы данных разработаны для быстрого сохранения и получения больших объемов информации. Ниже приведены некоторые характеристики реляционных баз данных и реляционной модели данных.

Использование ключей.

Каждая строка данных в таблице идентифицируется уникальным “ключом”, который называется первичным ключом. Зачастую, первичный ключ это автоматически увеличиваемое (автоинкрементное) число (1,2,3,4 и т.д). Данные в различных таблицах могут быть связаны вместе при использовании ключей. Значения первичного ключа одной таблицы могут быть добавлены в строки (записи) другой таблицы, тем самым, связывая эти записи вместе.

Используя структурированный язык запросов (SQL), данные из разных таблиц, которые связаны ключом, могут быть выбраны за один раз. Для примера вы можете создать запрос, который выберет все заказы из таблицы заказов (orders), которые принадлежат пользователю с идентификатором (id) 3 (Mike) из таблицы пользователей (users). О ключах мы поговорим далее, в следующих частях.

Столбец id в данной таблице является первичным ключом. Каждая запись имеет уникальный первичный ключ, часто число. Столбец usergroup (группы пользователей) является внешним ключом. Судя по ее названию, она видимо ссылается на таблицу, которая содержит группы пользователей.

Отсутствие избыточности данных.

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

Ограничение ввода.

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

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

Помимо типов данных РСУБД позволяет вам еще больше ограничить возможные для ввода данные. Например, ограничить длину или принудительно указать на уникальность значения записей в данном столбце. Последнее ограничение часто используется для полей, которые содержат регистрационные имена пользователей (логины), или адреса электронной почты.

Эти ограничения дают вам контроль над целостностью ваших данных и предотвращают ситуации, подобные следующим:

— ввод адреса (текста) в поле, в котором вы ожидаете увидеть число
— ввод индекса региона с длинной этого самого индекса в сотню символов
— создание пользователей с одним и тем же именем
— создание пользователей с одним и тем же адресом электронной почты
— ввод веса (числа) в поле дня рождения (дата)

Поддержание целостности данных.

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

Назначение прав.

Большинство РСУБД предлагают настройку прав доступа, которая позволяет назначать определенные права определенным пользователям. Некоторые действия, которые могут быть позволены или запрещены пользователю: SELECT (выборка), INSERT (вставка), DELETE (удаление), ALTER (изменение), CREATE (создание) и т.д. Это операции, которые могут быть выполнены с помощью структурированного языка запросов (SQL).

Структурированный язык запросов (SQL).

Для того, чтобы выполнять определенные операции над базой данных, такие, как сохранение данных, их выборка, изменение, используется структурированный язык запросов (SQL). SQL относительно легок для понимания и позволяет в т.ч. и уложненные выборки, например, выборка связанных данных из нескольких таблиц с помощью оператора SQL JOIN. Как и упоминалось ранее, SQL в данном руководстве обсуждаться не будет. Я сосредоточусь на проектировании баз данных.

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

Переносимость.

Реляционная модель данных стандартна. Следуя правилам реляционной модели данных вы можете быть уверены, что ваши данные могут быть перенесены в другую РСУБД относительно просто.

Как говорилось ранее, проектирование базы данных – это вопрос идентификации данных, их связи и помещение результатов решения данного вопроса на бумагу (или в компьютерную программу). Проектирование базы данных независимо от РСУБД, которую вы собираетесь использовать для ее создания.

В следующей части подробнее рассмотрим первичные ключи.

Руководство по архитектуре обработки запросов

Ядро СУБД SQL Server обрабатывает запросы к различным архитектурам хранения данных, таким как локальные таблицы, секционированные таблицы и таблицы, распределенные по нескольким серверам. В следующих разделах описывается, как SQL Server обрабатывает запросы и оптимизирует повторное использование запросов с помощью кэширования плана выполнения.

Режимы выполнения

Компонент SQL Server Database Engine может обрабатывать инструкции Transact-SQL в двух разных режимах:

выполнение в построчном режиме;
выполнение в пакетном режиме.

выполнение в построчном режиме;

Построчный режим выполнения — это метод обработки запросов, применяемый с традиционными таблицами RDBMS, при котором данные сохраняются в строковом формате. При выполнении запроса к данным в таблицах, хранящих строки, операторы дерева выполнения и дочерние операторы считывают каждую требуемую строку по всем столбцам, указанным в схеме таблицы. Затем SQL Server извлекает столбцы, необходимые для результирующего набора, как указано в инструкции SELECT, предикате JOIN или предикате фильтра.

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

выполнение в пакетном режиме.

Пакетный режим выполнения — это метод обработки запросов, при котором обрабатываются сразу несколько строк (поэтому он и называется пакетным). Каждый столбец из пакета сохраняется как вектор в отдельной области памяти. Таким образом, обработка в пакетном режиме основана на векторах. Кроме того, при обработке в пакетном режиме применяются алгоритмы, оптимизированные для многоядерных ЦП и увеличенной пропускной способности памяти, что характерно для современного оборудования.

При первом появлении выполнение в пакетом режиме было тесно интегрировано и оптимизировано для взаимодействия с форматом хранения columnstore. Однако начиная с SQL Server 2019 (15.x) и в Базе данных SQL Azure выполнение пакетного режима больше не требует индексов columnstore. Дополнительные сведения см. в разделе Пакетный режим для данных rowstore.

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

Когда запрос выполняется в пакетном режиме и получает доступ к данным в индексах columnstore, операторы дерева выполнения и дочерние операторы считывают сразу несколько строк по сегментам столбцов. SQL Server считывает только столбцы, необходимые для результата, как указано в инструкции SELECT, предикате JOIN или предикате фильтра. Дополнительные сведения об индексах columnstore см. в статье Архитектура индексов columnstore.

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

Обработка инструкций SQL

Обработка одиночной инструкции Transact-SQL — наиболее распространенный способ, с помощью которого SQL Server выполняет инструкции Transact-SQL. Шаги, используемые для обработки одиночной инструкции SELECT , которая обращается только к таблицам локальной базы (а не к представлениям и не к удаленным таблицам), иллюстрируют основной процесс.

Приоритет логического оператора

При использовании в инструкции нескольких логических операторов первым вычисляется NOT , затем AND и, наконец, OR . Арифметические и побитовые операторы выполняются до логических. Дополнительные сведения см. в разделе Приоритет операторов.

В приведенном ниже примере условие цвета относится к модели продукта 21, но не к модели продукта 20, так как оператора AND имеет приоритет над оператором OR .

SELECT ProductID, ProductModelID FROM Production.Product WHERE ProductModelID = 20 OR ProductModelID = 21 AND Color = 'Red'; GO

Можно изменить смысл запроса, добавляя скобки, чтобы добиться вычисления OR сначала. В приведенном ниже запросе будут найдены модели 20 и 21 красного цвета.

SELECT ProductID, ProductModelID FROM Production.Product WHERE (ProductModelID = 20 OR ProductModelID = 21) AND Color = 'Red'; GO

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

SELECT ProductID, ProductModelID FROM Production.Product WHERE ProductModelID = 20 OR (ProductModelID = 21 AND Color = 'Red'); GO

Оптимизация инструкций SELECT

Оператор SELECT не является процедурным; он не указывает точные шаги, которые сервер базы данных должен использовать для получения запрошенных данных. Это означает, что сервер базы данных должен проанализировать инструкцию для определения самого эффективного способа извлечения запрошенных данных. Это упоминается как оптимизация инструкции SELECT . Компонент, который делает это, называется оптимизатором запросов. Входные данные оптимизатора запросов включают сам запрос, схему базы данных (определения таблиц и индексов) и статистику базы данных. Выходные данные оптимизатора запросов — это план выполнения запроса, который иногда называется планом запроса или выполнения. Содержимое плана выполнения подробно описано далее в этой статье.

Входные и выходные данные оптимизатора запросов при оптимизации одиночной инструкции SELECT показаны на следующей схеме.

Инструкция SELECT определяет только следующее.

Формат результирующего набора. Он указан, главным образом, в списке выбора. Однако другие предложения, например ORDER BY и GROUP BY , также затрагивают конечную форму результирующего набора.
Таблицы, которые содержат исходные данные. Они указываются в предложении FROM .
Логическую связь между таблицами для инструкции SELECT . Это определяется в спецификациях соединения, которые могут появляться в предложении WHERE или в предложении ON , следующем за предложением FROM .
Условия, которым строки в исходных таблицах должны соответствовать для выбора их инструкцией SELECT . Они указываются в предложениях WHERE и HAVING .

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

Последовательности, в которой происходит обращение к исходным таблицам.
Как правило, существует много последовательностей, в которых сервер базы данных может обращаться к базовым таблицам для построения результирующего набора. Например, если инструкция SELECT ссылается на три таблицы, сервер базы данных сначала может обратиться к TableA , использовать данные из TableA для извлечения соответствующих строк из TableB , а затем использовать данные из TableB для извлечения данных из TableC . Другие последовательности, в которых сервер базы данных может обращаться к таблицам:
TableC , TableB , TableA или
TableB , TableA , TableC или
TableB , TableC , TableA или
TableC , TableA , TableB
Методы, используемые для извлечения данных из каждой таблицы.
Есть различные методы для обращения к данным в каждой таблице. Если необходимы только несколько строк с определенными ключевыми значениями, то сервер базы данных может использовать индекс. Если необходимы все строки в таблице, то сервер базы данных может пропустить индексы и выполнить просмотр таблицы. Если необходимы все строки в таблице, но есть индекс, ключевые столбцы которого находятся в ORDER BY , то просмотр индекса вместо просмотра таблицы позволит избежать отдельный сортировки результирующего набора. Если таблица является очень маленькой, то просмотры таблицы могут быть самым эффективным методом для практически всех обращений к таблице.
Методы, используемые для вычислений, а также фильтрации, статистической обработки и сортировки данных из каждой таблицы.
По мере доступа к данным из таблиц можно разными способами выполнять вычисления над данными (например, вычисления скалярных значений), а также статистическую обработку и сортировку данных, как определено в тексте запроса (например, при использовании предложения GROUP BY или ORDER BY ) и их фильтрацию (например, при использовании предложения WHERE или HAVING ).

Процесс выбора одного плана выполнения из множества потенциально возможных планов называется оптимизацией. Оптимизатор запросов является одним из наиболее важных компонентов ядра СУБД. Хотя для анализа запроса и выбора плана оптимизатору запросов требуются некоторые накладные расходы, эти накладные расходы обычно многократно окупаются, когда оптимизатор запроса выбирает эффективный план выполнения. Например, двум строительным компаниям могут быть предоставлены идентичные проекты дома. Если одна компания потратит сначала несколько дней на планирование того, как она будет строить дом, а другая компания начнет строить без планирования, то компания, которая потратит время на планирование проекта, вероятно, закончит первой.

Оптимизатор запросов SQL Server — это оптимизатор на основе затрат. Каждому возможному плану выполнения соответствует некоторая стоимость, определенная в терминах объема использованных вычислительных ресурсов. Оптимизатор запросов должен проанализировать возможные планы и выбрать один файл с самой низкой предполагаемой стоимостью. Для некоторых сложных инструкций SELECT есть тысячи возможных планов выполнения. В этих случаях оптимизатор запросов не анализирует все возможные сочетания. Вместо этого он использует сложные алгоритмы поиска плана выполнения, имеющего стоимость, близкую к минимальной возможной стоимости.

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

Оптимизатор запросов SQL Server использует статистику распределения, когда оценивает затраты на ресурсы различных методов для извлечения информации из таблицы или индекса. Статистика распределения хранится для столбцов и индексов и содержит сведения о плотности 1 базовых данных. Она указывает избирательность значений в определенном индексе или столбце. Например, в таблице, представляющей автомобили, много автомобилей имеют одного производителя, но каждый автомобиль имеет уникальный идентификационный номер транспортного средства (VIN). Индекс по VIN является более избирательным, чем индекс по производителям, так как VIN с меньшей плотностью, чем производитель. Если статистика индекса не является текущей, оптимизатор запросов, возможно, не сделает лучший выбор для текущего состояния таблицы. Дополнительные сведения о плотности см. в разделе Статистика.

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

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

СРЕДА SQL Server Management Studio имеет три варианта отображения планов выполнения:

*Предполагаемый план выполнения — это скомпилированный план, созданный оптимизатором запросов.
Действительный план выполнения — это скомпилированный план с контекстом выполнения. Сюда входят сведения о среде выполнения, доступные после завершения выполнения, такие как предупреждения о выполнении или в более новых версиях ядра СУБД, истекшие и время ЦП, используемое во время выполнения.
Статистика активных запросов — это скомпилированный план с контекстом выполнения. Сюда входят сведения о времени выполнения, которые обновляются каждую секунду. Эти сведения включают в себя, например, фактическое количество строк, передаваемых через операторы.

Обработка инструкции SELECT

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

Средство анализа просматривает инструкцию SELECT и разбивает ее на логические единицы, такие как ключевые слова, выражения, операторы и идентификаторы.
Строится дерево запроса, иногда называемое деревом последовательности, с описанием логических шагов, необходимых для преобразования исходных данных в формат, требуемый результирующему набору.
Оптимизатор запросов анализирует различные способы, с помощью которых можно обратиться к исходным таблицам. Затем он выбирает ряд шагов, которые возвращают результаты быстрее всего и используют меньше ресурсов. Дерево запроса обновляется для записи этого точного ряда шагов. Конечную, оптимизированную версию дерева запроса называют планом выполнения.
Реляционный механизм начинает реализовывать план выполнения. В ходе обработки шагов, требующих данных из базовых таблиц, реляционный механизм запрашивает у подсистемы хранилища передачу данных из набора строк, указанных реляционным механизмом.
Реляционный механизм преобразует данные, возвращенные подсистемой хранилища, в заданный для результирующего набора формат и возвращает результирующий набор клиенту.

Константная свертывание и оценка выражений

SQL Server оценивает некоторые константные выражения рано, чтобы повысить производительность запросов. Это называет сверткой констант. Константа — это литерал Transact-SQL, например 3 , ‘ABC’ , ‘2005-12-31’ , 1.0e3 или 0x12345678 .

Свертываемые выражения

SQL Server использует константное свертывание со следующими типами выражений:

Арифметические выражения, такие как 1 + 1 и 5 / 3 * 2 , которые содержат только константы.
Логические выражения, такие как 1 = 1 и 1 > 2 AND 3 > 4 , содержащие только константы.
Встроенные функции, которые считаются свертываемыми SQL Server, включая CAST и CONVERT . Обычно внутренняя функция является свертываемой, если это функция только своих входных данных, а не контекстуальных данных, таких как параметры SET, настройки языка, параметры базы данных, ключи шифрования. Недетерминированные функции не являются свертываемыми. Детерминированные встроенные функции являются свертываемыми за некоторыми исключениями.
Детерминированные методы определяемых пользователем типов CLR и детерминированные скалярные определяемые пользователем функции CLR (начиная с SQL Server 2012 (11.x)). Дополнительные сведения см. в разделе Свертка констант для определяемых пользователем функций и методов среды CLR.

Исключение делается для типов больших объектов. Если выходной тип процесса свертывания является большим типом объекта (text,ntext, image, nvarchar(max), varchar(max), varbinary(max) или XML), SQL Server не сворачивать выражение.

Неупаблемые выражения

Все остальные типы выражения являются несвертываемыми. В частности, несвертываемыми являются следующие типы выражений:

Неконстантные выражения, такие как выражение, результат которого зависит от значения столбца.
Выражения, результат которых зависит от локальной переменной или параметра, такие как @x.
Недетерминированные функции.
Функции TransactSQL, определяемые пользователем 1 .
Выражения, результат которых зависит от языковых настроек.
Выражения, результат которых зависит от параметров SET.
Выражения, результат которых зависит от параметров конфигурации сервера.

1 До SQL Server 2012 (11.x) детерминированные скалярные функции CLR, определяемые пользователем функции и методы определяемых пользователем типов СРЕДЫ CLR, не были свернутыми.

Примеры свертываемых и неотложенных константных выражений

Обратите внимание на следующий запрос:

SELECT * FROM Sales.SalesOrderHeader AS s INNER JOIN Sales.SalesOrderDetail AS d ON s.SalesOrderID = d.SalesOrderID WHERE TotalDue > 117.00 + 1000.00;

PARAMETERIZATION Если параметр базы данных не задан FORCED для этого запроса, то выражение 117.00 + 1000.00 вычисляется и заменяется его результатом до 1117.00 компиляции запроса. Такая свертка констант имеет следующие преимущества.

Выражение не требуется многократно оценивать во время выполнения.
Значение выражения после его вычисления используется оптимизатором запросов для оценки размера результирующего набора части запроса TotalDue > 117.00 + 1000.00 .

С другой стороны, если dbo.f это скалярная определяемая пользователем функция, выражение dbo.f(100) не сложено, так как SQL Server не сворачивать выражения, включающие определяемые пользователем функции, даже если они детерминированы. Дополнительные сведения о параметризации см. в разделе Принудительная параметризация далее в этой статье.

Вычисление выражения

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

Во время компиляции вычисляются следующие встроенные функции и специальные операторы (если их входные данные известны): UPPER , LOWER , RTRIM , DATEPART( YY only ) , GETDATE , CAST и CONVERT . Следующие операторы также вычисляются во время компиляции, если все входные данные известны:

Арифметические операторы: +, -, *, /, unary —
Логические операторы: AND , OR и NOT
Операторы сравнения: , =, <>, LIKE , IS NULL , IS NOT NULL

Остальные функции или операторы не вычисляются оптимизатором запросов во время оценки кратности.

Примеры вычисления выражений во время компиляции

Рассмотрим следующую хранимую процедуру:

USE AdventureWorks2022; GO CREATE PROCEDURE MyProc( @d datetime ) AS SELECT COUNT(*) FROM Sales.SalesOrderHeader WHERE OrderDate > @d+1;

Во время оптимизации инструкции SELECT в процедуре оптимизатор запросов пытается вычислить ожидаемую кратность результирующего набора для условия OrderDate > @d+1 . Выражение @d+1 не является константным, так как @d является параметром. Однако во время оптимизации значение этого параметра известно. Это дает возможность оптимизатору запросов точно оценить размер результирующего набора, что поможет выбрать наилучший план запроса.

Теперь рассмотрим пример, похожий на предыдущий, за исключением того, что локальная переменная @d2 заменена в запросе выражением @d+1 и это выражение вычисляется в инструкции SET вместо вычисления в запросе.

USE AdventureWorks2022; GO CREATE PROCEDURE MyProc2( @d datetime ) AS BEGIN DECLARE @d2 datetime SET @d2 = @d+1 SELECT COUNT(*) FROM Sales.SalesOrderHeader WHERE OrderDate > @d2 END;

SELECT Если инструкция оптимизирована в MyProc2 SQL Server, значение @d2 не известно. Поэтому в оптимизаторе запросов используется оценка по умолчанию для избирательности значений OrderDate > @d2 (в данном случае 30 %).

Обработка других инструкций

Основные шаги, описанные для обработки инструкции SELECT , применимы к другим инструкциям Transact-SQL, таким как INSERT , UPDATE и DELETE . Инструкции UPDATE и DELETE предназначены для набора строк, которые будут изменены или удалены. Идентификация этих строк выполняется так же, как и идентификация исходных строк, определяющих результирующий набор инструкции SELECT . Обе инструкции ( UPDATE и INSERT ) могут содержать встроенные инструкции SELECT , предоставляющие значения данных, которые будут обновлены или вставлены.

Даже инструкции языка описания данных (DDL), такие как CREATE PROCEDURE или ALTER TABLE , в конечном счете приводятся к ряду реляционных операций с таблицами системного каталога, а иногда (например, ALTER TABLE ADD COLUMN ) с таблицами данных.

Рабочие таблицы

Чтобы выполнить логическую операцию, указанную в инструкции Transact-SQL, реляционному модулю может потребоваться создать рабочую таблицу. Рабочие таблицы — это внутренние таблицы, предназначенные для хранения промежуточных результатов. Они создаются для некоторых запросов GROUP BY , ORDER BY или UNION . Например, если предложение ORDER BY ссылается на столбцы, не включенные в индексы, реляционному модулю может потребоваться создать рабочую таблицу, чтобы отсортировать результирующий набор в необходимом порядке. Рабочие таблицы также иногда применяются для временного хранения результатов выполнения части плана запроса. Рабочие таблицы создаются в базе данных tempdb , и когда они больше не нужны, автоматически удаляются.

Разрешение представления

Обработчик запросов SQL Server обращается с индексированными и неиндексированными представлениями по-разному:

Строки индексированного представления хранятся в базе данных в том же формате, что и таблица. Если оптимизатор запросов решает использовать индексированное представление в плане запроса, оно обрабатывается так же, как базовая таблица.
В случае неиндексированного представления хранится только его определение, но не строки. Оптимизатор запросов интегрирует логику из определения представления в план выполнения, который создается для инструкции Transact-SQL, ссылающейся на неиндексированное представление.

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

Если инструкция Transact-SQL ссылается на неиндексированное представление, средство синтаксического анализа и оптимизатор запросов анализируют исходный код инструкции Transact-SQL и представления, разрешая их в один план выполнения. Для инструкции Transact-SQL нет одного плана и отдельного плана для представления.

Рассмотрим следующее представление:

USE AdventureWorks2022; GO CREATE VIEW EmployeeName AS SELECT h.BusinessEntityID, p.LastName, p.FirstName FROM HumanResources.Employee AS h JOIN Person.Person AS p ON h.BusinessEntityID = p.BusinessEntityID; GO

Обе следующие инструкции Transact-SQL, основанные на данном представлении, выполняют одни и те же операции с базовой таблицей и дают одинаковый результат:

/* SELECT referencing the EmployeeName view. */ SELECT LastName AS EmployeeLastName, SalesOrderID, OrderDate FROM AdventureWorks2022.Sales.SalesOrderHeader AS soh JOIN AdventureWorks2022.dbo.EmployeeName AS EmpN ON (soh.SalesPersonID = EmpN.BusinessEntityID) WHERE OrderDate > '20020531'; /* SELECT referencing the Person and Employee tables directly. */ SELECT LastName AS EmployeeLastName, SalesOrderID, OrderDate FROM AdventureWorks2022.HumanResources.Employee AS e JOIN AdventureWorks2022.Sales.SalesOrderHeader AS soh ON soh.SalesPersonID = e.BusinessEntityID JOIN AdventureWorks2022.Person.Person AS p ON e.BusinessEntityID =p.BusinessEntityID WHERE OrderDate > '20020531';

Используя входящую в среду SQL Server Management Studio инструкцию Showplan, нужно убедиться, что для обеих инструкций SELECT реляционный модуль создает один и тот же план выполнения.

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

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

USE AdventureWorks2022; GO CREATE VIEW Person.AddrState WITH SCHEMABINDING AS SELECT a.AddressID, a.AddressLine1, s.StateProvinceCode, s.CountryRegionCode FROM Person.Address a WITH (NOLOCK), Person.StateProvince s WHERE a.StateProvinceID = s.StateProvinceID;

Предположим, что вводится следующий запрос:

SELECT AddressID, AddressLine1, StateProvinceCode, CountryRegionCode FROM Person.AddrState WITH (SERIALIZABLE) WHERE StateProvinceCode = 'WA';

Он завершится ошибкой, так как указание SERIALIZABLE , примененное в запросе к представлению Person.AddrState , при расширении представления распространится как на таблицу Person.Address , так и на таблицу Person.StateProvince . Однако при расширении представления будет также обнаружено указание NOLOCK , связанное с таблицей Person.Address . Из-за конфликта указаний SERIALIZABLE и NOLOCK результирующий запрос окажется неправильным.

Табличные указания PAGLOCK , NOLOCK , ROWLOCK , TABLOCK и TABLOCKX , а также HOLDLOCK , NOLOCK , READCOMMITTED , REPEATABLEREAD и SERIALIZABLE конфликтуют друг с другом.

Указания могут распространяться через уровни вложенных представлений. Предположим, что в запросе указание HOLDLOCK применяется к представлению v1 . При расширении представления v1 выясняется, что представление v2 является частью его определения. Определение v2 включает в себя связанное с одной из его базовых таблиц указание NOLOCK . Однако эта таблица также наследует представленное в запросе указание HOLDLOCK , примененное к представлению v1 . Из-за конфликта указаний NOLOCK и HOLDLOCK запрос завершится ошибкой.

Если в запросе, включающем представление, используется указание FORCE ORDER , порядок соединения таблиц в представлении определяется по позиции представления в конструкции упорядочения. Например, приведенный ниже запрос выбирает данные из трех таблиц и представления:

SELECT * FROM Table1, Table2, View1, Table3 WHERE Table1.Col1 = Table2.Col1 AND Table2.Col1 = View1.Col1 AND View1.Col2 = Table3.Col2; OPTION (FORCE ORDER);

Допустим, что представление View1 определено следующим образом:

CREATE VIEW View1 AS SELECT Colx, Coly FROM TableA, TableB WHERE TableA.ColZ = TableB.Colz;

В этом случае порядок соединения таблиц в плане запроса будет таким: Table1 , Table2 , TableA , TableB , Table3 .

Разрешение индексов в представлениях

Как и в любом индексе, SQL Server выбирает использование индексированного представления в плане запроса только в том случае, если оптимизатор запросов определяет, что это полезно.

Индексированные представления можно создавать в любом выпуске SQL Server. В некоторых выпусках некоторых старых версий SQL Server оптимизатор запросов автоматически рассматривает индексированное представление. В некоторых выпусках некоторых старых версий SQL Server для использования индексированного представления NOEXPAND необходимо использовать указание таблицы. До SQL Server 2016 (13.x) с пакетом обновления 1 (SP1) автоматическое использование индексированного представления оптимизатором запросов поддерживается только в определенных выпусках SQL Server. Так как все выпуски поддерживают автоматическое использование индексированного представления. База данных SQL Azure и Управляемый экземпляр SQL Azure также поддерживают автоматическое использование индексированных представлений без указания NOEXPAND указания.

Оптимизатор запросов SQL Server использует индексированное представление при выполнении следующих условий:

Для следующих параметров сеанса задано ON значение :
- ANSI_NULLS
- ANSI_PADDING
- ANSI_WARNINGS
- ARITHABORT
- CONCAT_NULL_YIELDS_NULL
- QUOTED_IDENTIFIER
- предикатами условия поиска в предложении WHERE;
- операциями соединения;
- Агрегатные функции
- Предложения GROUP BY
- ссылками на таблицы.
Указания READCOMMITTED и READCOMMITTEDLOCK в данном контексте всегда рассматриваются как разные, независимо от уровня изоляции текущей транзакции.

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

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

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

Использование подсказок с индексированных представлений

Чтобы индексы представления не использовались в запросе, можно задать указание запроса EXPAND VIEWS или табличное указание NOEXPAND , чтобы принудительно задействовать индекс для индексированного представления запроса в предложении FROM . Однако оптимизатору запросов следует разрешить динамически определять лучший метод доступа для каждого из запросов. Ограничьте применение указаний EXPAND и NOEXPAND только теми случаями, когда очевидно, что они значительно повысят производительность.
- Параметр EXPAND VIEWS указывает, что оптимизатор запросов не будет использовать индексы представления для всего запроса.
- Если для представления задано указание NOEXPAND , оптимизатор запросов предполагает использование всех индексов, определенных в представлении. NOEXPAND может иметь необязательное предложение INDEX() , которое активирует принудительное применение указанных индексов в оптимизаторе запросов. NOEXPAND можно указать только для индексированного представления и не может быть указан для представления, не индексированного. До SQL Server 2016 (13.x) с пакетом обновления 1 (SP1) автоматическое использование индексированного представления оптимизатором запросов поддерживается только в определенных выпусках SQL Server. Так как все выпуски поддерживают автоматическое использование индексированного представления. База данных SQL Azure и Управляемый экземпляр SQL Azure также поддерживают автоматическое использование индексированных представлений без указания NOEXPAND указания.
Если в запросе, содержащем представление, не заданы ни NOEXPAND , ни EXPAND VIEWS , это представление расширяется для доступа к базовым таблицам. Если запрос представления содержит какие-либо табличные указания, они распространяются на базовые таблицы. (Этот процесс подробно описан в разделе «Разрешение представлений».) Пока указания, имеющиеся в базовых таблицах представления, идентичны, для запроса может устанавливаться соответствие с индексированным представлением. Чаще всего эти указания соответствуют друг другу, поскольку они наследуются непосредственно из представления. Однако если запрос ссылается на таблицы вместо представлений, а указания, применяемые непосредственно к этим таблицам, идентичны, такой запрос не подходит для сопоставления с индексированных представлений. INDEX PAGLOCK Если таблицы, ROWLOCK на которые ссылается запрос после расширения представления, TABLOCKX UPDLOCK или XLOCK подсказки, применяются к таблицам, на которые ссылается запрос после расширения представления, запрос не может соответствовать индексированному представлению.

Если табличное указание в виде INDEX (index_val[ . n] ) ссылок на представление в запросе, и вы также не указываете NOEXPAND указание, то указание индекса игнорируется. Для указания конкретного индекса используйте NOEXPAND .

Обычно, если оптимизатор запросов устанавливает соответствие индексированного представления запросу, все заданные в таблицах или представлениях запроса указания применяются непосредственно к индексированному представлению. Если оптимизатор запросов решил не использовать индексированное представление, все указания распространяются непосредственно на таблицы, на которые ссылается это представление. Дополнительные сведения см. в разделе «Разрешение представлений». Это распространение не применяется к указаниям на присоединение. Они применяются только в той исходной позиции запроса, где они указаны. Указания в соединении не рассматриваются оптимизатором запросов при установке соответствия запроса индексированным представлениям. Если план запроса использует индексированное представление, соответствующее части запроса, содержащего подсказку соединения, то в плане не используется указание соединения.

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

Разрешение распределенных секционированных представлений

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

SQL Server создает интеллектуальные динамические планы, которые позволяют эффективно использовать распределенные запросы для доступа к данным в удаленных таблицах-элементах.
- Обработчик запросов сначала использует OLE DB для получения определений ограничений CHECK для каждой таблицы-элемента. Это позволяет ему определить распределение ключевых значений между таблицами серверов.
- Обработчик запросов сравнивает диапазоны ключей, заданные в инструкции Transact-SQL WHERE , со схемой распределения строк между таблицами-элементами. Затем обработчик запросов строит план выполнения, который использует распределенные запросы для получения только тех удаленных строк, которые требуются для завершения инструкции Transact-SQL. Кроме того, план выполнения строится таким образом, чтобы обращение к удаленным данным или метаданным выполнялось только в тот момент, когда они требуются.
Например, рассмотрим систему, в Customers которой таблица секционируется по серверу1 (от 1 до 3299999), Server2 ( CustomerID от 3300000 до 6599999) и Server3 ( CustomerID CustomerID от 6600000 до 9999999).

Допустим, план выполнения, созданный для этого запроса, выполняется на сервере Server1:
```
SELECT * FROM CompanyData.dbo.Customers WHERE CustomerID BETWEEN 3200000 AND 3400000; 
```
План выполнения этого запроса извлекает строки со значениями ключей CustomerID от 3200000 до 3299999 из локальной таблицы-элемента и вызывает распределенный запрос для получения строк со значениями ключей от 3300000 до 3400000 с сервера Server2.

Обработчик запросов SQL Server также может встраивать динамическую логику в планы выполнения запросов для инструкций Transact-SQL в тех случаях, когда значения ключей во время создания плана неизвестны. Рассмотрим следующую хранимую процедуру:
```
CREATE PROCEDURE GetCustomer @CustomerIDParameter INT AS SELECT * FROM CompanyData.dbo.Customers WHERE CustomerID = @CustomerIDParameter; 
```
SQL Server не может предсказать, какое значение ключа будет предоставляться параметром @CustomerIDParameter при каждом выполнении процедуры. Так как значение ключа невозможно спрогнозировать, обработчик запросов также не может предсказать, к какой таблице-члену придется получить доступ. В этом случае SQL Server создает план выполнения с условной логикой, называемой динамическими фильтрами, для управления доступом к таблицам-элементам на основе значения входного параметра. Если предположить, что хранимая процедура GetCustomer выполнена на сервере Server1, логику плана выполнения можно представить следующим образом:
```
IF @CustomerIDParameter BETWEEN 1 and 3299999 Retrieve row from local table CustomerData.dbo.Customer_33 ELSE IF @CustomerIDParameter BETWEEN 3300000 and 6599999 Retrieve row from linked table Server2.CustomerData.dbo.Customer_66 ELSE IF @CustomerIDParameter BETWEEN 6600000 and 9999999 Retrieve row from linked table Server3.CustomerData.dbo.Customer_99 
```
Иногда SQL Server создает динамические планы выполнения даже для непараметризованных запросов. Оптимизатор запросов может параметризовать запрос так, чтобы план выполнения можно было использовать повторно. Если оптимизатор запросов параметризует запрос, ссылающийся на секционированное представление, он не будет знать, находятся ли нужные строки в заданной базовой таблице. В дальнейшем ему придется использовать динамические фильтры в планах выполнения.

Хранимая процедура и выполнение триггера

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

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

Кэширование и повторное использование плана выполнения

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

В кэше планов есть два хранилища для всех скомпилированных планов:
- хранилище кэша Object Plans (OBJCP), которое используется для планов, связанных с сохраняемыми объектами (хранимыми процедурами, функциями и триггерами);
- хранилище кэша SQL Plans (SQLCP), которое используется для планов, связанных с автоматически параметризуемыми, динамическими или подготовленными запросами.
Следующий запрос предоставляет сведения об использовании памяти для этих двух хранилищ:
```
SELECT * FROM sys.dm_os_memory_clerks WHERE name LIKE '%plans%'; 
```
В кэше планов есть еще два хранилища, которые не используются для хранения планов.
- Хранилище кэша Bound Trees (PHDR) предназначено для структур данных, используемых во время компиляции плана для представлений, ограничений и значений по умолчанию. Эти структуры называются связанными деревьями или деревьями алгебризатора.
- Хранилище кэша Extended Stored Procedures (XPROC) предназначено для предварительно определенных системных процедур, таких как sp_executeSql или xp_cmdshell , которые определены с помощью библиотеки DLL, а не инструкций Transact-SQL. Кэшированная структура содержит только имя функции и имя библиотеки DLL, в которой реализована процедура.
В SQL Server планы выполнения состоят из следующих основных компонентов.
- Скомпилированный план (или план запроса)
  План запроса, создаваемый в результате компиляции, является, как правило, реентерабельной структурой данных только для чтения, которую могут использовать любое число пользователей. В нем хранятся следующие сведения:
  - Физические операторы, реализующие операцию, описанную логическими операторами.
  - порядок этих операторов, определяющий очередность доступа к данным, их фильтрации и агрегирования;
  - предполагаемое количество строк, передаваемых через операторы.
  Заметка В более новых версиях ядра СУБД также хранятся сведения об объектах статистики, используемых для оценки кратности.
  
  При выполнении любой инструкции Transact-SQL в SQL Server ядро СУБД сначала просматривает кэш планов, проверяя, нет ли в нем плана выполнения для такой же инструкции Transact-SQL. Инструкция Transact-SQL считается существующей, если она точно соответствует выполнявшейся ранее инструкции Transact-SQL с кэшированным планом, символ за символом. SQL Server повторно использует все найденные планы, что позволяет избежать перекомпиляции инструкций Transact-SQL. Если план выполнения не существует, SQL Server создает новый план выполнения для запроса.
  
  Планы выполнения для некоторых инструкций Transact-SQL не сохраняются в кэше планов. К ним относятся инструкции массовых операций, работающие в rowstore, а также инструкции, содержащие строковые литералы размером более 8 КБ. Такие планы существуют только во время выполнения запроса.
  
  SQL Server реализует эффективный алгоритм поиска существующих планов выполнения для любой инструкции Transact-SQL. В большинстве систем ресурсы, затрачиваемые на поиск готового плана, всегда меньше ресурсов, затрачиваемых на повторную компиляцию каждой инструкции Transact-SQL.
  
  Алгоритмы поиска соответствия инструкции Transact-SQL существующему неиспользуемому плану выполнения в кэше планов требуют, чтобы все ссылки на объекты были полными. Например, предположим, что Person является схемой по умолчанию для пользователя, выполняющего инструкции SELECT ниже. Хотя в этом примере не требуется, чтобы Person таблица была полностью выполнена, это означает, что второй оператор не соответствует существующему плану, но третий соответствует:
```
USE AdventureWorks2022; GO SELECT * FROM Person; GO SELECT * FROM Person.Person; GO SELECT * FROM Person.Person; GO 
```
  Изменение любого из следующих параметров SET для заданного выполнения повлияет на возможность повторного использования планов, так как ядро СУБД выполняет константную свертывание , и эти параметры влияют на результаты таких выражений:
  Похожие публикации:

Как проектировать запросы на изменение данных sql