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

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server даБаза данных SQL Azure нетAzure Synapse Analytics (хранилище данных SQL) нетParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Компонент Компонент SQL Server Database EngineSQL Server Database Engine обрабатывает запросы к различным архитектурам хранения данных, таким как локальные таблицы, секционированные таблицы и таблицы, распределенные по нескольким серверам.The Компонент SQL Server Database EngineSQL Server Database Engine processes queries on various data storage architectures such as local tables, partitioned tables, and tables distributed across multiple servers. В следующих разделах описано, как SQL ServerSQL Server обрабатывает запросы и оптимизирует повторное использование запросов с помощью кэширования плана выполнения.The following topics cover how SQL ServerSQL Server processes queries and optimizes query reuse through execution plan caching.

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

Компонент SQL Server Database EngineSQL Server Database Engine может обрабатывать инструкции Transact-SQLTransact-SQL в двух разных режимах:The Компонент SQL Server Database EngineSQL Server Database Engine can process Transact-SQLTransact-SQL statements using two distinct processing modes:

  • выполнение в построчном режиме;Row mode execution
  • выполнение в пакетном режиме.Batch mode execution

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

Построчный режим выполнения — это метод обработки запросов, применяемый с традиционными таблицами RDMBS, при котором данные сохраняются в строковом формате.Row mode execution is a query processing method used with traditional RDMBS tables, where data is stored in row format. При выполнении запроса к данным в таблицах, хранящих строки, операторы дерева выполнения и дочерние операторы считывают каждую требуемую строку по всем столбцам, указанным в схеме таблицы.When a query is executed and accesses data in row store tables, the execution tree operators and child operators read each required row, across all the columns specified in the table schema. Из каждой считанной строки SQL ServerSQL Server затем извлекает столбцы, необходимые для результирующего набора, как указано в инструкции SELECT, предикате JOIN или предикате фильтра.From each row that is read, SQL ServerSQL Server then retrieves the columns that are required for the result set, as referenced by a SELECT statement, JOIN predicate, or filter predicate.

Примечание

Построчный режим выполнения очень эффективен в сценариях OLTP, но может быть не так эффективен при обращении к большим объемам данных, например при работе с хранилищем данных.Row mode execution is very efficient for OLTP scenarios, but can be less efficient when scanning large amounts of data, for example in Data Warehousing scenarios.

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

Пакетный режим выполнения — это метод обработки запросов, при котором обрабатываются сразу несколько строк (поэтому он и называется пакетным).Batch mode execution is a query processing method used to process multiple rows together (hence the term batch). Каждый столбец из пакета сохраняется как вектор в отдельной области памяти. Таким образом, обработка в пакетном режиме основана на векторах.Each column within a batch is stored as a vector in a separate area of memory, so batch mode processing is vector-based. Кроме того, при обработке в пакетном режиме применяются алгоритмы, оптимизированные для многоядерных ЦП и увеличенной пропускной способности памяти, что характерно для современного оборудования.Batch mode processing also uses algorithms that are optimized for the multi-core CPUs and increased memory throughput that are found on modern hardware.

Выполнение в пакетном режиме тесно интегрировано и оптимизировано для взаимодействия с форматом хранения columnstore.Batch mode execution is closely integrated with, and optimized around, the columnstore storage format. Обработка в пакетном режиме применяется к сжатым данным, когда это возможно, и исключает необходимость применения оператора обмена, используемого в построчном режиме выполнения.Batch mode processing operates on compressed data when possible, and eliminates the exchange operator used by row mode execution. Это позволяет повысить уровень параллелизма и производительность.The result is better parallelism and faster performance.

Когда запрос выполняется в пакетном режиме и получает доступ к данным в индексах columnstore, операторы дерева выполнения и дочерние операторы считывают сразу несколько строк по сегментам столбцов.When a query is executed in batch mode, and accesses data in columnstore indexes, the execution tree operators and child operators read multiple rows together in column segments. SQL ServerSQL Server считывает только столбцы, которые требуются в результатах, как указано в инструкции SELECT, предикате JOIN или предикате фильтра.reads only the columns required for the result, as referenced by a SELECT statement, JOIN predicate, or filter predicate.
Дополнительные сведения об индексах columnstore см. в статье Архитектура индексов columnstore.For more information on columnstore indexes, see Columnstore Index Architecture.

Примечание

Пакетный режим выполнения очень эффективен в сценариях хранилищ данных, в которых считываются и вычисляются большие объемы данных.Batch mode execution is very efficient Data Warehousing scenarios, where large amounts of data are read and aggregated.

Обработка инструкций SQLSQL Statement Processing

Обработка одиночной инструкции Transact-SQLTransact-SQL — наиболее распространенный способ, с помощью которого SQL ServerSQL Server выполняет инструкции Transact-SQLTransact-SQL.Processing a single Transact-SQLTransact-SQL statement is the most basic way that SQL ServerSQL Server executes Transact-SQLTransact-SQL statements. Шаги, используемые для обработки одиночной инструкции SELECT , которая обращается только к таблицам локальной базы (а не к представлениям и не к удаленным таблицам), иллюстрируют основной процесс.The steps used to process a single SELECT statement that references only local base tables (no views or remote tables) illustrates the basic process.

Приоритет логических операторовLogical Operator Precedence

При использовании в инструкции нескольких логических операторов первым вычисляется NOT, затем AND и, наконец, OR.When more than one logical operator is used in a statement, NOT is evaluated first, then AND, and finally OR. Арифметические и побитовые операторы выполняются до логических.Arithmetic, and bitwise, operators are handled before logical operators. Дополнительные сведения см. в разделе Приоритет операторов.For more information, see Operator Precedence.

В приведенном ниже примере условие цвета относится к модели продукта 21, но не к модели продукта 20, так как оператора AND имеет приоритет над оператором OR.In the following example, the color condition pertains to product model 21, and not to product model 20, because AND has precedence over OR.

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

Можно изменить смысл запроса, добавляя скобки, чтобы добиться вычисления OR сначала.You can change the meaning of the query by adding parentheses to force evaluation of the OR first. В приведенном ниже запросе будут найдены модели 20 и 21 красного цвета.The following query finds only products under models 20 and 21 that are red.

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

С помощью скобок, даже если они не требуются, можно улучшить читаемость запросов и уменьшить вероятность совершения незаметной ошибки из-за приоритета операторов.Using parentheses, even when they are not required, can improve the readability of queries, and reduce the chance of making a subtle mistake because of operator precedence. Использование скобок практически не влияет на производительность.There is no significant performance penalty in using parentheses. Следующий пример более понятен, чем исходный, хотя синтаксически они равноправны.The following example is more readable than the original example, although they are syntactically the same.

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

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

Инструкция SELECT является непроцедурной. Она не определяет точные шаги, которые сервер базы данных должен предпринять для получения запрошенных данных.A SELECT statement is non-procedural; it does not state the exact steps that the database server should use to retrieve the requested data. Это означает, что сервер базы данных должен проанализировать инструкцию для определения самого эффективного способа извлечения запрошенных данных.This means that the database server must analyze the statement to determine the most efficient way to extract the requested data. Это упоминается как оптимизация инструкции SELECT .This is referred to as optimizing the SELECT statement. Компонент, который выполняет эти действия, называется оптимизатором запросов.The component that does this is called the Query Optimizer. Входные данные оптимизатора запросов включают сам запрос, схему базы данных (определения таблиц и индексов) и статистику базы данных.The input to the Query Optimizer consists of the query, the database schema (table and index definitions), and the database statistics. Выходные данные оптимизатора запросов — это план выполнения запроса, который иногда называется планом запроса или выполнения.The output of the Query Optimizer is a query execution plan, sometimes referred to as a query plan, or execution plan. Содержимое плана выполнения описывается более подробно далее в этом разделе.The contents of an execution plan are described in more detail later in this topic.

Входные и выходные данные оптимизатора запросов при оптимизации одиночной инструкции SELECT показаны на следующей схеме.The inputs and outputs of the Query Optimizer during optimization of a single SELECT statement are illustrated in the following diagram:

query_processor_io

Инструкция SELECT определяет только следующее.A SELECT statement defines only the following:

  • Формат результирующего набора.The format of the result set. Он указан, главным образом, в списке выбора.This is specified mostly in the select list. Однако другие предложения, например ORDER BY и GROUP BY , также затрагивают конечную форму результирующего набора.However, other clauses such as ORDER BY and GROUP BY also affect the final form of the result set.
  • Таблицы, которые содержат исходные данные.The tables that contain the source data. Они указываются в предложении FROM .This is specified in the FROM clause.
  • Логическую связь между таблицами для инструкции SELECT .How the tables are logically related for the purposes of the SELECT statement. Это определяется в спецификациях соединения, которые могут появляться в предложении WHERE или в предложении ON , следующем за предложением FROM.This is defined in the join specifications, which may appear in the WHERE clause or in an ON clause following FROM.
  • Условия, которым строки в исходных таблицах должны соответствовать для выбора их инструкцией SELECT .The conditions that the rows in the source tables must satisfy to qualify for the SELECT statement. Они указываются в предложениях WHERE и HAVING .These are specified in the WHERE and HAVING clauses.

План выполнения запроса представляет собой определение следующего.A query execution plan is a definition of the following:

  • Последовательности, в которой происходит обращение к исходным таблицам.The sequence in which the source tables are accessed. Как правило, существует много последовательностей, в которых сервер базы данных может обращаться к базовым таблицам для построения результирующего набора.Typically, there are many sequences in which the database server can access the base tables to build the result set. Например, если инструкция SELECT ссылается на три таблицы, сервер базы данных сначала может обратиться к TableA, использовать данные из TableA для извлечения соответствующих строк из TableB, а затем использовать данные из TableB для извлечения данных из TableC.For example, if the SELECT statement references three tables, the database server could first access TableA, use the data from TableA to extract matching rows from TableB, and then use the data from TableB to extract data from TableC. Другие последовательности, в которых сервер базы данных может обращаться к таблицам:The other sequences in which the database server could access the tables are:
    TableC, TableB, TableAилиTableC, TableB, TableA, or
    TableB, TableA, TableCилиTableB, TableA, TableC, or
    TableB, TableC, TableAилиTableB, TableC, TableA, or
    TableC, TableA, TableBTableC, TableA, TableB

  • Методы, используемые для извлечения данных из каждой таблицы.The methods used to extract data from each table.
    Есть различные методы для обращения к данным в каждой таблице.Generally, there are different methods for accessing the data in each table. Если необходимы только несколько строк с определенными ключевыми значениями, то сервер базы данных может использовать индекс.If only a few rows with specific key values are required, the database server can use an index. Если необходимы все строки в таблице, то сервер базы данных может пропустить индексы и выполнить просмотр таблицы.If all the rows in the table are required, the database server can ignore the indexes and perform a table scan. Если необходимы все строки в таблице, но есть индекс, ключевые столбцы которого находятся в ORDER BY, то просмотр индекса вместо просмотра таблицы позволит избежать отдельный сортировки результирующего набора.If all the rows in a table are required but there is an index whose key columns are in an ORDER BY, performing an index scan instead of a table scan may save a separate sort of the result set. Если таблица является очень маленькой, то просмотры таблицы могут быть самым эффективным методом для практически всех обращений к таблице.If a table is very small, table scans may be the most efficient method for almost all access to the table.

  • Методы, используемые для вычислений, а также фильтрации, статистической обработки и сортировки данных из каждой таблицы.The methods used to compute calculations, and how to filter, aggregate, and sort data from each table.
    По мере доступа к данным из таблиц можно разными способами выполнять вычисления над данными (например, вычисления скалярных значений), а также статистическую обработку и сортировку данных, как определено в тексте запроса (например, при использовании предложения GROUP BY или ORDER BY) и их фильтрацию (например, при использовании предложения WHERE или HAVING).As data is accessed from tables, there are different methods to perform calculations over data such as computing scalar values, and to aggregate and sort data as defined in the query text, for example when using a GROUP BY or ORDER BY clause, and how to filter data, for example when using a WHERE or HAVING clause.

Процесс выбора одного плана выполнения из множества потенциально возможных планов называется оптимизацией.The process of selecting one execution plan from potentially many possible plans is referred to as optimization. Оптимизатор запросов является одним из самых важных компонентов Компонент Database EngineDatabase Engine.The Query Optimizer is one of the most important components of the Компонент Database EngineDatabase Engine. Хотя для анализа запроса и выбора плана оптимизатору запросов требуются некоторые накладные расходы, эти накладные расходы обычно многократно окупаются, когда оптимизатор запроса выбирает эффективный план выполнения.While some overhead is used by the Query Optimizer to analyze the query and select a plan, this overhead is typically saved several-fold when the Query Optimizer picks an efficient execution plan. Например, двум строительным компаниям могут быть предоставлены идентичные проекты дома.For example, two construction companies can be given identical blueprints for a house. Если одна компания потратит сначала несколько дней на планирование того, как она будет строить дом, а другая компания начнет строить без планирования, то компания, которая потратит время на планирование проекта, вероятно, закончит первой.If one company spends a few days at the beginning to plan how they will build the house, and the other company begins building without planning, the company that takes the time to plan their project will probably finish first.

Оптимизатор запросов SQL ServerSQL Server основан на оценке стоимости.The SQL ServerSQL Server Query Optimizer is a cost-based optimizer. Каждому возможному плану выполнения соответствует некоторая стоимость, определенная в терминах объема использованных вычислительных ресурсов.Each possible execution plan has an associated cost in terms of the amount of computing resources used. Оптимизатор запросов должен проанализировать возможные планы и выбрать один файл с самой низкой предполагаемой стоимостью.The Query Optimizer must analyze the possible plans and choose the one with the lowest estimated cost. Для некоторых сложных инструкций SELECT есть тысячи возможных планов выполнения.Some complex SELECT statements have thousands of possible execution plans. В этих случаях оптимизатор запросов не анализирует все возможные комбинации.In these cases, the Query Optimizer does not analyze all possible combinations. Вместо этого он использует сложные алгоритмы поиска плана выполнения, имеющего стоимость, близкую к минимальной возможной стоимости.Instead, it uses complex algorithms to find an execution plan that has a cost reasonably close to the minimum possible cost.

Оптимизатор запросов SQL ServerSQL Server не выбирает план выполнения только на основе самой низкой стоимости ресурсов. Он выбирает такой план, который возвращает результаты пользователю при разумной стоимости ресурсов и делает это быстрее по сравнению с другими планами.The SQL ServerSQL Server Query Optimizer does not choose only the execution plan with the lowest resource cost; it chooses the plan that returns results to the user with a reasonable cost in resources and that returns the results the fastest. Например, параллельная обработка запроса обычно использует больше ресурсов, чем его последовательная обработка, но завершает выполнение запроса быстрее.For example, processing a query in parallel typically uses more resources than processing it serially, but completes the query faster. Оптимизатор запросов SQL ServerSQL Server будет использовать план параллельного выполнения для возврата результатов, если это не окажет неблагоприятного влияния на загрузку сервера.The SQL ServerSQL Server Query Optimizer will use a parallel execution plan to return results if the load on the server will not be adversely affected.

Оптимизатор запросов SQL ServerSQL Server полагается на статистику распределения при оценке затрат на ресурсы для различных методов извлечения сведений из таблицы или индекса.The SQL ServerSQL Server Query Optimizer relies on distribution statistics when it estimates the resource costs of different methods for extracting information from a table or index. Статистика распределения хранится для столбцов и индексов и содержит сведения о плотности 1 базовых данных.Distribution statistics are kept for columns and indexes, and hold information on the density1 of the underlying data. Она указывает избирательность значений в определенном индексе или столбце.This is used to indicate the selectivity of the values in a particular index or column. Например, в таблице, представляющей автомобили, много автомобилей имеют одного производителя, но каждый автомобиль имеет уникальный идентификационный номер транспортного средства (VIN).For example, in a table representing cars, many cars have the same manufacturer, but each car has a unique vehicle identification number (VIN). Индекс по VIN является более избирательным, чем индекс по производителям, так как VIN с меньшей плотностью, чем производитель.An index on the VIN is more selective than an index on the manufacturer, because VIN has lower density than manufacturer. Если статистика индекса не является текущей, оптимизатор запросов, возможно, не сделает лучший выбор для текущего состояния таблицы.If the index statistics are not current, the Query Optimizer may not make the best choice for the current state of the table. Дополнительные сведения о плотности см. в разделе Статистика.For more information about densities, see Statistics.

1 Плотность определяет распределение уникальных значений в данных или среднее количество повторяющихся значений для данного столбца.1 Density defines the distribution of unique values that exist in the data, or the average number of duplicate values for a given column. По мере повышения плотности избирательность значения повышается.As density decreases, selectivity of a value increases.

Оптимизатор запросов SQL ServerSQL Server очень важен, так как позволяет серверу базы данных динамически изменять конфигурацию в ответ на меняющиеся условия в базе данных без участия программиста или администратора базы данных.The SQL ServerSQL Server Query Optimizer is important because it enables the database server to adjust dynamically to changing conditions in the database without requiring input from a programmer or database administrator. Это дает возможность программистам сосредоточиться на описании конечного результата запроса.This enables programmers to focus on describing the final result of the query. Они могут положиться на то, что каждый раз при выполнении инструкции оптимизатор запросов SQL ServerSQL Server будет создавать эффективный план выполнения с учетом состояния базы данных.They can trust that the SQL ServerSQL Server Query Optimizer will build an efficient execution plan for the state of the database every time the statement is run.

Примечание

В SQL Server Management StudioSQL Server Management Studio есть три способа отображения планов выполнения:SQL Server Management StudioSQL Server Management Studio has three options to display execution plans:

  • Предполагаемый план выполнения  — это скомпилированный план, созданный оптимизатором запросов.The Estimated Execution Plan, which is the compiled plan, as produced by the Query Optimizer.
  • Действительный план выполнения  — это скомпилированный план с контекстом выполнения.The Actual Execution Plan, which is the same as the compiled plan plus its execution context. Сюда входят сведения времени выполнения, доступные после завершения выполнения, такие как предупреждения времени выполнения, а также в более новых версиях Компонент Database EngineDatabase Engine время, затраченное на выполнение, и время ЦП.This includes runtime information available after the execution completes, such as execution warnings, or in newer versions of the Компонент Database EngineDatabase Engine, the elapsed and CPU time used during execution.
  • Статистика активных запросов  — это скомпилированный план с контекстом выполнения.The Live Query Statistics, which is the same as the compiled plan plus its execution context. Сюда входят сведения о времени выполнения, которые обновляются каждую секунду.This includes runtime information during execution progress, and is updated every second. Эти сведения включают в себя, например, фактическое количество строк, передаваемых через операторы.Runtime information includes for example the actual number of rows flowing through the operators.

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

Основные шаги, используемые SQL ServerSQL Server для обработки одиночной инструкции SELECT, включают следующее:The basic steps that SQL ServerSQL Server uses to process a single SELECT statement include the following:

  1. Средство анализа просматривает инструкцию SELECT и разбивает ее на логические единицы, такие как ключевые слова, выражения, операторы и идентификаторы.The parser scans the SELECT statement and breaks it into logical units such as keywords, expressions, operators, and identifiers.
  2. Строится дерево запроса, иногда называемое деревом последовательности, с описанием логических шагов, необходимых для преобразования исходных данных в формат, требуемый результирующему набору.A query tree, sometimes referred to as a sequence tree, is built describing the logical steps needed to transform the source data into the format required by the result set.
  3. Оптимизатор запросов анализирует различные способы, с помощью которых можно обратиться к исходным таблицам.The Query Optimizer analyzes different ways the source tables can be accessed. Затем он выбирает ряд шагов, которые возвращают результаты быстрее всего и используют меньше ресурсов.It then selects the series of steps that return the results fastest while using fewer resources. Дерево запроса обновляется для записи этого точного ряда шагов.The query tree is updated to record this exact series of steps. Конечную, оптимизированную версию дерева запроса называют планом выполнения.The final, optimized version of the query tree is called the execution plan.
  4. Реляционный механизм начинает реализовывать план выполнения.The relational engine starts executing the execution plan. В ходе обработки шагов, требующих данных из базовых таблиц, реляционный механизм запрашивает у подсистемы хранилища передачу данных из набора строк, указанных реляционным механизмом.As the steps that require data from the base tables are processed, the relational engine requests that the storage engine pass up data from the rowsets requested from the relational engine.
  5. Реляционный механизм преобразует данные, возвращенные подсистемой хранилища, в заданный для результирующего набора формат и возвращает результирующий набор клиенту.The relational engine processes the data returned from the storage engine into the format defined for the result set and returns the result set to the client.

Свертывание констант и вычисление выраженийConstant Folding and Expression Evaluation

SQL ServerSQL Server предварительно вычисляет некоторые постоянные выражения для улучшения производительности запросов.evaluates some constant expressions early to improve query performance. Это называет сверткой констант.This is referred to as constant folding. Константа — это литерал языка Transact-SQLTransact-SQL, такой как 3, 'ABC', '2005-12-31', 1.0e3 или 0x12345678.A constant is a Transact-SQLTransact-SQL literal, such as 3, 'ABC', '2005-12-31', 1.0e3, or 0x12345678.

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

SQL ServerSQL Server использует свертку констант со следующими типами выражений.uses constant folding with the following types of expressions:

  • Арифметические выражения, такие как 1+1, 5/3*2, которые содержат только константы.Arithmetic expressions, such as 1+1, 5/3*2, that contain only constants.
  • Логические выражения, такие как 1=1, 1>2 и 3>4, которые содержат только константы.Logical expressions, such as 1=1 and 1>2 AND 3>4, that contain only constants.
  • Встроенные функции, которые считаются сворачиваемыми SQL ServerSQL Server, включая CAST и CONVERT.Built-in functions that are considered foldable by SQL ServerSQL Server, including CAST and CONVERT. Обычно внутренняя функция является свертываемой, если это функция только своих входных данных, а не контекстуальных данных, таких как параметры SET, настройки языка, параметры базы данных, ключи шифрования.Generally, an intrinsic function is foldable if it is a function of its inputs only and not other contextual information, such as SET options, language settings, database options, and encryption keys. Недетерминированные функции не являются свертываемыми.Nondeterministic functions are not foldable. Детерминированные встроенные функции являются свертываемыми за некоторыми исключениями.Deterministic built-in functions are foldable, with some exceptions.
  • Детерминированные методы определяемых пользователем типов данных CLR и детерминированные скалярные определяемые пользователем функции CLR (начиная с SQL Server 2012 (11.x)SQL Server 2012 (11.x)).Deterministic methods of CLR user-defined types and deterministic scalar-valued CLR user-defined functions (starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x)). Дополнительные сведения см. в разделе Свертка констант для определяемых пользователем функций и методов среды CLR.For more information, see Constant Folding for CLR User-Defined Functions and Methods.

Примечание

Исключение делается для типов больших объектов.An exception is made for large object types. Если в процессе свертки для выходных данных используется тип больших объектов (text, ntext, image, nvarchar(max), varchar(max), varbinary(max) или XML), то SQL ServerSQL Server не свертывает такое выражение.If the output type of the folding process is a large object type (text,ntext, image, nvarchar(max), varchar(max), varbinary(max), or XML), then SQL ServerSQL Server does not fold the expression.

Несворачиваемые выраженияNonfoldable Expressions

Все остальные типы выражения являются несвертываемыми.All other expression types are not foldable. В частности, несвертываемыми являются следующие типы выражений.In particular, the following types of expressions are not foldable:

  • Неконстантные выражения, такие как выражение, результат которого зависит от значения столбца.Nonconstant expressions such as an expression whose result depends on the value of a column.
  • Выражения, результат которых зависит от локальной переменной или параметра, такие как @x.Expressions whose results depend on a local variable or parameter, such as @x.
  • Недетерминированные функции.Nondeterministic functions.
  • Определяемые пользователем функции Transact-SQLTransact-SQL1.User-defined Transact-SQLTransact-SQL functions1.
  • Выражения, результат которых зависит от языковых настроек.Expressions whose results depend on language settings.
  • Выражения, результат которых зависит от параметров SET.Expressions whose results depend on SET options.
  • Выражения, результат которых зависит от параметров конфигурации сервера.Expressions whose results depend on server configuration options.

1 До SQL Server 2012 (11.x)SQL Server 2012 (11.x) детерминированные скалярные определяемые пользователем функции CLR и методы определяемых пользователем типов данных CLR не были свертываемыми.1 Before SQL Server 2012 (11.x)SQL Server 2012 (11.x), deterministic scalar-valued CLR user-defined functions and methods of CLR user-defined types were not foldable.

Примеры свертываемых и несвертываемых постоянных выраженийExamples of Foldable and Nonfoldable Constant Expressions

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

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) перед компиляцией запроса.If the PARAMETERIZATION database option is not set to FORCED for this query, then the expression 117.00 + 1000.00 is evaluated and replaced by its result, 1117.00, before the query is compiled. Такая свертка констант имеет следующие преимущества.Benefits of this constant folding include the following:

  • Нет необходимости вычислять выражение несколько раз во время выполнения.The expression does not have to be evaluated repeatedly at run time.
  • Значение выражения после его вычисления используется оптимизатором запросов для оценки размера результирующего набора части запроса TotalDue > 117.00 + 1000.00.The value of the expression after it is evaluated is used by the Query Optimizer to estimate the size of the result set of the portion of the query TotalDue > 117.00 + 1000.00.

С другой стороны, если dbo.f является скалярной, определяемой пользователем функцией, выражение dbo.f(100) не свертывается, так как SQL ServerSQL Server не свертывает выражения, которые включают определяемые пользователем функции, даже если они детерминированы.On the other hand, if dbo.f is a scalar user-defined function, the expression dbo.f(100) is not folded, because SQL ServerSQL Server does not fold expressions that involve user-defined functions, even if they are deterministic. Дополнительные сведения о параметризации см. в разделе Принудительная параметризация далее в этой статье.For more information on parameterization, see Forced Parameterization later in this article.

Вычисление выраженийExpression Evaluation

В дополнение некоторые выражения, которые не свертываются, но аргументы которых известны во время компиляции, где аргументы являются параметрами или постоянными, вычисляются механизмом оценки размера (количества элементов) набора результатов, который является частью оптимизатора во время оптимизации.In addition, some expressions that are not constant folded but whose arguments are known at compile time, whether the arguments are parameters or constants, are evaluated by the result-set size (cardinality) estimator that is part of the optimizer during optimization.

Во время компиляции вычисляются следующие встроенные функции и специальные операторы (если их входные данные известны): UPPER, LOWER, RTRIM, DATEPART( YY only ), GETDATE, CAST и CONVERT.Specifically, the following built-in functions and special operators are evaluated at compile time if all their inputs are known: UPPER, LOWER, RTRIM, DATEPART( YY only ), GETDATE, CAST, and CONVERT. Следующие операторы также вычисляются во время компиляции, если все входные данные известны:The following operators are also evaluated at compile time if all their inputs are known:

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

Остальные функции или операторы не вычисляются оптимизатором запросов во время оценки кратности.No other functions or operators are evaluated by the Query Optimizer during cardinality estimation.

Примеры вычисления выражений во время компиляцииExamples of Compile-Time Expression Evaluation

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

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

Во время оптимизации инструкции SELECT в процедуре оптимизатор запросов пытается вычислить ожидаемую кратность результирующего набора для условия OrderDate > @d+1.During optimization of the SELECT statement in the procedure, the Query Optimizer tries to evaluate the expected cardinality of the result set for the condition OrderDate > @d+1. Выражение @d+1 не свертывается, так как @d является параметром.The expression @d+1 is not constant-folded, because @d is a parameter. Однако во время оптимизации значение этого параметра известно.However, at optimization time, the value of the parameter is known. Это дает возможность оптимизатору запросов точно оценить размер результирующего набора, что поможет выбрать наилучший план запроса.This allows the Query Optimizer to accurately estimate the size of the result set, which helps it select a good query plan.

Теперь рассмотрим пример, похожий на предыдущий, за исключением того, что локальная переменная @d2 заменена в запросе выражением @d+1 и это выражение вычисляется в инструкции SET вместо вычисления в запросе.Now consider an example similar to the previous one, except that a local variable @d2 replaces @d+1 in the query and the expression is evaluated in a SET statement instead of in the query.

USE AdventureWorks2014;
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 ServerSQL Server, значение переменной @d2 неизвестно.When the SELECT statement in MyProc2 is optimized in SQL ServerSQL Server, the value of @d2 is not known. Поэтому в оптимизаторе запросов используется оценка по умолчанию для избирательности значений OrderDate > @d2 (в данном случае 30 %).Therefore, the Query Optimizer uses a default estimate for the selectivity of OrderDate > @d2, (in this case 30 percent).

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

Основные шаги, описанные для обработки инструкции SELECT, применимы к другим инструкциям Transact-SQLTransact-SQL, таким как INSERT, UPDATE и DELETE.The basic steps described for processing a SELECT statement apply to other Transact-SQLTransact-SQL statements such as INSERT, UPDATE, and DELETE. ИнструкцииUPDATE и DELETE предназначены для набора строк, которые будут изменены или удалены.UPDATE and DELETE statements both have to target the set of rows to be modified or deleted. Идентификация этих строк выполняется так же, как и идентификация исходных строк, определяющих результирующий набор инструкции SELECT .The process of identifying these rows is the same process used to identify the source rows that contribute to the result set of a SELECT statement. Обе инструкции (UPDATE и INSERT) могут содержать встроенные инструкции SELECT, предоставляющие значения данных, которые будут обновлены или вставлены.The UPDATE and INSERT statements may both contain embedded SELECT statements that provide the data values to be updated or inserted.

Даже инструкции языка описания данных (DDL), такие как CREATE PROCEDURE или ALTER TABLE, в конечном счете приводятся к ряду реляционных операций с таблицами системного каталога, а иногда (например, ALTER TABLE ADD COLUMN) с таблицами данных.Even Data Definition Language (DDL) statements, such as CREATE PROCEDURE or ALTER TABLE, are ultimately resolved to a series of relational operations on the system catalog tables and sometimes (such as ALTER TABLE ADD COLUMN) against the data tables.

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

Чтобы выполнить логическую операцию, указанную в инструкции Transact-SQLTransact-SQL, реляционному модулю может потребоваться создать рабочую таблицу.The Relational Engine may need to build a worktable to perform a logical operation specified in an Transact-SQLTransact-SQL statement. Рабочие таблицы — это внутренние таблицы, предназначенные для хранения промежуточных результатов.Worktables are internal tables that are used to hold intermediate results. Они создаются для некоторых запросов GROUP BY, ORDER BYили UNION .Worktables are generated for certain GROUP BY, ORDER BY, or UNION queries. Например, если предложение ORDER BY ссылается на столбцы, не включенные в индексы, реляционному модулю может потребоваться создать рабочую таблицу, чтобы отсортировать результирующий набор в необходимом порядке.For example, if an ORDER BY clause references columns that are not covered by any indexes, the Relational Engine may need to generate a worktable to sort the result set into the order requested. Рабочие таблицы также иногда применяются для временного хранения результатов выполнения части плана запроса.Worktables are also sometimes used as spools that temporarily hold the result of executing a part of a query plan. Рабочие таблицы строятся в базе данных tempdb и после того, как они больше не нужны, автоматически удаляются.Worktables are built in tempdb and are dropped automatically when they are no longer needed.

Разрешение представленийView Resolution

Обработчик запросов SQL ServerSQL Server обращается с индексированными и неиндексированными представлениями по-разному:The SQL ServerSQL Server query processor treats indexed and nonindexed views differently:

  • Строки индексированного представления хранятся в базе данных в том же формате, что и таблица.The rows of an indexed view are stored in the database in the same format as a table. Если оптимизатор запросов решает использовать индексированное представление в плане запроса, оно обрабатывается так же, как базовая таблица.If the Query Optimizer decides to use an indexed view in a query plan, the indexed view is treated the same way as a base table.
  • В случае неиндексированного представления хранится только его определение, но не строки.Only the definition of a nonindexed view is stored, not the rows of the view. Оптимизатор запросов интегрирует логику из определения представления в план выполнения, который создается для инструкции Transact-SQLTransact-SQL, ссылающейся на неиндексированное представление.The Query Optimizer incorporates the logic from the view definition into the execution plan it builds for the Transact-SQLTransact-SQL statement that references the nonindexed view.

Решение об использовании индексированного представления принимается оптимизатором запросов SQL ServerSQL Server на основе тех же принципов, что и решение об использовании индекса таблицы.The logic used by the SQL ServerSQL Server Query Optimizer to decide when to use an indexed view is similar to the logic used to decide when to use an index on a table. Если данные индексированного представления охватывают всю инструкцию Transact-SQLTransact-SQL или ее часть, и оптимизатор запросов определит, что использовать индекс представления выгодно с точки зрения стоимости, он выберет индекс независимо от того, имеется ли в запросе ссылка на представление по имени.If the data in the indexed view covers all or part of the Transact-SQLTransact-SQL statement, and the Query Optimizer determines that an index on the view is the low-cost access path, the Query Optimizer will choose the index regardless of whether the view is referenced by name in the query.

Если инструкция Transact-SQLTransact-SQL ссылается на неиндексированное представление, средство синтаксического анализа и оптимизатор запросов анализируют исходный код инструкции Transact-SQLTransact-SQL и представления, разрешая их в один план выполнения.When an Transact-SQLTransact-SQL statement references a nonindexed view, the parser and Query Optimizer analyze the source of both the Transact-SQLTransact-SQL statement and the view and then resolve them into a single execution plan. Отдельных планов для инструкции Transact-SQLTransact-SQL и представления нет.There is not one plan for the Transact-SQLTransact-SQL statement and a separate plan for the view.

Рассмотрим следующее представление:For example, consider the following view:

USE AdventureWorks2014;
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-SQLTransact-SQL, основанных на данном представлении, выполняют одни и те же операции над базовой таблицей, формируя одинаковый результат:Based on this view, both of these Transact-SQLTransact-SQL statements perform the same operations on the base tables and produce the same results:

/* SELECT referencing the EmployeeName view. */
SELECT LastName AS EmployeeLastName, SalesOrderID, OrderDate
FROM AdventureWorks2014.Sales.SalesOrderHeader AS soh
JOIN AdventureWorks2014.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 AdventureWorks2014.HumanResources.Employee AS e 
JOIN AdventureWorks2014.Sales.SalesOrderHeader AS soh
  ON soh.SalesPersonID = e.BusinessEntityID
JOIN AdventureWorks2014.Person.Person AS p
  ON e.BusinessEntityID =p.BusinessEntityID
WHERE OrderDate > '20020531';

Функция Showplan SQL ServerSQL Server Management Studio показывает, что реляционное ядро создает один и тот же план выполнения для обеих инструкций SELECT.The SQL ServerSQL Server Management Studio Showplan feature shows that the relational engine builds the same execution plan for both of these SELECT statements.

Использование указаний с представлениямиUsing Hints with Views

Указания, связываемые с представлениями в запросах, могут конфликтовать с другими указаниями, которые обнаруживаются при расширении представления для доступа к его базовым таблицам.Hints that are placed on views in a query may conflict with other hints that are discovered when the view is expanded to access its base tables. Когда это происходит, запрос возвращает ошибку.When this occurs, the query returns an error. Взгляните, например, на следующее представление, в определение которого входит табличное указание:For example, consider the following view that contains a table hint in its definition:

USE AdventureWorks2014;
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;

Предположим, что вводится следующий запрос:Now suppose you enter this query:

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

Он завершится ошибкой, так как указание SERIALIZABLE , примененное в запросе к представлению Person.AddrState , при расширении представления распространится как на таблицу Person.Address , так и на таблицу Person.StateProvince .The query fails, because the hint SERIALIZABLE that is applied on view Person.AddrState in the query is propagated to both tables Person.Address and Person.StateProvince in the view when it is expanded. Однако при расширении представления будет также обнаружено указание NOLOCK , связанное с таблицей Person.Address.However, expanding the view also reveals the NOLOCK hint on Person.Address. Из-за конфликта указаний SERIALIZABLE и NOLOCK результирующий запрос окажется неправильным.Because the SERIALIZABLE and NOLOCK hints conflict, the resulting query is incorrect.

Табличные указания PAGLOCK, NOLOCK, ROWLOCK, TABLOCKи TABLOCKX , а также HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREADи SERIALIZABLE конфликтуют друг с другом.The PAGLOCK, NOLOCK, ROWLOCK, TABLOCK, or TABLOCKX table hints conflict with each other, as do the HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE table hints.

Указания могут распространяться через уровни вложенных представлений.Hints can propagate through levels of nested views. Предположим, что в запросе указание HOLDLOCK применяется к представлению v1.For example, suppose a query applies the HOLDLOCK hint on a view v1. При расширении представления v1 выясняется, что представление v2 является частью его определения.When v1 is expanded, we find that view v2 is part of its definition. Определениеv2включает в себя связанное с одной из его базовых таблиц указание NOLOCK .v2's definition includes a NOLOCK hint on one of its base tables. Однако эта таблица также наследует представленное в запросе указание HOLDLOCK , примененное к представлению v1.But this table also inherits the HOLDLOCK hint from the query on view v1. Из-за конфликта указаний NOLOCK и HOLDLOCK запрос завершится ошибкой.Because the NOLOCK and HOLDLOCK hints conflict, the query fails.

Если в запросе, включающем представление, используется указание FORCE ORDER , порядок соединения таблиц в представлении определяется по позиции представления в конструкции упорядочения.When the FORCE ORDER hint is used in a query that contains a view, the join order of the tables within the view is determined by the position of the view in the ordered construct. Например, приведенный ниже запрос выбирает данные из трех таблиц и представления:For example, the following query selects from three tables and a view:

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 определено следующим образом:And View1 is defined as shown in the following:

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

В этом случае порядок соединения таблиц в плане запроса будет таким: Table1, Table2, TableA, TableB, Table3.The join order in the query plan is Table1, Table2, TableA, TableB, Table3.

Разрешение индексов для представленийResolving Indexes on Views

Как и при работе с любым индексом, SQL ServerSQL Server использует индексированное представление в плане запроса только в том случае, если оптимизатор запросов определит, что это целесообразно.As with any index, SQL ServerSQL Server chooses to use an indexed view in its query plan only if the Query Optimizer determines it is beneficial to do so.

Индексированные представления можно создавать в любом выпуске SQL ServerSQL Server.Indexed views can be created in any edition of SQL ServerSQL Server. В некоторых версиях SQL ServerSQL Server оптимизатор запросов автоматически учитывает индексированные представления.In some editions of some versions of SQL ServerSQL Server, the Query Optimizer automatically considers the indexed view. В некоторых версиях SQL ServerSQL Server для использования индексированного представления необходимо применить табличное указание NOEXPAND.In some editions of some versions of SQL ServerSQL Server, to use an indexed view, the NOEXPAND table hint must be used. Пояснения см. в документации для каждой версии.For clarification, see the documentation for each version.

Оптимизатор запросов SQL ServerSQL Server использует индексированные представления при соблюдении следующих условий.The SQL ServerSQL Server Query Optimizer uses an indexed view when the following conditions are met:

  • Для следующих параметров сеанса установлено значение ON:These session options are set to ON:
    • ANSI_NULLS
    • ANSI_PADDING
    • ANSI_WARNINGS
    • ARITHABORT
    • CONCAT_NULL_YIELDS_NULL
    • QUOTED_IDENTIFIER
  • Для параметра сеанса NUMERIC_ROUNDABORT установлено значение OFF.The NUMERIC_ROUNDABORT session option is set to OFF.
  • Оптимизатор запросов находит соответствие между столбцами индексированного представления и элементами запроса, например:The Query Optimizer finds a match between the view index columns and elements in the query, such as the following:
    • предикатами условия поиска в предложении WHERE;Search condition predicates in the WHERE clause
    • операциями соединения;Join operations
    • Агрегатные функцииAggregate functions
    • ПредложенияGROUP BYGROUP BY clauses
    • ссылками на таблицы.Table references
  • Предполагаемые затраты на использование индекса имеют меньшую стоимость по сравнению с любыми механизмами доступа, имеющимися в распоряжении оптимизатора запросов.The estimated cost for using the index has the lowest cost of any access mechanisms considered by the Query Optimizer.
  • Каждая таблица, на которую ссылается запрос (либо прямо, либо при расширении представления для доступа к его базовым таблицам), соответствующая табличной ссылке в индексированном представлении, должна иметь в запросе точно такой же набор указаний.Every table referenced in the query (either directly, or by expanding a view to access its underlying tables) that corresponds to a table reference in the indexed view must have the same set of hints applied on it in the query.

Примечание

Указания READCOMMITTED и READCOMMITTEDLOCK в данном контексте всегда рассматриваются как разные, независимо от уровня изоляции текущей транзакции.The READCOMMITTED and READCOMMITTEDLOCK hints are always considered different hints in this context, regardless of the current transaction isolation level.

За исключением требований к параметрам SET и табличным указаниям, это те же самые правила, по которым оптимизатор запросов выясняет, подходит ли индекс таблицы для выполнения запроса.Other than the requirements for the SET options and table hints, these are the same rules that the Query Optimizer uses to determine whether a table index covers a query. Для использования индексированного представления в запросе больше ничего указывать не нужно.Nothing else has to be specified in the query for an indexed view to be used.

Запрос не обязательно должен ссылаться в предложении FROM на индексированное представление, чтобы оптимизатор запросов его использовал.A query does not have to explicitly reference an indexed view in the FROM clause for the Query Optimizer to use the indexed view. Если запрос ссылается на столбцы в базовой таблице, которые также присутствуют в индексированном представлении, и оптимизатор запросов определяет, что индексированное представление будет иметь самую низкую стоимость механизма доступа, он применит индексированное представление точно так же, как он применяет индекс базовой таблицы, если на него отсутствуют прямые ссылки в запросе.If the query contains references to columns in the base tables that are also present in the indexed view, and the Query Optimizer estimates that using the indexed view provides the lowest cost access mechanism, the Query Optimizer chooses the indexed view, similar to the way it chooses base table indexes when they are not directly referenced in a query. Оптимизатор запросов может применить представление и в том случае, если оно содержит столбцы, на которые не ссылается запрос, если это представление обеспечивает самую низкую стоимость доступа к одному или нескольким столбцам, указанным в запросе.The Query Optimizer may choose the view when it contains columns that are not referenced by the query, as long as the view offers the lowest cost option for covering one or more of the columns specified in the query.

Индексированное представление, указанное в предложении FROM , оптимизатор запросов рассматривает как стандартное представление.The Query Optimizer treats an indexed view referenced in the FROM clause as a standard view. В начале процесса оптимизации оптимизатор запросов расширяет определение представления в запрос,The Query Optimizer expands the definition of the view into the query at the start of the optimization process. а затем выполняет в индексированном представлении поиск соответствий.Then, indexed view matching is performed. В окончательном плане выполнения, выбранном оптимизатором запросов, может быть использовано индексированное представление, или план может материализовать необходимые данные за счет доступа к базовым таблицам этого представления.The indexed view may be used in the final execution plan selected by the Query Optimizer, or instead, the plan may materialize necessary data from the view by accessing the base tables referenced by the view. Оптимизатор запросов выбирает вариант с наименьшей стоимостью.The Query Optimizer chooses the lowest-cost alternative.

Использование указаний с индексированными представлениямиUsing Hints with Indexed Views

Чтобы индексы представления не использовались в запросе, можно задать указание запроса EXPAND VIEWS или табличное указание NOEXPAND , чтобы принудительно задействовать индекс для индексированного представления запроса в предложении FROM .You can prevent view indexes from being used for a query by using the EXPAND VIEWS query hint, or you can use the NOEXPAND table hint to force the use of an index for an indexed view specified in the FROM clause of a query. Однако оптимизатору запросов следует разрешить динамически определять лучший метод доступа для каждого из запросов.However, you should let the Query Optimizer dynamically determine the best access methods to use for each query. Ограничьте применение указаний EXPAND и NOEXPAND только теми случаями, когда очевидно, что они значительно повысят производительность.Limit your use of EXPAND and NOEXPAND to specific cases where testing has shown that they improve performance significantly.

Параметр EXPAND VIEWS указывает, что оптимизатор запросов не будет использовать индексы представления для всего запроса.The EXPAND VIEWS option specifies that the Query Optimizer not use any view indexes for the whole query.

Если для представления задано указание NOEXPAND , оптимизатор запросов предполагает использование всех индексов, определенных в представлении.When NOEXPAND is specified for a view, the Query Optimizer considers using any indexes defined on the view. NOEXPAND может иметь необязательное предложение INDEX() , которое активирует принудительное применение указанных индексов в оптимизаторе запросов.NOEXPAND specified with the optional INDEX() clause forces the Query Optimizer to use the specified indexes. NOEXPAND может быть указано только для индексированного представления и не применяется для представлений без индексов.NOEXPAND can be specified only for an indexed view and cannot be specified for a view not indexed.

Если в запросе, содержащем представление, не заданы ни NOEXPAND , ни EXPAND VIEWS , это представление расширяется для доступа к базовым таблицам.When neither NOEXPAND nor EXPAND VIEWS is specified in a query that contains a view, the view is expanded to access underlying tables. Если запрос представления содержит какие-либо табличные указания, они распространяются на базовые таблицы.If the query that makes up the view contains any table hints, these hints are propagated to the underlying tables. (Этот процесс подробно описан в разделе "Разрешение представлений".) Пока указания, имеющиеся в базовых таблицах представления, идентичны, для запроса может устанавливаться соответствие с индексированным представлением.(This process is explained in more detail in View Resolution.) As long as the set of hints that exists on the underlying tables of the view are identical to each other, the query is eligible to be matched with an indexed view. Чаще всего эти указания соответствуют друг другу, поскольку они наследуются непосредственно из представления.Most of the time, these hints will match each other, because they are being inherited directly from the view. Однако если запрос ссылается на таблицы, а не на представления, и применяемые к этим таблицам указания неидентичны, то для такого запроса соответствие с индексированным представлением устанавливаться не может.However, if the query references tables instead of views, and the hints applied directly on these tables are not identical, then such a query is not eligible for matching with an indexed view. Если указания INDEX, PAGLOCK, ROWLOCK, TABLOCKX, UPDLOCKили XLOCK применяются к таблицам, на которые запрос ссылается после расширения представления, для этого запроса не может быть установлено соответствие с индексированным представлением.If the INDEX, PAGLOCK, ROWLOCK, TABLOCKX, UPDLOCK, or XLOCK hints apply to the tables referenced in the query after view expansion, the query is not eligible for indexed view matching.

Если табличное указание вида INDEX (index_val[ ,...n] ) ссылается на представление в запросе, а указание NOEXPAND не задано, указание индекса не обрабатывается.If a table hint in the form of INDEX (index_val[ ,...n] ) references a view in a query and you do not also specify the NOEXPAND hint, the index hint is ignored. Для указания конкретного индекса используйте NOEXPAND.To specify use of a particular index, use NOEXPAND.

Обычно, если оптимизатор запросов устанавливает соответствие индексированного представления запросу, все заданные в таблицах или представлениях запроса указания применяются непосредственно к индексированному представлению.Generally, when the Query Optimizer matches an indexed view to a query, any hints specified on the tables or views in the query are applied directly to the indexed view. Если оптимизатор запросов решил не использовать индексированное представление, все указания распространяются непосредственно на таблицы, на которые ссылается это представление.If the Query Optimizer chooses not to use an indexed view, any hints are propagated directly to the tables referenced in the view. Дополнительные сведения см. в разделе "Разрешение представлений".For more information, see View Resolution. Это не относится к указаниям соединения.This propagation does not apply to join hints. Они применяются только в той исходной позиции запроса, где они указаны.They are applied only in their original position in the query. Указания в соединении оптимизатором запросов при установке соответствия запроса индексированным представлениям не рассматриваются.Join hints are not considered by the Query Optimizer when matching queries to indexed views. Если план запроса использует индексированное представление, которое совпадает с частью запроса, содержащей указание соединения, последний в данном плане не используется.If a query plan uses an indexed view that matches part of a query that contains a join hint, the join hint is not used in the plan.

В определении индексированных представлений указания не допускаются.Hints are not allowed in the definitions of indexed views. В режимах совместимости 80 и выше SQL ServerSQL Server пропускает указания при работе с определениями индексированных представлений и при выполнении содержащих их запросов.In compatibility mode 80 and higher, SQL ServerSQL Server ignores hints inside indexed view definitions when maintaining them, or when executing queries that use indexed views. В режиме совместимости 80 использование указаний в определениях индексированных представлений не вызывает ошибок синтаксиса — они просто пропускаются.Although using hints in indexed view definitions will not produce a syntax error in 80 compatibility mode, they are ignored.

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

Обработчик запросов SQL ServerSQL Server оптимизирует производительность распределенных секционированных представлений.The SQL ServerSQL Server query processor optimizes the performance of distributed partitioned views. При оптимизации распределенных секционированных представлений важно минимизировать количество данных, передаваемых между серверами.The most important aspect of distributed partitioned view performance is minimizing the amount of data transferred between member servers.

SQL ServerSQL Server интеллектуальные динамические планы, которые позволяют эффективно использовать распределенные запросы для доступа к данным в таблицах удаленных серверов.builds intelligent, dynamic plans that make efficient use of distributed queries to access data from remote member tables:

  • Обработчик запросов сначала использует OLE DB для получения определений ограничений CHECK для каждой таблицы-элемента.The Query Processor first uses OLE DB to retrieve the check constraint definitions from each member table. Это позволяет ему определить распределение ключевых значений между таблицами серверов.This allows the query processor to map the distribution of key values across the member tables.
  • Обработчик запросов сравнивает диапазоны ключей, заданные в инструкции Transact-SQLTransact-SQLWHERE, со схемой распределения строк между таблицами-элементами.The Query Processor compares the key ranges specified in an Transact-SQLTransact-SQL statement WHERE clause to the map that shows how the rows are distributed in the member tables. Затем обработчик запросов строит план выполнения, который использует распределенные запросы для получения только тех удаленных строк, которые требуются для завершения инструкции Transact-SQLTransact-SQL.The query processor then builds a query execution plan that uses distributed queries to retrieve only those remote rows that are required to complete the Transact-SQLTransact-SQL statement. Кроме того, план выполнения строится таким образом, чтобы обращение к удаленным данным или метаданным выполнялось только в тот момент, когда они требуются.The execution plan is also built in such a way that any access to remote member tables, for either data or metadata, are delayed until the information is required.

Например, рассмотрим систему, в которой пользовательская таблица разделена на три секции на серверах Server1 (CustomerID от 1 до 3299999), Server2 (CustomerID от 3300000 до 6599999) и Server3 (CustomerID от 6600000 до 9999999).For example, consider a system where a customers table is partitioned across Server1 (CustomerID from 1 through 3299999), Server2 (CustomerID from 3300000 through 6599999), and Server3 (CustomerID from 6600000 through 9999999).

Допустим, план выполнения, созданный для этого запроса, выполняется на сервере Server1:Consider the execution plan built for this query executed on Server1:

SELECT *
FROM CompanyData.dbo.Customers
WHERE CustomerID BETWEEN 3200000 AND 3400000;

План выполнения этого запроса извлекает строки со значениями ключей CustomerID от 3200000 до 3299999 из локальной таблицы-элемента и вызывает распределенный запрос для получения строк со значениями ключей от 3300000 до 3400000 с сервера Server2.The execution plan for this query extracts the rows with CustomerID key values from 3200000 through 3299999 from the local member table, and issues a distributed query to retrieve the rows with key values from 3300000 through 3400000 from Server2.

Обработчик запросов SQL ServerSQL Server также может встраивать динамическую логику в планы выполнения запросов для инструкций Transact-SQLTransact-SQL в тех случаях, когда значения ключей во время создания плана неизвестны.The SQL ServerSQL Server Query Processor can also build dynamic logic into query execution plans for Transact-SQLTransact-SQL statements in which the key values are not known when the plan must be built. Рассмотрим следующую хранимую процедуру:For example, consider this stored procedure:

CREATE PROCEDURE GetCustomer @CustomerIDParameter INT
AS
SELECT *
FROM CompanyData.dbo.Customers
WHERE CustomerID = @CustomerIDParameter;

SQL ServerSQL Server не может предсказать, какое значение ключа будет выдавать параметр @CustomerIDParameter каждый раз при выполнении процедуры.cannot predict what key value will be supplied by the @CustomerIDParameter parameter every time the procedure is executed. Поскольку значение ключа предсказать нельзя, обработчик запросов не может заранее определить, к какой таблице потребуется доступ.Because the key value cannot be predicted, the query processor also cannot predict which member table will have to be accessed. В этом случае SQL ServerSQL Server встраивает в план выполнения условную логику, называемую динамическими фильтрами, для управления доступом к удаленным таблицам на основе значения входного параметра.To handle this case, SQL ServerSQL Server builds an execution plan that has conditional logic, referred to as dynamic filters, to control which member table is accessed, based on the input parameter value. Если предположить, что хранимая процедура GetCustomer выполнена на сервере Server1, логику плана выполнения можно представить следующим образом:Assuming the GetCustomer stored procedure was executed on Server1, the execution plan logic can be represented as shown in the following:

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 ServerSQL Server строит динамические планы даже для непараметризованных запросов.SQL ServerSQL Server sometimes builds these types of dynamic execution plans even for queries that are not parameterized. Оптимизатор запросов может параметризовать запрос так, чтобы план выполнения можно было использовать повторно.The Query Optimizer may parameterize a query so that the execution plan can be reused. Если оптимизатор запросов параметризует запрос, ссылающийся на секционированное представление, он не будет знать, находятся ли нужные строки в заданной базовой таблице.If the Query Optimizer parameterizes a query referencing a partitioned view, the Query Optimizer can no longer assume the required rows will come from a specified base table. В дальнейшем ему придется использовать динамические фильтры в планах выполнения.It will then have to use dynamic filters in the execution plan.

Выполнение хранимых процедур и триггеровStored Procedure and Trigger Execution

SQL ServerSQL Server хранит только исходный код хранимых процедур и триггеров.stores only the source for stored procedures and triggers. При выполнении хранимой процедуры или триггера в первый раз исходный код компилируется в план выполнения.When a stored procedure or trigger is first executed, the source is compiled into an execution plan. Если очередной вызов хранимой процедуры или триггера будет инициирован до устаревания плана выполнения, реляционный механизм обнаружит существующий план и использует его повторно.If the stored procedure or trigger is again executed before the execution plan is aged from memory, the relational engine detects the existing plan and reuses it. Если план устарел и был удален из памяти, будет создан новый план.If the plan has aged out of memory, a new plan is built. Этот процесс похож на то, как SQL ServerSQL Server обрабатывает все инструкции Transact-SQLTransact-SQL.This process is similar to the process SQL ServerSQL Server follows for all Transact-SQLTransact-SQL statements. Основное преимущество хранимых процедур и триггеров SQL ServerSQL Server над пакетами динамического кода Transact-SQLTransact-SQL в плане быстродействия заключается в том, что их инструкции Transact-SQLTransact-SQL всегда остаются постоянными.The main performance advantage that stored procedures and triggers have in SQL ServerSQL Server compared with batches of dynamic Transact-SQLTransact-SQL is that their Transact-SQLTransact-SQL statements are always the same. Благодаря этому реляционный механизм может с легкостью сопоставлять их с любыми существующими планами выполнения.Therefore, the relational engine easily matches them with any existing execution plans. Это облегчает повторное использование планов хранимых процедур и триггеров.Stored procedure and trigger plans are easily reused.

Планы выполнения хранимых процедур и триггеров обрабатываются отдельно от плана выполнения пакета, вызвавшего хранимую процедуру или приведшего к срабатыванию триггера.The execution plan for stored procedures and triggers is executed separately from the execution plan for the batch calling the stored procedure or firing the trigger. Это способствует повторному использованию планов выполнения хранимых процедур и триггеров.This allows for greater reuse of the stored procedure and trigger execution plans.

Кэширование и повторное использование плана выполненияExecution Plan Caching and Reuse

В SQL ServerSQL Server есть пул памяти, предназначенный для хранения планов выполнения и буферов данных.SQL ServerSQL Server has a pool of memory that is used to store both execution plans and data buffers. Процентное соотношение размера пула, выделенного для планов выполнения и буферов данных, динамически изменяется в зависимости от состояния системы.The percentage of the pool allocated to either execution plans or data buffers fluctuates dynamically, depending on the state of the system. Часть пула памяти, используемого для хранения планов выполнения, называется кэшем планов.The part of the memory pool that is used to store execution plans is referred to as the plan cache.

В кэше планов есть два хранилища для всех скомпилированных планов:The plan cache has two stores for all compiled plans:

  • хранилище кэша Object Plans (OBJCP), которое используется для планов, связанных с сохраняемыми объектами (хранимыми процедурами, функциями и триггерами);The Object Plans cache store (OBJCP) used for plans related to persisted objects (stored procedures, functions, and triggers).
  • хранилище кэша SQL Plans (SQLCP), которое используется для планов, связанных с автоматически параметризуемыми, динамическими или подготовленными запросами.The SQL Plans cache store (SQLCP) used for plans related to autoparameterized, dynamic, or prepared queries.

Следующий запрос предоставляет сведения об использовании памяти для этих двух хранилищ:The query below provides information about memory usage for these two cache stores:

SELECT * FROM sys.dm_os_memory_clerks
WHERE name LIKE '%plans%';

Примечание

В кэше планов есть еще два хранилища, которые не используются для хранения планов.The plan cache has two additional stores that are not used for storing plans:

  • Хранилище кэша Bound Trees (PHDR) предназначено для структур данных, используемых во время компиляции плана для представлений, ограничений и значений по умолчанию.The Bound Trees cache store (PHDR) used for data structures used during plan compilation for views, constraints, and defaults. Эти структуры называются связанными деревьями или деревьями алгебризатора.These structures are known as Bound Trees or Algebrizer Trees.
  • Хранилище кэша Extended Stored Procedures (XPROC) предназначено для предварительно определенных системных процедур, таких как sp_executeSql или xp_cmdshell, которые определены с помощью библиотеки DLL, а не инструкций Transact-SQL.The Extended Stored Procedures cache store (XPROC) used for predefined system procedures, like sp_executeSql or xp_cmdshell, that are defined using a DLL, not using Transact-SQL statements. Кэшированная структура содержит только имя функции и имя библиотеки DLL, в которой реализована процедура.The cached structure contains only the function name and the DLL name in which the procedure is implemented.

В SQL ServerSQL Server планы выполнения состоят из следующих основных компонентов.SQL ServerSQL Server execution plans have the following main components:

  • Скомпилированный план (или план запроса)Compiled Plan (or Query Plan)
    План запроса, создаваемый в результате компиляции, является, как правило, реентерабельной структурой данных только для чтения, которую могут использовать любое число пользователей.The query plan produced by the compilation process is mostly a re-entrant, read-only data structure used by any number of users. В нем хранятся следующие сведения:It stores information about:

    • физические операторы, которые реализуют действия, описанные логическими операторами;Physical operators which implement the operation described by logical operators.

    • порядок этих операторов, определяющий очередность доступа к данным, их фильтрации и агрегирования;The order of these operators, which determines the order in which data is accessed, filtered, and aggregated.

    • предполагаемое количество строк, передаваемых через операторы.The number of estimated rows flowing through the operators.

      Примечание

      В более новых версиях Компонент Database EngineDatabase Engine также сохраняются сведения об объектах статистики, которые использовались для оценки кратности.In newer versions of the Компонент Database EngineDatabase Engine, information about the statistics objects that were used for Cardinality Estimation is also stored.

    • Какие вспомогательные объекты необходимо создать, например рабочие таблицы или рабочие файлы в tempdb.What support objects must be created, such as worktables or workfiles in tempdb. Контекст пользователя или сведения времени выполнения в плане запроса не хранятся.No user context or runtime information is stored in the query plan. В памяти содержится одна или две копии плана запроса (но не более): одна — для всех последовательных выполнений, а другая — для всех параллельных выполнений.There are never more than one or two copies of the query plan in memory: one copy for all serial executions and another for all parallel executions. Одна параллельная копия обслуживает все параллельные выполнения независимо от степени параллелизма.The parallel copy covers all parallel executions, regardless of their degree of parallelism.

  • Контекст выполнения Execution Context
    Для каждого пользователя, который в настоящий момент выполняет запрос, имеется структура данных, которая содержит данные, относящиеся к данному выполнению, например значения параметров.Each user that is currently executing the query has a data structure that holds the data specific to their execution, such as parameter values. Эта структура данных называется контекстом выполнения.This data structure is referred to as the execution context. Структуры данных контекста выполнения являются повторно используемыми, в отличие от их содержимого.The execution context data structures are reused, but their content is not. Если другой пользователь выполняет тот же запрос, структуры данных инициализируются повторно контекстом нового пользователя.If another user executes the same query, the data structures are reinitialized with the context for the new user.

    execution_context

При выполнении любой инструкции Transact-SQLTransact-SQL в SQL ServerSQL Server Компонент Database EngineDatabase Engine сначала просматривает кэш планов, проверяя, нет ли в нем плана выполнения для такой же инструкции Transact-SQLTransact-SQL.When any Transact-SQLTransact-SQL statement is executed in SQL ServerSQL Server, the Компонент Database EngineDatabase Engine first looks through the plan cache to verify that an existing execution plan for the same Transact-SQLTransact-SQL statement exists. Инструкция Transact-SQLTransact-SQL считается существующей, если она точно соответствует выполнявшейся ранее инструкции Transact-SQLTransact-SQL с кэшированным планом, символ за символом.The Transact-SQLTransact-SQL statement qualifies as existing if it literally matches a previously executed Transact-SQLTransact-SQL statement with a cached plan, character per character. SQL ServerSQL Server повторно использует все найденные планы, что позволяет избежать перекомпиляции инструкций Transact-SQLTransact-SQL.reuses any existing plan it finds, saving the overhead of recompiling the Transact-SQLTransact-SQL statement. Если не найдено ни одного существующего плана, SQL ServerSQL Server формирует для этого запроса новый план.If no execution plan exists, SQL ServerSQL Server generates a new execution plan for the query.

Примечание

Планы выполнения для некоторых инструкций Transact-SQLTransact-SQL не сохраняются в кэше планов. К ним относятся инструкции массовых операций, работающие в rowstore, а также инструкции, содержащие строковые литералы размером более 8 КБ.The execution plans for some Transact-SQLTransact-SQL statements are not persisted in the plan cache, such as bulk operation statements running on rowstore or statements containing string literals larger than 8 KB in size. Такие планы существуют только во время выполнения запроса.These plans only exist while the query is being executed.

SQL ServerSQL Server реализует эффективный алгоритм поиска существующих планов выполнения для любой инструкции Transact-SQLTransact-SQL.has an efficient algorithm to find any existing execution plans for any specific Transact-SQLTransact-SQL statement. В большинстве систем ресурсы, затрачиваемые на поиск готового плана, всегда меньше ресурсов, затрачиваемых на повторную компиляцию каждой инструкции Transact-SQLTransact-SQL.In most systems, the minimal resources that are used by this scan are less than the resources that are saved by being able to reuse existing plans instead of compiling every Transact-SQLTransact-SQL statement.

Алгоритмы поиска соответствия инструкции Transact-SQLTransact-SQL существующему неиспользуемому плану выполнения в кэше планов требуют, чтобы все ссылки на объекты были полными.The algorithms to match new Transact-SQLTransact-SQL statements to existing, unused execution plans in the plan cache require that all object references be fully qualified. Например, предположим, что Person является схемой по умолчанию для пользователя, выполняющего инструкции SELECT ниже.For example, assume that Person is the default schema for the user executing the below SELECT statements. Хотя в этом примере для выполнения не обязательно, чтобы таблица Person была полной, это означает, что вторая инструкция не соответствует существующему плану, однако третья инструкция соответствует:While in this example it is not required that the Person table is fully qualified to execute, it means that the second statement is not matched with an existing plan, but the third is matched:

USE AdventureWorks2014;
GO
SELECT * FROM Person;
GO
SELECT * FROM Person.Person;
GO
SELECT * FROM Person.Person;
GO

Изменение любого из следующих параметров SET для данного выполнения повлияет на возможность повторного использования планов, так как Компонент Database EngineDatabase Engine выполняет свертку констант и эти параметры влияют на результаты таких выражений:Changing any of the following SET options for a given execution will affect the ability to reuse plans, because the Компонент Database EngineDatabase Engine performs constant folding and these options affect the results of such expressions:

ANSI_NULL_DFLT_OFFANSI_NULL_DFLT_OFF FORCEPLANFORCEPLAN ARITHABORTARITHABORT
DATEFIRSTDATEFIRST ANSI_PADDINGANSI_PADDING NUMERIC_ROUNDABORTNUMERIC_ROUNDABORT
ANSI_NULL_DFLT_ONANSI_NULL_DFLT_ON LANGUAGELANGUAGE CONCAT_NULL_YIELDS_NULLCONCAT_NULL_YIELDS_NULL
DATEFORMATDATEFORMAT ANSI_WARNINGSANSI_WARNINGS QUOTED_IDENTIFIERQUOTED_IDENTIFIER
ANSI_NULLSANSI_NULLS NO_BROWSETABLENO_BROWSETABLE ANSI_DEFAULTSANSI_DEFAULTS

Кэширование нескольких планов для одного запросаCaching multiple plans for the same query

Запросы и планы выполнения являются уникально идентифицируемыми в Компонент Database EngineDatabase Engine, как отпечатки пальцев.Queries and execution plans are uniquely identifiable in the Компонент Database EngineDatabase Engine, much like a fingerprint:

  • Хэш-значение плана запроса — это двоичное хэш-значение, вычисленное для плана выполнения данного запроса и используемое для уникальной идентификации планов выполнения со сходной логикой.The query plan hash is a binary hash value calculated on the execution plan for a given query, and used to uniquely identify similar execution plans.
  • Хэш-значение для запроса — это двоичное хэш-значение, вычисленное для текста Transact-SQLTransact-SQL запроса и используемое для уникальной идентификации запросов.The query hash is a binary hash value calculated on the Transact-SQLTransact-SQL text of a query, and is used to uniquely identify queries.

Скомпилированный план можно получить из кэша планов с помощью дескриптора плана, который представляет собой временный идентификатор, сохраняющий свое значение, только пока план остается в кэше.A compiled plan can be retrieved from the plan cache using a Plan Handle, which is a transient identifier that remains constant only while the plan remains in the cache. Дескриптор плана — это хэш-значение, которое выводится из скомпилированного плана целого пакета.The plan handle is a hash value derived from the compiled plan of the entire batch. Дескриптор скомпилированного плана остается неизменным даже в том случае, если одна или несколько инструкций в пакете перекомпилируются.The plan handle for a compiled plan remains the same even if one or more statements in the batch get recompiled.

Примечание

Если план был скомпилирован для пакета, а не для одной инструкции, то планы для отдельных инструкций в пакете можно получить с помощью дескриптора плана и смещений инструкций.If a plan was compiled for a batch instead of a single statement, the plan for individual statements in the batch can be retrieved using the plan handle and statement offsets.
Динамическое административное представление sys.dm_exec_requests содержит для каждой записи столбцы statement_start_offset и statement_end_offset, которые ссылаются на выполняемую в настоящее время инструкцию выполняющегося пакета или сохраняемого объекта.The sys.dm_exec_requests DMV contains the statement_start_offset and statement_end_offset columns for each record, which refer to the currently executing statement of a currently executing batch or persisted object. Дополнительные сведения см. в статье sys.dm_exec_requests (Transact-SQL).For more information, see sys.dm_exec_requests (Transact-SQL).
Динамическое административное представление sys.dm_exec_query_stats также содержит для каждой записи столбцы, ссылающиеся на положение инструкции внутри пакета или сохраняемого объекта.The sys.dm_exec_query_stats DMV also contains these columns for each record, which refer to the position of a statement within a batch or persisted object. Дополнительные сведения см. в статье sys.dm_exec_query_stats (Transact-SQL).For more information, see sys.dm_exec_query_stats (Transact-SQL).

Фактический текст Transact-SQLTransact-SQL пакета хранится в отдельной области памяти, которая не связана с кэшем планов и называется кэшем SQL Manager (SQLMGR).The actual Transact-SQLTransact-SQL text of a batch is stored in a separate memory space from the plan cache, called the SQL Manager cache (SQLMGR). Текст Transact-SQLTransact-SQL для скомпилированного плана можно получить из кэша SQL Manager с помощью дескриптора SQL, который представляет собой временный идентификатор, сохраняющий свое значение, только пока в кэше планов остается по крайней мере один ссылающийся на него план.The Transact-SQLTransact-SQL text for a compiled plan can be retrieved from the sql manager cache using a SQL Handle, which is a transient identifier that remains constant only while at least one plan that references it remains in the plan cache. Дескриптор SQL — это хэш-значение, которое выводится из всего текста пакета и гарантированно является уникальным для каждого пакета.The sql handle is a hash value derived from the entire batch text and is guaranteed to be unique for every batch.

Примечание

Как и скомпилированный план, текст Transact-SQLTransact-SQL хранится для каждого пакета, включая комментарии.Like a compiled plan, the Transact-SQLTransact-SQL text is stored per batch, including the comments. Дескриптор SQL содержит хэш-код MD5 всего текста пакета и гарантированно является уникальным для каждого пакета.The sql handle contains the MD5 hash of the entire batch text and is guaranteed to be unique for every batch.

Следующий запрос предоставляет сведения об использовании памяти для кэша SQL Manager:The query below provides information about memory usage for the sql manager cache:

SELECT * FROM sys.dm_os_memory_objects
WHERE type = 'MEMOBJ_SQLMGR';

Между дескриптором SQL и дескрипторами планов существует связь "один ко многим".There is a 1:N relation between a sql handle and plan handles. Такая ситуация возникает, когда ключ кэша для скомпилированных планов отличается.Such a condition occurs when the cache key for the compiled plans is different. Это может происходить из-за изменения параметров SET между двумя выполнениями одного пакета.This may occur due to change in SET options between two executions of the same batch.

Рассмотрим следующую хранимую процедуру:Consider the following stored procedure:

USE WideWorldImporters;
GO
CREATE PROCEDURE usp_SalesByCustomer @CID int
AS
SELECT * FROM Sales.Customers
WHERE CustomerID = @CID
GO

SET ANSI_DEFAULTS ON
GO

EXEC usp_SalesByCustomer 10
GO

Проверьте содержимое кэша планов, используя следующий запрос:Verify what can be found in the plan cache using the query below:

SELECT cp.memory_object_address, cp.objtype, refcounts, usecounts, 
    qs.query_plan_hash, qs.query_hash,
    qs.plan_handle, qs.sql_handle
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle)
CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle)
INNER JOIN sys.dm_exec_query_stats AS qs ON qs.plan_handle = cp.plan_handle
WHERE text LIKE '%usp_SalesByCustomer%'
GO

Результирующий набор:Here is the result set.

memory_object_address    objtype   refcounts   usecounts   query_plan_hash    query_hash
---------------------    -------   ---------   ---------   ------------------ ------------------ 
0x000001CC6C534060        Proc      2           1           0x3B4303441A1D7E6D 0xA05D5197DA1EAC2D  

plan_handle                                                                               
------------------------------------------------------------------------------------------
0x0500130095555D02D022F111CD01000001000000000000000000000000000000000000000000000000000000

sql_handle
------------------------------------------------------------------------------------------
0x0300130095555D02C864C10061AB000001000000000000000000000000000000000000000000000000000000

Теперь выполните хранимую процедуру с другим параметром, не внося иных изменений в контекст выполнения:Now execute the stored procedure with a different parameter, but no other changes to execution context:

EXEC usp_SalesByCustomer 8
GO

Еще раз проверьте содержимое кэша планов.Verify again what can be found in the plan cache. Результирующий набор:Here is the result set.

memory_object_address    objtype   refcounts   usecounts   query_plan_hash    query_hash
---------------------    -------   ---------   ---------   ------------------ ------------------ 
0x000001CC6C534060        Proc      2           2           0x3B4303441A1D7E6D 0xA05D5197DA1EAC2D  

plan_handle                                                                               
------------------------------------------------------------------------------------------
0x0500130095555D02D022F111CD01000001000000000000000000000000000000000000000000000000000000

sql_handle
------------------------------------------------------------------------------------------
0x0300130095555D02C864C10061AB000001000000000000000000000000000000000000000000000000000000

Обратите внимание, что значение usecounts увеличилось до 2. Это означает, что один и тот же кэшированный план использовался повторно "как есть", так как использовались повторно структуры данных контекста выполнения.Notice the usecounts has increased to 2, which means the same cached plan was re-used as-is, because the execution context data structures were reused. Теперь измените параметр SET ANSI_DEFAULTS и выполните хранимую процедуру с использованием того же параметра.Now change the SET ANSI_DEFAULTS option and execute the stored procedure using the same parameter.

SET ANSI_DEFAULTS OFF
GO

EXEC usp_SalesByCustomer 8
GO

Еще раз проверьте содержимое кэша планов.Verify again what can be found in the plan cache. Результирующий набор:Here is the result set.

memory_object_address    objtype   refcounts   usecounts   query_plan_hash    query_hash
---------------------    -------   ---------   ---------   ------------------ ------------------ 
0x000001CD01DEC060        Proc      2           1           0x3B4303441A1D7E6D 0xA05D5197DA1EAC2D  
0x000001CC6C534060        Proc      2           2           0x3B4303441A1D7E6D 0xA05D5197DA1EAC2D

plan_handle                                                                               
------------------------------------------------------------------------------------------
0x0500130095555D02B031F111CD01000001000000000000000000000000000000000000000000000000000000
0x0500130095555D02D022F111CD01000001000000000000000000000000000000000000000000000000000000

sql_handle
------------------------------------------------------------------------------------------
0x0300130095555D02C864C10061AB000001000000000000000000000000000000000000000000000000000000
0x0300130095555D02C864C10061AB000001000000000000000000000000000000000000000000000000000000

Обратите внимание, что в выходных данных динамического административного представления sys.dm_exec_cached_plans теперь есть две записи.Notice there are now two entries in the sys.dm_exec_cached_plans DMV output:

  • Первая запись в столбце usecounts содержит значение 1, что соответствует одному выполнению плана с параметром SET ANSI_DEFAULTS OFF.The usecounts column shows the value 1 in the first record which is the plan executed once with SET ANSI_DEFAULTS OFF.
  • Вторая запись в столбце usecounts содержит значение 2, что соответствует двум выполнениям плана с параметром SET ANSI_DEFAULTS ON.The usecounts column shows the value 2 in the second record which is the plan executed with SET ANSI_DEFAULTS ON, because it was executed twice.
  • Разные значения memory_object_address указывают на разные записи планов выполнения в кэше планов.The different memory_object_address refers to a different execution plan entry in the plan cache. Однако значение sql_handle одинаково для обеих записей, так как они ссылаются на один и тот же пакет.However, the sql_handle value is the same for both entries because they refer to the same batch.
    • Выполнение с параметром ANSI_DEFAULTS со значением OFF имеет новый дескриптор plan_handle и может использоваться повторно для вызовов с тем же набором параметров SET.The execution with ANSI_DEFAULTS set to OFF has a new plan_handle, and it's available for reuse for calls that have the same set of SET options. Новый дескриптор плана необходим по той причине, что контекст выполнения был инициализирован повторно из-за измененных параметров SET.The new plan handle is necessary because the execution context was reinitialized due to changed SET options. Но это не вызывает перекомпиляцию: обе записи ссылаются на одни и те же план и запрос, о чем свидетельствуют одинаковые значения query_plan_hash и query_hash.But that doesn't trigger a recompile: both entries refer to the same plan and query, as evidenced by the same query_plan_hash and query_hash values.

Таким образом, в кэше есть две записи планов, соответствующие одному пакету. Это подчеркивает важность того, что параметры SET, влияющие на кэш планов, должны быть одинаковы, когда одни и те же запросы выполняются повторно. Это позволяет оптимизировать повторное использование плана и свести необходимый размер кэша планов к минимуму.What this effectively means is that we have two plan entries in the cache corresponding to the same batch, and it underscores the importance of making sure that the plan cache affecting SET options are the same, when the same queries are executed repeatedly, to optimize for plan reuse and keep plan cache size to its required minimum.

Совет

Распространенной проблемой является то, что разные клиенты могут иметь разные значения по умолчанию для параметров SET.A common pitfall is that different clients may have different default values for the SET options. Например, при установлении подключения через SQL Server Management StudioSQL Server Management Studio параметр QUOTED_IDENTIFIER автоматически принимает значение ON, но SQLCMD присваивает параметру QUOTED_IDENTIFIER значение OFF.For example, a connection made through SQL Server Management StudioSQL Server Management Studio automatically sets QUOTED_IDENTIFIER to ON, while SQLCMD sets QUOTED_IDENTIFIER to OFF. Выполнение одних и тех же запросов из этих двух клиентов приведет к созданию нескольких планов (как описано в примере выше).Executing the same queries from these two clients will result in multiple plans (as described in the example above).

Удаление планов выполнения из кэша плановRemoving execution plans from the Plan Cache

Планы выполнения остаются в кэше планов до тех пор, пока для их хранения остается достаточно памяти.Execution plans remain in the plan cache as long as there is enough memory to store them. При нехватке памяти Компонент SQL Server Database EngineSQL Server Database Engine определяет планы выполнения, которые нужно удалить из кэша процедур, на основе стоимости планов.When memory pressure exists, the Компонент SQL Server Database EngineSQL Server Database Engine uses a cost-based approach to determine which execution plans to remove from the plan cache. Для принятия основанного на стоимости решения компонент Компонент SQL Server Database EngineSQL Server Database Engine увеличивает и уменьшает переменную текущей стоимости для каждого плана выполнения, руководствуясь следующими факторами.To make a cost-based decision, the Компонент SQL Server Database EngineSQL Server Database Engine increases and decreases a current cost variable for each execution plan according to the following factors.

Когда пользовательский процесс добавляет в кэш план выполнения, он устанавливает текущую стоимость равной стоимости компиляции исходного запроса. Для нерегламентированных планов выполнения пользовательский процесс устанавливает значение текущей стоимости равным нулю.When a user process inserts an execution plan into the cache, the user process sets the current cost equal to the original query compile cost; for ad-hoc execution plans, the user process sets the current cost to zero. После этого каждый раз, когда пользовательский процесс ссылается на план выполнения, он сбрасывает текущую стоимость, делая ее равной исходной стоимости компиляции. Для нерегламентированных планов выполнения пользовательский процесс повышает значение текущей стоимости.Thereafter, each time a user process references an execution plan, it resets the current cost to the original compile cost; for ad-hoc execution plans the user process increases the current cost. Для всех планов максимальное значение текущей стоимости равно исходной стоимости компиляции.For all plans, the maximum value for the current cost is the original compile cost.

При нехватке памяти Компонент SQL Server Database EngineSQL Server Database Engine удаляет планы выполнения из кэша планов.When memory pressure exists, the Компонент SQL Server Database EngineSQL Server Database Engine responds by removing execution plans from the plan cache. Чтобы определить, какие планы следует удалить, компонент Компонент SQL Server Database EngineSQL Server Database Engine многократно проверяет состояние каждого плана выполнения и удаляет те из них, для которых текущая стоимость равна нулю.To determine which plans to remove, the Компонент SQL Server Database EngineSQL Server Database Engine repeatedly examines the state of each execution plan and removes plans when their current cost is zero. План выполнения с нулевой текущей стоимостью не удаляется автоматически при возникновении нехватки памяти; он удаляется только после проверки компонентом Компонент SQL Server Database EngineSQL Server Database Engine, если его текущая стоимость равна нулю.An execution plan with zero current cost is not removed automatically when memory pressure exists; it is removed only when the Компонент SQL Server Database EngineSQL Server Database Engine examines the plan and the current cost is zero. При проверке плана выполнения компонент Компонент SQL Server Database EngineSQL Server Database Engine приближает текущую стоимость к нулю, уменьшая ее в случае, если запрос в данный момент не использует план.When examining an execution plan, the Компонент SQL Server Database EngineSQL Server Database Engine pushes the current cost towards zero by decreasing the current cost if a query is not currently using the plan.

Компонент Компонент SQL Server Database EngineSQL Server Database Engine многократно проверяет планы выполнения, пока не удалит достаточно, чтобы удовлетворить требования к памяти.The Компонент SQL Server Database EngineSQL Server Database Engine repeatedly examines the execution plans until enough have been removed to satisfy memory requirements. При нехватке памяти стоимость плана выполнения может увеличиться и уменьшиться несколько раз.While memory pressure exists, an execution plan may have its cost increased and decreased more than once. При восстановлении достаточного объема памяти Компонент SQL Server Database EngineSQL Server Database Engine прекращает уменьшать текущую стоимость неиспользуемых планов выполнения, и все планы выполнения остаются в кэше планов, даже если их стоимость равна нулю.When memory pressure no longer exists, the Компонент SQL Server Database EngineSQL Server Database Engine stops decreasing the current cost of unused execution plans and all execution plans remain in the plan cache, even if their cost is zero.

Компонент SQL Server Database EngineSQL Server Database Engine использует монитор ресурсов и пользовательские рабочие потоки для освобождения памяти, занимаемой кэшем планов, при нехватке памяти.The Компонент SQL Server Database EngineSQL Server Database Engine uses the resource monitor and user worker threads to free memory from the plan cache in response to memory pressure. Монитор ресурсов и пользовательские рабочие потоки могут проверять параллельно выполняющиеся планы, что позволяет уменьшать текущую стоимость для каждого неиспользуемого плана выполнения.The resource monitor and user worker threads can examine plans run concurrently to decrease the current cost for each unused execution plan. Монитор ресурсов удаляет планы выполнения из кэша планов при глобальной нехватке памяти.The resource monitor removes execution plans from the plan cache when global memory pressure exists. Он освобождает память для принудительного выполнения политик для системной памяти, памяти процессов, памяти пула ресурсов и максимального размера всех кэшей.It frees memory to enforce policies for system memory, process memory, resource pool memory, and maximum size for all caches.

Максимальный размер всех кэшей является функцией от размера буферного пула и не может превышать максимальный объем памяти сервера.The maximum size for all caches is a function of the buffer pool size and cannot exceed the maximum server memory. Дополнительные сведения о настройке максимального объема памяти сервера см. в описании параметра max server memory в статье об sp_configure.For more information on configuring the maximum server memory, see the max server memory setting in sp_configure.

Пользовательские рабочие потоки удаляют планы выполнения из кэша планов при нехватке памяти в одиночном кэше.The user worker threads remove execution plans from the plan cache when single cache memory pressure exists. Они обеспечивают выполнение политик для максимального размера кэша и максимума записей одиночного кэша.They enforce policies for maximum single cache size and maximum single cache entries.

В следующих примерах показано, какие планы выполнения удаляются из кэша планов.The following examples illustrate which execution plans get removed from the plan cache:

  • План выполнения часто используется, поэтому его стоимость никогда не принимает значение ноль.An execution plan is frequently referenced so that its cost never goes to zero. Этот план остается в кэше планов и не удаляется, пока имеется достаточный объем памяти, а его текущая стоимость не равна нулю.The plan remains in the plan cache and is not removed unless there is memory pressure and the current cost is zero.
  • Нерегламентированный план выполнения вставляется и не используется до возникновения нехватки памяти.An ad-hoc execution plan is inserted and is not referenced again before memory pressure exists. Так как нерегламентированные планы выполнения инициализируются с текущей стоимостью, равной нулю, то Компонент SQL Server Database EngineSQL Server Database Engine при проверке планов выполнения обнаруживает план выполнения с нулевой стоимостью и удаляет его из кэша планов.Since ad-hoc plans are initialized with a current cost of zero, when the Компонент SQL Server Database EngineSQL Server Database Engine examines the execution plan, it will see the zero current cost and remove the plan from the plan cache. Нерегламентированный план выполнения с нулевой текущей стоимостью остается в кэше планов при наличии достаточного объема памяти.The ad-hoc execution plan remains in the plan cache with a zero current cost when memory pressure does not exist.

Чтобы вручную удалить отдельный план выполнения или все планы, используйте команду DBCC FREEPROCCACHE.To manually remove a single plan or all plans from the cache, use DBCC FREEPROCCACHE. Начиная с версии SQL Server 2016 (13.x);SQL Server 2016 (13.x), для очистки кэша процедур (планов) для базы данных в области действия служит инструкция ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE.Starting with SQL Server 2016 (13.x);SQL Server 2016 (13.x), the ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE to clear the procedure (plan) cache for the database in scope.

Перекомпиляция планов выполненияRecompiling Execution Plans

Некоторые изменения в базе данных могут привести к тому, что план выполнения станет неэффективным или неправильным в зависимости от нового состояния базы данных.Certain changes in a database can cause an execution plan to be either inefficient or invalid, based on the new state of the database. SQL ServerSQL Server обнаруживает изменения, которые делают план выполнения недействительным, и помечает такой план как недействительный.detects the changes that invalidate an execution plan and marks the plan as not valid. При следующем выполнении данного запроса план должен быть перекомпилирован.A new plan must then be recompiled for the next connection that executes the query. План может стать недействительным в следующих случаях.The conditions that invalidate a plan include the following:

  • Изменены таблица или представления, на которые ссылается запрос (ALTER TABLE или ALTER VIEW).Changes made to a table or view referenced by the query (ALTER TABLE and ALTER VIEW).
  • Изменена одна процедура, которая удалит все планы для этой процедуры из кэша (ALTER PROCEDURE).Changes made to a single procedure, which would drop all plans for that procedure from the cache (ALTER PROCEDURE).
  • Изменены индексы, используемые планом выполнения.Changes to any indexes used by the execution plan.
  • Обновлена статистика, которая используется планом выполнения и сформирована либо явным образом по UPDATE STATISTICS, либо автоматически.Updates on statistics used by the execution plan, generated either explicitly from a statement, such as UPDATE STATISTICS, or generated automatically.
  • Удалены индексы, используемые планом выполнения.Dropping an index used by the execution plan.
  • Явный вызов sp_recompile.An explicit call to sp_recompile.
  • Частое изменение ключей (инструкциями INSERT или DELETE от пользователей, изменяющих таблицу, на которую ссылается запрос).Large numbers of changes to keys (generated by INSERT or DELETE statements from other users that modify a table referenced by the query).
  • Для таблиц с триггерами: значительный рост числа строк в таблицах inserted и deleted.For tables with triggers, if the number of rows in the inserted or deleted tables grows significantly.
  • Выполнение хранимой процедуры с помощью параметра WITH RECOMPILE .Executing a stored procedure using the WITH RECOMPILE option.

Большинство перекомпиляций необходимы либо для обеспечения правильности работы инструкции, либо для потенциального ускорения работы плана выполнения.Most recompilations are required either for statement correctness or to obtain potentially faster query execution plans.

В версиях SQL ServerSQL Server до 2005, независимо от того, какая из инструкций пакета вызвала перекомпиляцию, перекомпилируется весь пакет, переданный через хранимую процедуру, триггер, нерегламентированный пакет или подготовленную инструкцию.In SQL ServerSQL Server versions prior to 2005, whenever a statement within a batch causes recompilation, the entire batch, whether submitted through a stored procedure, trigger, ad-hoc batch, or prepared statement, was recompiled. В SQL Server 2005 (9.x)SQL Server 2005 (9.x) и более поздних версиях перекомпилируется только та инструкция пакета, которая вызвала перекомпиляцию.Starting with SQL Server 2005 (9.x)SQL Server 2005 (9.x), only the statement inside the batch that triggers recompilation is recompiled. Кроме этого, в SQL Server 2005 (9.x)SQL Server 2005 (9.x) и более поздних версиях есть дополнительные типы перекомпиляции, что вызвано расширением набора возможностей.Also, there are additional types of recompilations in SQL Server 2005 (9.x)SQL Server 2005 (9.x) and later because of its expanded feature set.

Перекомпиляция на уровне инструкции дает выигрыш в производительности, поскольку в большинстве случаев перекомпиляция небольшого числа инструкций и связанных с этим потерь занимает меньше ресурсов в плане использования времени ЦП и затрат на блокировки.Statement-level recompilation benefits performance because, in most cases, a small number of statements causes recompilations and their associated penalties, in terms of CPU time and locks. Этих потерь, таким образом, удается избежать для остальных инструкций пакета, которые в перекомпиляции не нуждаются.These penalties are therefore avoided for the other statements in the batch that do not have to be recompiled.

Расширенное событие sql_statement_recompile (xEvent) выводит сведения о перекомпиляции на уровне инструкций.The sql_statement_recompile extended event (xEvent) reports statement-level recompilations. Это событие xEvent возникает при перекомпиляции инструкций уровня для любого типа пакета.This xEvent occurs when a statement-level recompilation is required by any kind of batch. К таким пакетам относятся хранимые процедуры, триггеры, нерегламентированные пакеты и запросы.This includes stored procedures, triggers, ad hoc batches and queries. Пакеты можно отправлять с помощью различных интерфейсов, включая sp_executesql, динамический язык SQL, методы Prepare и Execute.Batches may be submitted through several interfaces, including sp_executesql, dynamic SQL, Prepare methods or Execute methods. Столбец recompile_cause для событий xEvent sql_statement_recompile содержит код причины перекомпиляции в виде целого числа.The recompile_cause column of sql_statement_recompile xEvent contains an integer code that indicates the reason for the recompilation. В следующей таблице приведены возможные причины.The following table contains the possible reasons:

Изменение схемыSchema changed Изменение статистикиStatistics changed
Отложенная компиляцияDeferred compile Изменение параметра SETSET option changed
Изменение временной таблицыTemporary table changed Изменение удаленного набора строкRemote rowset changed
Изменение разрешения FOR BROWSEFOR BROWSE permission changed Изменение среды уведомлений о запросахQuery notification environment changed
Изменение секционированного представленияPartitioned view changed Изменение параметров курсораCursor options changed
OPTION (RECOMPILE) запрошеноOPTION (RECOMPILE) requested Очистка параметризованного планаParameterized plan flushed
Изменение версии базы данных, влияющее на планPlan affecting database version changed Изменение политики форсирования плана для хранилища запросовQuery Store plan forcing policy changed
Сбой форсирования плана для хранилища запросовQuery Store plan forcing failed Отсутствие плана для хранилища запросовQuery Store missing the plan

Примечание

В выпусках SQL ServerSQL Server, в которых xEvents недоступны, для тех же целей (отслеживание перекомпиляции уровня инструкций) можно использовать событие трассировки SQL ServerSQL Server Profiler SP:Recompile.In SQL ServerSQL Server versions where xEvents are not available, then the SQL ServerSQL Server Profiler SP:Recompile trace event can be used for the same purpose of reporting statement-level recompilations. Событие трассировки SQL:StmtRecompile также сообщает о перекомпиляции уровня инструкций и может использоваться для отслеживания и отладки перекомпиляции.The trace event SQL:StmtRecompile also reports statement-level recompilations, and this trace event can also be used to track and debug recompilations. Событие SP:Recompile создается только для хранимых процедур и триггеров, а SQL:StmtRecompile — для хранимых процедур, триггеров, нерегламентированных пакетов, пакетов, которые выполняются с помощью sp_executesql, подготовленных запросов и динамического SQL.Whereas SP:Recompile generates only for stored procedures and triggers, SQL:StmtRecompile generates for stored procedures, triggers, ad-hoc batches, batches that are executed by using sp_executesql, prepared queries, and dynamic SQL. Столбец EventSubClass для событий SP:Recompile и SQL:StmtRecompile содержит код в виде целого числа, обозначающий причину перекомпиляции.The EventSubClass column of SP:Recompile and SQL:StmtRecompile contains an integer code that indicates the reason for the recompilation. Коды описаны здесь.The codes are described here.

Примечание

Если для параметра базы данных AUTO_UPDATE_STATISTICS установлено значение ON, то запросы перекомпилируются при условии, что они указывают на целевые таблицы или индексированные представления, для которых со времени последнего выполнения была изменена статистика или в значительной степени была изменена кратность.When the AUTO_UPDATE_STATISTICS database option is set to ON, queries are recompiled when they target tables or indexed views whose statistics have been updated or whose cardinalities have changed significantly since the last execution. Это относится к стандартным пользовательским таблицам, временным таблицам, а также таблицам inserted и deleted, созданным триггерами DML.This behavior applies to standard user-defined tables, temporary tables, and the inserted and deleted tables created by DML triggers. Если на производительность запроса оказывают влияние излишние перекомпиляции, измените значение этого параметра на OFF.If query performance is affected by excessive recompilations, consider changing this setting to OFF. Если для параметра базы данных AUTO_UPDATE_STATISTICS установлено значение OFF, перекомпиляция по причине изменения статистики или кратности не выполняется, за исключением вставляемых и удаляемых таблиц, созданных триггерами DML INSTEAD OF.When the AUTO_UPDATE_STATISTICS database option is set to OFF, no recompilations occur based on statistics or cardinality changes, with the exception of the inserted and deleted tables that are created by DML INSTEAD OF triggers. Так как данные таблицы создаются в базе данных tempdb, перекомпиляция запросов, которые обращаются к этим таблицам, зависит от значения параметра AUTO_UPDATE_STATISTICS в базе данных tempdb.Because these tables are created in tempdb, the recompilation of queries that access them depends on the setting of AUTO_UPDATE_STATISTICS in tempdb. Обратите внимание, что в SQL ServerSQL Server до версии 2005 запросы по-прежнему будут перекомпилироваться при изменении кратности в таблицах, вставляемых и удаляемых триггерами DML, даже если этот параметр имеет значение OFF.Note that in SQL ServerSQL Server prior to 2005, queries continue to recompile based on cardinality changes to the DML trigger inserted and deleted tables, even when this setting is OFF.

Параметры и повторное использование планов выполненияParameters and Execution Plan Reuse

Использование параметров, включая маркеры параметров в приложениях ADO, OLE DB и ODBC, может повысить уровень использования планов выполнения.The use of parameters, including parameter markers in ADO, OLE DB, and ODBC applications, can increase the reuse of execution plans.

Предупреждение

Использование параметров и маркеров параметров для хранения введенных конечными пользователями значений безопаснее, чем сцепление значений в строку, которая затем выполняется с помощью метода API доступа к данным, инструкции EXECUTE или хранимой процедуры sp_executesql .Using parameters or parameter markers to hold values that are typed by end users is more secure than concatenating the values into a string that is then executed by using either a data access API method, the EXECUTE statement, or the sp_executesql stored procedure.

Единственная разница между следующими двумя инструкциями SELECT — в значениях, сравниваемых в предложении WHERE :The only difference between the following two SELECT statements is the values that are compared in the WHERE clause:

SELECT * 
FROM AdventureWorks2014.Production.Product 
WHERE ProductSubcategoryID = 1;
SELECT * 
FROM AdventureWorks2014.Production.Product 
WHERE ProductSubcategoryID = 4;

Единственная разница между планами выполнения для этих запросов — в значении, хранимом для сравнения со столбцом ProductSubcategoryID .The only difference between the execution plans for these queries is the value stored for the comparison against the ProductSubcategoryID column. В то время как выявление факта формирования инструкциями одного и того же плана и повторного его использования является основной задачей SQL ServerSQL Server, SQL ServerSQL Server не всегда может это обнаружить в сложных инструкциях Transact-SQLTransact-SQL.While the goal is for SQL ServerSQL Server to always recognize that the statements generate essentially the same plan and reuse the plans, SQL ServerSQL Server sometimes does not detect this in complex Transact-SQLTransact-SQL statements.

Отделение констант от инструкции Transact-SQLTransact-SQL с помощью параметров помогает реляционному механизму распознавать дубликаты планов.Separating constants from the Transact-SQLTransact-SQL statement by using parameters helps the relational engine recognize duplicate plans. Параметры можно использовать следующими способами.You can use parameters in the following ways:

  • В Transact-SQLTransact-SQL используется атрибут sp_executesql:In Transact-SQLTransact-SQL , use sp_executesql:

    DECLARE @MyIntParm INT
    SET @MyIntParm = 1
    EXEC sp_executesql
       N'SELECT * 
       FROM AdventureWorks2014.Production.Product 
       WHERE ProductSubcategoryID = @Parm',
       N'@Parm INT',
       @MyIntParm
    

    Этот метод рекомендуется использовать для скриптов языка Transact-SQLTransact-SQL, хранимых процедур и триггеров, динамически формирующих инструкции SQL.This method is recommended for Transact-SQLTransact-SQL scripts, stored procedures, or triggers that generate SQL statements dynamically.

  • В технологиях ADO, OLE DB и ODBC используются маркеры параметров.ADO, OLE DB, and ODBC use parameter markers. Маркеры параметров представляют собой знаки вопроса (?), заменяющие константу в инструкции SQL и привязываемые к программной переменной.Parameter markers are question marks (?) that replace a constant in an SQL statement and are bound to a program variable. Например, в приложении ODBC можно сделать следующее:For example, you would do the following in an ODBC application:

    • использовать параметр SQLBindParameter для привязки целочисленной переменной к первому маркеру параметра в инструкции SQL;Use SQLBindParameter to bind an integer variable to the first parameter marker in an SQL statement.
    • поместить целочисленное значение в переменную;Put the integer value in the variable.
    • выполнить инструкцию, указав маркер параметра (?):Execute the statement, specifying the parameter marker (?):
    SQLExecDirect(hstmt, 
       "SELECT * 
       FROM AdventureWorks2014.Production.Product 
       WHERE ProductSubcategoryID = ?",
       SQL_NTS);
    

    Если в приложениях используются маркеры параметров, то поставщик OLE DB для собственного клиента SQL ServerSQL Server и драйвер ODBC для собственного клиента SQL ServerSQL Server, включенные в состав SQL ServerSQL Server, используют для отправки инструкций в SQL ServerSQL Server процедуру sp_executesql.The SQL ServerSQL Server Native Client OLE DB Provider and the SQL ServerSQL Server Native Client ODBC driver included with SQL ServerSQL Server use sp_executesql to send statements to SQL ServerSQL Server when parameter markers are used in applications.

  • Чтобы проектировать хранимые процедуры, использующие указанные разработчиком параметры.To design stored procedures, which use parameters by design.

Если структура приложения не предусматривает явного создания параметров, можно воспользоваться оптимизатором запросов SQL ServerSQL Server для автоматической параметризации некоторых запросов с использованием установленного по умолчанию поведения простой параметризации.If you do not explicitly build parameters into the design of your applications, you can also rely on the SQL ServerSQL Server Query Optimizer to automatically parameterize certain queries by using the default behavior of simple parameterization. В качестве альтернативы можно настроить принудительный учет параметризации всех запросов к базе данных в оптимизаторе запросов, установив для параметра PARAMETERIZATION инструкции ALTER DATABASE значение FORCED.Alternatively, you can force the Query Optimizer to consider parameterizing all queries in the database by setting the PARAMETERIZATION option of the ALTER DATABASE statement to FORCED.

При включенной принудительной параметризации может также иметь место и простая параметризация.When forced parameterization is enabled, simple parameterization can still occur. Например, в соответствии с правилами принудительной параметризации следующий запрос не может быть параметризован.For example, the following query cannot be parameterized according to the rules of forced parameterization:

SELECT * FROM Person.Address
WHERE AddressID = 1 + 2;

Однако он может быть параметризован согласно правилам простой параметризации.However, it can be parameterized according to simple parameterization rules. В случае неуспешной попытки принудительной параметризации впоследствии производятся попытки использования простой параметризации.When forced parameterization is tried but fails, simple parameterization is still subsequently tried.

Простая параметризацияSimple Parameterization

В SQL ServerSQL Server использование параметров или маркеров параметров в инструкциях Transact-SQL позволяет реляционному ядру более эффективно применять существующие скомпилированные планы выполнения для новых инструкций Transact-SQLTransact-SQL.In SQL ServerSQL Server, using parameters or parameter markers in Transact-SQL statements increases the ability of the relational engine to match new Transact-SQLTransact-SQL statements with existing, previously-compiled execution plans.

Предупреждение

Использование параметров и маркеров параметров для хранения введенных конечными пользователями значений безопаснее, чем сцепление значений в строку, которая затем выполняется с помощью метода API доступа к данным, инструкции EXECUTE или хранимой процедуры sp_executesql .Using parameters or parameter markers to hold values typed by end users is more secure than concatenating the values into a string that is then executed using either a data access API method, the EXECUTE statement, or the sp_executesql stored procedure.

Если инструкция Transact-SQLTransact-SQL выполняется без параметров, SQL ServerSQL Server неявно параметризует инструкцию, чтобы увеличить возможность ее противопоставления существующему плану выполнения.If a Transact-SQLTransact-SQL statement is executed without parameters, SQL ServerSQL Server parameterizes the statement internally to increase the possibility of matching it against an existing execution plan. Данный процесс называется простой параметризацией.This process is called simple parameterization. В версиях SQL ServerSQL Server до 2005 этот процесс назывался автоматической параметризацией.In SQL ServerSQL Server versions prior to 2005, the process was referred to as auto-parameterization.

Рассмотрим следующую инструкцию.Consider this statement:

SELECT * FROM AdventureWorks2014.Production.Product 
WHERE ProductSubcategoryID = 1;

Значение 1 в конце инструкции может быть указано в виде параметра.The value 1 at the end of the statement can be specified as a parameter. Реляционный механизм строит план выполнения для данного пакета, как если бы параметр был указан на месте значения 1.The relational engine builds the execution plan for this batch as if a parameter had been specified in place of the value 1. При помощи этой простой параметризации SQL ServerSQL Server распознает, что следующие две инструкции формируют, по сути, одинаковый план выполнения, и повторно использует первый план для второй инструкции:Because of this simple parameterization, SQL ServerSQL Server recognizes that the following two statements generate essentially the same execution plan and reuses the first plan for the second statement:

SELECT * FROM AdventureWorks2014.Production.Product 
WHERE ProductSubcategoryID = 1;
SELECT * FROM AdventureWorks2014.Production.Product 
WHERE ProductSubcategoryID = 4;

В процессе обработки сложных инструкций Transact-SQLTransact-SQL реляционный механизм может с трудом определять, какие выражения могут быть параметризованы.When processing complex Transact-SQLTransact-SQL statements, the relational engine may have difficulty determining which expressions can be parameterized. Чтобы увеличить возможность реляционного модуля находить соответствующие существующие неиспользованные планы выполнения для сложных инструкций Transact-SQLTransact-SQL, необходимо явно указать параметры с помощью процедуры sp_executesql или маркеров параметров.To increase the ability of the relational engine to match complex Transact-SQLTransact-SQL statements to existing, unused execution plans, explicitly specify the parameters using either sp_executesql or parameter markers.

Примечание

При использовании арифметических операторов (+, -, *, / или %) для явного или неявного преобразования постоянных значений типов данных int, smallint, tinyint или bigint в типы данных float, real, decimal или numeric SQL ServerSQL Server применяет специальные правила для определения типа и точности результатов выражения.When the +, -, *, /, or % arithmetic operators are used to perform implicit or explicit conversion of int, smallint, tinyint, or bigint constant values to the float, real, decimal or numeric data types, SQL ServerSQL Server applies specific rules to calculate the type and precision of the expression results. Однако эти правила различаются в зависимости от того, параметризован запрос или нет.However, these rules differ, depending on whether the query is parameterized or not. Таким образом, одинаковые выражения в запросах могут в некоторых случаях давать отличающиеся результаты.Therefore, similar expressions in queries can, in some cases, produce differing results.

При проведении простой параметризации SQL ServerSQL Server по умолчанию параметризует сравнительно небольшой класс запросов.Under the default behavior of simple parameterization, SQL ServerSQL Server parameterizes a relatively small class of queries. Однако можно указать, чтобы все запросы в базе данных были параметризованы в соответствии с определенными ограничениями, настроив параметр PARAMETERIZATION команды ALTER DATABASE на FORCED.However, you can specify that all queries in a database be parameterized, subject to certain limitations, by setting the PARAMETERIZATION option of the ALTER DATABASE command to FORCED. Уменьшая частоту компиляции запросов, эти действия улучшат производительность баз данных, которые испытывают большие объемы параллельных запросов.Doing so may improve the performance of databases that experience high volumes of concurrent queries by reducing the frequency of query compilations.

Иначе можно указать параметризацию одного запроса и других, синтаксически равных, но отличающихся значениями параметра, запросов.Alternatively, you can specify that a single query, and any others that are syntactically equivalent but differ only in their parameter values, be parameterized.

Принудительная параметризацияForced Parameterization

Можно переопределить простую параметризацию, используемую в SQL ServerSQL Server по умолчанию, указав, что все инструкции SELECT, INSERT, UPDATE и DELETE в базе данных должны быть параметризованы (с учетом некоторых ограничений).You can override the default simple parameterization behavior of SQL ServerSQL Server by specifying that all SELECT, INSERT, UPDATE, and DELETE statements in a database be parameterized, subject to certain limitations. Принудительная параметризация активируется путем установки для параметра PARAMETERIZATION значения FORCED в инструкции ALTER DATABASE .Forced parameterization is enabled by setting the PARAMETERIZATION option to FORCED in the ALTER DATABASE statement. Принудительная параметризация может улучшить производительность некоторых баз данных, сократив частоту выполнения компиляции и перекомпиляции запросов.Forced parameterization may improve the performance of certain databases by reducing the frequency of query compilations and recompilations. Базы данных, которым может пойти на пользу принудительная параметризация, — это, как правило, те, которым приходится выполнять большое количество параллельных запросов из источников наподобие приложений торговых точек.Databases that may benefit from forced parameterization are generally those that experience high volumes of concurrent queries from sources such as point-of-sale applications.

Если параметру PARAMETERIZATION присвоено значение FORCED, любое литеральное значение, представленное в инструкции SELECT, INSERT, UPDATEили DELETE , заявленной в любой форме, преобразуется в аргумент в процессе компиляции запроса.When the PARAMETERIZATION option is set to FORCED, any literal value that appears in a SELECT, INSERT, UPDATE, or DELETE statement, submitted in any form, is converted to a parameter during query compilation. Исключениями являются литералы, представленные в следующих конструкциях запроса.The exceptions are literals that appear in the following query constructs:

  • ИнструкцииINSERT...EXECUTE .INSERT...EXECUTE statements.
  • Инструкции в теле хранимых процедур, триггеров или определяемых пользователем функций.Statements inside the bodies of stored procedures, triggers, or user-defined functions. В SQL ServerSQL Server планы запросов для этих подпрограмм уже используются повторно.SQL ServerSQL Server already reuses query plans for these routines.
  • Подготовленные инструкции, которые уже были параметризованы приложением на стороне клиента.Prepared statements that have already been parameterized on the client-side application.
  • Инструкции, содержащие вызовы метода XQuery, где метод представлен в контексте, в котором его аргументы обычно параметризуются, например в предложении WHERE .Statements that contain XQuery method calls, where the method appears in a context where its arguments would typically be parameterized, such as a WHERE clause. Если метод представлен в контексте, где его аргументы не параметризуются, остальная часть инструкции будет параметризована.If the method appears in a context where its arguments would not be parameterized, the rest of the statement is parameterized.
  • Инструкции внутри курсора Transact-SQLTransact-SQL.Statements inside a Transact-SQLTransact-SQL cursor. (ИнструкцииSELECT внутри курсоров API-интерфейса параметризуются.)(SELECT statements inside API cursors are parameterized.)
  • Устаревшие конструкции запроса.Deprecated query constructs.
  • Любая инструкция, выполняемая в контексте ANSI_PADDING или ANSI_NULLS со значением OFF.Any statement that is run in the context of ANSI_PADDING or ANSI_NULLS set to OFF.
  • Инструкции, содержащие более 2 097 литералов, пригодных для параметризации.Statements that contain more than 2,097 literals that are eligible for parameterization.
  • Инструкции, ссылающиеся на переменные, такие как WHERE T.col2 >= @bb.Statements that reference variables, such as WHERE T.col2 >= @bb.
  • Инструкции, содержащие указание запроса RECOMPILE .Statements that contain the RECOMPILE query hint.
  • Инструкции, содержащие предложение COMPUTE .Statements that contain a COMPUTE clause.
  • Инструкции, содержащие предложение WHERE CURRENT OF .Statements that contain a WHERE CURRENT OF clause.

Кроме того, в запросах не параметризуются следующие предложенияAdditionally, the following query clauses are not parameterized. (следует иметь в виду, что не параметризуются только предложения;Note that in these cases, only the clauses are not parameterized. другие предложения внутри того же запроса могут оказаться пригодными для принудительной параметризации).Other clauses within the same query may be eligible for forced parameterization.

  • Список <select_list> любой инструкции SELECT.The <select_list> of any SELECT statement. Сюда входят списки SELECT во вложенных запросах и списки SELECT внутри инструкций INSERT.This includes SELECT lists of subqueries and SELECT lists inside INSERT statements.
  • Инструкции SELECT во вложенных запросах, представленные внутри инструкции IF .Subquery SELECT statements that appear inside an IF statement.
  • Предложения запроса TOP, TABLESAMPLE, HAVING, GROUP BY, ORDER BY, OUTPUT...INTO или FOR XML.The TOP, TABLESAMPLE, HAVING, GROUP BY, ORDER BY, OUTPUT...INTO, or FOR XML clauses of a query.
  • Аргументы, прямые или в качестве подвыражений, для OPENROWSET, OPENQUERY, OPENDATASOURCE, OPENXMLили для любого оператора FULLTEXT .Arguments, either direct or as subexpressions, to OPENROWSET, OPENQUERY, OPENDATASOURCE, OPENXML, or any FULLTEXT operator.
  • Аргументы pattern и escape_character предложения LIKE .The pattern and escape_character arguments of a LIKE clause.
  • Аргумент style предложения CONVERT .The style argument of a CONVERT clause.
  • Целочисленные константы внутри предложения IDENTITY .Integer constants inside an IDENTITY clause.
  • Константы, указанные использованием синтаксиса расширения ODBC.Constants specified by using ODBC extension syntax.
  • Свертываемые константные выражения, являющиеся аргументами операторов +, -, *, / и %.Constant-foldable expressions that are arguments of the +, -, *, /, and % operators. При определении пригодности для принудительной параметризации SQL ServerSQL Server рассматривает выражение как свертываемое, если верно хотя бы одно из следующих условий.When considering eligibility for forced parameterization, SQL ServerSQL Server considers an expression to be constant-foldable when either of the following conditions is true:
    • В выражении не представлены столбцы, переменные или вложенные запросы.No columns, variables, or subqueries appear in the expression.
    • Выражение содержит предложение CASE .The expression contains a CASE clause.
  • Аргументы для предложений указаний запросов.Arguments to query hint clauses. Сюда входит аргумент number_of_rows указания запроса FAST, аргумент number_of_processors указания запроса MAXDOP и аргумент number указания запроса MAXRECURSION.These include the number_of_rows argument of the FAST query hint, the number_of_processors argument of the MAXDOP query hint, and the number argument of the MAXRECURSION query hint.

Параметризация происходит на уровне отдельных инструкций Transact-SQLTransact-SQL.Parameterization occurs at the level of individual Transact-SQLTransact-SQL statements. Иными словами, параметризуются отдельные инструкции в пакете.In other words, individual statements in a batch are parameterized. После компиляции параметризированный запрос выполняется в контексте пакета, в котором он был изначально заявлен.After compiling, a parameterized query is executed in the context of the batch in which it was originally submitted. Если план выполнения для запроса кэширован, можно определить, был ли параметризован запрос, обратившись к столбцу sql в динамическом административном представлении sys.syscacheobjects.If an execution plan for a query is cached, you can determine whether the query was parameterized by referencing the sql column of the sys.syscacheobjects dynamic management view. Если запрос параметризован, имена и типы данных аргументов располагаются перед текстом заявленного пакета в этом столбце, например (@1 tinyint).If a query is parameterized, the names and data types of parameters come before the text of the submitted batch in this column, such as (@1 tinyint).

Примечание

Имена аргументов произвольны.Parameter names are arbitrary. Пользователи или приложения не должны опираться на какой-либо конкретный порядок именования.Users or applications should not rely on a particular naming order. Кроме того, в зависимости от версии SQL ServerSQL Server и пакетов обновления могут меняться имена параметров, выбор литералов, подлежащих параметризации, и разбивка параметризованного текста.Also, the following can change between versions of SQL ServerSQL Server and Service Pack upgrades: Parameter names, the choice of literals that are parameterized, and the spacing in the parameterized text.

Типы данных аргументовData Types of Parameters

Когда SQL ServerSQL Server параметризует литералы, аргументы преобразовываются в следующие типы данных.When SQL ServerSQL Server parameterizes literals, the parameters are converted to the following data types:

  • Целочисленные литералы, размер которых в ином случае соответствовал бы типу данных int, параметризуются в int. Большие целочисленные литералы, являющиеся частью предикатов, которые включают в себя любой оператор сравнения (в том числе <, <=, =, !=, >, >=, !<, !>, <>, ALL, ANY, SOME, BETWEEN и IN), параметризуются в numeric(38,0).Integer literals whose size would otherwise fit within the int data type parameterize to int. Larger integer literals that are parts of predicates that involve any comparison operator (includes <, <=, =, !=, >, >=, , !<, !>, <>, ALL, ANY, SOME, BETWEEN, and IN) parameterize to numeric(38,0). Большие литералы, не являющиеся частью предикатов, которые включают в себя операторы сравнения, параметризуются в числовой тип с точностью достаточно большой, чтобы поддержать их размер, и с масштабом 0.Larger literals that are not parts of predicates that involve comparison operators parameterize to numeric whose precision is just large enough to support its size and whose scale is 0.
  • Числовые литералы с фиксированной запятой, являющиеся частью предикатов, которые включают в себя операторы сравнения, параметризуются в числовой тип с точностью 38 и масштабом достаточно большим, чтобы поддержать их размер.Fixed-point numeric literals that are parts of predicates that involve comparison operators parameterize to numeric whose precision is 38 and whose scale is just large enough to support its size. Числовые литералы с фиксированной запятой, не являющиеся частью предикатов, которые включают в себя операторы сравнения, параметризуются в числовой тип с точностью и масштабом достаточно большими, чтобы поддержать их размер.Fixed-point numeric literals that are not parts of predicates that involve comparison operators parameterize to numeric whose precision and scale are just large enough to support its size.
  • Числовые литералы с плавающей запятой параметризуются в float(53).Floating point numeric literals parameterize to float(53).
  • Строковые литералы не в формате Юникод параметризуются в varchar(8000), если размер литерала не превышает 8000 символов, и в varchar(max), если он больше 8000 символов.Non-Unicode string literals parameterize to varchar(8000) if the literal fits within 8,000 characters, and to varchar(max) if it is larger than 8,000 characters.
  • Строковые литералы в формате Юникод параметризуются в nvarchar(4000), если размер литерала не превышает 4000 символов Юникода, и в nvarchar(max), если он больше 4000 символов.Unicode string literals parameterize to nvarchar(4000) if the literal fits within 4,000 Unicode characters, and to nvarchar(max) if the literal is larger than 4,000 characters.
  • Двоичные литералы параметризуются в varbinary(8000), если размер литерала не превышает 8000 байт.Binary literals parameterize to varbinary(8000) if the literal fits within 8,000 bytes. Если он больше 8000 байт, он преобразуется в varbinary(max).If it is larger than 8,000 bytes, it is converted to varbinary(max).
  • Денежные литералы параметризуются в тип money.Money type literals parameterize to money.

Рекомендации по использованию принудительной параметризацииGuidelines for Using Forced Parameterization

Устанавливая для параметра PARAMETERIZATION значение FORCED, примите во внимание следующие сведения.Consider the following when you set the PARAMETERIZATION option to FORCED:

  • Принудительная параметризация, в сущности, преобразует литеральные константы в запросе в параметры при компиляции запроса.Forced parameterization, in effect, changes the literal constants in a query to parameters when compiling a query. Следовательно, оптимизатор запросов может выбирать не самые оптимальные планы для запросов.Therefore, the Query Optimizer might choose suboptimal plans for queries. В частности, уменьшается вероятность того, что оптимизатор запросов сопоставит запрос с индексированным представлением или индексом по вычисляемому столбцу.In particular, the Query Optimizer is less likely to match the query to an indexed view or an index on a computed column. Он может также выбирать не самые оптимальные планы для запросов, ориентированных на секционированные таблицы или распределенные секционированные представления.It may also choose suboptimal plans for queries posed on partitioned tables and distributed partitioned views. Принудительная параметризация не должна использоваться в средах, в значительной степени опирающихся на индексированные представления и индексы по вычисляемым столбцам.Forced parameterization should not be used for environments that rely heavily on indexed views and indexes on computed columns. Параметр PARAMETERIZATION FORCED должны использовать только опытные администраторы баз данных и лишь после того, как они определят, что такое использование не повредит производительности.Generally, the PARAMETERIZATION FORCED option should only be used by experienced database administrators after determining that doing this does not adversely affect performance.
  • Распределенные запросы, ссылающиеся на более чем одну базу данных, пригодны для принудительной параметризации, если для параметра PARAMETERIZATION задано значение FORCED в базе данных, в контексте которой выполняется запрос.Distributed queries that reference more than one database are eligible for forced parameterization as long as the PARAMETERIZATION option is set to FORCED in the database whose context the query is running.
  • Установка для параметра PARAMETERIZATION на значения FORCED производит очистку всех планов запросов из кэша планов в базе данных за исключением тех, которые компилируются, перекомпилируются или выполняются в настоящий момент.Setting the PARAMETERIZATION option to FORCED flushes all query plans from the plan cache of a database, except those that currently are compiling, recompiling, or running. Планы для запросов, которые компилируются или выполняются в момент изменения настроек, параметризуются при следующем выполнении запроса.Plans for queries that are compiling or running during the setting change are parameterized the next time the query is executed.
  • Настройка параметра PARAMETERIZATION выполняется в режиме в сети и не требует монопольных блокировок на уровне базы данных.Setting the PARAMETERIZATION option is an online operation that it requires no database-level exclusive locks.
  • Текущая настройка параметра PARAMETERIZATION сохраняется при повторном присоединении или восстановлении базы данных.The current setting of the PARAMETERIZATION option is preserved when reattaching or restoring a database.

Можно перекрывать поведение принудительной параметризации, предписав выполнение попытки простой параметризации для отдельного запроса, а также всех остальных запросов с таким же синтаксисом, отличающихся только значениями аргументов.You can override the behavior of forced parameterization by specifying that simple parameterization be attempted on a single query, and any others that are syntactically equivalent but differ only in their parameter values. Справедливо и обратное: можно потребовать выполнения попытки принудительной параметризации для отдельного набора синтаксически эквивалентных запросов, даже если принудительная параметризация в базе данных отключена.Conversely, you can specify that forced parameterization be attempted on only a set of syntactically equivalent queries, even if forced parameterization is disabled in the database. В этих целях используютсяструктуры планов .Plan guides are used for this purpose.

Примечание

Если для параметра PARAMETERIZATION задано значение FORCED, то отчеты об ошибках могут отличаться от отчетов в случае, когда для параметра PARAMETERIZATION задано значение SIMPLE: при принудительной параметризации число сообщений об ошибках в некоторых случаях больше, чем при простой параметризации, а номера строк, в которых возникают ошибки, могут указываться неверно.When the PARAMETERIZATION option is set to FORCED, the reporting of error messages may differ from when the PARAMETERIZATION option is set to SIMPLE: multiple error messages may be reported under forced parameterization, where fewer messages would be reported under simple parameterization, and the line numbers in which errors occur may be reported incorrectly.

Подготовка инструкций SQLPreparing SQL Statements

В реляционном механизме SQL ServerSQL Server введена полная поддержка подготовки инструкций Transact-SQLTransact-SQL перед их выполнением.The SQL ServerSQL Server relational engine introduces full support for preparing Transact-SQLTransact-SQL statements before they are executed. Если приложению требуется выполнить инструкцию Transact-SQLTransact-SQL несколько раз, то оно может использовать API базы данных следующим образом.If an application has to execute an Transact-SQLTransact-SQL statement several times, it can use the database API to do the following:

  • Однократная подготовка инструкции.Prepare the statement once. Инструкция Transact-SQLTransact-SQL компилируется в план выполнения.This compiles the Transact-SQLTransact-SQL statement into an execution plan.
  • Ранее скомпилированный план выполнения выполняется каждый раз при необходимости использовать эту инструкцию.Execute the precompiled execution plan every time it has to execute the statement. Это избавляет от необходимости повторно компилировать инструкцию Transact-SQLTransact-SQL при каждом последующем выполнении.This prevents having to recompile the Transact-SQLTransact-SQL statement on each execution after the first time.
    Подготовка и выполнение инструкций контролируется функциями и методами API.Preparing and executing statements is controlled by API functions and methods. Они не имеют отношения к языку Transact-SQLTransact-SQL.It is not part of the Transact-SQLTransact-SQL language. Модель подготовки и выполнения инструкций Transact-SQLTransact-SQL поддерживается поставщиком OLE DB для собственного клиента SQL ServerSQL Server, а также драйвером ODBC для собственного клиента SQL ServerSQL Server.The prepare/execute model of executing Transact-SQLTransact-SQL statements is supported by the SQL ServerSQL Server Native Client OLE DB Provider and the SQL ServerSQL Server Native Client ODBC driver. При запросе на подготовку поставщик или драйвер отправляет в SQL ServerSQL Server инструкцию с запросом на подготовку инструкции.On a prepare request, either the provider or the driver sends the statement to SQL ServerSQL Server with a request to prepare the statement. SQL ServerSQL Server компилирует план выполнения и возвращает его дескриптор поставщику или драйверу.compiles an execution plan and returns a handle for that plan to the provider or driver. При запросе на выполнение поставщик или драйвер отправляет на сервер запрос на выполнение плана, связанного с этим дескриптором.On an execute request, either the provider or the driver sends the server a request to execute the plan that is associated with the handle.

В SQL ServerSQL Server подготовленные инструкции нельзя применять для создания временных объектов.Prepared statements cannot be used to create temporary objects on SQL ServerSQL Server. Подготовленные инструкции не могут содержать ссылки на системные хранимые процедуры, создающие временные объекты, такие как временные таблицы.Prepared statements cannot reference system stored procedures that create temporary objects, such as temporary tables. Эти процедуры следует выполнять напрямую.These procedures must be executed directly.

Злоупотребление моделью подготовки и выполнения может отрицательно сказаться на производительности.Excess use of the prepare/execute model can degrade performance. Если инструкция выполняется только один раз, то для прямого выполнения потребуется только один цикл приема-передачи с сервером.If a statement is executed only once, a direct execution requires only one network round-trip to the server. Для подготовки и выполнения инструкции Transact-SQLTransact-SQL, которая выполняется только один раз, потребуется два таких цикла: один для подготовки и один для выполнения.Preparing and executing an Transact-SQLTransact-SQL statement executed only one time requires an extra network round-trip; one trip to prepare the statement and one trip to execute it.

Подготовка инструкции более эффективна, если используются маркеры параметров.Preparing a statement is more effective if parameter markers are used. Предположим, что приложение случайно запросило сведения о продукте из образца базы данных AdventureWorks .For example, assume that an application is occasionally asked to retrieve product information from the AdventureWorks sample database. Эти сведения можно извлечь двумя способами.There are two ways the application can do this.

Приложение может выполнять отдельный запрос по каждому необходимому продукту:Using the first way, the application can execute a separate query for each product requested:

SELECT * FROM AdventureWorks2014.Production.Product
WHERE ProductID = 63;

Второй способ заключается в следующем.Using the second way, the application does the following:

  1. Приложение подготавливает инструкцию, содержащую маркер параметра (?):Prepares a statement that contains a parameter marker (?):
    SELECT * FROM AdventureWorks2014.Production.Product  
    WHERE ProductID = ?;
    
  2. Затем оно связывает переменную программы с этим маркером.Binds a program variable to the parameter marker.
  3. Каждый раз, когда требуются сведения о продукте, приложение присваивает связанной переменной ключевое значение и выполняет инструкцию.Each time product information is needed, fills the bound variable with the key value and executes the statement.

Второй способ более эффективен, если инструкция выполняется более трех раз.The second way is more efficient when the statement is executed more than three times.

В SQL ServerSQL Server модель подготовки и выполнения не дает существенного прироста производительности по сравнению с непосредственным выполнением из-за того, как SQL ServerSQL Server повторно использует планы выполнения.In SQL ServerSQL Server, the prepare/execute model has no significant performance advantage over direct execution, because of the way SQL ServerSQL Server reuses execution plans. В SQL ServerSQL Server предусмотрены эффективные алгоритмы для сопоставления текущих инструкций Transact-SQLTransact-SQL и планов выполнения, созданных для предыдущих случаев выполнения той же инструкции Transact-SQLTransact-SQL.SQL ServerSQL Server has efficient algorithms for matching current Transact-SQLTransact-SQL statements with execution plans that are generated for prior executions of the same Transact-SQLTransact-SQL statement. Если приложение несколько раз выполняет инструкцию Transact-SQLTransact-SQL с маркерами параметров, то со второго выполнения SQL ServerSQL Server будет использовать готовый план выполнения (если этот план не будет удален из кэша планов).If an application executes a Transact-SQLTransact-SQL statement with parameter markers multiple times, SQL ServerSQL Server will reuse the execution plan from the first execution for the second and subsequent executions (unless the plan ages from the plan cache). Впрочем, у модели подготовки и выполнения есть следующие достоинства:The prepare/execute model still has these benefits:

  • поиск плана производится путем идентификации дескриптора, что эффективнее алгоритмов, которые применяются для сопоставления инструкции Transact-SQLTransact-SQL и существующих планов выполнения;Finding an execution plan by an identifying handle is more efficient than the algorithms used to match an Transact-SQLTransact-SQL statement to existing execution plans.
  • приложение может управлять временем создания и повторного использования плана выполнения;The application can control when the execution plan is created and when it is reused.
  • Модель подготовки и выполнения можно переносить в другие базы данных, включая более ранние версии SQL ServerSQL Server.The prepare/execute model is portable to other databases, including earlier versions of SQL ServerSQL Server.

Сканирование параметровParameter Sniffing

Сканирование параметров — это процесс, посредством которого SQL ServerSQL Server "сканирует" текущие значения параметров во время компиляции или перекомпиляции и передает их оптимизатору запросов для создания более эффективных планов запросов."Parameter sniffing" refers to a process whereby SQL ServerSQL Server "sniffs" the current parameter values during compilation or recompilation, and passes it along to the Query Optimizer so that they can be used to generate potentially more efficient query execution plans.

Значения параметров сканируются во время компиляции или перекомпиляции для следующих типов пакетов:Parameter values are sniffed during compilation or recompilation for the following types of batches:

  • Хранимые процедурыStored procedures
  • Запросы, отправленные через sp_executesqlQueries submitted via sp_executesql
  • Подготовленные запросыPrepared queries

Дополнительные сведения об устранении неполадок с выявлением неверных параметров см. в разделе Устранение неполадок при выполнении запросов с проблемами в плане выполнения запроса, зависящими от параметров.For more information on troubleshooting bad parameter sniffing issues, see Troubleshoot queries with parameter-sensitive query execution plan issues.

Примечание

Для запросов, в которых используется указание RECOMPILE, сканируются как значения параметров, так и текущие значения локальных переменных.For queries using the RECOMPILE hint, both parameter values and current values of local variables are sniffed. Сканируемые значения (параметров и локальных переменных) — это значения, которые имеются в пакете, прямо перед выполнением указания RECOMPILE.The values sniffed (of parameters and local variables) are those that exist at the place in the batch just before the statement with the RECOMPILE hint. В частности для параметров значения, которые поставляются вместе с вызовом пакета, не сканируются.In particular, for parameters, the values that came along with the batch invocation call are not sniffed.

Параллельная обработка запросовParallel Query Processing

SQL ServerSQL Server обеспечивает параллельную обработку запросов, оптимизирующую выполнение запросов и операции с индексами на компьютерах, где установлено несколько микропроцессоров (ЦП).provides parallel queries to optimize query execution and index operations for computers that have more than one microprocessor (CPU). Благодаря параллельной обработке запросов и параллельному выполнению операций с индексами с помощью нескольких рабочих потоков операционной системы SQL ServerSQL Server выполняет эти операции быстрее и эффективнее.Because SQL ServerSQL Server can perform a query or index operation in parallel by using several operating system worker threads, the operation can be completed quickly and efficiently.

Во время оптимизации запроса SQL ServerSQL Server пытается обнаружить запросы и операции с индексами, которые можно ускорить за счет параллельного выполнения.During query optimization, SQL ServerSQL Server looks for queries or index operations that might benefit from parallel execution. Для таких запросов SQL ServerSQL Server вставляет в план выполнения операторы обмена, чтобы подготовить запрос к параллельной обработке.For these queries, SQL ServerSQL Server inserts exchange operators into the query execution plan to prepare the query for parallel execution. Операторы обмена служат для управления процессом, перераспределения данных и управления потоком.An exchange operator is an operator in a query execution plan that provides process management, data redistribution, and flow control. К ним относятся логические операторы Distribute Streams, Repartition Streamsи Gather Streams (в качестве подтипов), один или несколько из которых появляются в выводе инструкции Showplan плана запроса для параллельного запроса.The exchange operator includes the Distribute Streams, Repartition Streams, and Gather Streams logical operators as subtypes, one or more of which can appear in the Showplan output of a query plan for a parallel query.

Важно!

Определенные конструкции блокируют для SQL ServerSQL Server возможность использования параллелизма для всего плана выполнения или его частей.Certain constructs inhibit SQL ServerSQL Server's ability to leverage parallelism on the entire execution plan, or parts or the execution plan.

Конструкции, которые блокируют параллелизм, включают перечисленные ниже.Constructs that inhibit parallelism include:

После вставки операторов обмена получается план параллельного выполнения запроса.After exchange operators are inserted, the result is a parallel-query execution plan. План параллельного выполнения запроса может использовать несколько рабочих потоков.A parallel-query execution plan can use more than one worker thread. План последовательного выполнения, который используется для обработки непараллельных (серийных) запросов, использует только один рабочий поток.A serial execution plan, used by a non-parallel (serial) query, uses only one worker thread for its execution. Фактическое количество рабочих потоков для параллельного выполнения запроса определяется при инициализации плана выполнения запроса и зависит от сложности и степени параллелизма плана.The actual number of worker threads used by a parallel query is determined at query plan execution initialization and is determined by the complexity of the plan and the degree of parallelism.

Степень параллелизма определяет максимальное количество используемых ЦП, а не количество используемых рабочих потоков.Degree of parallelism (DOP) determines the maximum number of CPUs that are being used; it does not mean the number of worker threads that are being used. Ограничение этой степени задается для каждой задачи.The DOP limit is set per task. Оно не задается для каждого запроса.It is not a per request or per query limit. Это означает, что во время параллельного выполнения один запрос может порождать множество задач, назначаемых планировщику.This means that during a parallel query execution, a single request can spawn multiple tasks which are assigned to a scheduler. Количество процессоров, превышающее значение MAXDOP, может одновременно использоваться в любой момент выполнения запроса, когда разные задачи выполняются параллельно.More processors than specififed by the MAXDOP may be used concurrently at any given point of query execution, when different tasks are executed concurrently. Дополнительные сведения см. в статье Руководство по архитектуре потоков и задач.For more information, see the Thread and Task Architecture Guide.

Оптимизатор запросов SQL ServerSQL Server не использует план параллельного выполнения для запроса, если выполняется любое из следующих условий.The SQL ServerSQL Server Query Optimizer does not use a parallel execution plan for a query if any one of the following conditions is true:

  • Затраты на последовательное выполнение запроса не настолько высоки, чтобы альтернативой ему считался план параллельного выполнения.The serial execution cost of the query is not high enough to consider an alternative, parallel execution plan.
  • План последовательного выполнения признан более быстрым, чем любой другой возможный план параллельного выполнения данного запроса.A serial execution plan is considered faster than any possible parallel execution plan for the particular query.
  • Запрос содержит скалярные или реляционные операторы, параллельное выполнение которых невозможно.The query contains scalar or relational operators that cannot be run in parallel. Определенные операторы могут привести к выполнению участка запроса или всего плана целиком в последовательном режиме.Certain operators can cause a section of the query plan to run in serial mode, or the whole plan to run in serial mode.

Степень параллелизмаDegree of Parallelism

SQL ServerSQL Server автоматически обнаруживает высшую степень параллелизма для каждого экземпляра параллельного выполнения запроса или индекс операции языка DDL.automatically detects the best degree of parallelism for each instance of a parallel query execution or index data definition language (DDL) operation. Это осуществляется на основе следующих критериев.It does this based on the following criteria:

  1. Работает ли SQL ServerSQL Server на компьютере, имеющем более одного микропроцессора или ЦП (таком как симметричный многопроцессорный компьютер (SMP)).Whether SQL ServerSQL Server is running on a computer that has more than one microprocessor or CPU, such as a symmetric multiprocessing computer (SMP). Использовать параллельные запросы могут только компьютеры, имеющие более одного ЦП.Only computers that have more than one CPU can use parallel queries.

  2. Достаточно ли доступных рабочих потоков.Whether sufficient worker threads are available. Каждый запрос или операция с индексами требуют определенного числа рабочих потоков.Each query or index operation requires a certain number of worker threads to execute. Для выполнения параллельного плана требуется больше рабочих потоков, чем для выполнения последовательного плана, и по мере увеличения степени параллелизма число необходимых рабочих потоков возрастает.Executing a parallel plan requires more worker threads than a serial plan, and the number of required worker threads increases with the degree of parallelism. Если требования к рабочим потокам для параллельного плана с определенной степенью параллелизма не могут быть удовлетворены, Компонент SQL Server Database EngineSQL Server Database Engine автоматически уменьшает степень параллелизма или полностью отказывается от параллельного плана в указанном контексте рабочей нагрузки.When the worker thread requirement of the parallel plan for a specific degree of parallelism cannot be satisfied, the Компонент SQL Server Database EngineSQL Server Database Engine decreases the degree of parallelism automatically or completely abandons the parallel plan in the specified workload context. В этом случае он выполняет последовательный план (с одним рабочим потоком).It then executes the serial plan (one worker thread).

  3. Тип выполняемого запроса или операции с индексами.The type of query or index operation executed. Операции с индексами, которые создают или перестраивают индекс или удаляют кластеризованный индекс и запросы, интенсивно использующие циклы ЦП, являются лучшими кандидатами для параллельного плана.Index operations that create or rebuild an index, or drop a clustered index and queries that use CPU cycles heavily are the best candidates for a parallel plan. Например, хорошими кандидатами являются соединения больших таблиц, больших статистических выражений и сортировка больших результирующих наборов.For example, joins of large tables, large aggregations, and sorting of large result sets are good candidates. Простые запросы, часто находящиеся в приложениях обработки транзакций, находят дополнительную координацию, запрашиваемую для выполнения запроса в параллельном перевешивании возможного повышения производительности.Simple queries, frequently found in transaction processing applications, find the additional coordination required to execute a query in parallel outweigh the potential performance boost. Чтобы различать запросы, которые выигрывают от параллелизма, и запросы, которые не выигрывают от параллелизма, Компонент SQL Server Database EngineSQL Server Database Engine сравнивает предполагаемую стоимость выполняемого запроса или операции индекса со значением ценового ограничения для параллелизма.To distinguish between queries that benefit from parallelism and those that do not benefit, the Компонент SQL Server Database EngineSQL Server Database Engine compares the estimated cost of executing the query or index operation with the cost threshold for parallelism value. Пользователи могут изменить значение по умолчанию 5 при помощи sp_configure, если при надлежащем тестировании найдено другое значение, которое больше подходит для выполнения рабочей нагрузки.Users can change the default value of 5 using sp_configure if proper testing found that a different value is better suited for the running workload.

  4. Достаточно ли количество строк, подлежащих обработке.Whether there are a sufficient number of rows to process. Если оптимизатор запросов устанавливает, что число строк слишком мало, то для распространения строк он не вставляет операторы преобразования валюты.If the Query Optimizer determines that the number of rows is too low, it does not introduce exchange operators to distribute the rows. Следовательно, операторы обрабатываются последовательно.Consequently, the operators are executed serially. Обработка операторов в последовательном плане позволяет избежать сценариев, когда стоимость запуска, распределения и координации превышает преимущества, достигнутые параллельной обработкой оператора.Executing the operators in a serial plan avoids scenarios when the startup, distribution, and coordination costs exceed the gains achieved by parallel operator execution.

  5. Доступна ли статистика распределения.Whether current distribution statistics are available. Если наивысшая степень параллелизма невозможна, более низкие степени рассматриваются до того, как отвергается параллельный план.If the highest degree of parallelism is not possible, lower degrees are considered before the parallel plan is abandoned. Например, статистика распределения не может вычисляться при создании кластеризованного индекса на представлении, потому что кластеризованный индекс еще не существует.For example, when you create a clustered index on a view, distribution statistics cannot be evaluated, because the clustered index does not yet exist. В таком случае компонент Компонент SQL Server Database EngineSQL Server Database Engine не может предоставить наивысшую степень параллелизма для операции с индексами.In this case, the Компонент SQL Server Database EngineSQL Server Database Engine cannot provide the highest degree of parallelism for the index operation. Однако некоторые операторы, такие как сортировка и сканирование, по-прежнему могут выигрывать от параллельной обработки.However, some operators, such as sorting and scanning, can still benefit from parallel execution.

Примечание

Параллельные операции с индексами доступны только в выпусках SQL ServerSQL Server Developer Edition, Evaluation Edition и Enterprise Edition.Parallel index operations are only available in SQL ServerSQL Server Enterprise, Developer, and Evaluation editions.

Во время выполнения компонент Компонент SQL Server Database EngineSQL Server Database Engine устанавливает, разрешены ли описанные ранее текущая рабочая нагрузка системы и конфигурация для параллельного выполнения.At execution time, the Компонент SQL Server Database EngineSQL Server Database Engine determines whether the current system workload and configuration information previously described allow for parallel execution. Если параллельное выполнение гарантировано, Компонент SQL Server Database EngineSQL Server Database Engine определяет оптимальное число рабочих потоков и распределяет выполнение параллельного плана по этим рабочим потокам.If parallel execution is warranted, the Компонент SQL Server Database EngineSQL Server Database Engine determines the optimal number of worker threads and spreads the execution of the parallel plan across those worker threads. Если запрос или операция с индексами начинает параллельно выполняться в нескольких рабочих потоках, это же число рабочих потоков используется до тех пор, пока операция не будет завершена.When a query or index operation starts executing on multiple worker threads for parallel execution, the same number of worker threads is used until the operation is completed. Компонент SQL Server Database EngineSQL Server Database Engine повторно определяет оптимальное число рабочих потоков каждый раз при получении плана выполнения из кэша планов.The Компонент SQL Server Database EngineSQL Server Database Engine re-examines the optimal number of worker thread decisions every time an execution plan is retrieved from the plan cache. Например, при первом выполнении запроса может использоваться последовательный план, при повторном выполнении того же запроса — параллельный план с тремя рабочими потоками, при третьем выполнении — параллельный план с четырьмя рабочими потоками.For example, one execution of a query can result in the use of a serial plan, a later execution of the same query can result in a parallel plan using three worker threads, and a third execution can result in a parallel plan using four worker threads.

В плане параллельного выполнения запроса операторы вставки, обновления и удаления обрабатываются последовательно.In a parallel query execution plan, the insert, update, and delete operators are executed serially. Однако предложение WHERE инструкции UPDATE или DELETE или часть SELECT инструкции INSERT могут обрабатываться параллельно.However, the WHERE clause of an UPDATE or a DELETE statement, or the SELECT part of an INSERT statement may be executed in parallel. В таком случае изменения фактических данных последовательно применяются к базе данных.The actual data changes are then serially applied to the database.

Статические курсоры и курсоры, управляемые набором ключей, могут быть заполнены параллельными планами выполнения.Static and keyset-driven cursors can be populated by parallel execution plans. Однако поведение динамических курсоров может поддерживаться только последовательным выполнением.However, the behavior of dynamic cursors can be provided only by serial execution. Оптимизатор запросов всегда формирует последовательный план выполнения для запроса, являющегося частью динамического курсора.The Query Optimizer always generates a serial execution plan for a query that is part of a dynamic cursor.

Переопределение степеней параллелизмаOverriding Degrees of Parallelism

Максимальная степень параллелизма задает количество процессоров, используемых при одновременном исполнении планов.The degree of parallelism sets the number of processors to use in parallel plan execution. Эту конфигурацию можно задать на различных уровнях:This configuration can be set at various levels:

  1. На уровне сервера — с помощью параметра конфигурации сервера max degree of parallelism (MAXDOP) .Server level, using the max degree of parallelism (MAXDOP) server configuration option.
    Применимо к: SQL ServerSQL ServerApplies to: SQL ServerSQL Server

    Примечание

    SQL Server 2019 (15.x)SQL Server 2019 (15.x) содержит автоматические рекомендации по настройке параметра конфигурации сервера MAXDOP в процессе установки.SQL Server 2019 (15.x)SQL Server 2019 (15.x) introduces automatic recommendations for setting the MAXDOP server configuration option during the installation process. Пользовательский интерфейс программы установки позволяет либо принять рекомендуемые параметры, либо задать свое значение.The setup user interface allows you to either accept the recommended settings or enter your own value. Дополнительные сведения см. в разделе Конфигурация ядра СУБД — страница MaxDOP.For more information, see Database Engine Configuration - MaxDOP page.

  2. На уровне рабочих нагрузок — с помощью параметра конфигурации группы рабочей нагрузки Resource Governor MAX_DOP.Workload level, using the MAX_DOP Resource Governor workload group configuration option.
    Применимо к: SQL ServerSQL ServerApplies to: SQL ServerSQL Server

  3. На уровне базы данных — с помощью конфигурации области баз данных MAXDOP.Database level, using the MAXDOP database scoped configuration.
    Область применения: SQL ServerSQL Server и База данных SQL AzureAzure SQL DatabaseApplies to: SQL ServerSQL Server and База данных SQL AzureAzure SQL Database

  4. На уровне инструкции запроса или индекса — с помощью указания запроса MAXDOP или параметра индекса MAXDOP.Query or index statement level, using the MAXDOP query hint or MAXDOP index option. Например, с помощью параметра MAXDOP можно увеличить или уменьшить число процессоров, выделенных для операций с индексами в сети.For example, you can use the MAXDOP option to control, by increasing or reducing, the number of processors dedicated to an online index operation. Таким образом, можно сбалансировать ресурсы, используемые операцией с индексами с теми текущими пользователями.In this way, you can balance the resources used by an index operation with those of the concurrent users.
    Область применения: SQL ServerSQL Server и База данных SQL AzureAzure SQL DatabaseApplies to: SQL ServerSQL Server and База данных SQL AzureAzure SQL Database

Если указать для параметра "максимальная степень параллелизма" значение 0 (по умолчанию), SQL ServerSQL Server сможет использовать все доступные процессоры (до 64) при выполнении параллельного плана.Setting the max degree of parallelism option to 0 (default) enables SQL ServerSQL Server to use all available processors up to a maximum of 64 processors in a parallel plan execution. Хотя при использовании значения 0 параметра MAXDOP целевое количество процессоров в SQL ServerSQL Server составляет 64 логических процессора, при необходимости можно указать другое значение вручную.Although SQL ServerSQL Server sets a runtime target of 64 logical processors when MAXDOP option is set to 0, a different value can be manually set if needed. При использовании значения 0 параметра MAXDOP для запросов и индексов SQL ServerSQL Server использует все доступные процессоры (максимально допустимое количество процессоров равно 64) для данных запросов и индексов при выполнении параллельного плана.Setting MAXDOP to 0 for queries and indexes allows SQL ServerSQL Server to use all available processors up to a maximum of 64 processors for the given queries or indexes in a parallel plan execution. Значение MAXDOP не применяется принудительно для всех параллельных запросов. Оно представляет собой условный целевой показатель для всех запросов, которые можно выполнять параллельно.MAXDOP is not an enforced value for all parallel queries, but rather a tentative target for all queries eligible for parallelism. Это означает, что если во время выполнения доступно недостаточное количество рабочих потоков, запрос может выполняться со степенью параллелизма, более низкой по сравнению с той, которая задана в параметре MAXDOP.This means that if not enough worker threads are available at runtime, a query may execute with a lower degree of parallelism than the MAXDOP server configuration option.

Совет

Инструкции по настройке MAXDOP см. на этой странице документации.Refer to this documentation page for guidelines on configuring MAXDOP.

Пример параллельного запросаParallel Query Example

В нижеследующем запросе подсчитывается количество заказов, размещенных в течение указанного квартала, начиная с 1 апреля 2000, в которых хотя бы один элемент из списка заказанных товаров был получен заказчиком позже фиксированной даты.The following query counts the number of orders placed in a specific quarter, starting on April 1, 2000, and in which at least one line item of the order was received by the customer later than the committed date. В этом запросе представлен подсчет таких заказов, сгруппированных в соответствии со срочностью каждого заказа и отсортированных в возрастающем порядке.This query lists the count of such orders grouped by each order priority and sorted in ascending priority order.

В этом примере используются теоретические имена таблицы и столбцов.This example uses theoretical table and column names.

SELECT o_orderpriority, COUNT(*) AS Order_Count
FROM orders
WHERE o_orderdate >= '2000/04/01'
   AND o_orderdate < DATEADD (mm, 3, '2000/04/01')
   AND EXISTS
         (
          SELECT *
            FROM    lineitem
            WHERE l_orderkey = o_orderkey
               AND l_commitdate < l_receiptdate
         )
   GROUP BY o_orderpriority
   ORDER BY o_orderpriority

Предположим, что в таблицах lineitem и orders определены следующие индексы:Assume the following indexes are defined on the lineitem and orders tables:

CREATE INDEX l_order_dates_idx 
   ON lineitem
      (l_orderkey, l_receiptdate, l_commitdate, l_shipdate)

CREATE UNIQUE INDEX o_datkeyopr_idx
   ON ORDERS
      (o_orderdate, o_orderkey, o_custkey, o_orderpriority)

Вот один из возможных параллельных планов, созданный для запроса, показанного выше:Here is one possible parallel plan generated for the query previously shown:

|--Stream Aggregate(GROUP BY:([ORDERS].[o_orderpriority])
                  DEFINE:([Expr1005]=COUNT(*)))
    |--Parallelism(Gather Streams, ORDER BY:
                  ([ORDERS].[o_orderpriority] ASC))
         |--Stream Aggregate(GROUP BY:
                  ([ORDERS].[o_orderpriority])
                  DEFINE:([Expr1005]=Count(*)))
              |--Sort(ORDER BY:([ORDERS].[o_orderpriority] ASC))
                   |--Merge Join(Left Semi Join, MERGE:
                  ([ORDERS].[o_orderkey])=
                        ([LINEITEM].[l_orderkey]),
                  RESIDUAL:([ORDERS].[o_orderkey]=
                        [LINEITEM].[l_orderkey]))
                        |--Sort(ORDER BY:([ORDERS].[o_orderkey] ASC))
                        |    |--Parallelism(Repartition Streams,
                           PARTITION COLUMNS:
                           ([ORDERS].[o_orderkey]))
                        |         |--Index Seek(OBJECT:
                     ([tpcd1G].[dbo].[ORDERS].[O_DATKEYOPR_IDX]),
                     SEEK:([ORDERS].[o_orderdate] >=
                           Apr  1 2000 12:00AM AND
                           [ORDERS].[o_orderdate] <
                           Jul  1 2000 12:00AM) ORDERED)
                        |--Parallelism(Repartition Streams,
                     PARTITION COLUMNS:
                     ([LINEITEM].[l_orderkey]),
                     ORDER BY:([LINEITEM].[l_orderkey] ASC))
                             |--Filter(WHERE:
                           ([LINEITEM].[l_commitdate]<
                           [LINEITEM].[l_receiptdate]))
                                  |--Index Scan(OBJECT:
         ([tpcd1G].[dbo].[LINEITEM].[L_ORDER_DATES_IDX]), ORDERED)

На рисунке показан план запросов, который выполняется со степенью параллелизма, равной 4, и включает соединение двух таблиц.The illustration below shows a query plan executed with a degree of parallelism equal to 4 and involving a two-table join.

parallel_plan

Параллельный план содержит три оператора параллелизма.The parallel plan contains three parallelism operators. Оба оператора, Index Seek для индекса o_datkey_ptr и Index Scan для индекса l_order_dates_idx, выполняются параллельно.Both the Index Seek operator of the o_datkey_ptr index and the Index Scan operator of the l_order_dates_idx index are performed in parallel. В результате образуется несколько исключающих потоков.This produces several exclusive streams. Это можно определить по ближайшим операторам параллелизма над операторами Index Scan и Index Seek соответственно.This can be determined from the nearest Parallelism operators above the Index Scan and Index Seek operators, respectively. Оба перераспределяют тип обмена.Both are repartitioning the type of exchange. То есть они всего лишь перегруппируют данные между потоками и выдают в результате столько же потоков на выходе, сколько их было на входе.That is, they are just reshuffling data among the streams and producing the same number of streams on their output as they have on their input. Количество потоков равно степени параллелизма.This number of streams is equal to the degree of parallelism.

Оператор параллелизма над оператором Index Seek l_order_dates_idx перераспределяет свои входные потоки с использованием значения L_ORDERKEY в качестве ключа.The parallelism operator above the l_order_dates_idx Index Scan operator is repartitioning its input streams using the value of L_ORDERKEY as a key. В этом случае те же значения L_ORDERKEY выдаются в том же выходном потоке.In this way, the same values of L_ORDERKEY end up in the same output stream. Одновременно в выходных потоках сохраняется порядок в столбце L_ORDERKEY для соответствия требованиям оператора Merge Join к входным данным.At the same time, output streams maintain the order on the L_ORDERKEY column to meet the input requirement of the Merge Join operator.

Оператор параллелизма над оператором Index Seek перераспределяет свои входные потоки с использованием значения O_ORDERKEY.The parallelism operator above the Index Seek operator is repartitioning its input streams using the value of O_ORDERKEY. Так как его входные данные не сортируются по значениям столбца O_ORDERKEY, а он является столбцом соединения в операторе Merge Join, то оператор Sort между операторами параллелизма и Merge Join обеспечивает сортировку входных данных для оператора Merge Join по столбцам соединения.Because its input is not sorted on the O_ORDERKEY column values and this is the join column in the Merge Join operator, the Sort operator between the parallelism and Merge Join operators make sure that the input is sorted for the Merge Join operator on the join columns. Оператор Sort, как и оператор Merge Join, выполняется параллельно.The Sort operator, like the Merge Join operator, is performed in parallel.

Первый оператор параллелизма объединяет результаты из нескольких потоков в один.The topmost parallelism operator gathers results from several streams into a single stream. Результаты частичной статистической обработки, выполняемой оператором Stream Aggregate под оператором параллелизма, затем собираются в единое значение SUM для каждого отдельного значения O_ORDERPRIORITY в операторе Stream Aggregate над оператором параллелизма.Partial aggregations performed by the Stream Aggregate operator below the parallelism operator are then accumulated into a single SUM value for each different value of the O_ORDERPRIORITY in the Stream Aggregate operator above the parallelism operator. Так как этот план состоит из двух сегментов обмена со степенью параллелизма, равной 4, в этом плане используется восемь рабочих потоков.Because this plan has two exchange segments, with degree of parallelism equal to 4, it uses eight worker threads.

Дополнительные сведения об операторах, используемых в этом примере, см. в справочнике по логическим и физическим операторам Showplan.For more information on the operators used in this example, refer to the Showplan Logical and Physical Operators Reference.

Параллельные операции с индексамиParallel Index Operations

Планы запросов, созданные для операций создания или перестроения индекса либо удаления кластеризованного индекса, поддерживают возможность параллельной обработки в нескольких рабочих потоках на многопроцессорных компьютерах.The query plans built for the index operations that create or rebuild an index, or drop a clustered index, allow for parallel, multi-worker threaded operations on computers that have multiple microprocessors.

Примечание

Параллельные операции с индексами доступны, начиная с выпуска SQL Server 2008SQL Server 2008.Parallel index operations are only available in Enterprise Edition, starting with SQL Server 2008SQL Server 2008.

В SQL ServerSQL Server используются одни и те же алгоритмы определения степени параллелизма (общего числа отдельных рабочих потоков, которые будут запущены) как для операций с индексами, так и для других запросов.SQL ServerSQL Server uses the same algorithms to determine the degree of parallelism (the total number of separate worker threads to run) for index operations as it does for other queries. Максимальная степень параллелизма для операции с индексом определяется параметром конфигурации сервера max degree of parallelism .The maximum degree of parallelism for an index operation is subject to the max degree of parallelism server configuration option. Значение max degree of parallelism можно переопределять для отдельных операций с индексами путем настройки параметра индекса MAXDOP в инструкциях CREATE INDEX, ALTER INDEX, DROP INDEX и ALTER TABLE.You can override the max degree of parallelism value for individual index operations by setting the MAXDOP index option in the CREATE INDEX, ALTER INDEX, DROP INDEX, and ALTER TABLE statements.

Когда Компонент SQL Server Database EngineSQL Server Database Engine создает план выполнения индекса, количество параллельных операций устанавливается в самое низкое из следующих значений.When the Компонент SQL Server Database EngineSQL Server Database Engine builds an index execution plan, the number of parallel operations is set to the lowest value from among the following:

  • Число микропроцессоров (ЦП) в компьютере.The number of microprocessors, or CPUs in the computer.
  • Число, указанное в качестве параметра конфигурации сервера max degree of parallelism.The number specified in the max degree of parallelism server configuration option.
  • Число ЦП, которые не превышают порог загруженности для рабочих потоков SQL ServerSQL Server.The number of CPUs not already over a threshold of work performed for SQL ServerSQL Server worker threads.

Например, на компьютере с восемью ЦП, на котором максимальная степень параллелизма равна 6, для операций с индексами создается не более шести параллельных рабочих потоков.For example, on a computer that has eight CPUs, but where max degree of parallelism is set to 6, no more than six parallel worker threads are generated for an index operation. Если на пяти ЦП компьютера будет превышено ограничение количества рабочих потоков SQL ServerSQL Server, когда строится план выполнения индексации, в плане выполнения будет указано только три параллельных рабочих потока.If five of the CPUs in the computer exceed the threshold of SQL ServerSQL Server work when an index execution plan is built, the execution plan specifies only three parallel worker threads.

Главные фазы параллельных операций с индексами таковы.The main phases of a parallel index operation include the following:

  • Координирующий рабочий поток быстро и случайным образом просматривает таблицу для оценки распределения ключей индекса.A coordinating worker thread quickly and randomly scans the table to estimate the distribution of the index keys. Координирующий рабочий поток устанавливает ключевые границы, образующие число диапазонов ключей, равное степени параллелизма. Каждый диапазон должен покрывать примерно одинаковое число строк.The coordinating worker thread establishes the key boundaries that will create a number of key ranges equal to the degree of parallel operations, where each key range is estimated to cover similar numbers of rows. Например, если в таблице четыре миллиона строк, а степень параллелизма равна 4, то координирующий рабочий поток определит ключевые значения, которые разделят все строки на четыре набора строк по одному миллиону строк в каждом.For example, if there are four million rows in the table and the degree of parallelism is 4, the coordinating worker thread will determine the key values that delimit four sets of rows with 1 million rows in each set. Если для использования всех ЦП невозможно установить достаточное число диапазонов ключей, степень параллелизма соответствующим образом снижается.If enough key ranges cannot be established to use all CPUs, the degree of parallelism is reduced accordingly.
  • Координирующий рабочий поток запускает рабочие потоки, количество которых равно степени параллелизма операций, и ожидает завершения этих потоков.The coordinating worker thread dispatches a number of worker threads equal to the degree of parallel operations and waits for these worker threads to complete their work. Каждый из рабочих потоков просматривает базовую таблицу с использованием фильтра, который отделяет только строки со значениями ключей в диапазоне, назначенном этому рабочему потоку.Each worker thread scans the base table using a filter that retrieves only rows with key values within the range assigned to the worker thread. Каждый рабочий поток создает структуру индекса для строк в своем диапазоне ключей.Each worker thread builds an index structure for the rows in its key range. В случае секционированного индекса каждый из рабочих потоков создает заданное число секций.In the case of a partitioned index, each worker thread builds a specified number of partitions. Одни и те же секции не разделяются между несколькими рабочими потоками.Partitions are not shared among worker threads.
  • После завершения работы всех параллельных рабочих потоков координирующий рабочих поток связывает компоненты индекса в единый индекс.After all the parallel worker threads have completed, the coordinating worker thread connects the index subunits into a single index. Эта фаза применяется только для операций с индексами в сети.This phase applies only to offline index operations.

В отдельных инструкциях CREATE TABLE или ALTER TABLE могут содержаться несколько ограничений, требующих создания индекса.Individual CREATE TABLE or ALTER TABLE statements can have multiple constraints that require that an index be created. Такие множественные операции по созданию индекса выполняются последовательно, хотя каждая из них может быть параллельной операцией на многопроцессорном компьютере.These multiple index creation operations are performed in series, although each individual index creation operation may be a parallel operation on a computer that has multiple CPUs.

Архитектура распределенных запросовDistributed Query Architecture

Microsoft SQL ServerSQL Server поддерживает два метода обращения к разнородным источникам данных OLE DB в инструкциях Transact-SQLTransact-SQL.Microsoft SQL ServerSQL Server supports two methods for referencing heterogeneous OLE DB data sources in Transact-SQLTransact-SQL statements:

  • Имена связанных серверовLinked server names
    Системные хранимые процедуры sp_addlinkedserver и sp_addlinkedsrvlogin используются для задания серверного имени источнику данных OLE DB.The system stored procedures sp_addlinkedserver and sp_addlinkedsrvlogin are used to give a server name to an OLE DB data source. К объектам на этих связанных серверах можно обращаться в инструкциях языка Transact-SQLTransact-SQL по четырехкомпонентным именам.Objects in these linked servers can be referenced in Transact-SQLTransact-SQL statements using four-part names. Например, если имя связанного сервера DeptSQLSrvr определено для другого экземпляра SQL ServerSQL Server, для обращения к таблице на таком сервере используется следующая инструкция.For example, if a linked server name of DeptSQLSrvr is defined against another instance of SQL ServerSQL Server, the following statement references a table on that server:

    SELECT JobTitle, HireDate 
    FROM DeptSQLSrvr.AdventureWorks2014.HumanResources.Employee;
    

    Имя связанного сервера можно также указать в инструкции OPENQUERY для открытия набора строк из источника данных OLE DB.The linked server name can also be specified in an OPENQUERY statement to open a rowset from the OLE DB data source. К этому набору строк можно обращаться в инструкциях языка Transact-SQLTransact-SQL так же, как и к таблице:This rowset can then be referenced like a table in Transact-SQLTransact-SQL statements.

  • Имена нерегламентированных соединителейAd hoc connector names
    Для нечастых обращений к источнику данных используются функции OPENROWSET или OPENDATASOURCE , которым задаются данные, необходимые для подключения к связанному серверу.For infrequent references to a data source, the OPENROWSET or OPENDATASOURCE functions are specified with the information needed to connect to the linked server. Затем можно обращаться к набору строк в инструкциях языка Transact-SQLTransact-SQL тем же путем, что и к таблице:The rowset can then be referenced the same way a table is referenced in Transact-SQLTransact-SQL statements:

    SELECT *
    FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
          'c:\MSOffice\Access\Samples\Northwind.mdb';'Admin';'';
          Employees);
    

В SQL ServerSQL Server для коммуникации между реляционным механизмом и подсистемой хранилища используется технология OLE DB.SQL ServerSQL Server uses OLE DB to communicate between the relational engine and the storage engine. Реляционный механизм разбивает каждую инструкцию языка Transact-SQLTransact-SQL на последовательные операции над простыми наборами строк OLE DB, открываемые подсистемой хранилища из базовых таблиц.The relational engine breaks down each Transact-SQLTransact-SQL statement into a series of operations on simple OLE DB rowsets opened by the storage engine from the base tables. Это означает, что реляционный механизм может также открывать простые наборы строк OLE DB на любом источнике данных OLE DB.This means the relational engine can also open simple OLE DB rowsets on any OLE DB data source.
oledb_storageoledb_storage
В реляционном механизме используется прикладной программный интерфейс (API) OLE DB для открытия наборов строк на связанных серверах, выборки строк и управления транзакциями.The relational engine uses the OLE DB application programming interface (API) to open the rowsets on linked servers, fetch the rows, and manage transactions.

Для каждого источника данных OLE DB, доступ к которому осуществляется как к связанному серверу, на сервере с запущенной службой SQL ServerSQL Server должен быть поставщик OLE DB.For each OLE DB data source accessed as a linked server, an OLE DB provider must be present on the server running SQL ServerSQL Server. Набор операций языка Transact-SQLTransact-SQL, которые можно использовать с конкретным источником данных OLE DB, зависит от возможностей поставщика OLE DB.The set of Transact-SQLTransact-SQL operations that can be used against a specific OLE DB data source depends on the capabilities of the OLE DB provider.

Для каждого экземпляра SQL ServerSQL Server участники предопределенной роли сервера sysadmin могут включать или отключать использование нерегламентированных имен соединителей для поставщика OLE DB с помощью свойства SQL ServerSQL Server DisallowAdhocAccess.For each instance of SQL ServerSQL Server, members of the sysadmin fixed server role can enable or disable the use of ad-hoc connector names for an OLE DB provider using the SQL ServerSQL Server DisallowAdhocAccess property. Если нерегламентированный доступ включен, любой пользователь, зарегистрированный на данном экземпляре, может выполнять инструкции Transact-SQLTransact-SQL, содержащие имена нерегламентированных соединителей, обращающиеся к любым источникам данных в сети, доступ к которым возможен посредством данного поставщика OLE DB.When ad-hoc access is enabled, any user logged on to that instance can execute Transact-SQLTransact-SQL statements containing ad-hoc connector names, referencing any data source on the network that can be accessed using that OLE DB provider. Для управления доступом к источникам данных члены роли sysadmin могут отключить нерегламентированный доступ к определенному поставщику OLE DB, ограничивая таким образом пользователям доступ лишь к тем источникам данных, обращение к которым производится по именам связанных серверов, определенным администраторами.To control access to data sources, members of the sysadmin role can disable ad-hoc access for that OLE DB provider, thereby limiting users to only those data sources referenced by linked server names defined by the administrators. По умолчанию нерегламентированный доступ включен для поставщика OLE DB SQL ServerSQL Server и отключен для всех остальных поставщиков OLE DB.By default, ad-hoc access is enabled for the SQL ServerSQL Server OLE DB provider, and disabled for all other OLE DB providers.

С помощью распределенных запросов пользователи могут обращаться к другим источникам данных (например, файлам, нереляционным источникам данных, таким как Active Directory и др.) с помощью контекста безопасности учетной записи Microsoft Windows, от имени которой запущена служба SQL ServerSQL Server.Distributed queries can allow users to access another data source (for example, files, non-relational data sources such as Active Directory, and so on) using the security context of the Microsoft Windows account under which the SQL ServerSQL Server service is running. SQL ServerSQL Server может олицетворять имена для входа в Windows, но в случае имен для входа SQL ServerSQL Server это невозможно.impersonates the login appropriately for Windows logins; however, that is not possible for SQL ServerSQL Server logins. Это потенциально может открыть пользователю распределенного запроса доступ к другому источнику данных, для которого у него нет разрешения, но у учетной записи, под которой запущена служба SQL ServerSQL Server, такое разрешение есть.This can potentially allow a distributed query user to access another data source for which they do not have permissions, but the account under which the SQL ServerSQL Server service is running does have permissions. Для указания конкретных имен входа, которым будет разрешен доступ к соответствующему связанному серверу, используется процедура sp_addlinkedsrvlogin .Use sp_addlinkedsrvlogin to define the specific logins that are authorized to access the corresponding linked server. Для нерегламентированных имен такой контроль недоступен, поэтому следует проявлять осторожность при включении нерегламентированного доступа к поставщику OLE DB.This control is not available for ad-hoc names, so use caution in enabling an OLE DB provider for ad-hoc access.

По возможности SQL ServerSQL Server принудительно отправляет реляционные операции (соединения, ограничения, проекции, сортировки и группировки по операциям) к источнику данных OLE DB.When possible, SQL ServerSQL Server pushes relational operations such as joins, restrictions, projections, sorts, and group by operations to the OLE DB data source. По умолчанию SQL ServerSQL Server не просматривает базовую таблицу в SQL ServerSQL Server и не выполняет реляционных операций самостоятельно.SQL ServerSQL Server does not default to scanning the base table into SQL ServerSQL Server and performing the relational operations itself. SQL ServerSQL Server запрашивает у поставщика OLE DB уровень поддерживаемой им грамматики SQL и на основе этих данных направляет поставщику максимально возможное число реляционных операций.queries the OLE DB provider to determine the level of SQL grammar it supports, and, based on that information, pushes as many relational operations as possible to the provider.

SQL ServerSQL Server указывает поставщику OLE DB механизм возвращения статистики распределения ключевых значений в пределах источника данных OLE DB.specifies a mechanism for an OLE DB provider to return statistics indicating how key values are distributed within the OLE DB data source. Это позволяет оптимизатору запросов SQL ServerSQL Server лучше проанализировать шаблон данных в источнике данных на соответствие требованиям для каждой инструкции Transact-SQLTransact-SQL, что позволяет более эффективно создавать оптимальные планы выполнения.This lets the SQL ServerSQL Server Query Optimizer better analyze the pattern of data in the data source against the requirements of each Transact-SQLTransact-SQL statement, increasing the ability of the Query Optimizer to generate optimal execution plans.

Улучшенные возможности обработки запросов для секционированных таблиц и индексовQuery Processing Enhancements on Partitioned Tables and Indexes

В SQL Server 2008SQL Server 2008 повышена эффективность обработки запросов к секционированным таблицам для множества параллельных планов, изменен способ представления параллельных и последовательных планов и улучшены сведения о секционировании, содержащиеся в планах выполнения времени компиляции и времени выполнения.SQL Server 2008SQL Server 2008 improved query processing performance on partitioned tables for many parallel plans, changes the way parallel and serial plans are represented, and enhanced the partitioning information provided in both compile-time and run-time execution plans. В этом разделе описываются названные улучшения, содержится справочник об интерпретации планов выполнения запросов таблиц секционирования и индексов и дополнительные сведения об улучшении производительности запросов к секционированным объектам.This topic describes these improvements, provides guidance on how to interpret the query execution plans of partitioned tables and indexes, and provides best practices for improving query performance on partitioned objects.

Примечание

Секционированные таблицы и индексы поддерживаются только в выпусках SQL ServerSQL Server Enterprise Edition, Developer Edition и Evaluation Edition.Partitioned tables and indexes are supported only in the SQL ServerSQL Server Enterprise, Developer, and Evaluation editions.

Новая операция поиска, учитывающая секционированиеNew Partition-Aware Seek Operation

В SQL ServerSQL Server внутреннее представление секционированной таблицы изменено таким образом, что таблица представляется обработчику запросов как индекс по нескольким столбцам с PartitionID в качестве начального столбца.In SQL ServerSQL Server, the internal representation of a partitioned table is changed so that the table appears to the query processor to be a multicolumn index with PartitionID as the leading column. PartitionID представляет собой скрытый внутренний вычисляемый столбец для представления ID секции, содержащей определенную строку.PartitionID is a hidden computed column used internally to represent the ID of the partition containing a specific row. Например, предположим, что таблица T, определенная как T(a, b, c), секционирована по столбцу a и содержит кластеризованный индекс по столбцу b.For example, assume the table T, defined as T(a, b, c), is partitioned on column a, and has a clustered index on column b. В SQL ServerSQL Server эта секционированная таблица обрабатывается как несекционированная таблица со схемой T(PartitionID, a, b, c) и кластеризованным индексом по составному ключу (PartitionID, b).In SQL ServerSQL Server, this partitioned table is treated internally as a nonpartitioned table with the schema T(PartitionID, a, b, c) and a clustered index on the composite key (PartitionID, b). Это позволяет оптимизатору запросов выполнять операции поиска на основе PartitionID по любой секционированной таблице или индексу.This allows the Query Optimizer to perform seek operations based on PartitionID on any partitioned table or index.

Устранение секций теперь осуществляется в этой операции поиска.Partition elimination is now done in this seek operation.

In addition, the Query Optimizer is extended so that a seek or scan operation with one condition can be done on PartitionID (в качестве логического начального столбца) и, возможно, по другим ключевым столбцам индекса, а затем может быть выполнен поиск второго уровня с другим условием по одному дополнительному столбцу или более для каждого уникального значения, удовлетворяющего операции поиска первого уровня.In addition, the Query Optimizer is extended so that a seek or scan operation with one condition can be done on PartitionID (as the logical leading column) and possibly other index key columns, and then a second-level seek, with a different condition, can be done on one or more additional columns, for each distinct value that meets the qualification for the first-level seek operation. Операция, называемая "просмотр с пропуском", позволяет оптимизатору запросов выполнять операцию поиска или просмотра по одному условию для определения секций, к которым будет осуществляться доступ, и операцию поиска индекса второго уровня с помощью этого оператора для выборки строк из этих секций, удовлетворяющих другому условию.That is, this operation, called a skip scan, allows the Query Optimizer to perform a seek or scan operation based on one condition to determine the partitions to be accessed and a second-level index seek operation within that operator to return rows from these partitions that meet a different condition. Например, рассмотрим следующий запрос.For example, consider the following query.

SELECT * FROM T WHERE a < 10 and b = 2;

В данном примере, предположим, таблица T, определенная как T(a, b, c), секционирована по столбцу a и содержит кластеризованный индекс по столбцу b.For this example, assume that table T, defined as T(a, b, c), is partitioned on column a, and has a clustered index on column b. Границы секции для таблицы T определены следующей функцией секционирования:The partition boundaries for table T are defined by the following partition function:

CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES (3, 7, 10);

Для разрешения запроса обработчик запросов выполняет операцию поиска первого уровня для нахождения каждой секции, содержащей строки, удовлетворяющие условию T.a < 10.To solve the query, the query processor performs a first-level seek operation to find every partition that contains rows that meet the condition T.a < 10. Это позволяет выявить секции, к которым необходимо получить доступ.This identifies the partitions to be accessed. В каждой выявленной секции обработчик выполняет поиск второго уровня по кластеризованному индексу по столбцу b для нахождения строк, удовлетворяющих условию T.b = 2 и T.a < 10.Within each partition identified, the processor then performs a second-level seek into the clustered index on column b to find the rows that meet the condition T.b = 2 and T.a < 10.

На следующем рисунке изображено логическое представление операции просмотра с пропуском.The following illustration is a logical representation of the skip scan operation. На нем изображена таблица T с данными в столбцах a и b.It shows table T with data in columns a and b. Секции пронумерованы от 1 до 4, а границы секций показаны вертикальными штриховыми линиями.The partitions are numbered 1 through 4 with the partition boundaries shown by dashed vertical lines. Операция поиска первого уровня для секций (на иллюстрации не показана) определила, что секции 1, 2 и 3 удовлетворяют условию поиска, предполагаемого секционированием, определенным для таблицы и предиката по столбцу a.A first-level seek operation to the partitions (not shown in the illustration) has determined that partitions 1, 2, and 3 meet the seek condition implied by the partitioning defined for the table and the predicate on column a. то есть T.a < 10.That is, T.a < 10. Путь, пройденный частью операции просмотра с пропуском, поиском второго уровня, изображен изогнутой линией.The path traversed by the second-level seek portion of the skip scan operation is illustrated by the curved line. Фактически операция просмотра с пропуском выполняет поиск строк, удовлетворяющих условию b = 2в каждой их этих секций.Essentially, the skip scan operation seeks into each of these partitions for rows that meet the condition b = 2. Общие затраты на выполнение операции просмотра с пропуском соответствуют трем отдельным поискам по индексу.The total cost of the skip scan operation is the same as that of three separate index seeks.

skip_scan

Отображение сведений о секционировании в планах выполнения запросовDisplaying Partitioning Information in Query Execution Plans

Планы выполнения запросов в секционированных таблицах и индексах можно изучить с помощью инструкций SET SHOWPLAN_XML или SET STATISTICS XML языка Transact-SQLTransact-SQL SET или с помощью графического представления в среде SQL ServerSQL Server Management Studio.The execution plans of queries on partitioned tables and indexes can be examined by using the Transact-SQLTransact-SQL SET statements SET SHOWPLAN_XML or SET STATISTICS XML, or by using the graphical execution plan output in SQL ServerSQL Server Management Studio. Например, план выполнения времени компиляции можно отобразить, щелкнув Показать предполагаемый план выполнения на панели инструментов редактора запросов, а план времени выполнения — щелкнув Включить действительный план выполнения.For example, you can display the compile-time execution plan by clicking Display Estimated Execution Plan on the Query Editor toolbar and the run-time plan by clicking Include Actual Execution Plan.

С помощью этих средств можно получить следующую информацию:Using these tools, you can ascertain the following information:

  • операции, такие как scans, seeks, inserts, updates, mergesи deletes , которые осуществляют доступ к таблицам и индексам;The operations such as scans, seeks, inserts, updates, merges, and deletes that access partitioned tables or indexes.
  • секции, к которым запрос получает доступ —The partitions accessed by the query. например, в планах времени выполнения приведено общее число секций, к которым получен доступ, и диапазоны смежных секций, к которым получен доступ;For example, the total count of partitions accessed and the ranges of contiguous partitions that are accessed are available in run-time execution plans.
  • когда операция просмотра с пропуском используется в операции поиска или просмотра для получения данных из одной секции или более.When the skip scan operation is used in a seek or scan operation to retrieve data from one or more partitions.

Улучшенные возможности информации о секцииPartition Information Enhancements

SQL ServerSQL Server содержит расширенные сведения о секционировании как для планов времени компиляции, так и для планов времени выполнения.provides enhanced partitioning information for both compile-time and run-time execution plans. Планы выполнения теперь содержат следующую информацию.Execution plans now provide the following information:

  • Дополнительный атрибут Partitioned указывает, что оператор, например seek, scan, insert, update, mergeили delete, выполняется в отношении секционированной таблицы.An optional Partitioned attribute that indicates that an operator, such as a seek, scan, insert, update, merge, or delete, is performed on a partitioned table.
  • Новый элемент SeekPredicateNew с вложенным элементом SeekKeys , содержащим PartitionID в качестве начального ключевого столбца индекса и условия фильтра, определяющие операции поиска по диапазону в PartitionID.A new SeekPredicateNew element with a SeekKeys subelement that includes PartitionID as the leading index key column and filter conditions that specify range seeks on PartitionID. Наличие двух вложенных элементов SeekKeys указывает на то, что в отношении PartitionID используется операция просмотра с пропуском.The presence of two SeekKeys subelements indicates that a skip scan operation on PartitionID is used.
  • Сводные данные об общем числе секций, к которым получен доступ.Summary information that provides a total count of the partitions accessed. Эта информация доступна только в планах времени выполнения.This information is available only in run-time plans.

Для демонстрации отображения этой информации как в графическом плане выполнения, так и в отчете инструкции XML Showplan рассмотрим следующий запрос по секционированной таблице fact_sales.To demonstrate how this information is displayed in both the graphical execution plan output and the XML Showplan output, consider the following query on the partitioned table fact_sales. Этот запрос обновляет данные в двух секциях.This query updates data in two partitions.

UPDATE fact_sales
SET quantity = quantity * 2
WHERE date_id BETWEEN 20080802 AND 20080902;

На следующем рисунке показаны свойства оператора Clustered Index Seek в плане выполнения времени компиляции для этого запроса.The following illustration shows the properties of the Clustered Index Seek operator in the compile-time execution plan for this query. Определение таблицы fact_sales и определение секции см. в подразделе "Пример" в этом разделе.To view the definition of the fact_sales table and the partition definition, see "Example" in this topic.

clustered_index_seek

Атрибут PartitionedPartitioned Attribute

Когда оператор, такой как Index Seek, выполняется применительно к секционированной таблице или индексу, в планах времени компиляции и времени выполнения появляется атрибут Partitioned со значением True (1).When an operator such as an Index Seek is executed on a partitioned table or index, the Partitioned attribute appears in the compile-time and run-time plan and is set to True (1). Этот атрибут не отображается, если его значение установлено как False (0).The attribute does not display when it is set to False (0).

Атрибут Partitioned может встречаться в следующих физических и логических операторах:The Partitioned attribute can appear in the following physical and logical operators:
||| |--------|--------| |Table ScanTable Scan|Index ScanIndex Scan| |Index SeekIndex Seek|ВставитьInsert| |UpdateUpdate|DELETEDelete| |ОбъединитьMerge||

Как показано на предыдущей иллюстрации, этот атрибут отображается в свойствах оператора, в котором он определен.As shown in the previous illustration, this attribute is displayed in the properties of the operator in which it is defined. В отчете инструкции XML Showplan этот атрибут появляется как Partitioned="1" в узле RelOp оператора, в котором он определен.In the XML Showplan output, this attribute appears as Partitioned="1" in the RelOp node of the operator in which it is defined.

Предикат New SeekNew Seek Predicate

В выводе инструкции XML Showplan элемент SeekPredicateNew появляется в операторе, в котором он определен.In XML Showplan output, the SeekPredicateNew element appears in the operator in which it is defined. Он может содержать до двух экземпляров вложенного элемента SeekKeys .It can contain up to two occurrences of the SeekKeys sub-element. Первый элемент SeekKeys определяет операцию поиска первого уровня на уровне идентификатора секции логического индекса.The first SeekKeys item specifies the first-level seek operation at the partition ID level of the logical index. То есть эта операция поиска определяет секции, к которым должен быть осуществлен доступ для удовлетворения условий запроса.That is, this seek determines the partitions that must be accessed to satisfy the conditions of the query. Второй элемент SeekKeys определяет часть операции просмотра с пропуском, поиск второго уровня, который производится в каждой секции, определенной поиском первого уровня.The second SeekKeys item specifies the second-level seek portion of the skip scan operation that occurs within each partition identified in the first-level seek.

Сводные данные по секциямPartition Summary Information

В планах времени выполнения сводка по секциям содержит данные о числе секций, к которым осуществлен доступ, и фактический перечень секций, к которым осуществлен доступ.In run-time execution plans, partition summary information provides a count of the partitions accessed and the identity of the actual partitions accessed. С помощью этих данных можно проверить, к правильным ли секциям обращается запрос и исключены ли из рассмотрения остальные секции.You can use this information to verify that the correct partitions are accessed in the query and that all other partitions are eliminated from consideration.

Предоставляется следующая информация: Actual Partition Countи Partitions Accessed.The following information is provided: Actual Partition Count, and Partitions Accessed.

Actual Partition Count — это общее число секций, к которым запрос получает доступ.Actual Partition Count is the total number of partitions accessed by the query.

Partitions Accessedв выводе инструкции XML Showplan — это сводные данные по секциям, которые появляются в новом элементе RuntimePartitionSummary в узле RelOp оператора, в котором он определен.Partitions Accessed, in XML Showplan output, is the partition summary information that appears in the new RuntimePartitionSummary element in RelOp node of the operator in which it is defined. В следующем примере показано содержимое элемента RuntimePartitionSummary , указывающее, что получен доступ только к двум секциям (секции 2 и 3).The following example shows the contents of the RuntimePartitionSummary element, indicating that two total partitions are accessed (partitions 2 and 3).

<RunTimePartitionSummary>

    <PartitionsAccessed PartitionCount="2" >

        <PartitionRange Start="2" End="3" />

    </PartitionsAccessed>

</RunTimePartitionSummary>

Отображение сведений о секционировании с помощью других методов ShowplanDisplaying Partition Information by Using Other Showplan Methods

Методы Showplan SHOWPLAN_ALL, SHOWPLAN_TEXTи STATISTICS PROFILE не формируют сведения о секционировании, описанные в этом разделе, за следующим исключением.The Showplan methods SHOWPLAN_ALL, SHOWPLAN_TEXT, and STATISTICS PROFILE do not report the partition information described in this topic, with the following exception. Как часть предиката SEEK , секции, к которым необходимо получить доступ, обозначаются предикатом по диапазону в вычисляемом столбце, представляющем идентификатор секций.As part of the SEEK predicate, the partitions to be accessed are identified by a range predicate on the computed column representing the partition ID. В следующем примере показан предикат SEEK для оператора Clustered Index Seek .The following example shows the SEEK predicate for a Clustered Index Seek operator. К секциям 2 и 3 происходит обращение, и оператор поиска производит фильтрацию по строкам, удовлетворяющим условию date_id BETWEEN 20080802 AND 20080902.Partitions 2 and 3 are accessed, and the seek operator filters on the rows that meet the condition date_id BETWEEN 20080802 AND 20080902.

|--Clustered Index Seek(OBJECT:([db_sales_test].[dbo].[fact_sales].[ci]), 

        SEEK:([PtnId1000] >= (2) AND [PtnId1000] \<= (3) 

                AND [db_sales_test].[dbo].[fact_sales].[date_id] >= (20080802) 

                AND [db_sales_test].[dbo].[fact_sales].[date_id] <= (20080902)) 

                ORDERED FORWARD)

Интерпретация планов выполнения для секционированной кучиInterpreting Execution Plans for Partitioned Heaps

Секционированная куча обрабатывается как логический индекс по идентификатору секции.A partitioned heap is treated as a logical index on the partition ID. Устранение секций на секционированной куче представлено в плане выполнения в виде оператора Table Scan с предикатом SEEK по идентификатору секции.Partition elimination on a partitioned heap is represented in an execution plan as a Table Scan operator with a SEEK predicate on partition ID. Следующий пример отображает сведения Showplan:The following example shows the Showplan information provided:

|-- Table Scan (OBJECT: ([db].[dbo].[T]), SEEK: ([PtnId1001]=[Expr1011]) ORDERED FORWARD)

Интерпретация планов выполнения для выровненных соединенийInterpreting Execution Plans for Collocated Joins

Выравнивание соединений может возникать, когда две таблицы секционированы с использованием одной и той же функции или эквивалентных функций секционирования и столбцы секционирования из обеих сторон соединения указываются в условии соединения запроса.Join collocation can occur when two tables are partitioned using the same or equivalent partitioning function and the partitioning columns from both sides of the join are specified in the join condition of the query. Оптимизатор запросов может сформировать план, в котором секции каждой таблицы, имеющие равные идентификаторы, соединяются отдельно.The Query Optimizer can generate a plan where the partitions of each table that have equal partition IDs are joined separately. Выровненные соединения могут выполняться быстрее, чем невыровненные, поскольку требуют меньшего объема памяти и времени обработки.Collocated joins can be faster than non-collocated joins because they can require less memory and processing time. Оптимизатор выбирает невыровненный план или выровненный план исходя из расчета затрат.The Query Optimizer chooses a non-collocated plan or a collocated plan based on cost estimates.

В выровненных планах соединение Nested Loops считывает одну или более секций для соединяемых таблиц или индексов с внутренней стороны.In a collocated plan, the Nested Loops join reads one or more joined table or index partitions from the inner side. Цифры в операторах Constant Scan представляют собой номера секций.The numbers within the Constant Scan operators represent the partition numbers.

Если для секционированных таблиц или индексов формируются параллельные планы для выровненных соединений, то между операторами соединения Constant Scan и Nested Loops появляется оператор Parallelism.When parallel plans for collocated joins are generated for partitioned tables or indexes, a Parallelism operator appears between the Constant Scan and the Nested Loops join operators. В этом случае каждый из нескольких рабочих потоков на внешней стороне соединения считывает разные секции и работает с разными секциями.In this case, multiple worker threads on the outer side of the join each read and work on a different partition.

Следующий рисунок демонстрирует план параллельных запросов для выровненных соединений.The following illustration demonstrates a parallel query plan for a collocated join.
colocated_join

Стратегия выполнения параллельных запросов для секционированных объектовParallel Query Execution Strategy for Partitioned Objects

Обработчик запросов использует стратегию параллельного выполнения для запросов, производящих выборку из секционированных объектов.The query processor uses a parallel execution strategy for queries that select from partitioned objects. В рамках стратегии выполнения обработчик запросов определяет секции таблицы, необходимые для запроса, и долю рабочих потоков, которую следует выделить для каждой секции.As part of the execution strategy, the query processor determines the table partitions required for the query and the proportion of worker threads to allocate to each partition. В большинстве случаев обработчик запросов выделяет равное или почти равное количество рабочих потоков для каждой секции, а затем выполняет запрос параллельно на всех секциях.In most cases, the query processor allocates an equal or almost equal number of worker threads to each partition, and then executes the query in parallel across the partitions. Выделение рабочих потоков более подробно описано ниже.The following paragraphs explain worker thread allocation in greater detail.

Рабочий поток 1

Если число рабочих потоков меньше числа секций, обработчик запросов назначает по одному рабочему потоку каждой отдельной секции, оставляя несколько секций без рабочих потоков.If the number of worker threads is less than the number of partitions, the query processor assigns each worker thread to a different partition, initially leaving one or more partitions without an assigned worker thread. Когда рабочий поток завершает работу с секцией, обработчик запросов назначает этот поток следующей секции. Это продолжается до тех пор, пока у каждой секции не будет по одному рабочему потоку.When a worker thread finishes executing on a partition, the query processor assigns it to the next partition until each partition has been assigned a single worker thread. Это единственный случай, когда обработчик запросов перераспределяет рабочие потоки к другим секциям.This is the only case in which the query processor reallocates worker threads to other partitions.
Отображает рабочий поток, повторно назначенный после завершения.Shows worker thread reassigned after it finishes. Если число рабочих потоков равно числу секций, обработчик запросов назначает каждой секции по одному рабочему потоку.If the number of worker threads is equal to the number of partitions, the query processor assigns one worker thread to each partition. После того как рабочий поток заканчивает работу, он не назначается другой секции.When a worker thread finishes, it is not reallocated to another partition.

Рабочий поток 2

Если число рабочих потоков больше числа секций, обработчик запросов назначает каждой секции одинаковое число рабочих потоков.If the number of worker threads is greater than the number of partitions, the query processor allocates an equal number of worker threads to each partition. Если число рабочих потоков не кратно числу секций, обработчик запросов выделяет по одному дополнительному рабочему потоку для некоторых секций, чтобы были использованы все доступные рабочие потоки.If the number of worker threads is not an exact multiple of the number of partitions, the query processor allocates one additional worker thread to some partitions in order to use all of the available worker threads. Обратите внимание, что если секция всего одна, ей будут назначены все потоки.Note that if there is only one partition, all worker threads will be assigned to that partition. На приведенном ниже рисунке показаны четыре секции и 14 рабочих потоков.In the diagram below, there are four partitions and 14 worker threads. Каждой секции назначено по 3 рабочих потока, у двух секций есть дополнительные рабочие потоки; всего назначено 14 рабочих потоков.Each partition has 3 worker threads assigned, and two partitions have an additional worker thread, for a total of 14 worker thread assignments. После того как рабочий поток заканчивает работу, он не назначается другой секции.When a worker thread finishes, it is not reassigned to another partition.

Рабочий поток 3

В приведенных выше примерах демонстрируется достаточно прямолинейный способ распределения рабочих потоков. Реальная стратегия более сложна; она учитывает другие факторы, которые возникают при выполнении запроса.Although the above examples suggest a straightforward way to allocate worker threads, the actual strategy is more complex and accounts for other variables that occur during query execution. Например, если таблица секционирована и имеет кластеризованный индекс для столбца А, а в запросе используется предложение предиката WHERE A IN (13, 17, 25), то обработчик запросов выделит один рабочий поток или несколько каждому из трех искомых значений из значений поиска (A=13, A=17 и A=25), а не каждой секции таблицы.For example, if the table is partitioned and has a clustered index on column A and a query has the predicate clause WHERE A IN (13, 17, 25), the query processor will allocate one or more worker threads to each of these three seek values (A=13, A=17, and A=25) instead of each table partition. Запрос необходимо выполнить только в секциях, содержащих эти значения; если все предикаты поиска будут расположены в одной секции таблицы, все рабочие потоки будут назначены этой секции.It is only necessary to execute the query in the partitions that contain these values, and if all of these seek predicates happen to be in the same table partition, all of the worker threads will be assigned to the same table partition.

Другой пример: предположим, что таблица имеет четыре секции для столбца A с граничными точками (10, 20, 30), индекс на столбце B, а в запросе содержится предикат WHERE B IN (50, 100, 150).To take another example, suppose that the table has four partitions on column A with boundary points (10, 20, 30), an index on column B, and the query has a predicate clause WHERE B IN (50, 100, 150). Так как секции таблицы основаны на значениях A, значения столбца B могут появляться во всех секциях таблицы.Because the table partitions are based on the values of A, the values of B can occur in any of the table partitions. Поэтому обработчик запросов будет искать каждое из этих трех значений столбца B (50, 100, 150) в каждой из четырех секций таблицы.Thus, the query processor will seek for each of the three values of B (50, 100, 150) in each of the four table partitions. Обработчик запросов распределит рабочие потоки пропорционально, чтобы эти 12 операций сканирования запроса могли выполняться параллельно.The query processor will assign worker threads proportionately so that it can execute each of these 12 query scans in parallel.

Секции таблицы основаны на столбце АTable partitions based on column A Операции поиска для столбца B в каждой секции таблицыSeeks for column B in each table partition
Секция таблицы 1: A < 10Table Partition 1: A < 10 B = 50, B = 100, B = 150B=50, B=100, B=150
Секция таблицы 2: A >= 10 AND A < 20Table Partition 2: A >= 10 AND A < 20 B = 50, B = 100, B = 150B=50, B=100, B=150
Секция таблицы 3: A >= 20 И A < 30Table Partition 3: A >= 20 AND A < 30 B = 50, B = 100, B = 150B=50, B=100, B=150
Секция таблицы 4: A >= 30Table Partition 4: A >= 30 B = 50, B = 100, B = 150B=50, B=100, B=150

РекомендацииBest Practices

Для увеличения производительности запросов, обращающихся к большому количеству данных из больших секционированных таблиц и индексов, предлагаются следующие рекомендации.To improve the performance of queries that access a large amount of data from large partitioned tables and indexes, we recommend the following best practices:

  • Распределяйте каждую секцию по нескольким дискам.Stripe each partition across many disks. Это особенно актуально при использовании шпиндельных жестких дисков.This is especially relevant when using spinning disks.
  • Чтобы снизить затраты на ввод-вывод, по возможности используйте сервер с достаточным объемом основной памяти, вмещающей секции, требующие частого доступа, или все секции.When possible, use a server with enough main memory to fit frequently accessed partitions or all partitions in memory to reduce I/O cost.
  • Если данные, по которым выполняется запрос, не помещаются в памяти, рекомендуется выполнить сжатие таблиц и индексов.If the data you query will not fit in memory, compress the tables and indexes. Это позволит снизить затраты на ввод-вывод.This will reduce I/O cost.
  • Чтобы в полной мере реализовать возможности параллельной обработки запросов, используйте сервер с быстрыми процессорами и как можно большим числом процессорных ядер.Use a server with fast processors and as many processor cores as you can afford, to take advantage of parallel query processing capability.
  • Обеспечьте достаточную пропускную способность контроллера ввода-вывода для сервера.Ensure the server has sufficient I/O controller bandwidth.
  • Чтобы в полной мере реализовать возможности оптимизированного просмотра сбалансированного дерева, создайте кластеризованный индекс по каждой большой секционированной таблице.Create a clustered index on every large partitioned table to take advantage of B-tree scanning optimizations.
  • При массовой загрузке данных в секционированные таблицы следуйте рекомендациям, приведенным в техническом документе The Data Loading Performance Guide (Руководство по эффективной загрузке данных).Follow the best practice recommendations in the white paper, The Data Loading Performance Guide, when bulk loading data into partitioned tables.

ПримерExample

В следующем примере показано создание тестовой базы данных, состоящей из одной таблицы с семью секциями.The following example creates a test database containing a single table with seven partitions. Чтобы при выполнении запросов в этом примере просматривать сведения о секционировании в планах времени компиляции и времени выполнения, следует пользоваться инструментами, описанными ранее.Use the tools described previously when executing the queries in this example to view partitioning information for both compile-time and run-time plans.

Примечание

В данном примере в таблицу вставляется более 1 миллиона строк.This example inserts more than 1 million rows into the table. В зависимости от имеющегося оборудования выполнение данного примера может занять несколько минут.Running this example may take several minutes depending on your hardware. Перед выполнением этого примера следует убедиться, что на диске 1,5 ГБ свободного места.Before executing this example, verify that you have more than 1.5 GB of disk space available.

USE master;
GO
IF DB_ID (N'db_sales_test') IS NOT NULL
    DROP DATABASE db_sales_test;
GO
CREATE DATABASE db_sales_test;
GO
USE db_sales_test;
GO
CREATE PARTITION FUNCTION [pf_range_fact](int) AS RANGE RIGHT FOR VALUES 
(20080801, 20080901, 20081001, 20081101, 20081201, 20090101);
GO
CREATE PARTITION SCHEME [ps_fact_sales] AS PARTITION [pf_range_fact] 
ALL TO ([PRIMARY]);
GO
CREATE TABLE fact_sales(date_id int, product_id int, store_id int, 
    quantity int, unit_price numeric(7,2), other_data char(1000))
ON ps_fact_sales(date_id);
GO
CREATE CLUSTERED INDEX ci ON fact_sales(date_id);
GO
PRINT 'Loading...';
SET NOCOUNT ON;
DECLARE @i int;
SET @i = 1;
WHILE (@i<1000000)
BEGIN
    INSERT INTO fact_sales VALUES(20080800 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, '');
    SET @i += 1;
END;
GO
DECLARE @i int;
SET @i = 1;
WHILE (@i<10000)
BEGIN
    INSERT INTO fact_sales VALUES(20080900 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, '');
    SET @i += 1;
END;
PRINT 'Done.';
GO
-- Two-partition query.
SET STATISTICS XML ON;
GO
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080802 AND 20080902
GROUP BY date_id ;
GO
SET STATISTICS XML OFF;
GO
-- Single-partition query.
SET STATISTICS XML ON;
GO
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080801 AND 20080831
GROUP BY date_id;
GO
SET STATISTICS XML OFF;
GO

Дополнительные материалыAdditional Reading

Справочник по логическим и физическим операторам ShowplanShowplan Logical and Physical Operators Reference
Расширенные событияExtended Events
Рекомендации по хранилищу запросовBest Practice with the Query Store
Оценка количества элементовCardinality Estimation
Интеллектуальная обработка запросов Intelligent query processing
Приоритет операторов Operator Precedence
Планы выполнения Execution Plans
Центр производительности для базы данных SQL Azure и ядра СУБД SQL ServerPerformance Center for SQL Server Database Engine and Azure SQL Database