查询处理体系结构指南Query Processing Architecture Guide

适用对象:yesSQL ServeryesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

SQL Server 数据库引擎SQL Server Database Engine 可处理对各种数据存储体系结构(例如,本地表、已分区表和分布在多个服务器上的表)执行的查询。The SQL Server 数据库引擎SQL 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 数据库引擎SQL Server Database Engine 可使用两种不同的处理模式处理 Transact-SQLTransact-SQL 语句:The SQL Server 数据库引擎SQL 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. 批模式处理还使用一些算法,这些算法针对多核 CPU 和新式硬件上的内存吞吐量增加进行了优化。Batch mode processing also uses algorithms that are optimized for the multi-core CPUs and increased memory throughput that are found on modern hardware.

批模式执行与列存储存储格式紧密集成,并且围绕列存储存储格式进行了优化。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.

当在批模式下执行查询并且查询访问列存储索引中的数据时,执行树运算符和子运算符会一次读取列段中的多行。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.
有关列存储索引的详细信息,请参阅列存储索引体系结构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.

SQL 语句处理SQL 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

当一个语句中使用了多个逻辑运算符时,计算顺序依次为:NOTAND最后是 ORWhen 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 的优先级高于 ORIn 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

优化 SELECT 语句Optimizing 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 just a plan. 本主题的后续各节将详细介绍查询计划的内容。The contents of a query 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 BYGROUP 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. 这些条件在 WHEREHAVING 子句中指定。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:
    TableCTableBTableATableC, TableB, TableA, or
    TableBTableATableCTableB, TableA, TableC, or
    TableBTableCTableATableB, TableC, TableA, or
    TableCTableATableBTableC, 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 process of selecting one execution plan from potentially many possible plans is referred to as optimization. 查询优化器是 SQL 数据库系统的最重要组件之一。The Query Optimizer is one of the most important components of a SQL database system. 虽然查询优化器在分析查询和选择计划时要使用一些开销,但当查询优化器选择了有效的执行计划时,这一开销将节省数倍。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 Query 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 then 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.

处理 SELECT 语句Processing 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 returns 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 AND 3>4。Logical expressions, such as 1=1 and 1>2 AND 3>4, that contain only constants.
  • SQL ServerSQL Server 认为可折叠的内置函数包括 CASTCONVERTBuilt-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.

备注

使用大型对象类型时将出现例外。An exception is made for large object types. 如果折叠进程的输出类型是大型对象类型(text、image、nvarchar(max)、varchar(max) 或 varbinary(max)),则 SQL ServerSQL Server 不折叠该表达式。If the output type of the folding process is a large object type (text, image, nvarchar(max), varchar(max), or varbinary(max)), 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-SQL 和 CLR)。User-defined functions (both Transact-SQLTransact-SQL and CLR).
  • 结果取决于语言设置的表达式。Expressions whose results depend on language settings.
  • 结果取决于 SET 选项的表达式。Expressions whose results depend on SET options.
  • 结果取决于服务器配置选项的表达式。Expressions whose results depend on server configuration options.

可折叠和不可折叠常量表达式示例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.

具体而言,在编译时将计算下列内置函数和特殊运算符(如果它们的所有输入都已知):UPPERLOWERRTRIMDATEPART( YY only )GETDATECASTCONVERTSpecifically, 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:

  • 算术运算符:+、-、*、/、一元运算符 -Arithmetic operators: +, -, *, /, unary -
  • 逻辑运算符:ANDORNOTLogical Operators: AND, OR, NOT
  • 比较运算符:<、>、<=、>=、<>、LIKEIS NULLIS 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;

SQL ServerSQL Server 中优化 MyProc2 中的 SELECT语句时,@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 语句,例如 INSERTUPDATEDELETEThe basic steps described for processing a SELECT statement apply to other Transact-SQLTransact-SQL statements such as INSERT, UPDATE, and DELETE. UPDATEDELETE 语句必须把要修改或要删除的行集作为目标。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. UPDATEINSERT 语句都可以包含嵌入式 SELECT 语句,该语句提供要更新或插入的数据值。The UPDATE and INSERT statements may both contain embedded SELECT statements that provide the data values to be updated or inserted.

即使像 CREATE PROCEDUREALTER TABLE 这样的数据定义语言 (DDL) 语句也被最终解析为系统目录表上的一系列关系操作,而有时则根据数据表解析(如 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 BYORDER BYUNION 查询会生成工作表。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';

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.AddressPerson.StateProvinceThe 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.

PAGLOCKNOLOCKROWLOCKTABLOCKTABLOCKX 表提示相互冲突, HOLDLOCKNOLOCKREADCOMMITTEDREPEATABLEREADSERIALIZABLE 表提示也一样。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;

查询计划中的联接顺序为 Table1Table2TableATableBTable3The join order in the query plan is Table1, Table2, TableA, TableB, Table3.

解析视图的索引Resolving Indexes on Views

与任何索引相同,仅当查询优化器确定在 SQL ServerSQL Server 的查询计划中使用索引视图有益时,SQL 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:

  • 下列会话选项均设置为 ONThese 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 BY 子句GROUP 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.

备注

在此上下文中,不管当前事务隔离级别如何, READCOMMITTEDREADCOMMITTEDLOCK 提示始终被认为是不同的提示。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. 只在经测试证实 EXPANDNOEXPAND 可显著提高性能的特定情形中使用它们。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. 如果在视图展开后, INDEXPAGLOCKROWLOCKTABLOCKXUPDLOCKXLOCK 提示应用于查询中引用的表,则查询不适用于索引视图匹配。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. 若要指定使用特定索引,请使用 NOEXPANDTo 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-SQL 语句 WHERE 子句中指定的键范围与显示行在成员表中如何分布的映射进行比较。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 的行,并发出分布式查询以从 Server2 中检索键值从 3300000 到 3400000 的行。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. 假设在 Server1 上执行了 GetCustomer 存储过程,则执行计划逻辑可以表示如下: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. 与动态 Transact-SQLTransact-SQL 的批处理相比,存储过程和触发器在 SQL ServerSQL Server 中的主要性能优势是它们的 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 有一个用于存储执行计划和数据缓冲区的内存池。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.

SQL ServerSQL Server 执行计划包含下列主要组件:execution plans have the following main components:

  • 查询执行计划 Query Execution Plan
    执行计划的主体是一个重入的只读数据结构,可由任意数量的用户使用。The bulk of the execution plan is a re-entrant, read-only data structure used by any number of users. 这称为查询计划。This is referred to as the query plan. 查询计划中不存储用户上下文。No user context 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. 如果用户执行查询而其中的一个结构未使用,将会用新用户的上下文重新初始化该结构。If a user executes a query and one of the structures is not being used, it is reinitialized with the context for the new user.

execution_context

SQL ServerSQL Server 中执行任何 Transact-SQLTransact-SQL 语句时,关系引擎将首先查看计划缓存,以确认是否存在用于同一 Transact-SQLTransact-SQL 语句的现有执行计划。When any Transact-SQLTransact-SQL statement is executed in SQL ServerSQL Server, the Relational 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 existing execution plan exists, SQL ServerSQL Server generates a new execution plan for the query.

备注

有些 Transact-SQLTransact-SQL 语句未缓存,如在行存储上运行大容量操作语句或包含大于 8 KB 的字符串文字的语句。Some Transact-SQLTransact-SQL statements are not cached, such as bulk operation statements running on rowstore or statements containing string literals larger than 8 KB in size.

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 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:

SELECT * FROM Person;
GO
SELECT * FROM Person.Person;
GO
SELECT * FROM Person.Person;
GO

从计划缓存中删除执行计划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 数据库引擎SQL Server Database Engine将使用基于开销的方法来确定从计划缓存中删除哪些执行计划。When memory pressure exists, the SQL Server 数据库引擎SQL Server Database Engine uses a cost-based approach to determine which execution plans to remove from the plan cache. 要做出基于开销的决策,SQL Server 数据库引擎SQL Server Database Engine将根据以下因素对每个执行计划增加和降低当前开销变量。To make a cost-based decision, the SQL Server 数据库引擎SQL 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 数据库引擎SQL Server Database Engine会通过从计划缓存中删除执行计划来进行响应。When memory pressure exists, the SQL Server 数据库引擎SQL Server Database Engine responds by removing execution plans from the plan cache. 为了确定删除哪些执行计划,SQL Server 数据库引擎SQL Server Database Engine会重复检查每个执行计划的状态并将删除当前开销为零的执行计划。To determine which plans to remove, the SQL Server 数据库引擎SQL Server Database Engine repeatedly examines the state of each execution plan and removes plans when their current cost is zero. 如果存在内存不足的情况,当前开销为零的执行计划不会自动被删除,而只有在SQL Server 数据库引擎SQL 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 数据库引擎SQL Server Database Engine examines the plan and the current cost is zero. 当检查执行计划时,如果当前没有查询使用该计划,则SQL Server 数据库引擎SQL Server Database Engine将降低当前开销以将其推向零。When examining an execution plan, the SQL Server 数据库引擎SQL 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 数据库引擎SQL Server Database Engine会重复检查执行计划,直至删除了足够多的执行计划,以满足内存需求为止。The SQL Server 数据库引擎SQL 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 数据库引擎SQL Server Database Engine将停止降低未使用执行计划的当前开销,并且所有执行计划都将保留在计划缓存中,即使其开销为零也是如此。When memory pressure no longer exists, the SQL Server 数据库引擎SQL 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 数据库引擎SQL Server Database Engine使用资源监视器和用户工作线程从计划缓存中释放内存,以响应内存不足。The SQL Server 数据库引擎SQL 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 数据库引擎SQL Server Database Engine检查执行计划时,会发现当前开销为零,于是从计划缓存中删除该计划。Since ad-hoc plans are initialized with a current cost of zero, when the SQL Server 数据库引擎SQL 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 FREEPROCCACHETo 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 TABLEALTER 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_recompileAn explicit call to sp_recompile.
  • 对键进行大量更改(这些更改是对查询所引用的表进行修改的其他用户执行 INSERTDELETE 语句所产生的)。Large numbers of changes to keys (generated by INSERT or DELETE statements from other users that modify a table referenced by the query).
  • 对于带触发器的表,如果插入的或删除的表内的行数显著增长。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 2000 中,只要批处理中的语句导致重新编译,就会重新编译整个批处理,无论此批处理是通过存储过程、触发器、即席批处理,还是通过预定义的语句进行提交。In SQL ServerSQL Server 2000, whenever a statement within a batch causes recompilation, the whole batch, whether submitted through a stored procedure, trigger, ad-hoc batch, or prepared statement, is 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 causes recompilation is recompiled. 由于这种差异,SQL ServerSQL Server 2000 和更高版本中的重新编译计数不可比较。Because of this difference, recompilation counts in SQL ServerSQL Server 2000 and later releases are not comparable. 另外,由于 SQL Server 2005 (9.x)SQL Server 2005 (9.x) 和更高版本扩展了功能集,因此它们具有更多类型的重新编译。Also, there are more types of recompilations in SQL Server 2005 (9.x)SQL Server 2005 (9.x) and later because of its expanded feature set.

语句级重新编译有助于提高性能,因为在大多数情况下,只有少数语句导致了重新编译并造成相关损失(指 CPU 时间和锁)。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、“准备”方法或“执行”方法。Batches may be submitted through several interfaces, including sp_executesql, dynamic SQL, Prepare methods or Execute methods. sql_statement_recompile xEvent 的 recompile_cause 列包含一个整数代码,指示重新编译的原因。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 SET 选项已更改SET option changed
临时表已更改Temporary table changed 远程行集已更改Remote rowset changed
FOR BROWSE 权限已更改FOR 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

备注

在 xEvents 不可用的 SQL ServerSQL Server 版本中,SQL ServerSQL Server 探查器 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. SP:RecompileSQL:StmtRecompile 的 EventSubClass 列都包含一个整数代码,用以指明重新编译的原因。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. 此行为适用于标准用户定义表、临时表以及由 DML 触发器创建的插入表和删除表。This behavior applies to standard user-defined tables, temporary tables, and the inserted and deleted tables created by DML triggers. 如果过多的重新编译影响到查询性能,请考虑将此设置更改为 OFFIf 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 中创建的,因此,是否重新编译访问这些表的查询取决于 tempdb 中 AUTO_UPDATE_STATISTICS 的设置。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 2000 中,即使此设置为 OFF,查询也将继续基于 DML 触发器插入表和删除表的基数更改进行重新编译。Note that in SQL ServerSQL Server 2000, 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_executesqlIn 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);
    

    在应用程序中使用参数标记时,SQL ServerSQL Server 附带的 SQL ServerSQL Server Native Client OLE DB 提供程序和 SQL ServerSQL Server Native Client ODBC 驱动程序使用 sp_executesql 将语句发送到 SQL ServerSQL ServerThe 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. 另外,也可以通过将 ALTER DATABASE 语句的 PARAMETERIZATION 选项设置为 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 2000 中,此过程称为自动参数化。In SQL ServerSQL Server 2000, 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

通过指定将数据库中的所有 SELECTINSERTUPDATEDELETE 语句参数化,可以覆盖 SQL ServerSQL Server 的默认简单参数化行为(但会受到某些限制)。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时, SELECTINSERTUPDATEDELETE 语句中出现的任何文本值(无论以什么形式提交)都将在查询编译期间转换为参数。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 已对这些例程重用了查询计划。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. (API 游标内的SELECT 语句将参数化。)(SELECT statements inside API cursors are parameterized.)
  • 不推荐使用的查询构造。Deprecated query constructs.
  • ANSI_PADDINGANSI_NULLS 上下文中运行的任何语句都设置为 OFFAny 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 >= @bbStatements 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 语句的 <select_list>。The <select_list> of any SELECT statement. 这包括子查询的 SELECT 列表和 INSERT 语句内的 SELECT 列表。This includes SELECT lists of subqueries and SELECT lists inside INSERT statements.
  • SELECT 语句中出现的子查询 IF 语句。Subquery SELECT statements that appear inside an IF statement.
  • 查询的 TOPTABLESAMPLEHAVINGGROUP BYORDER BYOUTPUT...INTOFOR XML 子句。The TOP, TABLESAMPLE, HAVING, GROUP BY, ORDER BY, OUTPUT...INTO, or FOR XML clauses of a query.
  • OPENROWSETOPENQUERYOPENDATASOURCEOPENXML或任意 FULLTEXT 运算符的参数(直接或作为子表达式)。Arguments, either direct or as subexpressions, to OPENROWSET, OPENQUERY, OPENDATASOURCE, OPENXML, or any FULLTEXT operator.
  • LIKE 子句的模式和 escape_character 参数。The pattern and escape_character arguments of a LIKE clause.
  • 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 参数以及 MAXRECURSION 查询提示的 number 参数。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. 如果缓存了查询的执行计划,则可以通过引用 sys.syscacheobjects 动态管理视图的 sql 列来确定此查询是否已参数化。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 版本和 Service Pack 升级版之间进行更改:参数名称、参数化文字的选择以及参数化文本中的间距。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。对于较大的整数文本,如果它是包含任意比较运算符(包括 <、<=、=、!=、>、>=、!<、!>、<>、ALLANYSOMEBETWEENIN)的谓词的组成部分,则将这些文本参数化为 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). 如果它不是包含比较运算符的谓词的组成部分,则此类文本将参数化为数字,其精度仅够表示其大小,并且没有小数位。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).
  • 如果非 Unicode 字符串文本在 8,000 个字符以内,将参数化为 varchar(8000),如果多于 8,000 个字符,则参数化为 varchar(max)。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.
  • 如果 Unicode 字符串文本在 4,000 个字符以内,将参数化为 nvarchar(4000),如果多于 4,000 个字符,则参数化为 nvarchar(max)。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.
  • 如果二进制文本在 8,000 字节以内,将参数化为 varbinary(8000)。Binary literals parameterize to varbinary(8000) if the literal fits within 8,000 bytes. 如果多于 8,000 字节,则转换为 varbinary(max)。If it is larger than 8,000 bytes, it is converted to varbinary(max).
  • Money 类型的文本,将参数化为 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.

准备 SQL 语句Preparing SQL Statements

SQL ServerSQL Server 关系引擎完全支持在执行 SQL 语句前准备 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. SQL ServerSQL Server Native Client OLE DB 访问接口和 SQL ServerSQL Server Native Client ODBC 驱动程序支持用于执行 Transact-SQLTransact-SQL 语句的准备/执行模型。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 语句时生成的执行计划进行匹配。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 ServerThe 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_executesql 提交的查询Queries submitted via sp_executesql
  • 预定义查询Prepared queries

备注

对于使用 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 为具有多个微处理器 (CPU) 的计算机提供了并行查询,以优化查询执行和索引操作。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 StreamsRepartition StreamsGather Streams 逻辑运算符,其中的一个或多个运算符会出现在并行查询的查询计划的显示计划输出中。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 nonparallel 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. 并行度确定要使用的最大 CPU 数;它并不表示要使用的工作线程数。Degree of parallelism determines the maximum number of CPUs that are being used; it does not mean the number of worker threads that are being used. 并行度的值在服务器级别设置,并可使用 sp_configure 系统存储过程进行修改。The degree of parallelism value is set at the server level and can be modified by using the sp_configure system stored procedure. 通过指定 MAXDOP 查询提示或 MAXDOP 索引选项,可以针对单个查询语句或索引语句覆盖此值。You can override this value for individual query or index statements by specifying the MAXDOP query hint or MAXDOP index option.

如果下列情况之一为真,则 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 是否运行在具有多个微处理器或 CPU 的计算机(例如对称多处理计算机,即 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).
    只有具有多个 CPU 的计算机才能使用并行查询。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 数据库引擎SQL Server Database Engine将自动减少并行度或完全放弃指定的工作负荷上下文中的并行计划。When the worker thread requirement of the parallel plan for a specific degree of parallelism cannot be satisfied, the SQL Server 数据库引擎SQL 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.
    创建索引、重新生成索引或删除聚集索引等索引操作,以及大量占用 CPU 周期的查询最适合采用并行计划。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 数据库引擎SQL Server Database Engine 会将执行查询或索引操作的估计开销与并行的开销阈值进行比较。To distinguish between queries that benefit from parallelism and those that do not benefit, the SQL Server 数据库引擎SQL Server Database Engine compares the estimated cost of executing the query or index operation with the cost threshold for parallelism value. 如果适合性测试发现其他值更适合正在运行的工作负载,用户可以使用 sp_configure 更改默认值 5。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 数据库引擎SQL Server Database Engine无法为索引操作提供最高并行度。In this case, the SQL Server 数据库引擎SQL 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 Enterprise Edition、Developer Edition 和 Evaluation Edition 中使用。Parallel index operations are only available in SQL ServerSQL Server Enterprise, Developer, and Evaluation editions.

执行时,SQL Server 数据库引擎SQL Server Database Engine将确定当前系统工作负荷和前面介绍的配置信息是否允许并行执行。At execution time, the SQL Server 数据库引擎SQL Server Database Engine determines whether the current system workload and configuration information previously described allow for parallel execution. 如果可以确保并行执行,则SQL Server 数据库引擎SQL Server Database Engine将确定最佳工作线程数,并在这些工作线程间分配并行计划的执行。If parallel execution is warranted, the SQL Server 数据库引擎SQL 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 数据库引擎SQL Server Database Engine都将重新检查最佳工作线程数决策。The SQL Server 数据库引擎SQL 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. 不过,UPDATE 或 DELETE 语句的 WHERE 子句,或者 INSERT 语句的 SELECT 部分可以并行执行。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

可以使用最大并行度 (MAXDOP) 服务器配置选项(SQL 数据库SQL Database 上的 ALTER DATABASE SCOPED CONFIGURATION)来限制并行计划执行中使用的处理器数。You can use the max degree of parallelism (MAXDOP) server configuration option (ALTER DATABASE SCOPED CONFIGURATION on SQL 数据库SQL Database ) to limit the number of processors to use in parallel plan execution. 通过指定 MAXDOP 查询提示或 MAXDOP 索引选项,可以用单独查询和索引操作语句来覆盖最大并行度选项。The max degree of parallelism option can be overridden for individual query and index operation statements by specifying the MAXDOP query hint or MAXDOP index option. MAXDOP 提供对单独查询和索引操作的详细控制。MAXDOP provides more control over individual queries and index operations. 例如,可以使用 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.

将“最大并行度”选项设置为 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. 尽管 MAXDOP 选项设置为 0 时,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. 针对查询和索引将 MAXDOP 选项设置为 0 时,将允许 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.

请参阅 Microsoft 支持文章,了解有关配置 MAXDOP 的最佳实践。Refer to this Microsoft Support Article for best practices on configuring MAXDOP.

并行查询示例Parallel Query Example

以下查询计算指定季度(自 2000 年 4 月 1 日开始)内下的订单数,其中至少有一个订单项的客户接收日期晚于提交日期。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

假设以下索引在 lineitemorders 表中进行定义: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. o_datkey_ptr 索引的 Index Seek 运算符和 l_order_dates_idx 索引的 Index Scan 运算符都会并行执行。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 运算符上面最接近的 Parallelism 运算符来确定。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.

l_order_dates_idx Index Scan 运算符上面的 parallelism 运算符正在将 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 运算符上面的 parallelism 运算符正在使用 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 运算符中的联接列,所以 parallelism 和 Merge Join 运算符之间的 Sort 运算符确保为联接列上的 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. 与 Merge Join 运算符一样,Sort 运算符也是并行执行的。The Sort operator, like the Merge Join operator, is performed in parallel.

最上面的 parallelism 运算符将若干流的结果收集成一个流。The topmost parallelism operator gathers results from several streams into a single stream. 之后,该 parallelism 运算符下方的 Stream Aggregate 运算符所执行的部分聚合被聚集到单个的 SUM 值中,这个值是该 parallelism 运算符上方的 Stream Aggregate 运算符中每个不同的 O_ORDERPRIORITY 值之和。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 使用与其用于其他查询的算法相同的算法来确定索引操作的并行度(单独运行的总工作线程数)。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. 通过在 CREATE INDEX、ALTER INDEX、DROP INDEX 和 ALTER TABLE 语句中设置 MAXDOP 索引选项,可以覆盖单个索引操作的最大并行度值。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 数据库引擎SQL Server Database Engine生成一个索引执行计划时,并行操作数将设置为下列各项中的最低值:When the SQL Server 数据库引擎SQL Server Database Engine builds an index execution plan, the number of parallel operations is set to the lowest value from among the following:

  • 计算机中的微处理器 (CPU) 数。The number of microprocessors, or CPUs in the computer.
  • 最大并行度服务器配置选项中指定的数目。The number specified in the max degree of parallelism server configuration option.
  • 尚未超过为 SQL ServerSQL Server 工作线程执行的工作的阈值的 CPU 数。The number of CPUs not already over a threshold of work performed for SQL ServerSQL Server worker threads.

例如,在具有 8 个 CPU 的计算机上,而最大并行度设置为 6,则为索引操作生成的并行工作线程数不超过 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. 如果计算机中的五个 CPU 超过生成索引执行计划时的 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,则协调工作线程确定用于将行分为四个行集的键值,其中每个行集中包含 1 百万行。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. 如果无法建立足以使用所有 CPU 的键范围,则并行度也将相应降低。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 TABLEALTER TABLE 语句可以有多个要求创建索引的约束。Individual CREATE TABLE or ALTER TABLE statements can have multiple constraints that require that an index be created. 这些多个索引创建操作连续执行,但每个单独的索引创建操作可以是具有多个 CPU 的计算机上的并行操作。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 支持两种方法在 Transact-SQLTransact-SQL 语句中引用异类 OLE DB 数据源:Microsoft SQL ServerSQL Server supports two methods for referencing heterogeneous OLE DB data sources in Transact-SQLTransact-SQL statements:

  • 链接服务器名称Linked server names
    系统存储过程 sp_addlinkedserversp_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
    在很少引用数据源时, OPENROWSETOPENDATASOURCE 函数是用连接到链接服务器所需的信息指定的。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 在关系引擎和存储引擎之间通信。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
关系引擎使用 OLE DB 应用程序编程接口 (API) 打开链接服务器上的行集、提取行并管理事务。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. 在特定 OLE DB 数据源上可执行哪些 Transact-SQLTransact-SQL 操作取决于 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 固定服务器角色的成员可以使用 SQL ServerSQL Server DisallowAdhocAccess 属性启用或禁用对 OLE DB 提供程序使用即席连接器名称。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. 默认情况下,对 SQL ServerSQL Server OLE DB 访问接口启用即席访问,而对所有其他的 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.

分布式查询可允许用户使用运行 SQL ServerSQL Server 服务的 Microsoft Windows 帐户的安全上下文来访问其他数据源(例如,文件、非关系数据源 [如 Active Directory] 等)。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 中,也不会执行关系操作本身。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 改进了许多并行计划的已分区表的查询处理性能,更改了并行和串行计划的表示方式,并增强了编译时和运行时执行计划中所提供的分区信息。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 是一个内部使用的隐藏计算列,用于表示包含特定行的分区的 IDPartitionID is a hidden computed column used internally to represent the ID of the partition containing a specific row. 例如,假设一个定义为 T(a, b, c)的表 T 在 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(a, b, c)的表 T 在 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 = 2T.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 列中包含数据的表 bIt 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 < 10That 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

可以使用 Transact-SQL Transact-SQLTransact-SQL SET 语句 SET SHOWPLAN_XMLSET STATISTICS XML,或者使用 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:

  • 访问已分区表或已分区索引的操作,如 scansseeksinsertsupdatesmergesdeletesThe 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 属性,它指示对某已分区表执行某个运算符(例如 seekscaninsertupdatemergedelete)。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 显示计划输出中的显示方式,请考虑对已分区表 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

Partitioned 属性Partitioned 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 Scan
  • Index Scan
  • Index Seek
  • Insert
  • Update
  • Delete
  • Merge

如上图所示,该属性显示在包含其定义的运算符的属性中。As shown in the previous illustration, this attribute is displayed in the properties of the operator in which it is defined. 在 XML 显示计划输出中,该属性在包含其定义的运算符的 Partitioned="1" 节点中显示为 RelOpIn the XML Showplan output, this attribute appears as Partitioned="1" in the RelOp node of the operator in which it is defined.

新增的 Seek 谓词New Seek Predicate

在 XML 显示计划输出中, 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 实例项指定位于逻辑索引的分区 ID 级别的第一级查找操作。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 CountPartitions AccessedThe 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.

在 XML 显示计划输出中,Partitions Accessed是显示在新的 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>

使用其他显示计划方法来显示分区信息Displaying Partition Information by Using Other Showplan Methods

显示计划方法 SHOWPLAN_ALLSHOWPLAN_TEXTSTATISTICS 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 谓词的一部分,要访问的分区由表示该分区 ID 的计算列上的范围谓词标识。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

已分区堆被视为分区 ID 的逻辑索引。A partitioned heap is treated as a logical index on the partition ID. 已分区堆的分区排除在执行计划中表示为一个 Table Scan 运算符,其中对分区 ID 使用了 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. 下面的示例显示了所提供的显示计划信息: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. 查询优化器可以生成一个计划,其中具有相等分区 ID 的每个表的分区将分别联接在一起。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 ScanNested 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. 在下图中,有 4 个分区和 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. 例如,如果表已分区,并在 A 列上有一个聚集索引,并且查询有谓词子句 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.

基于 A 列的表分区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 和 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.
  • 尽可能使用具有足够主内存的服务器以便在内存中保留频繁访问的分区或所有分区,以减少 I/O 开销。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. 这会减少 I/O 开销。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.
  • 确保服务器具有足够的 I/O 控制器带宽。Ensure the server has sufficient I/O controller bandwidth.
  • 对每个大型已分区表创建聚集索引,以充分利用 B 树扫描优化。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.

备注

本示例要向表中插入超过 100 万行数据。This example inserts more than 1 million rows into the table. 根据您的硬件情况,运行本示例可能需要几分钟时间。Running this example may take several minutes depending on your hardware. 在执行本示例之前,请确保您有超过 1.5 GB 的可用磁盘空间。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

Showplan 逻辑运算符和物理运算符参考Showplan Logical and Physical Operators Reference
扩展事件Extended Events
Query Store 最佳实践Best Practice with the Query Store
基数估计Cardinality Estimation
智能查询处理 Intelligent query processing
运算符优先级 Operator Precedence
执行计划 Execution Plans
SQL Server 数据库引擎和 Azure SQL 数据库的性能中心Performance Center for SQL Server Database Engine and Azure SQL Database