查詢處理架構指南Query Processing Architecture Guide

適用於: 是SQL Server 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

SQL Server Database EngineSQL Server Database Engine 可在多種資料儲存架構處理查詢,例如本機資料表、資料分割資料表及散發到多部伺服器的資料表。The SQL Server Database EngineSQL Server Database Engine processes queries on various data storage architectures such as local tables, partitioned tables, and tables distributed across multiple servers. 下列主題涵蓋了 SQL ServerSQL Server 如何處理查詢以及透過執行計畫快取最佳化查詢重複使用。The following topics cover how SQL ServerSQL Server processes queries and optimizes query reuse through execution plan caching.

執行模式Execution modes

SQL Server Database EngineSQL Server Database Engine 可以使用兩種不同的處理模式來處理 Transact-SQLTransact-SQL 陳述式:The SQL Server Database EngineSQL Server Database Engine can process Transact-SQLTransact-SQL statements using two distinct processing modes:

  • 資料列模式執行Row mode execution
  • 批次模式執行Batch mode execution

資料列模式執行Row mode execution

「資料列模式執行」 是可搭配傳統 RDMBS 資料表使用的查詢處理方法,其中資料是以資料列格式儲存。Row mode execution is a query processing method used with traditional RDMBS tables, where data is stored in row format. 當查詢執行並存取資料列存放區資料表中的資料時,執行樹狀目錄運算子和子運算子會在資料表結構描述中指定的所有資料行之間,讀取每個必要的資料列。When a query is executed and accesses data in row store tables, the execution tree operators and child operators read each required row, across all the columns specified in the table schema. 從所讀取的每個資料列,SQL ServerSQL Server 會接著擷取結果集所需的資料行,以供 SELECT 陳述式、聯結述詞或篩選述詞參考。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. 當情況允許時,批次模式處理會在壓縮的資料上作業,並排除資料列模式執行所使用的 Exchange 運算子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 陳述式、聯結述詞或篩選述詞參考。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

當陳述式中使用一個以上的邏輯運算子,NOT 會第一個計算,接下來是 AND,最後才是 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. 例如,在表示車種的資料表中,許多車種的製造商都是相同的,但每輛車都有一個唯一的汽車識別號碼。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. 這個作業稱為常數摺疊 (Constant Folding)。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.

即使資料定義語言 (DDL) 陳述式 (例如 CREATE PROCEDUREALTER TABLE) 最後會解析為系統目錄資料表上一連串的關聯式作業,但有時還是會根據資料表來解析 (例如 ALTER TABLE ADD COLUMN)。Even Data Definition Language (DDL) statements, such as CREATE PROCEDURE or ALTER TABLE, are ultimately resolved to a series of relational operations on the system catalog tables and sometimes (such as ALTER TABLE ADD COLUMN) against the data tables.

工作資料表Worktables

關聯式引擎在執行 Transact-SQLTransact-SQL 陳述式中所指定的邏輯作業前,可能需要先建立一個工作資料表。The relational engine may need to build a worktable to perform a logical operation specified in an Transact-SQLTransact-SQL statement. 工作資料表屬於內部資料表,可用來保存中繼結果。Worktables are internal tables that are used to hold intermediate results. 工作資料表會針對特定的 GROUP 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.StateProvince 資料表中。The query fails, because the hint SERIALIZABLE that is applied on view Person.AddrState in the query is propagated to both tables Person.Address and Person.StateProvince in the view when it is expanded. 不過,展開檢視表也會顯示 NOLOCK 上的 Person.Address提示。However, expanding the view also reveals the NOLOCK hint on Person.Address. 由於 SERIALIZABLE 提示與 NOLOCK 提示相衝突,因此會產生不正確的查詢。Because the SERIALIZABLE and NOLOCK hints conflict, the resulting query is incorrect.

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. 因為 NOLOCKHOLDLOCK 提示相衝突,所以查詢會失敗。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 才會選擇在其查詢計畫中使用索引檢視表。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. 只有在測試已顯現出其大幅改善效能的特定情況下,才能使用 EXPANDNOEXPANDLimit 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.

如果未在含有檢視表的查詢中指定 NOEXPANDEXPAND 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 (從 1 到 3299999 的CustomerID )、Server2 (從 3300000 到 6599999 的CustomerID ),以及 Server3 (從 6600000 到 9999999 的CustomerID ) 進行資料分割。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. 假設 GetCustomer 預存程序是在 Server1 上執行,則執行計畫邏輯就能以下列形式來表示:Assuming the GetCustomer stored procedure was executed on Server1, the execution plan logic can be represented as shown in the following:

IF @CustomerIDParameter BETWEEN 1 and 3299999
   Retrieve row from local table CustomerData.dbo.Customer_33
ELSE IF @CustomerIDParameter BETWEEN 3300000 and 6599999
   Retrieve row from linked table Server2.CustomerData.dbo.Customer_66
ELSE IF @CustomerIDParameter BETWEEN 6600000 and 9999999
   Retrieve row from linked table Server3.CustomerData.dbo.Customer_99

SQL ServerSQL Server 有時候甚至會為尚未參數化的查詢建立這些動態執行計畫類型。sometimes builds these types of dynamic execution plans even for queries that are not parameterized. 查詢最佳化工具可能會參數化查詢以重複使用執行計畫。The Query Optimizer may parameterize a query so that the execution plan can be reused. 如果查詢最佳化工具將對參考資料分割檢視的查詢進行參數化,則查詢最佳化工具不會再假設需要的資料列將取自指定的基底資料表。If the Query Optimizer parameterizes a query referencing a partitioned view, the Query Optimizer can no longer assume the required rows will come from a specified base table. 接著在執行計畫中必須使用動態篩選。It will then have to use dynamic filters in the execution plan.

預存程序與觸發程序執行Stored Procedure and Trigger Execution

SQL ServerSQL Server 只會儲存預存程序和觸發程序的來源。stores only the source for stored procedures and triggers. 當先執行預存程序或觸發程序時,會將來源編譯成執行計畫。When a stored procedure or trigger is first executed, the source is compiled into an execution plan. 如果在執行計畫從記憶體中淘汰之前,再執行一次預存程序或觸發程序,關聯式引擎會偵測到現有的計畫並重複使用它。If the stored procedure or trigger is again executed before the execution plan is aged from memory, the relational engine detects the existing plan and reuses it. 如果計畫已從記憶體淘汰,就會建立新計畫。If the plan has aged out of memory, a new plan is built. 此處理序與 SQL ServerSQL Server 對於所有 Transact-SQLTransact-SQL 陳述式所依循的處理序類似。This process is similar to the process SQL ServerSQL Server follows for all Transact-SQLTransact-SQL statements. SQL ServerSQL Server 中,相較於動態 Transact-SQLTransact-SQL 的批次,預存程序與觸發程序的主要效能優點為,其 Transact-SQLTransact-SQL 陳述式一律是相同的。The main performance advantage that stored procedures and triggers have in SQL ServerSQL Server compared with batches of dynamic Transact-SQLTransact-SQL is that their Transact-SQLTransact-SQL statements are always the same. 因此,關聯式引擎可以輕易地將這些陳述式與任何現有的執行計畫配對。Therefore, the relational engine easily matches them with any existing execution plans. 就可以輕易地重複使用預存程序及觸發程序計畫Stored procedure and trigger plans are easily reused.

預存程序及觸發程序的執行計畫,將分別自呼叫預存程序,或引發觸發程序之批次的執行計畫中執行。The execution plan for stored procedures and triggers is executed separately from the execution plan for the batch calling the stored procedure or firing the trigger. 這可以允許更多次重複使用預存程序及觸發程序執行計畫。This allows for greater reuse of the stored procedure and trigger execution plans.

執行計畫快取與重複使用Execution Plan Caching and Reuse

SQL ServerSQL Server 具有一個記憶體集區,可用來儲存執行計畫以及資料緩衝區。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 陳述式的負擔。SQL ServerSQL Server 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 Database EngineSQL Server Database Engine 就會使用以成本為基礎的方法來判斷要從計畫快取中移除哪些執行計畫。When memory pressure exists, the SQL Server Database EngineSQL Server Database Engine uses a cost-based approach to determine which execution plans to remove from the plan cache. 為了進行以成本為基礎的決策,SQL Server Database EngineSQL Server Database Engine 會根據下列因素,針對每個執行計畫增加和減少目前的成本變數。To make a cost-based decision, the SQL Server Database EngineSQL Server Database Engine increases and decreases a current cost variable for each execution plan according to the following factors.

當使用者處理序將執行計畫插入快取時,該使用者處理序會將目前的成本設定為等於原始查詢編譯成本。若為特定執行計畫,使用者處理序則會將目前成本設定為零。When a user process inserts an execution plan into the cache, the user process sets the current cost equal to the original query compile cost; for ad-hoc execution plans, the user process sets the current cost to zero. 因此,每當使用者程序參考執行計畫時,它都會將目前成本重設為原始編譯成本;如果是特定執行計畫,使用者程序會增加目前成本。Thereafter, each time a user process references an execution plan, it resets the current cost to the original compile cost; for ad-hoc execution plans the user process increases the current cost. 對於所有計畫而言,目前成本的最大值就是原始編譯成本。For all plans, the maximum value for the current cost is the original compile cost.

當記憶體壓力存在時,SQL Server Database EngineSQL Server Database Engine 會從計畫快取中移除執行計畫,藉此進行回應。When memory pressure exists, the SQL Server Database EngineSQL Server Database Engine responds by removing execution plans from the plan cache. 為了判斷要移除哪些計畫,SQL Server Database EngineSQL Server Database Engine 會重複檢查每個執行計畫的狀態,然後移除目前成本為零的計畫。To determine which plans to remove, the SQL Server Database EngineSQL Server Database Engine repeatedly examines the state of each execution plan and removes plans when their current cost is zero. 當記憶體壓力存在時,系統不會自動移除目前成本為零的執行計畫。只有當 SQL Server Database EngineSQL Server Database Engine 檢查計畫並且發現目前成本為零時,才會移除此計畫。An execution plan with zero current cost is not removed automatically when memory pressure exists; it is removed only when the SQL Server Database EngineSQL Server Database Engine examines the plan and the current cost is zero. 檢查執行計畫時,如果查詢目前沒有使用計畫,SQL Server Database EngineSQL Server Database Engine 就會減少目前成本,藉此將目前成本推向零。When examining an execution plan, the SQL Server Database EngineSQL Server Database Engine pushes the current cost towards zero by decreasing the current cost if a query is not currently using the plan.

SQL Server Database EngineSQL Server Database Engine 會重複檢查執行計畫,直到移除足夠的計畫,可滿足記憶體需求為止。The SQL Server Database EngineSQL Server Database Engine repeatedly examines the execution plans until enough have been removed to satisfy memory requirements. 當記憶體壓力存在時,執行計畫可能會多次增加和減少其成本。While memory pressure exists, an execution plan may have its cost increased and decreased more than once. 當記憶體壓力不再存在時,SQL Server Database EngineSQL Server Database Engine 就會停止減少未使用之執行計畫的目前成本,而且所有執行計畫都會保留在計畫快取中,即使其成本為零也一樣。When memory pressure no longer exists, the SQL Server Database EngineSQL Server Database Engine stops decreasing the current cost of unused execution plans and all execution plans remain in the plan cache, even if their cost is zero.

為了回應記憶體壓力,SQL Server Database EngineSQL Server Database Engine 會使用資源監視器和使用者背景工作執行緒來釋放計畫快取中的記憶體。The SQL Server Database EngineSQL Server Database Engine uses the resource monitor and user worker threads to free memory from the plan cache in response to memory pressure. 資源監視器和使用者背景工作執行緒可以檢查計畫並同時執行,以便針對每個未使用的執行計畫減少目前成本。The resource monitor and user worker threads can examine plans run concurrently to decrease the current cost for each unused execution plan. 當全域記憶體壓力存在時,資源監視器就會從計畫快取中移除執行計畫。The resource monitor removes execution plans from the plan cache when global memory pressure exists. 它會釋放記憶體,以便強制執行系統記憶體、處理序記憶體、資源集區記憶體和所有快取大小上限的原則。It frees memory to enforce policies for system memory, process memory, resource pool memory, and maximum size for all caches.

所有快取大小上限是緩衝集區大小的函數,而且不能超過伺服器記憶體的最大值。The maximum size for all caches is a function of the buffer pool size and cannot exceed the maximum server memory. 如需設定最大伺服器記憶體的詳細資訊,請參閱 max server memory 中的 sp_configure設定。For more information on configuring the maximum server memory, see the max server memory setting in sp_configure.

當單一快取記憶體壓力存在時,使用者背景工作執行緒就會從計畫快取中移除執行計畫。The user worker threads remove execution plans from the plan cache when single cache memory pressure exists. 它們會強制執行最大單一快取大小和最大單一快取項目的原則。They enforce policies for maximum single cache size and maximum single cache entries.

下列範例說明要從計畫快取中移除哪些執行計畫:The following examples illustrate which execution plans get removed from the plan cache:

  • 執行計畫經常被參考,所以它的成本永遠都不會變成零。An execution plan is frequently referenced so that its cost never goes to zero. 計畫依然在計畫快取中,而且除非有記憶體壓力且目前成本為零,否則不會移除計畫。The plan remains in the plan cache and is not removed unless there is memory pressure and the current cost is zero.
  • 系統會插入特定執行計畫,但在記憶體壓力存在之前,不會再次參考它。An ad-hoc execution plan is inserted and is not referenced again before memory pressure exists. 由於特定計畫會以目前成本為零來初始化,所以當 SQL Server Database EngineSQL Server Database Engine 檢查執行計畫時,它將會看到目前成本為零,並從計畫快取中移除此計畫。Since ad-hoc plans are initialized with a current cost of zero, when the SQL Server Database EngineSQL Server Database Engine examines the execution plan, it will see the zero current cost and remove the plan from the plan cache. 當記憶體壓力不存在時,特定執行計畫會留在計畫快取中,且目前成本為零。The ad-hoc execution plan remains in the plan cache with a zero current cost when memory pressure does not exist.

若要手動從快取中移除單一計畫或所有計畫,請使用 DBCC 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、Prepare 方法或 Execute 方法。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 Profiler SP:Recompile 追蹤事件。In SQL ServerSQL Server versions where xEvents are not available, then the SQL ServerSQL Server Profiler SP:Recompile trace event can be used for the same purpose of reporting statement-level recompilations. 追蹤事件 SQL:StmtRecompile 也會報告陳述式層級重新編譯,而且此追蹤事件也可用來追蹤及偵錯重新編譯。The trace event SQL:StmtRecompile also reports statement-level recompilations, and this trace event can also be used to track and debug recompilations. SP:Recompile 只能針對預存程序及觸發程序來產生;相較之下,SQL:StmtRecompile 可針對預存程序、觸發程序、隨選批次、使用 sp_executesql 執行的批次、準備查詢及動態 SQL 來產生。Whereas SP:Recompile generates only for stored procedures and triggers, SQL:StmtRecompile generates for stored procedures, triggers, ad-hoc batches, batches that are executed by using sp_executesql, prepared queries, and dynamic SQL. SP:RecompileSQL:StmtRecompileEventSubClass 資料行包含一個整數碼,可指出重新編譯的原因。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 選項設為 FORCEDAlternatively, 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 子句的 pattern 和 escape_character 引數。The pattern and escape_character arguments of a LIKE clause.
  • CONVERT 子句的 style 引數。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). 不屬於含有比較運算子之述詞的較大常值會參數化為 numeric,其有效位數夠大正好足以支援其大小,而其小數位數為 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.
  • 屬於含有比較運算子之述詞的固定點數值常值會參數化為 numeric,其有效位數為 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. 不屬於含有比較運算子之述詞的固定點數值常值會參數化為 numeric,其有效位數與小數位數夠大正好足以支援其大小。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 個 Unicode 字元中,即會參數化為 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 關聯式引擎在執行 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 ServerOn 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

如需如何針對錯誤參數探測問題進行疑難排解的詳細資訊,請參閱針對含參數敏感查詢執行計畫問題的查詢進行疑難排解For more information on troubleshooting bad parameter sniffing issues, see Troubleshoot queries with parameter-sensitive query execution plan issues.

注意

若是使用 RECOMPILE 提示的查詢,則會探查參數值和區域變數的目前值。For queries using the RECOMPILE hint, both parameter values and current values of local variables are sniffed. 探查到的值 (參數和區域變數值) 是存在於批次中具有 RECOMPILE 提示的陳述式之前位置的值。The values sniffed (of parameters and local variables) are those that exist at the place in the batch just before the statement with the RECOMPILE hint. 特別是對於參數,不會探查批次引動過程呼叫隨附的值。In particular, for parameters, the values that came along with the batch invocation call are not sniffed.

平行查詢處理Parallel Query Processing

SQL ServerSQL Server 提供平行查詢,讓擁有多個處理器 (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 Database EngineSQL Server Database Engine 會自動降低平行處理原則的程度,或是完全放棄指定工作負載內容中的平行計畫。When the worker thread requirement of the parallel plan for a specific degree of parallelism cannot be satisfied, the SQL Server Database EngineSQL Server Database Engine decreases the degree of parallelism automatically or completely abandons the parallel plan in the specified workload context. 然後,它會執行序列計畫 (一個背景工作執行緒)。It then executes the serial plan (one worker thread).

  3. 已執行的查詢或索引作業類型。The type of query or index operation executed.
    建立或重建索引,或是卸除叢集索引的索引作業,以及大量使用 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 Database EngineSQL Server Database Engine 會比較執行查詢或索引作業的預估成本與平行處理的成本臨界值的值。To distinguish between queries that benefit from parallelism and those that do not benefit, the SQL Server Database EngineSQL Server Database Engine compares the estimated cost of executing the query or index operation with the cost threshold for parallelism value. 如果適當測試發現不同的值更適合執行的工作負載,使用者可以使用 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 Database EngineSQL Server Database Engine 無法為索引作業提供平行處理原則的最高程度。In this case, the SQL Server Database EngineSQL Server Database Engine cannot provide the highest degree of parallelism for the index operation. 然而,有些運算子 (如排序及掃描) 仍可從平行執行獲益。However, some operators, such as sorting and scanning, can still benefit from parallel execution.

注意

只有 SQL ServerSQL Server Enterprise、Developer 和 Evaluation 版本才可使用平行索引作業。Parallel index operations are only available in SQL ServerSQL Server Enterprise, Developer, and Evaluation editions.

執行時,SQL Server Database EngineSQL Server Database Engine 會判定先前描述的目前系統工作負載及組態資訊是否允許平行執行。At execution time, the SQL Server Database EngineSQL Server Database Engine determines whether the current system workload and configuration information previously described allow for parallel execution. 如果保證可以平行執行,則 SQL Server Database EngineSQL Server Database Engine 會判定最佳的背景工作執行緒數目,並將平行計畫的執行分散到那些背景工作執行緒上。If parallel execution is warranted, the SQL Server Database EngineSQL Server Database Engine determines the optimal number of worker threads and spreads the execution of the parallel plan across those worker threads. 當查詢或索引作業開始在多個背景工作執行緒上執行,以進行平行執行時,則在完成作業之前,都會使用相同數目的背景工作執行緒。When a query or index operation starts executing on multiple worker threads for parallel execution, the same number of worker threads is used until the operation is completed. 每次從計畫快取擷取執行計畫時,SQL Server Database EngineSQL Server Database Engine 都會重新檢查最佳的背景工作執行緒決策數目。The SQL Server Database EngineSQL Server Database Engine re-examines the optimal number of worker thread decisions every time an execution plan is retrieved from the plan cache. 例如,執行查詢可能會使用到序列計畫,稍後執行同一個查詢會導致平行計畫使用三個背景工作執行緒,而第三次執行查詢的結果則是平行計畫使用四個背景工作執行緒。For example, one execution of a query can result in the use of a serial plan, a later execution of the same query can result in a parallel plan using three worker threads, and a third execution can result in a parallel plan using four worker threads.

在平行查詢執行計畫中,會循序執行插入、更新及刪除運算子。In a parallel query execution plan, the insert, update, and delete operators are executed serially. 然而,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 DatabaseSQL Database 上的 ALTER DATABASE SCOPED CONFIGURATION),來限制要在平行計畫執行中使用的處理器數目。You can use the max degree of parallelism (MAXDOP) server configuration option (ALTER DATABASE SCOPED CONFIGURATION on SQL DatabaseSQL 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.

如需設定 MAXDOP 的最佳做法,請參閱這篇 Microsoft 支援文章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. Sort 運算子 (如 Merge Join 運算子) 會以平行方式執行。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 運算子所執行的部分彙總,會累積為 Parallelism 運算子上方之 Stream Aggregate 運算子中,各個不同 O_ORDERPRIORITY 值的單一 SUM 值。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.

如需此範例中所使用之運算子的詳細資訊,請參閱執行程序表邏輯和實體運算子參考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 開始的 Enterprise Edition 才支援平行索引作業。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. 索引作業的平行處理原則最大程度受限於 平行處理原則的最大程度 伺服器組態選項。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 Database EngineSQL Server Database Engine 建立索引執行計畫時,會將平行作業的數目設定為下列項目中的最低值:When the SQL Server Database EngineSQL Server Database Engine builds an index execution plan, the number of parallel operations is set to the lowest value from among the following:

  • 微處理器的數目或電腦中的 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.

例如,電腦上有八個 CPU,但 [平行處理原則的最大程度] 設定為 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.

分散式查詢可使用 Microsoft Windows 帳戶 (SQL ServerSQL Server 服務正在其下執行) 的安全性內容,允許使用者存取其他資料來源 (例如,檔案或 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. ad hoc 名稱無法使用此控制,所以啟用 ad hoc 存取的 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、Developer 和 Evaluation 版本才支援資料分割資料表和索引。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,其中的資料行 ab 中有資料。It shows table T with data in columns a and b. 資料分割以 1 到 4 進行編號,並以垂直虛線來顯示資料分割界限。The partitions are numbered 1 through 4 with the partition boundaries shown by dashed vertical lines. 對資料分割的第一層搜尋作業 (此圖並未顯示) 判斷出,資料分割 1、2 和 3 符合針對資料行 a 上資料表和述詞定義之資料分割所默許的搜尋條件。A first-level seek operation to the partitions (not shown in the illustration) has determined that partitions 1, 2, and 3 meet the seek condition implied by the partitioning defined for the table and the predicate on column a. 也就是說, T.a < 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-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:

  • 像是可以存取分割資料表或索引的 scansseeksinsertsupdatesmergesdeletes 等作業。The operations such as scans, seeks, inserts, updates, merges, and deletes that access partitioned tables or indexes.
  • 查詢所存取的資料分割。The partitions accessed by the query. 例如,所存取的資料分割總計數和所存取的連續資料分割範圍可以在執行階段執行計畫內使用。For example, the total count of partitions accessed and the ranges of contiguous partitions that are accessed are available in run-time execution plans.
  • 當搜尋或掃描作業中使用略過掃描作業來擷取一或多個資料分割中的資料時。When the skip scan operation is used in a seek or scan operation to retrieve data from one or more partitions.

資料分割資訊增強Partition Information Enhancements

SQL ServerSQL Server 同時針對編譯時間和執行階段的執行計畫提供了增強的資料分割資訊。provides enhanced partitioning information for both compile-time and run-time execution plans. 執行計畫現在會提供下列資訊:Execution plans now provide the following information:

  • 選擇性的 Partitioned 屬性,其指出在資料分割的資料表上執行像是 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

如同上圖所示,這個屬性 (Attribute) 會顯示在其定義所在之運算子的屬性 (Property) 內。As shown in the previous illustration, this attribute is displayed in the properties of the operator in which it is defined. 在 XML 執行程序表輸出中,這個屬性會以 Partitioned="1" 的形式出現在其定義所在之運算子的 RelOp 節點內。In the XML Showplan output, this attribute appears as Partitioned="1" in the RelOp node of the operator in which it is defined.

新的搜尋述詞New 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 項目會在邏輯索引的資料分割識別碼層級上指定第一層搜尋作業。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.

Partitions Accessed(位於 XML 執行程序表輸出內) 為資料分割摘要資訊,會出現在它定義所在之運算子的 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 述詞的一部分) 是由表示資料分割識別碼之計算資料行上的範圍述詞所識別。As part of the SEEK predicate, the partitions to be accessed are identified by a range predicate on the computed column representing the partition ID. 下列範例會顯示 SEEK 運算子的 Clustered Index Seek 述詞。The following example shows the SEEK predicate for a Clustered Index Seek operator. 系統會存取資料分割 2 和 3,而且搜尋運算子會篩選符合 date_id BETWEEN 20080802 AND 20080902條件的資料列。Partitions 2 and 3 are accessed, and the seek operator filters on the rows that meet the condition date_id BETWEEN 20080802 AND 20080902.

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

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

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

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

                ORDERED FORWARD)

解譯資料分割堆積的執行計畫Interpreting Execution Plans for Partitioned Heaps

資料分割的堆積會被視為資料分割識別碼上的邏輯索引。A partitioned heap is treated as a logical index on the partition ID. 資料分割堆積上的資料分割刪除會在執行計畫中表示為 Table Scan 運算子 (在資料分割識別碼上具有 SEEK 述詞)。Partition elimination on a partitioned heap is represented in an execution plan as a Table Scan operator with a SEEK predicate on partition ID. 下列範例會顯示所提供的執行程序表資訊:The following example shows the Showplan information provided:

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

解譯共置聯結的執行計畫Interpreting Execution Plans for Collocated Joins

當使用相同或相當的資料分割函數來分割兩個資料表,而且聯結兩端的資料分割資料行指定於查詢的聯結條件內時,可能會發生聯結共現。Join collocation can occur when two tables are partitioned using the same or equivalent partitioning function and the partitioning columns from both sides of the join are specified in the join condition of the query. 查詢最佳化工具可以產生一個計畫,好讓每一個資料表中具有相同資料分割識別碼的資料分割都會個別聯結。The Query Optimizer can generate a plan where the partitions of each table that have equal partition IDs are joined separately. 共置聯結的速度快於非共置聯結,因為共置聯結所需的記憶體和處理時間比較少。Collocated joins can be faster than non-collocated joins because they can require less memory and processing time. 查詢最佳化工具會根據成本估計來選擇非共置計畫或共置計畫。The Query Optimizer chooses a non-collocated plan or a collocated plan based on cost estimates.

在共置計畫中, Nested Loops 聯結會從內部讀取一或多個聯結資料表或索引資料分割。In a collocated plan, the Nested Loops join reads one or more joined table or index partitions from the inner side. Constant Scan 運算子內的數字代表資料分割編號。The numbers within the Constant Scan operators represent the partition numbers.

當針對資料分割資料表或索引產生共置聯結的平行計畫時,平行處理原則運算子就會出現在 Constant ScanNested Loops 聯結運算子之間。When parallel plans for collocated joins are generated for partitioned tables or indexes, a Parallelism operator appears between the Constant Scan and the Nested Loops join operators. 在此情況下,聯結外部的多個背景工作執行緒每個都會讀取和處理不同的資料分割。In this case, multiple worker threads on the outer side of the join each read and work on a different partition.

下圖將示範共置聯結的平行查詢計畫。The following illustration demonstrates a parallel query plan for a collocated join.
colocated_join

資料分割物件的平行查詢執行策略Parallel Query Execution Strategy for Partitioned Objects

查詢處理器會將平行執行策略用於從資料分割物件選取的查詢。The query processor uses a parallel execution strategy for queries that select from partitioned objects. 在執行策略中,查詢處理器會判斷查詢所需的資料表資料分割,以及配置給每一個資料分割的背景工作執行緒比例。As part of the execution strategy, the query processor determines the table partitions required for the query and the proportion of worker threads to allocate to each partition. 在大多數情況下,查詢處理器會將相同或幾乎相同的背景工作執行緒數目配置給每一個資料分割,然後以平行方式在資料分割之間執行查詢。In most cases, the query processor allocates an equal or almost equal number of worker threads to each partition, and then executes the query in parallel across the partitions. 以下段落將更詳細地說明背景工作執行緒配置。The following paragraphs explain worker thread allocation in greater detail.

背景工作執行緒1

如果背景工作執行緒數目小於資料分割數目,查詢處理器會將每一個背景工作執行緒指派給不同的資料分割,一開始會讓一或多個資料分割未具指派的背景工作執行緒。If the number of worker threads is less than the number of partitions, the query processor assigns each worker thread to a different partition, initially leaving one or more partitions without an assigned worker thread. 當背景工作執行緒在資料分割上完成執行時,查詢處理器會將它指派給下一個資料分割,直到每一個資料分割都已指派單一背景工作執行緒為止。When a worker thread finishes executing on a partition, the query processor assigns it to the next partition until each partition has been assigned a single worker thread. 這是查詢處理器將背景工作執行緒重新配置給其他資料分割的唯一情況。This is the only case in which the query processor reallocates worker threads to other partitions.
顯示完成後重新指派的背景工作執行緒。Shows worker thread reassigned after it finishes. 如果背景工作執行緒數目等於資料分割數目,則查詢處理器會將一個背景工作執行緒指派給每一個資料分割。If the number of worker threads is equal to the number of partitions, the query processor assigns one worker thread to each partition. 當背景工作執行緒完成時,不會將它重新配置給另一個資料分割。When a worker thread finishes, it is not reallocated to another partition.

背景工作執行緒2

如果背景工作執行緒數目大於資料分割數目,則查詢處理器會將相同的背景工作執行緒數目指派給每一個資料分割。If the number of worker threads is greater than the number of partitions, the query processor allocates an equal number of worker threads to each partition. 如果背景工作執行緒數目不是資料分割數目的倍數,則查詢處理器會將一個額外的背景工作執行緒配置給某些資料分割,如此才可使用所有可用的背景工作執行緒。If the number of worker threads is not an exact multiple of the number of partitions, the query processor allocates one additional worker thread to some partitions in order to use all of the available worker threads. 請注意,如果只有一個資料分割,則所有背景工作執行緒都將指派給該資料分割。Note that if there is only one partition, all worker threads will be assigned to that partition. 在下圖中,有四個資料分割和 14 個背景工作執行緒。In the diagram below, there are four partitions and 14 worker threads. 每一個資料分割都指派 3 個背景工作執行緒,其中的兩個資料分割有一個額外的背景工作執行緒,所以一共指派了 14 個背景工作執行緒。Each partition has 3 worker threads assigned, and two partitions have an additional worker thread, for a total of 14 worker thread assignments. 當背景工作執行緒完成時,不會將它重新指派給另一個資料分割。When a worker thread finishes, it is not reassigned to another partition.

背景工作執行緒3

雖然上面的範例建議一個直接的方式來配置背景工作執行緒,但是實際的策略會更複雜,而且要考量在查詢執行期間所發生的其他變數。Although the above examples suggest a straightforward way to allocate worker threads, the actual strategy is more complex and accounts for other variables that occur during query execution. 例如,如果資料表已進行資料分割,並在資料行 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 在每一個資料表資料分割中搜尋資料行 BSeeks for column B in each table partition
資料表分割區 1:A < 10Table Partition 1: A < 10 B=50, B=100, B=150B=50, B=100, B=150
資料表分割區 2:A >= 10 AND A < 20Table Partition 2: A >= 10 AND A < 20 B=50, B=100, B=150B=50, B=100, B=150
資料表分割區 3:A >= 20 AND 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.

注意

這個範例會將一百萬個以上的資料列插入資料表中。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 Logical and Physical Operators Reference
擴充事件Extended Events
使用查詢存放區的最佳作法Best Practice with the Query Store
基數估計Cardinality Estimation
智慧查詢處理 Intelligent query processing
運算子優先順序 Operator Precedence
執行計畫 Execution Plans
SQL Server Database Engine 和 Azure SQL Database 的效能中心Performance Center for SQL Server Database Engine and Azure SQL Database