Guía de arquitectura de procesamiento de consultasQuery Processing Architecture Guide

SE APLICA A: síSQL Server noAzure SQL Database noAzure SQL Data Warehouse noAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

El Motor de base de datos de SQL ServerSQL Server Database Engine procesa consultas en varias arquitecturas de almacenamiento de datos como tablas locales, tablas con particiones y tablas distribuidas en varios servidores.The Motor de base de datos de SQL ServerSQL Server Database Engine processes queries on various data storage architectures such as local tables, partitioned tables, and tables distributed across multiple servers. En los temas siguientes se trata el modo en que SQL ServerSQL Server procesa las consultas y optimiza la reutilización de consultas a través del almacenamiento en caché de los planes de ejecución.The following topics cover how SQL ServerSQL Server processes queries and optimizes query reuse through execution plan caching.

Modos de ejecuciónExecution modes

Motor de base de datos de SQL ServerSQL Server Database Engine puede procesar las instrucciones Transact-SQLTransact-SQL mediante dos modos de procesamiento distintos:The Motor de base de datos de SQL ServerSQL Server Database Engine can process Transact-SQLTransact-SQL statements using two distinct processing modes:

  • Ejecución del modo de filaRow mode execution
  • Ejecución del modo por lotesBatch mode execution

Ejecución del modo de filaRow mode execution

La ejecución del modo de fila es un método de procesamiento de consultas que se usa con tablas RDMBS tradicionales, donde los datos se almacenan en formato de fila.Row mode execution is a query processing method used with traditional RDMBS tables, where data is stored in row format. Cuando se ejecuta una consulta y accede a los datos de tablas de almacén de filas, los operadores del árbol de ejecución y los operadores secundarios leen todas las filas necesarias, en todas las columnas especificadas en el esquema de tabla.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. De cada fila que se lee, SQL ServerSQL Server recupera las columnas que son necesarias para el conjunto de resultados, como se hace referencia mediante una instrucción SELECT, un predicado JOIN o un predicado de filtro.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.

Nota

La ejecución del modo de fila es muy eficaz para escenarios OLTP, pero puede serlo menos cuando se analizan grandes cantidades de datos, por ejemplo en escenarios de almacenamiento de datos.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.

Ejecución del modo por lotesBatch mode execution

La ejecución del modo por lotes es un método de procesamiento de consultas en el que las consultas procesan varias filas a la vez (de ahí el término "por lotes").Batch mode execution is a query processing method used to process multiple rows together (hence the term batch). Cada columna dentro de un lote se almacena como un vector en un área de memoria independiente, por lo que el procesamiento del modo por lotes se basa en vectores.Each column within a batch is stored as a vector in a separate area of memory, so batch mode processing is vector-based. En el procesamiento del modo por lotes también se usan algoritmos que se optimizan para las CPU de varios núcleos y el rendimiento de aumento de memoria que se encuentran en el hardware moderno.Batch mode processing also uses algorithms that are optimized for the multi-core CPUs and increased memory throughput that are found on modern hardware.

La ejecución del modo por lotes está estrechamente integrada con el formato de almacenamiento de almacén de columnas y optimizada alrededor del mismo.Batch mode execution is closely integrated with, and optimized around, the columnstore storage format. El procesamiento del modo por lotes funciona en los datos comprimidos siempre que sea posible y elimina el operador de intercambio que usa el procesamiento del modo de fila.Batch mode processing operates on compressed data when possible, and eliminates the exchange operator used by row mode execution. El resultado es un mayor paralelismo y un rendimiento más rápido.The result is better parallelism and faster performance.

Cuando una consulta se ejecuta en el modo por lotes y tiene acceso a los datos de índices de almacén de columnas, los operadores del árbol de ejecución y los operadores secundarios leen varias filas en segmentos de columna.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 solo lee las columnas necesarias para el resultado, tal y como se indica en una instrucción SELECT, un predicado JOIN o un predicado de filtro.reads only the columns required for the result, as referenced by a SELECT statement, JOIN predicate, or filter predicate.
Para más información sobre los índices de almacén de columnas, vea Arquitectura de los índices de almacén de columnas.For more information on columnstore indexes, see Columnstore Index Architecture.

Nota

La ejecución del modo por lotes es muy eficaz en escenarios de almacenamiento de datos, donde se leen y se agregan grandes cantidades de datos.Batch mode execution is very efficient Data Warehousing scenarios, where large amounts of data are read and aggregated.

Procesamiento de instrucciones SQLSQL Statement Processing

La forma más básica de ejecutar instrucciones Transact-SQLTransact-SQL en SQL ServerSQL Server consiste en procesar una única instrucción Transact-SQLTransact-SQL.Processing a single Transact-SQLTransact-SQL statement is the most basic way that SQL ServerSQL Server executes Transact-SQLTransact-SQL statements. Los pasos que se usan para procesar una única instrucción SELECT que solo hace referencia a tablas base locales (no a vistas ni a tablas remotas) ilustran el proceso básico.The steps used to process a single SELECT statement that references only local base tables (no views or remote tables) illustrates the basic process.

Prioridad de los operadores lógicosLogical Operator Precedence

Cuando en una instrucción se usa más de un operador lógico, primero se evalúa NOT, luego AND y, finalmente, OR.When more than one logical operator is used in a statement, NOT is evaluated first, then AND, and finally OR. Los operadores aritméticos y bit a bit se tratan antes que los operadores lógicos.Arithmetic, and bitwise, operators are handled before logical operators. Para más información, vea Prioridad de operador.For more information, see Operator Precedence.

En el siguiente ejemplo, la condición de color pertenece al modelo de producto 21 y no al modelo de producto 20, ya que porque AND tiene prioridad sobre OR.In the following example, the color condition pertains to product model 21, and not to product model 20, because AND has precedence over OR.

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

Puede cambiar el significado de la consulta si agrega paréntesis para provocar que OR se evalúe primero.You can change the meaning of the query by adding parentheses to force evaluation of the OR first. La siguiente consulta busca solamente los productos en los modelos 20 y 21 que sean rojos.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

El uso de paréntesis, incluso cuando no se necesitan, puede mejorar la comprensión de las consultas y reducir las posibilidades de cometer un error debido a la prioridad de los operadores.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. No hay ninguna reducción significativa del rendimiento que sea achacable al uso de paréntesis.There is no significant performance penalty in using parentheses. El siguiente ejemplo se lee mejor que el ejemplo original, aunque ambos son sintácticamente iguales.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

Optimización de las instrucciones SELECTOptimizing SELECT statements

Una instrucción SELECT no es de procedimiento, ya que no expone los pasos exactos que el servidor de la base de datos debe usar para recuperar los datos solicitados.A SELECT statement is non-procedural; it does not state the exact steps that the database server should use to retrieve the requested data. Esto significa que el servidor de la base de datos debe analizar la instrucción para determinar la manera más eficaz de extraer los datos solicitados.This means that the database server must analyze the statement to determine the most efficient way to extract the requested data. Este proceso se denomina optimizar la instrucción SELECT .This is referred to as optimizing the SELECT statement. El componente que lo lleva a cabo se denomina Optimizador de consultas.The component that does this is called the Query Optimizer. La entrada al Optimizador de consultas consta de la consulta, el esquema de la base de datos (definiciones de tabla e índice) y las estadísticas de base de datos.The input to the Query Optimizer consists of the query, the database schema (table and index definitions), and the database statistics. La salida del Optimizador de consultas es un plan de ejecución de consultas, en ocasiones denominado plan de consulta o simplemente plan.The output of the Query Optimizer is a query execution plan, sometimes referred to as a query plan or just a plan. El contenido de un plan de consulta se describe con más detalle posteriormente en este tema.The contents of a query plan are described in more detail later in this topic.

En el siguiente diagrama se muestran las entradas y salidas del optimizador de consultas durante la optimización de una única instrucción 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

Una instrucción SELECT define únicamente los siguientes elementos:A SELECT statement defines only the following:

  • El formato del conjunto de resultados.The format of the result set. Este elemento se especifica principalmente en la lista de selección.This is specified mostly in the select list. Sin embargo, también afectan a la forma final del conjunto de resultados otras cláusulas como ORDER BY y GROUP BY .However, other clauses such as ORDER BY and GROUP BY also affect the final form of the result set.
  • Las tablas que contienen los datos de origen.The tables that contain the source data. Se especifica en la cláusula FROM .This is specified in the FROM clause.
  • Cómo se relacionan las tablas de forma lógica para la instrucción SELECT .How the tables are logically related for the purposes of the SELECT statement. Esto se define en las especificaciones de combinación, que pueden aparecer en la cláusula WHERE o en una cláusula ON que sigue a FROM.This is defined in the join specifications, which may appear in the WHERE clause or in an ON clause following FROM.
  • Las condiciones que deben cumplir las filas de las tablas de origen para satisfacer los requisitos de la instrucción SELECT .The conditions that the rows in the source tables must satisfy to qualify for the SELECT statement. Se especifican en las cláusulas WHERE y HAVING .These are specified in the WHERE and HAVING clauses.

Un plan de ejecución de consulta es una definición de los siguientes elementos:A query execution plan is a definition of the following:

  • La secuencia en la que se tiene acceso a las tablas de origen.The sequence in which the source tables are accessed.
    Normalmente, hay muchas secuencias diferentes en las que el servidor de la base de datos puede tener acceso a las tablas base para generar el conjunto de resultados.Typically, there are many sequences in which the database server can access the base tables to build the result set. Por ejemplo, si la instrucción SELECT hace referencia a tres tablas, el servidor de la base de datos podría tener acceso primero a TableA, utilizar los datos de TableA para extraer las filas que coincidan con las de TableBy, finalmente, utilizar los datos de TableB para extraer datos de 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. Las demás secuencias en las que el servidor de base de datos podría tener acceso a las tablas son:The other sequences in which the database server could access the tables are:
    TableC, TableB, TableAoTableC, TableB, TableA, or
    TableB, TableA, TableCoTableB, TableA, TableC, or
    TableB, TableC, TableAoTableB, TableC, TableA, or
    TableC, TableA, TableBTableC, TableA, TableB

  • Los métodos que se utilizan para extraer los datos de cada tabla.The methods used to extract data from each table.
    Por lo general, hay métodos diferentes para tener acceso a los datos de cada tabla.Generally, there are different methods for accessing the data in each table. Si solo se necesitan unas cuantas filas con valores de clave específicos, el servidor de la base de datos puede utilizar un índice.If only a few rows with specific key values are required, the database server can use an index. Si se necesitan todas las filas de una tabla, el servidor de la base de datos puede omitir los índices y realizar un recorrido de la tabla.If all the rows in the table are required, the database server can ignore the indexes and perform a table scan. Si se necesitan todas las filas de la tabla, pero hay un índice cuyas columnas de clave están ordenadas con ORDER BY, realizar un recorrido del índice en lugar de un recorrido de la tabla puede evitar otra ordenación del conjunto de resultados.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. Si la tabla es muy pequeña, el recorrido de la misma puede ser el método más eficaz para la mayoría de los accesos a la tabla.If a table is very small, table scans may be the most efficient method for almost all access to the table.

El proceso de selección de un plan de ejecución entre varios planes posibles se conoce como optimización.The process of selecting one execution plan from potentially many possible plans is referred to as optimization. El optimizador de consultas es uno de los componentes más importantes de un sistema de base de datos SQL.The Query Optimizer is one of the most important components of a SQL database system. Mientras que parte de la carga de trabajo se debe al análisis de la consulta y selección de un plan por parte del optimizador de consultas, esta carga suele reducirse cuando dicho optimizador elige un plan de ejecución eficaz.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. Por ejemplo, se pueden dar a dos constructoras planos idénticos para una casa.For example, two construction companies can be given identical blueprints for a house. Si una de las constructoras tarda unos días más en planear cómo construirá la casa y la otra comienza a construir inmediatamente sin planear, la que ha planeado su proyecto probablemente terminará antes.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.

El Optimizador de consultas de SQL ServerSQL Server es un optimizador basado en el costo.The SQL ServerSQL Server Query Optimizer is a cost-based Query Optimizer. Cada plan de ejecución posible tiene asociado un costo en términos de la cantidad de recursos del equipo que se utilizan.Each possible execution plan has an associated cost in terms of the amount of computing resources used. El optimizador de consultas debe analizar los planes posibles y elegir el de menor costo estimado.The Query Optimizer must analyze the possible plans and choose the one with the lowest estimated cost. Algunas instrucciones SELECT complejas tienen miles de planes de ejecución posibles.Some complex SELECT statements have thousands of possible execution plans. En estos casos, el optimizador de consultas no analiza todas las combinaciones posibles.In these cases, the Query Optimizer does not analyze all possible combinations. En lugar de esto, utiliza algoritmos complejos para encontrar un plan de ejecución que tenga un costo razonablemente cercano al mínimo posible.Instead, it uses complex algorithms to find an execution plan that has a cost reasonably close to the minimum possible cost.

El Optimizador de consultas de SQL ServerSQL Server elige, además del plan de ejecución con el costo de recursos mínimo, el plan que devuelve resultados al usuario con un costo razonable de recursos y con la mayor brevedad posible.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. Por ejemplo, el procesamiento de una consulta en paralelo suele utilizar más recursos que el procesamiento en serie, pero completa la consulta más rápidamente.For example, processing a query in parallel typically uses more resources than processing it serially, but completes the query faster. El Optimizador de consultas de SQL ServerSQL Server usará un plan de ejecución en paralelo para devolver resultados si esto no afecta negativamente a la carga del servidor.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.

El Optimizador de consultas de SQL ServerSQL Server confía en las estadísticas de distribución cuando calcula los costos de recursos de métodos diferentes para extraer información de una tabla o un índice.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. Se mantienen estadísticas de distribución para las columnas y los índices, y se conserva información sobre la densidad1 de los datos subyacentes.Distribution statistics are kept for columns and indexes, and hold information on the density1 of the underlying data. Esto se usa para indicar la selectividad de los valores de una columna o un índice determinado.This is used to indicate the selectivity of the values in a particular index or column. Por ejemplo, en una tabla que representa automóviles, muchos automóviles tienen el mismo fabricante, pero cada uno dispone de un único número de identificación de vehículo (NIV).For example, in a table representing cars, many cars have the same manufacturer, but each car has a unique vehicle identification number (VIN). Un índice del NIV es más selectivo que un índice del fabricante, porque NIV tiene una densidad inferior a la del fabricante.An index on the VIN is more selective than an index on the manufacturer, because VIN has lower density then manufacturer. Si las estadísticas de los índices no están actualizadas, puede que el optimizador de consultas no realice la mejor elección para el estado actual de la tabla.If the index statistics are not current, the Query Optimizer may not make the best choice for the current state of the table. Para obtener más información sobre las densidades, vea Estadísticas.For more information about densities, see Statistics.

1 La densidad define la distribución de valores únicos que existen en los datos, o bien el promedio de valores duplicados para una columna determinada.1 Density defines the distribution of unique values that exist in the data, or the average number of duplicate values for a given column. A medida que disminuye la densidad, aumenta la selectividad de un valor.As density decreases, selectivity of a value increases.

El Optimizador de consultas de SQL ServerSQL Server es importante porque permite que el servidor de la base de datos se ajuste dinámicamente a las condiciones cambiantes de la base de datos, sin necesitar la entrada de un programador o de un administrador de base de datos.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. Esto permite a los programadores centrarse en la descripción del resultado final de la consulta.This enables programmers to focus on describing the final result of the query. Pueden estar seguros de que el Optimizador de consultas de SQL ServerSQL Server creará un plan de ejecución eficaz para el estado de la base de datos cada vez que se ejecuta la instrucción.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.

Procesar una instrucción SELECTProcessing a SELECT Statement

Los pasos básicos que SQL ServerSQL Server utiliza para procesar una única instrucción SELECT incluyen lo siguiente:The basic steps that SQL ServerSQL Server uses to process a single SELECT statement include the following:

  1. El analizador examina la instrucción SELECT y la divide en unidades lógicas como palabras clave, expresiones, operadores e identificadores.The parser scans the SELECT statement and breaks it into logical units such as keywords, expressions, operators, and identifiers.
  2. Se genera un árbol de la consulta, a veces denominado árbol de secuencia, que describe los pasos lógicos que se requieren para transformar los datos de origen en el formato que necesita el conjunto de resultados.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. El optimizador de consultas analiza diferentes formas de acceso a las tablas de origen.The Query Optimizer analyzes different ways the source tables can be accessed. A continuación, selecciona la serie de pasos que devuelve los resultados de la forma más rápida utilizando el menor número posible de recursos.It then selects the series of steps that returns the results fastest while using fewer resources. El árbol de la consulta se actualiza para registrar esta serie exacta de pasos.The query tree is updated to record this exact series of steps. La versión final y optimizada del árbol de la consulta se denomina plan de ejecución.The final, optimized version of the query tree is called the execution plan.
  4. El motor relacional comienza a ejecutar el plan de ejecución.The relational engine starts executing the execution plan. A medida que se procesan los pasos que necesitan datos de las tablas base, el motor relacional solicita al motor de almacenamiento que pase los datos de los conjuntos de filas solicitados desde el motor relacional.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. El motor relacional procesa los datos que devuelve el motor de almacenamiento en el formato definido para el conjunto de resultados y devuelve el conjunto de resultados al cliente.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.

Doblado de constantes y evaluación de expresionesConstant Folding and Expression Evaluation

SQL ServerSQL Server evalúa algunas expresiones constantes con antelación para mejorar el rendimiento de las consultas.evaluates some constant expressions early to improve query performance. Es lo que se conoce como doblado de constantes.This is referred to as constant folding. Una constante es un literal de Transact-SQLTransact-SQL, como 3, "ABC", "2005-12-31", 1.0e3 o 0x12345678.A constant is a Transact-SQLTransact-SQL literal, such as 3, 'ABC', '2005-12-31', 1.0e3, or 0x12345678.

Expresiones que pueden doblarseFoldable Expressions

SQL ServerSQL Server usa el doblado de constantes con los siguientes tipos de expresiones:uses constant folding with the following types of expressions:

  • Expresiones aritméticas, como 1+1, 5/3*2, que solo incluyen constantes.Arithmetic expressions, such as 1+1, 5/3*2, that contain only constants.
  • Expresiones lógicas, como 1=1 y 1>2 AND 3>4, que solo incluyen constantes.Logical expressions, such as 1=1 and 1>2 AND 3>4, that contain only constants.
  • Funciones integradas que SQL ServerSQL Server considera que pueden doblarse, incluidas CAST y CONVERT.Built-in functions that are considered foldable by SQL ServerSQL Server, including CAST and CONVERT. Por lo general, una función intrínseca puede doblarse si se trata de una función exclusiva de sus entradas y no contiene ninguna otra información contextual, como opciones SET, configuración de idioma, opciones de la base de datos y claves de cifrado.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. Las funciones no deterministas no pueden doblarse.Nondeterministic functions are not foldable. Excepto algunas excepciones, las funciones deterministas integradas pueden doblarse.Deterministic built-in functions are foldable, with some exceptions.

Nota

Los tipos de objetos grandes constituyen una excepción.An exception is made for large object types. Si el tipo de salida del proceso de doblado es un tipo de objeto grande (text, image, nvarchar(max), varchar(max) o varbinary(max)), SQL ServerSQL Server no dobla la expresión.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.

Expresiones que no pueden doblarseNonfoldable Expressions

El resto de tipos de expresiones no pueden doblarse.All other expression types are not foldable. En concreto, los siguientes tipos de expresiones no pueden doblarse:In particular, the following types of expressions are not foldable:

  • Expresiones no constantes como una expresión cuyo resultado dependa del valor de una columna.Nonconstant expressions such as an expression whose result depends on the value of a column.
  • Expresiones cuyos resultados dependan de una variable o parámetro locales, como @x.Expressions whose results depend on a local variable or parameter, such as @x.
  • Funciones no deterministas.Nondeterministic functions.
  • Funciones definidas por el usuario (Transact-SQLTransact-SQL y CLR)User-defined functions (both Transact-SQLTransact-SQL and CLR).
  • Expresiones cuyos resultados dependan de la configuración de idioma.Expressions whose results depend on language settings.
  • Expresiones cuyos resultados dependan de las opciones SET.Expressions whose results depend on SET options.
  • Expresiones cuyos resultados dependan de las opciones de configuración del servidor.Expressions whose results depend on server configuration options.

Ejemplos de expresiones constantes que pueden doblarse y que no pueden doblarseExamples of Foldable and Nonfoldable Constant Expressions

Estudie la siguiente consulta: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;

Si la opción de base de datos PARAMETERIZATION no se establece en FORCED para la consulta, la expresión 117.00 + 1000.00 se evalúa y sustituye por su resultado, 1117.00, antes de que se compile la consulta.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. Entre las ventajas de este doblado de constantes figuran las siguientes:Benefits of this constant folding include the following:

  • La expresión no tiene que evaluarse repetidas veces durante el tiempo de ejecución.The expression does not have to be evaluated repeatedly at run time.
  • El valor de la expresión después de su evaluación lo utiliza el optimizador de consultas para estimar el tamaño del conjunto de resultados del fragmento de la consulta 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.

Por otra parte, si la función dbo.f es una función escalar definida por el usuario, la expresión dbo.f(100) no se dobla, puesto que SQL ServerSQL Server no dobla las expresiones que impliquen a funciones definidas por el usuario, incluso si son deterministas.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. Para obtener más información sobre la parametrización, consulte Parametrización forzada más adelante en este artículo.For more information on parameterization, see Forced Parameterization later in this article.

Evaluación de expresionesExpression Evaluation

Además, durante la optimización el estimador (de cardinalidad) del tamaño del conjunto de resultados que forma parte del optimizador evalúa algunas expresiones cuyas constantes no se doblan pero cuyos argumentos se conocen en tiempo de compilación, tanto si se trata de parámetros como de constantes.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.

Se evalúan las funciones integradas siguientes y los operadores especiales en tiempo de compilación específicamente, si se conocen todas sus entradas: UPPER, LOWER, RTRIM, DATEPART( YY only ), GETDATE, CAST y CONVERT.Specifically, the following built-in functions and special operators are evaluated at compile time if all their inputs are known: UPPER, LOWER, RTRIM, DATEPART( YY only ), GETDATE, CAST, and CONVERT. Los siguientes operadores también se evalúan en tiempo de compilación si se conocen todas sus entradas:The following operators are also evaluated at compile time if all their inputs are known:

  • Operadores aritméticos: +, -, *, /, unarios -Arithmetic operators: +, -, *, /, unary -
  • Operadores lógicos: AND, OR, NOTLogical Operators: AND, OR, NOT
  • Operadores de comparación: <, >, <=, >=, <>, LIKE, IS NULL, IS NOT NULLComparison operators: <, >, <=, >=, <>, LIKE, IS NULL, IS NOT NULL

El optimizador de consultas no evalúa ninguna otra función ni operador durante la estimación de la cardinalidad.No other functions or operators are evaluated by the Query Optimizer during cardinality estimation.

Ejemplos de evaluación de expresiones en tiempo de compilaciónExamples of Compile-Time Expression Evaluation

Observe este procedimiento almacenado:Consider this stored procedure:

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

Durante la optimización de la instrucción OrderDate > @d+1 del procedimiento, el optimizador de consultas intenta evaluar la cardinalidad esperada del conjunto de resultados para la condición SELECT.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. La expresión @d+1 no admite el doblado de constantes porque @d es un parámetro.The expression @d+1 is not constant-folded, because @d is a parameter. Sin embargo, el valor del parámetro ya se conoce durante la optimización.However, at optimization time, the value of the parameter is known. Esto permite que el optimizador de consultas estime con exactitud el tamaño del conjunto de resultados, lo que ayuda a seleccionar un buen plan de consulta.This allows the Query Optimizer to accurately estimate the size of the result set, which helps it select a good query plan.

Observe ahora un ejemplo similar al anterior, con la diferencia de que se utiliza una variable local, @d2, para sustituir @d+1 en la consulta y de que la expresión se evalúa en una instrucción SET en lugar de en una consulta.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;

Cuando se optimiza la instrucción SELECT de MyProc2 en SQL ServerSQL Server, el valor de @d2 no se conoce.When the SELECT statement in MyProc2 is optimized in SQL ServerSQL Server, the value of @d2 is not known. Por lo tanto, el optimizador de consultas utiliza una estimación predeterminada para la selectividad de OrderDate > @d2, (en este caso, un 30 por ciento).Therefore, the Query Optimizer uses a default estimate for the selectivity of OrderDate > @d2, (in this case 30 percent).

Procesar otras instruccionesProcessing Other Statements

Los pasos básicos descritos para procesar una instrucción SELECT se aplican a otras instrucciones Transact-SQLTransact-SQL, como INSERT, UPDATE y DELETE.The basic steps described for processing a SELECT statement apply to other Transact-SQLTransact-SQL statements such as INSERT, UPDATE, and DELETE. Las instruccionesUPDATE y DELETE deben identificar el conjunto de filas que se van a modificar o eliminar.UPDATE and DELETE statements both have to target the set of rows to be modified or deleted. El proceso de identificación de estas filas es el mismo que se utiliza para identificar las filas de origen que contribuyen al conjunto de resultados de una instrucción 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. Las instrucciones UPDATE e INSERT pueden contener instrucciones SELECT incrustadas que proporcionan los valores de los datos que se van a actualizar o insertar.The UPDATE and INSERT statements may both contain embedded SELECT statements that provide the data values to be updated or inserted.

Incluso las instrucciones del lenguaje de definición de datos (DDL), como CREATE PROCEDURE o ALTER TABLE, se resuelven en última instancia en un conjunto de operaciones relacionales en las tablas de catálogo del sistema y, a veces (como ALTER TABLE ADD COLUMN) en las tablas de datos.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.

Tablas de trabajoWorktables

El motor relacional puede necesitar generar una tabla de trabajo para realizar una operación lógica que se especifica en una instrucción Transact-SQLTransact-SQL.The relational engine may need to build a worktable to perform a logical operation specified in an Transact-SQLTransact-SQL statement. Las tablas de trabajo son tablas internas que se utilizan para almacenar resultados intermedios.Worktables are internal tables that are used to hold intermediate results. Se generan para determinadas consultas GROUP BY, ORDER BYo UNION .Worktables are generated for certain GROUP BY, ORDER BY, or UNION queries. Por ejemplo, si una cláusula ORDER BY hace referencia a columnas que no están cubiertas por índices, el motor relacional puede necesitar generar una tabla de trabajo para ordenar el conjunto de resultados en el orden solicitado.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. Las tablas de trabajo también se utilizan en ocasiones a modo de colas que contienen temporalmente el resultado de ejecutar parte de un plan de consulta.Worktables are also sometimes used as spools that temporarily hold the result of executing a part of a query plan. Las tablas de trabajo se generan en tempdb y se eliminan de forma automática cuando ya no se necesitan.Worktables are built in tempdb and are dropped automatically when they are no longer needed.

Resolución de vistasView Resolution

El procesador de consultas de SQL ServerSQL Server trata las vistas indizadas y no indizadas de manera diferente.The SQL ServerSQL Server query processor treats indexed and nonindexed views differently:

  • Las filas de una vista indizada se almacenan en la base de datos con el mismo formato que una tabla.The rows of an indexed view are stored in the database in the same format as a table. Si el optimizador de consultas decide utilizar una vista indizada en un plan de consulta, ésta recibe el mismo tratamiento que la tabla base.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.
  • Solo se almacena la definición de una vista no indizada, y no las filas de la vista.Only the definition of a nonindexed view is stored, not the rows of the view. El optimizador de consultas incorpora la lógica de la definición de la vista en el plan de ejecución que genera para la instrucción Transact-SQLTransact-SQL que hace referencia a la vista no indexada.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.

La lógica usada por el Optimizador de consultas de SQL ServerSQL Server para decidir cuándo se usa una vista indexada es muy similar a la lógica que se usa para decidir cuándo se usa un índice en una tabla.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. Si los datos de la vista indexada cubren toda o parte de la instrucción Transact-SQLTransact-SQL y el optimizador de consultas determina que un índice de la vista es la ruta de acceso menos costosa, este elegirá el índice independientemente de si se hace referencia a la vista por su nombre en la consulta.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.

Si una instrucción Transact-SQLTransact-SQL hace referencia a una vista no indizada, el analizador y el optimizador de consultas analizarán el origen de la instrucción Transact-SQLTransact-SQL y de la vista. Luego, las resolverán en un plan de ejecución único.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. No hay un plan para la instrucción Transact-SQLTransact-SQL y otro para la vista.There is not one plan for the Transact-SQLTransact-SQL statement and a separate plan for the view.

Por ejemplo, considere la vista siguiente: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

En función de esta vista, ambas instrucciones Transact-SQLTransact-SQL realizan las mismas operaciones en las tablas base y producen el mismo resultado: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';

La característica del plan de presentación de SQL ServerSQL Server Management Studio muestra que el motor relacional genera el mismo plan de ejecución para estas dos instrucciones 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.

Utilizar sugerencias con vistasUsing Hints with Views

Las sugerencias que se colocan en las vistas de una consulta pueden entrar en conflicto con otras sugerencias que se descubren al expandir la vista para obtener acceso a sus tablas base.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. En ese caso, la consulta devuelve un error.When this occurs, the query returns an error. Por ejemplo, considere la siguiente vista que contiene una sugerencia de tabla en su definición: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;

Ahora supongamos que usted ingresa la siguiente consulta:Now suppose you enter this query:

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

La consulta genera un error porque la sugerencia SERIALIZABLE que se aplica a la vista Person.AddrState de la consulta se propaga a las tablas Person.Address y Person.StateProvince de la vista cuando ésta se expande.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. No obstante, la expansión de la vista también mostrará la sugerencia NOLOCK en Person.Address.However, expanding the view also reveals the NOLOCK hint on Person.Address. Dado que las sugerencias SERIALIZABLE y NOLOCK entran en conflicto, la consulta resultante es incorrecta.Because the SERIALIZABLE and NOLOCK hints conflict, the resulting query is incorrect.

Las sugerencias de tabla PAGLOCK, NOLOCK, ROWLOCK, TABLOCKo TABLOCKX entran en conflicto las unas con las otras, lo mismo que las sugerencias de tabla HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREADy SERIALIZABLE .The PAGLOCK, NOLOCK, ROWLOCK, TABLOCK, or TABLOCKX table hints conflict with each other, as do the HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE table hints.

Las sugerencias pueden propagarse por los niveles de las vistas anidadas.Hints can propagate through levels of nested views. Por ejemplo, supongamos que una consulta aplica la sugerencia HOLDLOCK a una vista v1.For example, suppose a query applies the HOLDLOCK hint on a view v1. Cuando se expande v1 , observamos que la vista v2 forma parte de su definición.When v1 is expanded, we find that view v2 is part of its definition. La definición dev2incluye una sugerencia NOLOCK en una de sus tablas base.v2's definition includes a NOLOCK hint on one of its base tables. Sin embargo, esta tabla también hereda la sugerencia HOLDLOCK de la consulta de la vista v1.But this table also inherits the HOLDLOCK hint from the query on view v1. Dado que las sugerencias NOLOCK y HOLDLOCK entran en conflicto, se produce un error en la consulta.Because the NOLOCK and HOLDLOCK hints conflict, the query fails.

Si se utiliza la sugerencia FORCE ORDER en una consulta que contiene una vista, el orden de combinación de las tablas que se encuentran dentro de la vista se determina mediante la posición de la vista en la construcción ordenada.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. Por ejemplo, la siguiente consulta realiza una selección entre tres tablas y una vista: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);

Y View1 se define tal como se muestra a continuación:And View1 is defined as shown in the following:

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

El orden de combinación en el plan de consulta es Table1, Table2, TableA, TableB, Table3.The join order in the query plan is Table1, Table2, TableA, TableB, Table3.

Resolver índices de vistasResolving Indexes on Views

Al igual que cualquier índice, SQL ServerSQL Server decide usar una vista indexada en el plan de consulta cuando el Optimizador de consultas considera que es útil.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.

Las vistas indizadas se pueden crear en cualquier edición de SQL ServerSQL Server.Indexed views can be created in any edition of SQL ServerSQL Server. En algunas ediciones de algunas versiones de SQL ServerSQL Server, el Optimizador de consultas tiene en cuenta automáticamente la vista indexada.In some editions of some versions of SQL ServerSQL Server, the Query Optimizer automatically considers the indexed view. En algunas ediciones de algunas versiones de SQL ServerSQL Server, para usar una vista indexada, se debe usar la sugerencia de tabla NOEXPAND.In some editions of some versions of SQL ServerSQL Server, to use an indexed view, the NOEXPAND table hint must be used. Para una mayor aclaración, consulte la documentación de cada versión.For clarification, see the documentation for each version.

El Optimizador de consultas de SQL ServerSQL Server usa una vista indexada cuando se cumplen las siguientes condiciones:The SQL ServerSQL Server Query Optimizer uses an indexed view when the following conditions are met:

  • Las siguientes opciones de sesión están establecidas en ON:These session options are set to ON:
    • ANSI_NULLS
    • ANSI_PADDING
    • ANSI_WARNINGS
    • ARITHABORT
    • CONCAT_NULL_YIELDS_NULL
    • QUOTED_IDENTIFIER
    • La opción de sesión NUMERIC_ROUNDABORT está establecida en OFF.The NUMERIC_ROUNDABORT session option is set to OFF.
  • El optimizador de consultas encuentra una coincidencia entre las columnas de índice de la vista y los elementos de la consulta, como:The Query Optimizer finds a match between the view index columns and elements in the query, such as the following:
    • Predicados de condiciones de búsqueda de la cláusula WHERESearch condition predicates in the WHERE clause
    • Operaciones de combinaciónJoin operations
    • Funciones de agregadoAggregate functions
    • CláusulasGROUP BYGROUP BY clauses
    • Referencias a tablaTable references
  • El costo estimado de utilización del índice es el más bajo de todos los mecanismos de acceso que tiene en cuenta el optimizador de consultas.The estimated cost for using the index has the lowest cost of any access mechanisms considered by the Query Optimizer.
  • Todas las tablas a las que se hace referencia en la consulta (directamente o con la expansión de una vista para tener acceso a sus tablas subyacentes) que se correspondan con una referencia a tabla de la vista indizada deben tener el mismo conjunto de sugerencias de la consulta aplicado en ellas.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.

Nota

Las sugerencias READCOMMITTED y READCOMMITTEDLOCK siempre se consideran distintas en este contexto, independientemente del nivel de aislamiento de transacción actual.The READCOMMITTED and READCOMMITTEDLOCK hints are always considered different hints in this context, regardless of the current transaction isolation level.

Además de los requisitos de las sugerencias de tabla y opciones SET, estas reglas son las mismas que usa el Optimizador de consultas para determinar si un índice de la tabla satisface una consulta.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. No ha de especificarse ningún otro elemento para que la consulta utilice una vista indizada.Nothing else has to be specified in the query for an indexed view to be used.

Una consulta no tiene que hacer referencia explícita a una vista indexada en la cláusula FROM del Optimizador de consultas para usar dicha vista.A query does not have to explicitly reference an indexed view in the FROM clause for the Query Optimizer to use the indexed view. Si la consulta contiene referencias a columnas de las tablas base que también se encuentran en la vista indizada y el optimizador de consultas considera que la vista indizada constituye el mecanismo de acceso de menor costo, el optimizador elige la vista indizada, de forma similar a como elige los índices de la tabla base cuando no se hace referencia directa a los mismos en una consulta.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. El optimizador de consultas puede elegir la vista en caso de contener columnas a las que no se hace referencia en la consulta, siempre y cuando la vista ofrezca la opción de costo más bajo para incluir una o varias de las columnas especificadas en la consulta.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.

El optimizador de consultas trata FROM como vista estándar cualquier vista indexada a la que se haga referencia en la cláusula.The Query Optimizer treats an indexed view referenced in the FROM clause as a standard view. Expande la definición de la vista en la consulta al inicio del proceso de optimización.The Query Optimizer expands the definition of the view into the query at the start of the optimization process. A continuación se realiza la coincidencia de vista indizada.Then, indexed view matching is performed. Puede que se use la vista indexada en el plan de ejecución final seleccionado por el Optimizador de consultas o que, en su lugar, el plan materialice los datos necesarios de la vista mediante el acceso a las tablas base a las que hace referencia la vista.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. El Optimizador de consultas elige la alternativa de menor costo.The Query Optimizer chooses the lowest-cost alternative.

Usar sugerencias con vistas indizadasUsing Hints with Indexed Views

Puede evitar que los índices de la vista se utilicen en una consulta mediante el uso de la sugerencia de consulta EXPAND VIEWS o utilizar la sugerencia de tabla NOEXPAND para exigir el uso de un índice de una vista indexada especificada en la cláusula FROM de una consulta.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. Sin embargo, debe dejar que sea el optimizador de consultas el que determine dinámicamente los mejores métodos de acceso para cada consulta.However, you should let the Query Optimizer dynamically determine the best access methods to use for each query. Limite la utilización de EXPAND y NOEXPAND a casos específicos en los que se haya comprobado que el rendimiento mejora significativamente.Limit your use of EXPAND and NOEXPAND to specific cases where testing has shown that they improve performance significantly.

La opción EXPAND VIEWS determina que el Optimizador de consultas no use ninguno de los índices de la vista en toda la consulta.The EXPAND VIEWS option specifies that the Query Optimizer not use any view indexes for the whole query.

Cuando se especifica NOEXPAND en una vista, el Optimizador de consultas considera la posibilidad de usar cualquiera de los índices definidos en la vista.When NOEXPAND is specified for a view, the Query Optimizer considers using any indexes defined on the view. Cuando se especifica NOEXPAND con la cláusula INDEX() opcional, el Optimizador de consultas está obligado a usar los índices especificados.NOEXPAND specified with the optional INDEX() clause forces the Query Optimizer to use the specified indexes. NOEXPAND solo se puede especificar en una vista indexada, nunca en una vista no indexada.NOEXPAND can be specified only for an indexed view and cannot be specified for a view not indexed.

Cuando no se especifica NOEXPAND ni EXPAND VIEWS en una consulta que contiene una vista, ésta se expande para tener acceso a las tablas subyacentes.When neither NOEXPAND nor EXPAND VIEWS is specified in a query that contains a view, the view is expanded to access underlying tables. Si la consulta que compone la vista contiene sugerencias de tabla, éstas se propagan a las tablas subyacentes.If the query that makes up the view contains any table hints, these hints are propagated to the underlying tables. (Este proceso se explica con más detalle en Resolución de vistas). Siempre que los conjuntos de sugerencias que existen en las tablas subyacentes sean idénticos entre sí, se puede seleccionar la consulta para que coincida con una vista indizada.(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. La mayoría de las veces, estas sugerencias coinciden entre sí, porque se han heredado directamente de la vista.Most of the time, these hints will match each other, because they are being inherited directly from the view. Sin embargo, si la consulta hace referencia a tablas en lugar de vistas y las sugerencias aplicadas directamente en estas tablas no son idénticas, no se puede seleccionar dicha consulta para que coincida con una vista indizada.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. Si las sugerencias INDEX, PAGLOCK, ROWLOCK, TABLOCKX, UPDLOCKo XLOCK se aplican a las tablas a las que hace referencia la consulta después de la expansión de la vista, se puede seleccionar la consulta para la coincidencia de vistas indexadas.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.

Si una sugerencia de tabla en forma de INDEX (index_val[ ,...n] ) hace referencia a una vista de una consulta y no se especifica también la sugerencia NOEXPAND , se pasa por alto la sugerencia de índice.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. Para especificar el uso de un determinado índice, use NOEXPAND.To specify use of a particular index, use NOEXPAND.

Por lo general, cuando el Optimizador de consultas hace coincidir una vista indexada con una consulta, las sugerencias especificadas en las tablas o vistas de la consulta se aplican directamente a la vista indexada.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. Si el optimizador de consultas elige no utilizar una vista indizada, las sugerencias se propagan directamente a las tablas a las que se hace referencia en la vista.If the Query Optimizer chooses not to use an indexed view, any hints are propagated directly to the tables referenced in the view. Para más información, consulte Resolución de vistas.For more information, see View Resolution. Esta propagación no se aplica a las sugerencias de combinación.This propagation does not apply to join hints. Solo se aplican en su posición original en la consulta.They are applied only in their original position in the query. El optimizador de consultas no tiene en cuenta las sugerencias de combinación al hacer coincidir consultas con vistas indizadas.Join hints are not considered by the Query Optimizer when matching queries to indexed views. Si un plan de consulta utiliza una vista indizada que coincide con parte de una consulta que contiene una sugerencia de consulta, no se utiliza la sugerencia de consulta en el plan.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.

No se permiten sugerencias en las definiciones de vistas indizadas.Hints are not allowed in the definitions of indexed views. En los modos de compatibilidad 80 y superiores, SQL ServerSQL Server pasa por alto las sugerencias incluidas en definiciones de vistas indizadas al mantenerlas o al ejecutar consultas que utilizan vistas indizadas.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. Aunque utilizar sugerencias en definiciones de vistas indizadas no genera un error de sintaxis en el modo de compatibilidad 80, las sugerencias se pasan por alto.Although using hints in indexed view definitions will not produce a syntax error in 80 compatibility mode, they are ignored.

Resolver vistas con particiones distribuidasResolving Distributed Partitioned Views

El procesador de consultas de SQL ServerSQL Server optimiza el rendimiento de las vistas con particiones distribuidas.The SQL ServerSQL Server query processor optimizes the performance of distributed partitioned views. El aspecto más importante del rendimiento de las vistas con particiones distribuidas es la minimización de la cantidad de datos transferidos entre los servidores miembro.The most important aspect of distributed partitioned view performance is minimizing the amount of data transferred between member servers.

SQL ServerSQL Server crea planes dinámicos e inteligentes que hacen un uso eficaz de las consultas distribuidas para tener acceso a los datos de las tablas miembro remotas:builds intelligent, dynamic plans that make efficient use of distributed queries to access data from remote member tables:

  • El Procesador de consultas usa en primer lugar OLE DB para recuperar las definiciones de la restricción CHECK de cada tabla miembro.The Query Processor first uses OLE DB to retrieve the check constraint definitions from each member table. Esto permite al procesador de consultas asignar la distribución de valores clave a las tablas miembro.This allows the query processor to map the distribution of key values across the member tables.
  • El procesador de consultas compara los intervalos de clave especificados en la cláusula WHERE de una instrucción Transact-SQLTransact-SQL con el mapa que muestra cómo se distribuyen las filas en las tablas miembro.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. El procesador de consultas crea entonces un plan de ejecución de consultas que utiliza consultas distribuidas para recuperar únicamente las filas remotas necesarias para completar la instrucción 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. El plan de ejecución se crea también de tal manera que cualquier acceso a las tablas miembro remotas, para datos o metadatos, se demora hasta que se requiere la información.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.

Por ejemplo, imagine un sistema donde una tabla de clientes está dividida en Server1 (CustomerID de 1 a 3299999), Server2 (CustomerID de 3300000 a 6599999) y Server3 (CustomerID de 6600000 a 9999999).For example, consider a system where a customers table is partitioned across Server1 (CustomerID from 1 through 3299999), Server2 (CustomerID from 3300000 through 6599999), and Server3 (CustomerID from 6600000 through 9999999).

Tome como ejemplo el plan de ejecución que se crea para esta consulta ejecutada en Server1:Consider the execution plan built for this query executed on Server1:

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

El plan de ejecución para esta consulta extrae las filas con los valores clave de CustomerID de 3200000 a 3299999 de la tabla miembro local, y emite una consulta distribuida para recuperar las filas con los valores de clave de 3300000 a 3400000 de Server2.The execution plan for this query extracts the rows with CustomerID key values from 3200000 through 3299999 from the local member table, and issues a distributed query to retrieve the rows with key values from 3300000 through 3400000 from Server2.

El procesador de consultas SQL ServerSQL Server también puede crear lógica dinámica en planes de ejecución de consultas para instrucciones Transact-SQLTransact-SQL. Los valores clave de estas instrucciones no se conocen cuando se tiene que crear el plan.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. Tome como ejemplo este procedimiento almacenado:For example, consider this stored procedure:

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

SQL ServerSQL Server no puede predecir el valor de clave que proporcionará el parámetro @CustomerIDParameter cada vez que se ejecute el procedimiento.cannot predict what key value will be supplied by the @CustomerIDParameter parameter every time the procedure is executed. Puesto que el valor de clave no se puede predecir, el procesador de consultas no puede predecir tampoco a qué tabla miembro deberá tenerse acceso.Because the key value cannot be predicted, the query processor also cannot predict which member table will have to be accessed. Para tratar este caso, SQL ServerSQL Server crea un plan de ejecución con lógica condicional, que se denominada filtros dinámicos, y que sirve para controlar la tabla miembro a la que se tendrá acceso en función del valor del parámetro de entrada.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. Suponiendo que el procedimiento almacenado GetCustomer se ejecutó en Server1, la lógica del plan de ejecución puede representarse como se muestra en el siguiente ejemplo: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

A veces SQL ServerSQL Server genera ese tipo de planes de ejecución dinámicos incluso para consultas sin parámetros.SQL ServerSQL Server sometimes builds these types of dynamic execution plans even for queries that are not parameterized. El Optimizador puede parametrizar una consulta de modo que el plan de ejecución pueda volver a usarse.The Query Optimizer may parameterize a query so that the execution plan can be reused. Si el Optimizador de consultas parametriza una consulta que hace referencia a una vista con particiones, el optimizador ya no puede dar por supuesto que las filas necesarias vendrán de una tabla base de datos especificada,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. y tendrá que utilizar los filtros dinámicos en el plan de ejecución.It will then have to use dynamic filters in the execution plan.

Ejecutar un procedimiento almacenado y un desencadenadorStored Procedure and Trigger Execution

SQL ServerSQL Serveralmacena únicamente el origen de procedimientos almacenados y desencadenadores.stores only the source for stored procedures and triggers. La primera vez que se ejecuta un procedimiento almacenado o un desencadenador, el origen se compila en un plan de ejecución.When a stored procedure or trigger is first executed, the source is compiled into an execution plan. Si el procedimiento almacenado o el desencadenador se ejecutan de nuevo antes de que el plan de ejecución quede anticuado en la memoria, el motor relacional detecta el plan existente y vuelve a utilizarlo.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. Si el plan ha quedado anticuado en la memoria, se genera uno nuevo.If the plan has aged out of memory, a new plan is built. Este proceso es similar al que sigue SQL ServerSQL Server para procesar todas las instrucciones Transact-SQLTransact-SQL.This process is similar to the process SQL ServerSQL Server follows for all Transact-SQLTransact-SQL statements. La principal ventaja de rendimiento que tienen los procedimientos almacenados y los desencadenadores en SQL ServerSQL Server en comparación con lotes de Transact-SQLTransact-SQL dinámico, es que sus instrucciones Transact-SQLTransact-SQL siempre son las mismas.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. Por lo tanto, el motor relacional los hace coincidir fácilmente con los planes de ejecución existentes.Therefore, the relational engine easily matches them with any existing execution plans. El procedimiento almacenado y los planes del desencadenador se reutilizan fácilmente.Stored procedure and trigger plans are easily reused.

El plan de ejecución para los procedimientos almacenados y los desencadenadores se ejecuta aparte del plan de ejecución del lote que llama al procedimiento almacenado o que activa el desencadenador.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. Esto proporciona mayor flexibilidad para volver a utilizar los planes de ejecución de los procedimientos almacenados y desencadenadores.This allows for greater reuse of the stored procedure and trigger execution plans.

Almacenar en caché y volver a utilizar un plan de ejecuciónExecution Plan Caching and Reuse

SQL ServerSQL Server tiene un bloque de memoria que se utiliza para almacenar planes de ejecución y búferes de datos.has a pool of memory that is used to store both execution plans and data buffers. El porcentaje del conjunto que se asigna a los planes de ejecución o a los búferes de datos varía dinámicamente según el estado del sistema.The percentage of the pool allocated to either execution plans or data buffers fluctuates dynamically, depending on the state of the system. La parte del bloque de memoria que se usa para almacenar los planes de ejecución se denomina caché de planes.The part of the memory pool that is used to store execution plans is referred to as the plan cache.

Los planes de ejecución de SQL ServerSQL Server tienen los siguientes componentes principales:SQL ServerSQL Server execution plans have the following main components:

  • Plan de ejecución de la consulta Query Execution Plan
    La mayor parte del plan de ejecución es una estructura de datos reentrante de solo lectura que varios usuarios pueden utilizar.The bulk of the execution plan is a re-entrant, read-only data structure used by any number of users. Esto se conoce como plan de consulta.This is referred to as the query plan. No se almacena ningún contexto de usuario en el plan de consulta.No user context is stored in the query plan. Nunca hay más de una o dos copias del plan de consulta en la memoria: una copia para todas las ejecuciones en serie y otra para todas las ejecuciones en paralelo.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. La copia en paralelo cubre todas las ejecuciones en paralelo, sin tener en cuenta el grado de paralelismo.The parallel copy covers all parallel executions, regardless of their degree of parallelism.
  • Contexto de ejecución Execution Context
    Cada usuario que ejecuta la consulta tiene una estructura de datos que alberga los datos específicos de su ejecución, como los valores de los parámetros.Each user that is currently executing the query has a data structure that holds the data specific to their execution, such as parameter values. Esta estructura de datos se conoce como contexto de ejecución.This data structure is referred to as the execution context. Las estructuras de datos del contexto de ejecución se vuelven a utilizar.The execution context data structures are reused. Si un usuario ejecuta una consulta y una de las estructuras no está en uso, ésta se reinicializa con el contexto del nuevo usuario.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

Al ejecutar una instrucción Transact-SQLTransact-SQL en SQL ServerSQL Server, el motor relacional busca primero en la caché de planes para comprobar la existencia de un plan de ejecución para la misma instrucción 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. La instrucción Transact-SQLTransact-SQL se considera existente si coincide literalmente con una instrucción Transact-SQLTransact-SQL ejecutada anteriormente con un plan en caché, carácter a carácter.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 vuelve a usar cualquier plan existente que encuentra, de forma que se ahorra la sobrecarga de volver a compilar la instrucción Transact-SQLTransact-SQL.reuses any existing plan it finds, saving the overhead of recompiling the Transact-SQLTransact-SQL statement. Si no existe ningún plan de ejecución, SQL ServerSQL Server genera uno nuevo para la consulta.If no existing execution plan exists, SQL ServerSQL Server generates a new execution plan for the query.

Nota

Algunas instrucciones Transact-SQLTransact-SQL no se almacenan en la caché, como, por ejemplo, instrucciones de operaciones masivas que se ejecutan en almacén de filas o instrucciones que contienen literales de cadenas de más de 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 tiene un algoritmo eficiente que permite encontrar cualquier plan de ejecución existente para una determinada instrucción Transact-SQLTransact-SQL.has an efficient algorithm to find any existing execution plans for any specific Transact-SQLTransact-SQL statement. En la mayor parte de los sistemas, los recursos mínimos que utiliza este recorrido son menos que los recursos que se ahorran al poder utilizar de nuevo los planes existentes en lugar de compilar cada instrucción 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.

Los algoritmos que hacen coincidir las instrucciones Transact-SQLTransact-SQL nuevas con los planes de ejecución existentes no utilizados de la caché requieren que todas las referencias a objetos estén completas.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. Por ejemplo, supongamos que Person es el esquema predeterminado para el usuario que ejecuta las instrucciones SELECT siguientes.For example, assume that Person is the default schema for the user executing the below SELECT statements. Aunque en este ejemplo no es necesario que la tabla Person tenga un nombre completo para ejecutarse, significa que la segunda instrucción no se corresponde con un plan existente, pero se hace coincidir con la tercera: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

Quitar los planes de ejecución de la caché de planesRemoving Execution Plans from the Plan Cache

Los planes de ejecución permanecen en la caché de planes mientras haya suficiente memoria para almacenarlos.Execution plans remain in the plan cache as long as there is enough memory to store them. Cuando existe presión de memoria, Motor de base de datos de SQL ServerSQL Server Database Engine usa un enfoque basado en costos para determinar qué planes de ejecución hay que quitar de la caché de planes.When memory pressure exists, the Motor de base de datos de SQL ServerSQL Server Database Engine uses a cost-based approach to determine which execution plans to remove from the plan cache. Para tomar una decisión basada en costos, Motor de base de datos de SQL ServerSQL Server Database Engine incrementa y reduce una variable de costo actual por cada plan de ejecución de acuerdo con los factores siguientes.To make a cost-based decision, the Motor de base de datos de SQL ServerSQL Server Database Engine increases and decreases a current cost variable for each execution plan according to the following factors.

Cuando un proceso de usuario inserta un plan de ejecución en la memoria caché, el proceso de usuario establece el costo actual igual al costo de compilación de la consulta original; para los planes de ejecución ad hoc, el proceso de usuario establece el costo actual en cero.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. Después, cada vez que un proceso de usuario hace referencia a un plan de ejecución, restablece el costo actual en el costo de compilación original; en el caso de los planes de ejecución ad hoc, el proceso de usuario aumenta el costo actual.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. Para todos los planes, el valor máximo del costo actual es el costo de compilación original.For all plans, the maximum value for the current cost is the original compile cost.

Cuando existe presión de memoria, Motor de base de datos de SQL ServerSQL Server Database Engine responde quitando los planes de ejecución de la caché de planes.When memory pressure exists, the Motor de base de datos de SQL ServerSQL Server Database Engine responds by removing execution plans from the plan cache. Para determinar qué planes quitar, Motor de base de datos de SQL ServerSQL Server Database Engine examina repetidamente el estado de cada plan de ejecución y quita los planes cuando su costo actual es cero.To determine which plans to remove, the Motor de base de datos de SQL ServerSQL Server Database Engine repeatedly examines the state of each execution plan and removes plans when their current cost is zero. Un plan de ejecución con un costo actual de cero no se quita automáticamente cuando existe presión de memoria; solamente se quita cuando Motor de base de datos de SQL ServerSQL Server Database Engine examina el plan y el costo actual es cero.An execution plan with zero current cost is not removed automatically when memory pressure exists; it is removed only when the Motor de base de datos de SQL ServerSQL Server Database Engine examines the plan and the current cost is zero. Al examinar un plan de ejecución, Motor de base de datos de SQL ServerSQL Server Database Engine impulsa el costo actual hacia cero reduciendo el costo actual si una consulta no está usando actualmente el plan.When examining an execution plan, the Motor de base de datos de SQL ServerSQL Server Database Engine pushes the current cost towards zero by decreasing the current cost if a query is not currently using the plan.

Motor de base de datos de SQL ServerSQL Server Database Engine examina repetidamente los planes de ejecución hasta que se han quitado los suficientes para satisfacer los requisitos de memoria.The Motor de base de datos de SQL ServerSQL Server Database Engine repeatedly examines the execution plans until enough have been removed to satisfy memory requirements. Mientras existe presión de memoria, un plan de ejecución puede ver su costo incrementado y reducido más de una vez.While memory pressure exists, an execution plan may have its cost increased and decreased more than once. Cuando ya no existe presión de memoria, Motor de base de datos de SQL ServerSQL Server Database Engine deja de reducir el costo actual de los planes de ejecución no usados y todos los planes de ejecución permanecen en la caché de planes, incluso aunque su costo sea cero.When memory pressure no longer exists, the Motor de base de datos de SQL ServerSQL 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.

Motor de base de datos de SQL ServerSQL Server Database Engine usa el monitor de recursos y los subprocesos de trabajo de usuario para liberar memoria de la caché de planes como respuesta a la presión de memoria.The Motor de base de datos de SQL ServerSQL Server Database Engine uses the resource monitor and user worker threads to free memory from the plan cache in response to memory pressure. El monitor de recursos y los subprocesos de trabajo de usuario pueden examinar los planes que se ejecutan simultáneamente y reducir el costo actual de los planes de ejecución no usados.The resource monitor and user worker threads can examine plans run concurrently to decrease the current cost for each unused execution plan. El monitor de recursos quita los planes de ejecución de la caché de planes cuando hay presión de memoria global.The resource monitor removes execution plans from the plan cache when global memory pressure exists. Libera memoria para aplicar las directivas correspondientes a la memoria del sistema, memoria de procesos, memoria del grupo de recursos y tamaño máximo de todas las memorias de caché.It frees memory to enforce policies for system memory, process memory, resource pool memory, and maximum size for all caches.

El tamaño máximo de todas las memorias de caché es una función del tamaño del conjunto de búferes y no puede exceder de la memoria máxima del servidor.The maximum size for all caches is a function of the buffer pool size and cannot exceed the maximum server memory. Para más información sobre la configuración de la memoria máxima del servidor, consulte la configuración de max server memory en sp_configure.For more information on configuring the maximum server memory, see the max server memory setting in sp_configure.

Los subprocesos de trabajo de usuario quitan los planes de ejecución de la caché de planes cuando existe presión de memoria caché única.The user worker threads remove execution plans from the plan cache when single cache memory pressure exists. Aplican las directivas del tamaño máximo de la memoria caché única y de las entradas máximas de la memoria caché única.They enforce policies for maximum single cache size and maximum single cache entries.

En los ejemplos siguientes se muestra qué planes de ejecución se quitan de la caché de planes:The following examples illustrate which execution plans get removed from the plan cache:

  • Se suele hacer referencia a un plan de ejecución como si su costo nunca llegara a ser cero.An execution plan is frequently referenced so that its cost never goes to zero. El plan permanece en la caché de planes y no se quita a menos que haya presión de memoria y el costo actual sea cero.The plan remains in the plan cache and is not removed unless there is memory pressure and the current cost is zero.
  • Se insertó un plan de ejecución ad hoc y no se le vuelve a hacer referencia antes de que exista presión de memoria.An ad-hoc execution plan is inserted and is not referenced again before memory pressure exists. Dado que los planes ad hoc se inician con un costo actual de cero, cuando Motor de base de datos de SQL ServerSQL Server Database Engine examina el plan de ejecución, verá el costo actual de cero y quitará el plan de la caché de planes.Since ad-hoc plans are initialized with a current cost of zero, when the Motor de base de datos de SQL ServerSQL Server Database Engine examines the execution plan, it will see the zero current cost and remove the plan from the plan cache. El plan de ejecución ad hoc permanece en la caché de planes con el costo actual de cero cuando ya no hay presión de memoria.The ad-hoc execution plan remains in the plan cache with a zero current cost when memory pressure does not exist.

Para quitar manualmente un único plan o todos los planes de la memoria caché, utilice DBCC FREEPROCCACHE.To manually remove a single plan or all plans from the cache, use DBCC FREEPROCCACHE. Desde SQL Server 2016 (13.x)SQL Server 2016 (13.x), ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE borra la memoria caché (de plan) de procedimientos de la base de datos en ámbito.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.

Volver a compilar planes de ejecuciónRecompiling Execution Plans

Algunos cambios en una base de datos puede hacer que un plan de ejecución resulte ineficaz o no válido, según el nuevo estado de la base de datos.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 detecta los cambios que invalidan un plan de ejecución y marca el plan como no válido.detects the changes that invalidate an execution plan and marks the plan as not valid. Después, debe volver a compilarse un nuevo plan para la próxima conexión que ejecute la consulta.A new plan must then be recompiled for the next connection that executes the query. Las condiciones que hacen que un plan no sea válido son:The conditions that invalidate a plan include the following:

  • Los cambios realizados en una tabla o vista a las que hace referencia la consulta (ALTER TABLE y ALTER VIEW).Changes made to a table or view referenced by the query (ALTER TABLE and ALTER VIEW).
  • Los cambios realizados en un procedimiento único, que descartaría todos los planes de dicho procedimiento de la memoria caché (ALTER PROCEDURE).Changes made to a single procedure, which would drop all plans for that procedure from the cache (ALTER PROCEDURE).
  • Cambios en los índices que utilizan el plan de ejecución.Changes to any indexes used by the execution plan.
  • Actualizaciones de estadísticas que utiliza el plan de ejecución y que se generan explícitamente desde instrucciones, como UPDATE STATISTICS, o automáticamente.Updates on statistics used by the execution plan, generated either explicitly from a statement, such as UPDATE STATISTICS, or generated automatically.
  • Quitar un índice que utiliza el plan de ejecución.Dropping an index used by the execution plan.
  • Una llamada explícita a sp_recompile.An explicit call to sp_recompile.
  • Numerosos cambios en las claves (generados por las instrucciones INSERT o DELETE de otros usuarios que modifican una tabla a la que hace referencia la consulta).Large numbers of changes to keys (generated by INSERT or DELETE statements from other users that modify a table referenced by the query).
  • Para tablas con desencadenadores, si el número de filas de las tablas insertadas o eliminadas crece significativamente.For tables with triggers, if the number of rows in the inserted or deleted tables grows significantly.
  • Ejecutar un procedimiento almacenado mediante la opción WITH RECOMPILE .Executing a stored procedure using the WITH RECOMPILE option.

La mayoría de las recompilaciones se necesitan para comprobar si las instrucciones son correctas o para obtener planes de ejecución de consultas potencialmente más rápidos.Most recompilations are required either for statement correctness or to obtain potentially faster query execution plans.

En SQL ServerSQL Server 2000, siempre que una instrucción de un lote provoca una recompilación, se vuelve a compilar todo el lote, independientemente de si se ha enviado por medio de un procedimiento almacenado, un desencadenador, un lote ad hoc o una instrucción preparada.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. A partir de SQL Server 2005 (9.x)SQL Server 2005 (9.x), solo se vuelve a compilar la instrucción del lote que provoca la recompilación.Starting with SQL Server 2005 (9.x)SQL Server 2005 (9.x), only the statement inside the batch that causes recompilation is recompiled. Debido a esta diferencia, los recuentos de recompilaciones de SQL ServerSQL Server 2000 y de versiones posteriores no son comparables.Because of this difference, recompilation counts in SQL ServerSQL Server 2000 and later releases are not comparable. Además, existen otros tipos de compilaciones en SQL Server 2005 (9.x)SQL Server 2005 (9.x) y en versiones posteriores, gracias al conjunto de características ampliado.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.

La recompilación de instrucciones beneficia al rendimiento ya que, en la mayoría de los casos, un pequeño número de instrucciones provocan recompilaciones con sus penalizaciones asociadas, en lo que respecta a los bloqueos y el tiempo de la 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. Estas penalizaciones se evitan para otras instrucciones del lote que no es necesario volver a compilar.These penalties are therefore avoided for the other statements in the batch that do not have to be recompiled.

El evento extendido sql_statement_recompile (xEvent) envía informes de recompilaciones de nivel de instrucción.The sql_statement_recompile extended event (xEvent) reports statement-level recompilations. Este xEvent se produce cuando un lote de cualquier tipo requiere una recompilación de nivel de instrucción.This xEvent occurs when a statement-level recompilation is required by any kind of batch. Esto incluye procedimientos almacenados, desencadenadores, lotes ad hoc y consultas.This includes stored procedures, triggers, ad hoc batches and queries. Los lotes pueden enviarse mediante diversas interfaces, incluidas sp_executesql, SQL dinámico, métodos Prepare o métodos Execute.Batches may be submitted through several interfaces, including sp_executesql, dynamic SQL, Prepare methods or Execute methods. La columna recompile_cause del xEvent sql_statement_recompile contiene un código de número entero que indica la razón de la recompilación.The recompile_cause column of sql_statement_recompile xEvent contains an integer code that indicates the reason for the recompilation. La tabla siguiente contiene las posibles razones:The following table contains the possible reasons:

Esquema modificadoSchema changed Estadísticas modificadasStatistics changed
Compilación diferidaDeferred compile Opción SET modificadaSET option changed
Tabla temporal modificadaTemporary table changed Conjunto de filas remoto modificadoRemote rowset changed
Permiso FOR BROWSE modificadoFOR BROWSE permission changed Entorno de notificación de consultas modificadoQuery notification environment changed
Vista con particiones modificadaPartitioned view changed Opciones de cursor modificadasCursor options changed
OPTION (RECOMPILE) solicitadoOPTION (RECOMPILE) requested Plan parametrizado vaciadoParameterized plan flushed
Plan que afecta a la versión de la base de datos modificadoPlan affecting database version changed Directiva que fuerza el plan del almacén de consultas modificadaQuery Store plan forcing policy changed
Error al forzar el plan del almacén de consultasQuery Store plan forcing failed Falta el plan en el almacén de consultasQuery Store missing the plan

Nota

En las versiones de SQL ServerSQL Server en las que no hay xEvents disponibles, se puede usar el evento de seguimiento SP:Recompile del generador de perfiles de SQL ServerSQL Server para crear informes de recompilaciones de instrucciones.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. El evento de seguimiento SQL:StmtRecompile también informa de recompilaciones de instrucciones, y se puede usar para realizar un seguimiento de las recompilaciones y depurarlas.The trace event SQL:StmtRecompile also reports statement-level recompilations, and this trace event can also be used to track and debug recompilations. Aunque SP:Recompile solo se genera para procedimientos almacenados y desencadenadores, SQL:StmtRecompile se genera para procedimientos almacenados, desencadenadores, lotes ad hoc, lotes que se ejecutan mediante sp_executesql, consultas preparadas y SQL dinámico.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. La columna EventSubClass de SP:Recompile y SQL:StmtRecompile contiene un código de número entero que indica la razón de la recompilación.The EventSubClass column of SP:Recompile and SQL:StmtRecompile contains an integer code that indicates the reason for the recompilation. Los códigos se describen aquí.The codes are described here.

Nota

Si la opción de base de datos AUTO_UPDATE_STATISTICS se establece en ON, las consultas se vuelven a compilar cuando su destino son tablas o vistas indexadas cuyas estadísticas se han actualizado o cuyas cardinalidades han cambiado mucho desde la última ejecución.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. Este comportamiento se aplica a tablas estándar definidas por el usuario, a tablas temporales y a tablas insertadas y eliminadas creadas por desencadenadores DML.This behavior applies to standard user-defined tables, temporary tables, and the inserted and deleted tables created by DML triggers. Si el rendimiento de la consulta se ve afectado por un número excesivo de recompilaciones, considere la posibilidad de cambiar esta opción a OFF.If query performance is affected by excessive recompilations, consider changing this setting to OFF. Cuando la opción de base de datos AUTO_UPDATE_STATISTICS está establecida en OFF, no se producen recompilaciones basadas en estadísticas o cambios en la cardinalidad, a excepción de las tablas insertadas y eliminadas que se crean mediante los desencadenadores 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. Como estas tablas se crean en tempdb, la recompilación de las consultas a las que tienen acceso depende de la configuración de AUTO_UPDATE_STATISTICS en tempdb.Because these tables are created in tempdb, the recompilation of queries that access them depends on the setting of AUTO_UPDATE_STATISTICS in tempdb. Tenga en cuenta que en SQL ServerSQL Server 2000, las consultas se siguen recompilando en función de los cambios de cardinalidad de las tablas insertadas y eliminadas del desencadenador DML, incluso cuando esta opción está establecida en OFF.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.

Parámetros y reutilización de un plan de ejecuciónParameters and Execution Plan Reuse

El uso de parámetros, incluidos los marcadores de parámetros de las aplicaciones ADO, OLE DB y ODBC, puede incrementar las posibilidades de volver a utilizar los planes de ejecución.The use of parameters, including parameter markers in ADO, OLE DB, and ODBC applications, can increase the reuse of execution plans.

Advertencia

La utilización de parámetros o marcadores de parámetros para contener valores que especifican los usuarios finales es más segura que la concatenación de valores en una cadena que después se ejecuta mediante un método de API de acceso de datos, la instrucción EXECUTE o el procedimiento almacenado 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.

La única diferencia entre las dos instrucciones SELECT siguientes son los valores que se comparan en la cláusula 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;

La única diferencia entre los planes de ejecución para estas consultas es el valor que se almacena para la comparación con la columna ProductSubcategoryID .The only difference between the execution plans for these queries is the value stored for the comparison against the ProductSubcategoryID column. Aunque el objetivo de SQL ServerSQL Server es reconocer siempre que las instrucciones generan básicamente el mismo plan y volver a utilizar los planes, SQL ServerSQL Server no siempre lo detecta en las instrucciones Transact-SQLTransact-SQL complejas.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.

Separar las constantes de la instrucción Transact-SQLTransact-SQL mediante parámetros ayuda al motor relacional a reconocer los planes duplicados.Separating constants from the Transact-SQLTransact-SQL statement by using parameters helps the relational engine recognize duplicate plans. Puede utilizar los parámetros de varias maneras:You can use parameters in the following ways:

  • En Transact-SQLTransact-SQL, use sp_executesql:In Transact-SQLTransact-SQL , use sp_executesql:

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

    Este método se recomienda para los scripts Transact-SQLTransact-SQL, procedimientos almacenados o desencadenadores que generan dinámicamente instrucciones SQL.This method is recommended for Transact-SQLTransact-SQL scripts, stored procedures, or triggers that generate SQL statements dynamically.

  • Con ADO, OLE DB y ODBC, utilice marcadores de parámetros.ADO, OLE DB, and ODBC use parameter markers. Los marcadores de parámetros son signos de interrogación (?) que sustituyen a una constante en una instrucción SQL y se enlazan a una variable de programa.Parameter markers are question marks (?) that replace a constant in an SQL statement and are bound to a program variable. Por ejemplo, podría hacer lo siguiente en una aplicación ODBC:For example, you would do the following in an ODBC application:

    • Utilice SQLBindParameter para enlazar una variable de tipo entero al primer marcador de parámetros en una instrucción SQL.Use SQLBindParameter to bind an integer variable to the first parameter marker in an SQL statement.
    • Coloque el valor entero en la variable.Put the integer value in the variable.
    • Ejecute la instrucción y especifique el marcador de parámetros (?):Execute the statement, specifying the parameter marker (?):
    SQLExecDirect(hstmt, 
       "SELECT * 
       FROM AdventureWorks2014.Production.Product 
       WHERE ProductSubcategoryID = ?",
       SQL_NTS);
    

    El proveedor OLE DB de SQL ServerSQL Server Native Client y el controlador ODBC de SQL ServerSQL Server Native Client que se incluyen en SQL ServerSQL Server usan sp_executesql para enviar instrucciones a SQL ServerSQL Server cuando se usan marcadores de parámetros en las aplicaciones.The SQL ServerSQL Server Native Client OLE DB Provider and the SQL ServerSQL Server Native Client ODBC driver included with SQL ServerSQL Server use sp_executesql to send statements to SQL ServerSQL Server when parameter markers are used in applications.

  • Para diseñar procedimientos almacenados, que utilizan parámetros por diseño.To design stored procedures, which use parameters by design.

Si no incorpora explícitamente parámetros en el diseño de las aplicaciones, puede usar el Optimizador de consultas de SQL ServerSQL Server para parametrizar automáticamente determinadas consultas mediante el uso del comportamiento predeterminado de parametrización simple.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. O bien, puede forzar que el Optimizador de consultas tenga en cuenta la parametrización de todas las consultas de la base de datos si establece la opción PARAMETERIZATION de la instrucción ALTER DATABASE en FORCED.Alternatively, you can force the Query Optimizer to consider parameterizing all queries in the database by setting the PARAMETERIZATION option of the ALTER DATABASE statement to FORCED.

Cuando se habilita la parametrización forzada, se puede producir la parametrización simple.When forced parameterization is enabled, simple parameterization can still occur. Por ejemplo, la siguiente consulta no se puede parametrizar según las reglas de parametrización forzada:For example, the following query cannot be parameterized according to the rules of forced parameterization:

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

Sin embargo, se puede parametrizar según las reglas de parametrización simple.However, it can be parameterized according to simple parameterization rules. Cuando la parametrización forzada se intenta pero falla, después se sigue intentando la parametrización simple.When forced parameterization is tried but fails, simple parameterization is still subsequently tried.

Parametrización simpleSimple Parameterization

En SQL ServerSQL Server, el uso de parámetros o marcadores de parámetros en instrucciones Transact-SQL aumenta la posibilidad de que el motor relacional encuentre planes de ejecución existentes y compilados previamente que coincidan con nuevas instrucciones 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.

Advertencia

La utilización de parámetros o marcadores de parámetros para contener valores que especifican los usuarios finales es más segura que la concatenación de valores en una cadena que después se ejecuta mediante un método de la API de acceso de datos, la instrucción EXECUTE o el procedimiento almacenado 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.

Si una instrucción Transact-SQLTransact-SQL se ejecuta sin parámetros, SQL ServerSQL Server parametriza la instrucción internamente para aumentar las posibilidades de hacerla coincidir con un plan de ejecución existente.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. Este proceso se denomina parametrización simple.This process is called simple parameterization. En SQL ServerSQL Server 2000, el proceso se denominaba parametrización automática.In SQL ServerSQL Server 2000, the process was referred to as auto-parameterization.

Considere esta instrucción:Consider this statement:

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

El valor 1 del final de la instrucción puede especificarse como un parámetro.The value 1 at the end of the statement can be specified as a parameter. El motor relacional genera el plan de ejecución para este lote como si se hubiera especificado un parámetro en lugar del valor 1.The relational engine builds the execution plan for this batch as if a parameter had been specified in place of the value 1. Debido a esta parametrización simple, SQL ServerSQL Server reconoce que las dos instrucciones siguientes generan esencialmente el mismo plan de ejecución y reutiliza el primer plan para la segunda instrucción: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;

Al procesar instrucciones Transact-SQLTransact-SQL complejas, el motor relacional puede tener dificultades para determinar qué expresiones pueden parametrizarse.When processing complex Transact-SQLTransact-SQL statements, the relational engine may have difficulty determining which expressions can be parameterized. Para aumentar las posibilidades de que el motor relacional encuentre planes de ejecución existentes no utilizados que coincidan con instrucciones Transact-SQLTransact-SQL complejas, especifique explícitamente los parámetros con sp_executesql o con marcadores de parámetros.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.

Nota

Cuando los operadores aritméticos +, -, *, / o % se usan para realizar la conversión implícita o explícita de los valores de constante int, smallint, tinyint o bigint en los tipos de datos flotantes, reales, decimales o numéricos, SQL ServerSQL Server aplica reglas específicas para calcular el tipo y la precisión de los resultados de expresión.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. Sin embargo, estas reglas varían en función de si la consulta se parametriza o no.However, these rules differ, depending on whether the query is parameterized or not. Por lo tanto, expresiones similares utilizadas en consultas pueden, en ciertos casos, producir resultados diferentes.Therefore, similar expressions in queries can, in some cases, produce differing results.

En el comportamiento predeterminado de parametrización simple, SQL ServerSQL Server parametriza una clase relativamente pequeña de consultas.Under the default behavior of simple parameterization, SQL ServerSQL Server parameterizes a relatively small class of queries. No obstante, se puede especificar que, con algunas limitaciones, todas las consultas de una base de datos se parametricen al establecer la opción PARAMETERIZATION del comando ALTER DATABASE en 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. De este modo, puede que mejore el rendimiento de bases de datos que reciben grandes volúmenes de consultas simultáneas si se reduce la frecuencia de las compilaciones de consultas.Doing so may improve the performance of databases that experience high volumes of concurrent queries by reducing the frequency of query compilations.

O bien, puede especificar que una sola consulta, y cualquier otra que sea sintácticamente equivalente pero solo se diferencie en los valores de parámetros, no incluya parámetros.Alternatively, you can specify that a single query, and any others that are syntactically equivalent but differ only in their parameter values, be parameterized.

Parametrización forzadaForced Parameterization

Puede reemplazar el comportamiento predeterminado de parametrización simple de SQL ServerSQL Server si especifica que, con algunas limitaciones, todas las instrucciones SELECT, INSERT, UPDATE y DELETE de una base de datos incluyan parámetros.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. La parametrización forzada se habilita al establecer la opción PARAMETERIZATION en FORCED en la instrucción ALTER DATABASE .Forced parameterization is enabled by setting the PARAMETERIZATION option to FORCED in the ALTER DATABASE statement. Puede que la parametrización forzada mejore el rendimiento de determinadas bases de datos al reducir la frecuencia de las compilaciones y recompilaciones de consultas.Forced parameterization may improve the performance of certain databases by reducing the frequency of query compilations and recompilations. Las bases de datos que pueden beneficiarse de la parametrización forzada suelen ser las que experimentan grandes volúmenes de consultas simultáneas de orígenes como las aplicaciones de punto de venta.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.

Cuando la opción PARAMETERIZATION está establecida en FORCED, cualquier valor literal que aparezca en una instrucción SELECT, INSERT, UPDATEo DELETE , enviado de cualquier forma, se convierte en un parámetro durante la compilación de consultas.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. Las excepciones son los literales que aparecen en las siguientes construcciones de consulta:The exceptions are literals that appear in the following query constructs:

  • InstruccionesINSERT...EXECUTE .INSERT...EXECUTE statements.
  • Instrucciones incluidas en los cuerpos de procedimientos almacenados, desencadenadores o funciones definidas por el usuario.Statements inside the bodies of stored procedures, triggers, or user-defined functions. SQL ServerSQL Server ya vuelve a usar planes de consultas para estas rutinas.already reuses query plans for these routines.
  • Instrucciones preparadas que ya incluyen parámetros en la aplicación del lado cliente.Prepared statements that have already been parameterized on the client-side application.
  • Instrucciones que contienen llamadas a métodos XQuery, en las que el método aparece en un contexto en el que los argumentos suelen incluir parámetros, como una cláusula 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. Si el método aparece en un contexto en el que los argumentos no incluyen parámetros, el resto de la instrucción incluye parámetros.If the method appears in a context where its arguments would not be parameterized, the rest of the statement is parameterized.
  • Instrucciones incluidas en un cursor de Transact-SQLTransact-SQL.Statements inside a Transact-SQLTransact-SQL cursor. (Las instruccionesSELECT contenidas en cursores de la API incluyen parámetros).(SELECT statements inside API cursors are parameterized.)
  • Construcciones de consulta desaprobadas.Deprecated query constructs.
  • Cualquier instrucción que se ejecuta en el contexto de ANSI_PADDING o ANSI_NULLS establecido en OFF.Any statement that is run in the context of ANSI_PADDING or ANSI_NULLS set to OFF.
  • Instrucciones que contienen más de 2.097 literales aptos para parametrización.Statements that contain more than 2,097 literals that are eligible for parameterization.
  • Instrucciones que hacen referencia a variables, como WHERE T.col2 >= @bb.Statements that reference variables, such as WHERE T.col2 >= @bb.
  • Instrucciones que contienen la sugerencia de consulta RECOMPILE .Statements that contain the RECOMPILE query hint.
  • Instrucciones que contienen una cláusula COMPUTE .Statements that contain a COMPUTE clause.
  • Instrucciones que contienen una cláusula WHERE CURRENT OF .Statements that contain a WHERE CURRENT OF clause.

Las siguientes cláusulas de consulta no incluyen parámetros.Additionally, the following query clauses are not parameterized. Tenga en cuenta que, en estos casos, las cláusulas son las únicas que no incluyen parámetros.Note that in these cases, only the clauses are not parameterized. Otras cláusulas de la misma consulta podrían ser candidatas para la parametrización forzada.Other clauses within the same query may be eligible for forced parameterization.

  • La <lista_de_selección> de cualquier instrucción SELECT.The <select_list> of any SELECT statement. Esto incluye listas SELECT de subconsultas y SELECT listas dentro de instrucciones INSERT.This includes SELECT lists of subqueries and SELECT lists inside INSERT statements.
  • Instrucciones SELECT de subconsulta que aparezcan dentro de una instrucción IF .Subquery SELECT statements that appear inside an IF statement.
  • las cláusulas TOP, TABLESAMPLE, HAVING, GROUP BY, ORDER BY, OUTPUT...INTOo FOR XML de una consulta.The TOP, TABLESAMPLE, HAVING, GROUP BY, ORDER BY, OUTPUT...INTO, or FOR XML clauses of a query.
  • Argumentos, directos o como subexpresiones, a OPENROWSET, OPENQUERY, OPENDATASOURCE, OPENXML, o a cualquier operador FULLTEXT .Arguments, either direct or as subexpressions, to OPENROWSET, OPENQUERY, OPENDATASOURCE, OPENXML, or any FULLTEXT operator.
  • Los argumentos pattern y escape_character de una cláusula LIKE .The pattern and escape_character arguments of a LIKE clause.
  • El argumento de estilo de una cláusula CONVERT .The style argument of a CONVERT clause.
  • Constantes de tipo entero dentro de una cláusula IDENTITY .Integer constants inside an IDENTITY clause.
  • Constantes especificadas mediante la sintaxis de extensiones ODBC.Constants specified by using ODBC extension syntax.
  • Expresiones que admiten el doblado de constantes y son argumentos de los operadores +, -, *, / y %Constant-foldable expressions that are arguments of the +, -, *, /, and % operators. Al considerar la posibilidad de que se pueda elegir la parametrización forzada, SQL ServerSQL Server tiene en cuenta que una expresión admite el doblado de constantes cuando se cumple alguna de las siguientes condiciones: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 aparecen columnas, variables ni subconsultas en la expresión.No columns, variables, or subqueries appear in the expression.
    • La expresión contiene una cláusula CASE .The expression contains a CASE clause.
  • Argumentos para cláusulas de sugerencias de consulta.Arguments to query hint clauses. Estos incluyen el argumento number_of_rows de la sugerencia de consulta FAST , el argumento number_of_processors de la sugerencia de consulta MAXDOP y el argumento de número de la sugerencia de consulta MAXRECURSION .These include the number_of_rows argument of the FAST query hint, the number_of_processors argument of the MAXDOP query hint, and the number argument of the MAXRECURSION query hint.

La parametrización se produce a nivel de instrucciones Transact-SQLTransact-SQL individuales.Parameterization occurs at the level of individual Transact-SQLTransact-SQL statements. En otras palabras, las instrucciones individuales de un lote incluyen parámetros.In other words, individual statements in a batch are parameterized. Tras la compilación, una consulta con parámetros se ejecuta en el contexto del lote en el que se envió originalmente.After compiling, a parameterized query is executed in the context of the batch in which it was originally submitted. Si un plan de ejecución de una consulta se almacena en caché, puede determinar si la consulta incluía parámetros haciendo referencia a la columna sql de la vista de administración dinámica sys.syscacheobjects.If an execution plan for a query is cached, you can determine whether the query was parameterized by referencing the sql column of the sys.syscacheobjects dynamic management view. Si una consulta incluye parámetros, los nombres y tipos de datos de parámetros se anteponen al texto del lote enviado en esta columna, como (@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).

Nota

Los nombres de parámetros son arbitrarios.Parameter names are arbitrary. Los usuarios o las aplicaciones no deben basarse en un determinado orden de nombres.Users or applications should not rely on a particular naming order. Además, puede cambiar lo siguiente entre las versiones de SQL ServerSQL Server y las actualizaciones de Service Pack: los nombres de parámetro, la elección de literales con parámetros y el espaciado en el texto con parámetros.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.

Tipos de datos de parámetrosData Types of Parameters

Cuando SQL ServerSQL Server parametriza literales, los parámetros se convierten a los siguientes tipos de datos:When SQL ServerSQL Server parameterizes literals, the parameters are converted to the following data types:

  • Los literales de tipo entero cuyo tamaño cabría de otro modo dentro del tipo de datos int se parametrizan a int. Los literales de tipo entero de mayor tamaño que forman parte de predicados que incluyen algún operador de comparación (incluidos <, <=, =, !=, >, >=, , !<, !>, <>, ALL, ANY, SOME, BETWEEN y IN) se parametrizan a 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). Los literales de mayor tamaño que no forman parte de predicados que incluyen operadores de comparación se parametrizan a numéricos cuya precisión sea lo suficientemente grande como para admitir su tamaño y cuya escala sea igual a 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.
  • Los literales numéricos de punto fijo que forman parte de predicados que incluyen operadores de comparación se parametrizan a numéricos, con una precisión de 38 y una escala lo suficientemente grande como para admitir su tamaño.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. Los literales numéricos de punto fijo que no forman parte de predicados que incluyen operadores de comparación se parametrizan a numéricos, con una precisión y escala lo suficientemente grandes como para admitir su tamaño.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.
  • Los literales numéricos de punto flotante se parametrizan a float(53).Floating point numeric literals parameterize to float(53).
  • Los literales de cadena no Unicode se parametrizan a varchar(8000) si el literal se ajusta a 8000 caracteres y a varchar(max) si tiene un número superior a este.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.
  • Los literales de cadena Unicode se parametrizan a nvarchar(4000) si el literal se ajusta a 4000 caracteres Unicode y a nvarchar(max) si tiene un número superior a este.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.
  • Los literales de tipo binario se parametrizan a varbinary(8000) si el literal se ajusta a 8000 bytes.Binary literals parameterize to varbinary(8000) if the literal fits within 8,000 bytes. Si tiene más de 8000 bytes, se convierte a varbinary(max).If it is larger than 8,000 bytes, it is converted to varbinary(max).
  • Los literales de tipo money se parametrizan a money.Money type literals parameterize to money.

Directrices para utilizar la parametrización forzadaGuidelines for Using Forced Parameterization

Tenga en cuenta lo siguiente al establecer la opción PARAMETERIZATION en FORCED:Consider the following when you set the PARAMETERIZATION option to FORCED:

  • La parametrización forzada, en efecto, cambia las constantes literales de una consulta a parámetros al compilar una consulta.Forced parameterization, in effect, changes the literal constants in a query to parameters when compiling a query. Por tanto, puede que el optimizador de consultas elija planes menos adecuados para las consultas.Therefore, the Query Optimizer might choose suboptimal plans for queries. En concreto, es menos probable que el optimizador de consultas haga coincidir la consulta con una vista indizada o un índice de una columna calculada.In particular, the Query Optimizer is less likely to match the query to an indexed view or an index on a computed column. Puede que también elija planes menos adecuados para consultas formuladas en tablas con particiones y vistas con particiones distribuidas.It may also choose suboptimal plans for queries posed on partitioned tables and distributed partitioned views. No se debe utilizar la parametrización forzada en entornos que se basan en su mayor parte en vistas indizadas e índices en columnas calculadas.Forced parameterization should not be used for environments that rely heavily on indexed views and indexes on computed columns. Por lo general, solo los administradores de bases de datos con experiencia deben utilizar la opción PARAMETERIZATION FORCED después de determinar que con ello no se afecta negativamente al rendimiento.Generally, the PARAMETERIZATION FORCED option should only be used by experienced database administrators after determining that doing this does not adversely affect performance.
  • Las consultas distribuidas que hacen referencia a más de una base de datos se pueden elegir para la parametrización forzada siempre que la opción PARAMETERIZATION se establezca en FORCED en la base de datos en cuyo contexto se ejecuta la consulta.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.
  • Al establecer la opción PARAMETERIZATION en FORCED se vacían todos los planes de consulta de la caché de planes de una base de datos, excepto aquéllos que se estén compilando, recompilando o ejecutando en ese momento.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. Los planes de consulta que se compilen o ejecuten durante el cambio de opción incluyen parámetros la próxima vez que se ejecute la consulta.Plans for queries that are compiling or running during the setting change are parameterized the next time the query is executed.
  • El establecimiento de la opción PARAMETERIZATION es una operación en línea que requiere bloqueos no exclusivos de la base de datos.Setting the PARAMETERIZATION option is an online operation that it requires no database-level exclusive locks.
  • El valor actual de la opción PARAMETERIZATION se mantiene al volver a adjuntar o restaurar una base de datos.The current setting of the PARAMETERIZATION option is preserved when reattaching or restoring a database.

Puede reemplazar el comportamiento de parametrización forzada si especifica que se trate de realizar la parametrización simple en una sola consulta, y en cualquier otra que sea sintácticamente equivalente pero solo se diferencie en los valores de parámetro.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. Por el contrario, puede especificar que se trate de forzar la parametrización solo en un conjunto de consultas sintácticamente equivalentes, aunque la parametrización forzada esté deshabilitada en la base de datos.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. Se utilizanguías de planes con este fin.Plan guides are used for this purpose.

Nota

Cuando la opción PARAMETERIZATION está establecida en FORCED, la notificación de mensajes de error podría ser distinta a cuando la opción PARAMETERIZATION se establece en SIMPLE: podrían notificarse varios mensajes de error con una parametrización forzada, en la que se notificarían menos mensajes con la parametrización simple, y los números de línea en los que ocurren los errores podrían indicarse incorrectamente.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.

Preparar instrucciones SQLPreparing SQL Statements

El motor relacional de SQL ServerSQL Server proporciona compatibilidad completa para preparar las instrucciones Transact-SQLTransact-SQL antes de que se ejecuten.The SQL ServerSQL Server relational engine introduces full support for preparing Transact-SQLTransact-SQL statements before they are executed. Si una aplicación necesita ejecutar una instrucción Transact-SQLTransact-SQL varias veces, puede utilizar la API de bases de datos para lo siguiente:If an application has to execute an Transact-SQLTransact-SQL statement several times, it can use the database API to do the following:

  • Preparar la instrucción una vez.Prepare the statement once. Esto compila la instrucción Transact-SQLTransact-SQL en un plan de ejecución.This compiles the Transact-SQLTransact-SQL statement into an execution plan.
  • Ejecutar el plan de ejecución compilado previamente cada vez que necesite ejecutar la instrucción.Execute the precompiled execution plan every time it has to execute the statement. Esto evita tener que volver a compilar la instrucción Transact-SQLTransact-SQL después de la primera ejecución.This prevents having to recompile the Transact-SQLTransact-SQL statement on each execution after the first time.
    Las funciones y los métodos de la API controlan la preparación y la ejecución de las instrucciones.Preparing and executing statements is controlled by API functions and methods. No forma parte del lenguaje Transact-SQLTransact-SQL.It is not part of the Transact-SQLTransact-SQL language. El modelo de preparación y ejecución para ejecutar instrucciones Transact-SQLTransact-SQL es compatible con el proveedor OLE DB de Native Client de SQL ServerSQL Server y con el controlador ODBC de Native Client de SQL ServerSQL Server.The prepare/execute model of executing Transact-SQLTransact-SQL statements is supported by the SQL ServerSQL Server Native Client OLE DB Provider and the SQL ServerSQL Server Native Client ODBC driver. En una solicitud de preparación, el proveedor o el controlador envían la instrucción a SQL ServerSQL Server con una solicitud para preparar la instrucción.On a prepare request, either the provider or the driver sends the statement to SQL ServerSQL Server with a request to prepare the statement. SQL ServerSQL Server compila un plan de ejecución y devuelve un identificador para ese plan al proveedor o al controlador.compiles an execution plan and returns a handle for that plan to the provider or driver. En una solicitud de ejecución, el proveedor o el controlador envían al servidor una solicitud para ejecutar el plan asociado al identificador.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.

Las instrucciones preparadas no se pueden utilizar para crear objetos temporales en SQL ServerSQL Server.Prepared statements cannot be used to create temporary objects on SQL ServerSQL Server. Las instrucciones preparadas no pueden hacer referencia a procedimientos almacenados del sistema que creen objetos temporales, como tablas temporales.Prepared statements cannot reference system stored procedures that create temporary objects, such as temporary tables. Estos procedimientos deben ejecutarse directamente.These procedures must be executed directly.

El uso excesivo del modelo de preparación y ejecución puede reducir el rendimiento.Excess use of the prepare/execute model can degrade performance. Si una instrucción solo se ejecuta una vez, una ejecución directa solo requiere un recorrido de ida y vuelta por la red al servidor.If a statement is executed only once, a direct execution requires only one network round-trip to the server. El hecho de preparar y ejecutar una instrucción Transact-SQLTransact-SQL que solo se ejecuta una vez requiere un recorrido de ida y vuelta adicional al servidor: uno para preparar la instrucción y otro para ejecutarla.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.

Preparar una instrucción es más eficaz si se utilizan marcadores de parámetros.Preparing a statement is more effective if parameter markers are used. Por ejemplo, suponga que se solicita ocasionalmente a una aplicación que recupere información de productos de la base de datos de ejemplo AdventureWorks .For example, assume that an application is occasionally asked to retrieve product information from the AdventureWorks sample database. Hay dos maneras en que la aplicación puede llevarlo cabo.There are two ways the application can do this.

En la primera, la aplicación puede ejecutar una consulta independiente para cada producto que se solicita:Using the first way, the application can execute a separate query for each product requested:

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

En la segunda, la aplicación hace lo siguiente:Using the second way, the application does the following:

  1. Prepara una instrucción que contiene un marcador de parámetros (?):Prepares a statement that contains a parameter marker (?):
    SELECT * FROM AdventureWorks2014.Production.Product  
    WHERE ProductID = ?;
    
  2. Enlaza una variable de programa al marcador de parámetros.Binds a program variable to the parameter marker.
  3. Cada vez que se necesite información de productos, llena la variable enlazada con el valor de clave y ejecuta la instrucción.Each time product information is needed, fills the bound variable with the key value and executes the statement.

La segunda forma es más eficaz cuando la instrucción se ejecuta más de tres veces.The second way is more efficient when the statement is executed more than three times.

En SQL ServerSQL Server, el modelo de preparación y ejecución no tiene ninguna ventaja significativa sobre el rendimiento de la ejecución directa, debido a la manera en que SQL ServerSQL Server reutiliza los planes de ejecución.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 dispone de algoritmos eficientes para hacer corresponder las instrucciones Transact-SQLTransact-SQL actuales con los planes de ejecución que se generan para ejecuciones anteriores de la misma instrucción 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. Si una aplicación ejecuta varias veces una instrucción Transact-SQLTransact-SQL con marcadores de parámetros, SQL ServerSQL Server volverá a usar el plan de ejecución de la primera ejecución para la segunda ejecución, así como para las siguientes (a menos que el plan quede anticuado en la caché de planes).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). El modelo de preparación y ejecución sigue teniendo estas ventajas:The prepare/execute model still has these benefits:

  • Buscar un plan de ejecución mediante un identificador es más eficaz que los algoritmos que se utilizan para encontrar planes de ejecución existentes que coincidan con una instrucción 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.
  • La aplicación puede controlar cuándo se crea el plan de ejecución y cuándo se vuelve a utilizar.The application can control when the execution plan is created and when it is reused.
  • El modelo de preparación y ejecución se puede transportar a otras bases de datos, incluidas las versiones anteriores de SQL ServerSQL Server.The prepare/execute model is portable to other databases, including earlier versions of SQL ServerSQL Server.

Examen de parámetrosParameter Sniffing

Con la expresión "examen de parámetros" se hace referencia a un proceso mediante el cual SQL ServerSQL Server "examina" los valores de parámetros actuales durante la compilación o la recompilación y los pasa al Optimizador de consultas para que se puedan usar para generar planes de ejecución de consultas potencialmente más eficaces."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.

Los valores de parámetros se examinan durante la compilación o la recompilación de los siguientes tipos de lotes:Parameter values are sniffed during compilation or recompilation for the following types of batches:

  • Procedimientos almacenadosStored procedures
  • Consultas enviadas mediante sp_executesqlQueries submitted via sp_executesql
  • Consultas preparadasPrepared queries

Para obtener más información sobre cómo solucionar problemas de exámenes de parámetros incorrectos, vea Troubleshoot queries with parameter-sensitive query execution plan issues (Solución de problemas de consultas con un plan de ejecución de consultas sensible a parámetros).For more information on troubleshooting bad parameter sniffing issues, see Troubleshoot queries with parameter-sensitive query execution plan issues.

Nota

En el caso de las consultas que usan la sugerencia RECOMPILE, se examinan tanto los valores de parámetro como los valores actuales de las variables locales.For queries using the RECOMPILE hint, both parameter values and current values of local variables are sniffed. Los valores examinados (de los parámetros y las variables locales) son los que existen en el lote justo antes de la instrucción con la sugerencia 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. En concreto, en el caso de los parámetros, no se examinan los valores que aparecieron con la llamada de invocación del lote.In particular, for parameters, the values that came along with the batch invocation call are not sniffed.

Procesar una consulta en paraleloParallel Query Processing

SQL ServerSQL Server proporciona consultas en paralelo para optimizar la ejecución de consultas y las operaciones con índices en equipos que disponen de más de un microprocesador (CPU).provides parallel queries to optimize query execution and index operations for computers that have more than one microprocessor (CPU). Debido a que SQL ServerSQL Server puede realizar una operación de consulta o índice en paralelo mediante varios subprocesos de trabajo del sistema operativo, la operación se puede completar de forma rápida y eficaz.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.

Durante la optimización de una consulta, SQL ServerSQL Server busca operaciones de consulta o índice que podrían beneficiarse de la ejecución en paralelo.During query optimization, SQL ServerSQL Server looks for queries or index operations that might benefit from parallel execution. Para estas consultas, SQL ServerSQL Server inserta operadores de intercambio en el plan de ejecución de consultas para preparar su ejecución en paralelo.For these queries, SQL ServerSQL Server inserts exchange operators into the query execution plan to prepare the query for parallel execution. Un operador de intercambio es un operador de un plan de ejecución de consultas que proporciona administración de procesos, redistribución de datos y control del flujo.An exchange operator is an operator in a query execution plan that provides process management, data redistribution, and flow control. El operador de intercambio incluye los operadores lógicos Distribute Streams, Repartition Streamsy Gather Streams como subtipos; uno o varios de estos operadores pueden aparecer en la salida del Plan de presentación de un plan de consulta para una consulta en paralelo.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.

Importante

Determinadas construcciones impiden la capacidad de SQL ServerSQL Server de aprovechar el paralelismo en el plan de ejecución completo o en partes de este.Certain constructs inhibit SQL ServerSQL Server's ability to leverage parallelism on the entire execution plan, or parts or the execution plan.

Entre las construcciones que impiden el paralelismo se incluyen las siguientes:Constructs that inhibit parallelism include:

Tras la inserción de operadores de intercambio, el resultado es un plan de ejecución de consultas en paralelo.After exchange operators are inserted, the result is a parallel-query execution plan. Un plan de ejecución de consultas en paralelo puede usar más de un subproceso de trabajo.A parallel-query execution plan can use more than one worker thread. Un plan de ejecución en serie, usado por una consulta no paralela, solo usa un subproceso de trabajo para su ejecución.A serial execution plan, used by a nonparallel query, uses only one worker thread for its execution. El número real de subprocesos de trabajo que usa una consulta en paralelo se determina en la inicialización de la ejecución del plan de consulta y viene determinado por la complejidad del plan y el grado de paralelismo.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. El grado de paralelismo determina el número máximo de CPU que se están usando; no significa el número de subprocesos de trabajo que se están usando.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. El valor del grado de paralelismo se establece en el servidor y se puede modificar mediante el procedimiento almacenado del sistema 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. Puede reemplazar este valor para instrucciones individuales de consulta o índice especificando la sugerencia de consulta MAXDOP o la opción de índice MAXDOP .You can override this value for individual query or index statements by specifying the MAXDOP query hint or MAXDOP index option.

El Optimizador de consultas de SQL ServerSQL Server no usa un plan de ejecución en paralelo para una consulta si se cumple alguna de las siguientes condiciones: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:

  • El costo de la ejecución en serie de la consulta no es lo suficientemente alto como para considerar un plan de ejecución en paralelo alternativo.The serial execution cost of the query is not high enough to consider an alternative, parallel execution plan.
  • El plan de ejecución en serie se considera más rápido que cualquier otro plan de ejecución en paralelo de una consulta determinada.A serial execution plan is considered faster than any possible parallel execution plan for the particular query.
  • La consulta contiene operadores escalares o relacionales que no se pueden ejecutar en paralelo.The query contains scalar or relational operators that cannot be run in parallel. Es posible que algunos operadores hagan que una sección del plan de consulta se ejecute en modo de serie, o que todo el plan se ejecute en modo de serie.Certain operators can cause a section of the query plan to run in serial mode, or the whole plan to run in serial mode.

Grado de paralelismoDegree of Parallelism

SQL ServerSQL Server detecta de forma automática el mejor grado de paralelismo para cada instancia de una ejecución de consulta en paralelo o de una operación de índice del lenguaje de definición de datos (DDL).automatically detects the best degree of parallelism for each instance of a parallel query execution or index data definition language (DDL) operation. Para ello utiliza los siguientes criterios:It does this based on the following criteria:

  1. Si SQL ServerSQL Server se ejecuta en un equipo que disponga de más de un microprocesador o CPU, por ejemplo, un equipo de multiproceso simétrico (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).
    Solo los equipos con más de una CPU pueden utilizar consultas en paralelo.Only computers that have more than one CPU can use parallel queries.

  2. Si hay suficientes subprocesos de trabajo disponibles.Whether sufficient worker threads are available.
    Cada operación de consulta o índice requiere que se ejecute un determinado número de subprocesos de trabajo.Each query or index operation requires a certain number of worker threads to execute. La ejecución de un plan en paralelo requiere más subprocesos de trabajo que un plan en serie, y el número de subprocesos de trabajo aumenta con el grado de paralelismo.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. Si no es posible cumplir el requisito de subprocesos de trabajo del plan en paralelo para un grado de paralelismo específico, el Motor de base de datos de SQL ServerSQL Server Database Engine reduce automáticamente el grado de paralelismo o abandona por completo el plan en paralelo en el contexto de carga de trabajo especificado.When the worker thread requirement of the parallel plan for a specific degree of parallelism cannot be satisfied, the Motor de base de datos de SQL ServerSQL Server Database Engine decreases the degree of parallelism automatically or completely abandons the parallel plan in the specified workload context. Es entonces cuando ejecuta un plan en serie (un subproceso de trabajo).It then executes the serial plan (one worker thread).

  3. El tipo de operación de consulta o índice ejecutado.The type of query or index operation executed.
    Las operaciones de índice que crean o vuelven a crear un índice, o que eliminan un índice clúster o las consultas que utilizan constantemente los ciclos de la CPU, son los candidatos idóneos para un plan de paralelismo.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. Por ejemplo, las combinaciones de tablas grandes, agregaciones importantes y la ordenación de grandes conjuntos de resultados son buenos candidatos.For example, joins of large tables, large aggregations, and sorting of large result sets are good candidates. En las consultas simples, que suelen encontrarse en aplicaciones de procesamiento de transacciones, la coordinación adicional necesaria para ejecutar una consulta en paralelo es más importante que el aumento potencial del rendimiento.Simple queries, frequently found in transaction processing applications, find the additional coordination required to execute a query in parallel outweigh the potential performance boost. Para distinguir entre las consultas que se benefician del paralelismo y las que no, Motor de base de datos de SQL ServerSQL Server Database Engine compara el costo estimado de ejecutar la operación de consulta o índice con el valor Umbral de costo para paralelismo.To distinguish between queries that benefit from parallelism and those that do not benefit, the Motor de base de datos de SQL ServerSQL Server Database Engine compares the estimated cost of executing the query or index operation with the cost threshold for parallelism value. Los usuarios pueden cambiar el valor predeterminado de 5 mediante sp_configure si unas pruebas correctas han detectado que otro valor es más adecuado para la carga de trabajo en ejecución.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. Si hay un número suficiente de filas para procesar.Whether there are a sufficient number of rows to process.
    Si el optimizador de consultas determina que el número de filas es demasiado bajo, no proporciona operadores de intercambio para distribuir las filas.If the Query Optimizer determines that the number of rows is too low, it does not introduce exchange operators to distribute the rows. En consecuencia, los operadores se ejecutan en serie.Consequently, the operators are executed serially. Ejecutar los operadores en un plan en serie evita los escenarios en que el costo del inicio, distribución y coordinación excede las ganancias logradas mediante la ejecución del operador en paralelo.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. Si las estadísticas de distribución actuales están disponibles.Whether current distribution statistics are available.
    Si no es posible establecer el grado de paralelismo más alto, se tienen en cuenta los grados inferiores antes de abandonar el plan en paralelo.If the highest degree of parallelism is not possible, lower degrees are considered before the parallel plan is abandoned.
    Por ejemplo, cuando sea crea un índice clúster en una vista, las estadísticas de distribución no se pueden evaluar porque el índice clúster aún no existe.For example, when you create a clustered index on a view, distribution statistics cannot be evaluated, because the clustered index does not yet exist. En este caso, el Motor de base de datos de SQL ServerSQL Server Database Engine no puede proporcionar el grado de paralelismo más alto para la operación de índice.In this case, the Motor de base de datos de SQL ServerSQL Server Database Engine cannot provide the highest degree of parallelism for the index operation. Sin embargo, algunos operadores, como sorting y scannig, se siguen beneficiando de la ejecución en paralelo.However, some operators, such as sorting and scanning, can still benefit from parallel execution.

Nota

Las operaciones de índices en paralelo están únicamente disponibles en las ediciones Enterprise, Developer y Evaluation de SQL ServerSQL Server.Parallel index operations are only available in SQL ServerSQL Server Enterprise, Developer, and Evaluation editions.

En tiempo de ejecución, el Motor de base de datos de SQL ServerSQL Server Database Engine determina si la carga de trabajo actual del sistema y la información de configuración descrita previamente permiten la ejecución en paralelo.At execution time, the Motor de base de datos de SQL ServerSQL Server Database Engine determines whether the current system workload and configuration information previously described allow for parallel execution. Si se garantiza la ejecución en paralelo, el Motor de base de datos de SQL ServerSQL Server Database Engine determina el número óptimo de subprocesos de trabajo y propaga la ejecución del plan en paralelo a dichos subprocesos.If parallel execution is warranted, the Motor de base de datos de SQL ServerSQL Server Database Engine determines the optimal number of worker threads and spreads the execution of the parallel plan across those worker threads. Cuando una operación de consulta o índice empieza a ejecutarse en varios subprocesos de trabajo para la ejecución en paralelo, se usa el mismo número de subprocesos hasta que la operación se completa.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. El Motor de base de datos de SQL ServerSQL Server Database Engine vuelve a examinar el número óptimo de decisiones de subprocesos de trabajo cada vez que se recupera un plan de ejecución de la caché de planes.The Motor de base de datos de SQL ServerSQL Server Database Engine re-examines the optimal number of worker thread decisions every time an execution plan is retrieved from the plan cache. Por ejemplo, la ejecución de una consulta puede conllevar el uso de un plan en serie, una ejecución posterior de la misma consulta puede conllevar que un plan en paralelo use tres subprocesos de trabajo y una tercera ejecución puede conllevar que un plan en paralelo use cuatro subprocesos de trabajo.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.

En un plan de ejecución de consultas en paralelo, los operadores insert, update y delete se ejecutan en serie.In a parallel query execution plan, the insert, update, and delete operators are executed serially. Sin embargo, la cláusula WHERE de una instrucción UPDATE o DELETE, o la parte SELECT de una instrucción INSERT pueden ejecutarse en paralelo.However, the WHERE clause of an UPDATE or a DELETE statement, or the SELECT part of an INSERT statement may be executed in parallel. Los cambios reales de los datos se aplican en serie a la base de datos.The actual data changes are then serially applied to the database.

Los cursores estáticos y los dinámicos pueden llenarse mediante planes de ejecución en paralelo.Static and keyset-driven cursors can be populated by parallel execution plans. Sin embargo, el comportamiento de los cursores dinámicos solo puede proporcionarse mediante la ejecución en serie.However, the behavior of dynamic cursors can be provided only by serial execution. El optimizador de consultas siempre genera un plan de ejecución en serie para una consulta que es parte de un cursor dinámico.The Query Optimizer always generates a serial execution plan for a query that is part of a dynamic cursor.

Anular grados de paralelismoOverriding Degrees of Parallelism

Puede usar la opción de configuración del servidor grado máximo de paralelismo (MAXDOP) (ALTER DATABASE SCOPED CONFIGURATION en SQL DatabaseSQL Database) para limitar el número de procesadores que se usarán en la ejecución del plan en paralelo.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. La opción "grado máximo de paralelismo" puede anularse para las instrucciones de operaciones de consultas e índices individuales especificando la sugerencia de consulta MAXDOP o la opción de índice 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 proporciona un mayor control sobre las operaciones de consultas e índices individuales.MAXDOP provides more control over individual queries and index operations. Por ejemplo, puede usar la opción MAXDOP para controlar, mediante el aumento o la reducción, el número de procesadores dedicados en una operación de índices en línea.For example, you can use the MAXDOP option to control, by increasing or reducing, the number of processors dedicated to an online index operation. De este modo, es posible equilibrar los recursos utilizados por una operación con los de los usuarios simultáneos.In this way, you can balance the resources used by an index operation with those of the concurrent users.

Al establecer la opción de grado máximo de paralelismo en 0 (valor predeterminado), SQL ServerSQL Server puede usar todos los procesadores disponibles, hasta un máximo de 64, en una ejecución de planes paralelos.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. Aunque SQL ServerSQL Server establece un destino en tiempo de ejecución de 64 procesadores lógicos cuando la opción MAXDOP se establece en 0, si es necesario se puede configurar manualmente un valor diferente.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. Si se establece MAXDOP en 0 para consultas o índices, SQL ServerSQL Server puede usar todos los procesadores disponibles, hasta un máximo de 64, para las consultas o los índices especificados en una ejecución de planes paralelos.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 no es un valor forzado para todas las consultas en paralelo, sino un destino provisional para todas las consultas aptas para el paralelismo.MAXDOP is not an enforced value for all parallel queries, but rather a tentative target for all queries eligible for parallelism. Esto significa que, si no hay suficientes subprocesos de trabajo disponibles en tiempo de ejecución, se puede ejecutar una consulta con un grado de paralelismo inferior al de la opción MAXDOP de configuración del servidor.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.

Vea este artículo de Soporte técnico de Microsoft para obtener información sobre los procedimientos recomendados para la configuración de MAXDOP.Refer to this Microsoft Support Article for best practices on configuring MAXDOP.

Ejemplo de consulta en paraleloParallel Query Example

La consulta siguiente cuenta el número de pedidos realizados en un determinado trimestre, a partir del 1 de abril de 2000, y en los cuales el cliente recibió al menos un elemento de la línea del pedido después de la fecha de confirmación.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. Esta consulta muestra el número de dichos pedidos agrupados por orden de prioridad y de manera ascendente.This query lists the count of such orders grouped by each order priority and sorted in ascending priority order.

Este ejemplo utiliza una tabla y nombres de columnas teóricos.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

Se supone que los índices siguientes están definidos en las tablas lineitem y orders:Assume the following indexes are defined on the lineitem and orders tables:

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

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

Éste es un posible plan en paralelo generado para la consulta mostrada anteriormente: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)

En la ilustración siguiente se muestra un plan de consultas ejecutado con un grado de paralelismo igual a 4 y que implica una combinación de dos tablas.The illustration below shows a query plan executed with a degree of parallelism equal to 4 and involving a two-table join.

parallel_plan

El plan en paralelo contiene tres operadores Parallelism.The parallel plan contains three parallelism operators. Tanto el operador Index Seek del índice o_datkey_ptr como el operador Index Scan del índice l_order_dates_idx se realizan en paralelo.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. Esto produce varios flujos exclusivos.This produces several exclusive streams. Esto puede determinarse a partir de los operadores Parallelism más cercanos que están encima de los operadores Index Scan e Index Seek, respectivamente.This can be determined from the nearest Parallelism operators above the Index Scan and Index Seek operators, respectively. Ambos reparten el tipo de intercambio.Both are repartitioning the type of exchange. Es decir, solo reconstruyen los datos de los flujos que producen el mismo número de flujos en la salida que en la entrada.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. Este número de flujos equivale al grado de paralelismo.This number of streams is equal to the degree of parallelism.

El operador Parallelism situado encima del operador Index Scan l_order_dates_idx reparte sus flujos de entrada con el valor de L_ORDERKEY como clave.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. De este modo, los mismos valores de L_ORDERKEY finalizan en el mismo flujo de salida.In this way, the same values of L_ORDERKEY end up in the same output stream. Al mismo tiempo, los flujos de salida conservan el orden de la columna L_ORDERKEY para cumplir los requisitos de entrada del operador 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.

El operador Parallelism situado encima del operador Index Seek reparte sus flujos de entrada con el valor de O_ORDERKEY.The parallelism operator above the Index Seek operator is repartitioning its input streams using the value of O_ORDERKEY. Como su entrada no está ordenada en los valores de la columna O_ORDERKEY y esta es la columna de combinación del operador Merge Join, el operador Sort que está entre los operadores Parallelism y Merge Join asegura que la entrada esté ordenada para el operador Merge Join en las columnas de combinación.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. El operador Sort, al igual que el operador Merge Join, se ejecuta en paralelo.The Sort operator, like the Merge Join operator, is performed in parallel.

El primer operador Parallelism recopila los resultados de varios flujos en un solo flujo.The topmost parallelism operator gathers results from several streams into a single stream. Las agregaciones parciales que realiza el operador Stream Aggregate que está situado debajo del operador Parallelism se acumulan en un solo valor SUM para cada valor diferente de O_ORDERPRIORITY en el operador Stream Aggregate situado encima del operador Parallelism.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. Como este plan tiene dos segmentos de intercambio y un grado de paralelismo igual a 4, usa ocho subprocesos de trabajo.Because this plan has two exchange segments, with degree of parallelism equal to 4, it uses eight worker threads.

Para más información sobre los operadores usados en este ejemplo, vea Referencia de operadores lógicos y físicos del plan de presentación.For more information on the operators used in this example, refer to the Showplan Logical and Physical Operators Reference.

Operaciones de índice en paraleloParallel Index Operations

Los planes de consulta generados para las operaciones de índice que crean o regeneran un índice o quitan un índice agrupado permiten las operaciones en paralelo de varios subprocesos de trabajo en equipos con varios microprocesadores.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.

Nota

Las operaciones de índice en paralelo solo están disponibles en Enterprise Edition a partir de SQL Server 2008SQL Server 2008.Parallel index operations are only available in Enterprise Edition, starting with SQL Server 2008SQL Server 2008.

SQL ServerSQL Server usa los mismos algoritmos para determinar el grado de paralelismo (el número total de subprocesos de trabajo diferentes que se ejecutan) en las operaciones de índice que en otras consultas.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. El grado máximo de paralelismo en una operación de índice está sujeto a la opción de configuración del servidor grado máximo de paralelismo .The maximum degree of parallelism for an index operation is subject to the max degree of parallelism server configuration option. Puede reemplazar el valor de "grado máximo de paralelismo" en operaciones individuales de índice si establece la opción de índice MAXDOP en las instrucciones CREATE INDEX, ALTER INDEX, DROP INDEX y ALTER TABLE.You can override the max degree of parallelism value for individual index operations by setting the MAXDOP index option in the CREATE INDEX, ALTER INDEX, DROP INDEX, and ALTER TABLE statements.

Cuando el Motor de base de datos de SQL ServerSQL Server Database Engine genera un plan de ejecución de índice, el número de operaciones en paralelo se establece en el valor más bajo de los valores siguientes:When the Motor de base de datos de SQL ServerSQL Server Database Engine builds an index execution plan, the number of parallel operations is set to the lowest value from among the following:

  • El número de microprocesadores o CPU en el equipo.The number of microprocessors, or CPUs in the computer.
  • El número especificado en la opción de configuración del servidor "grado máximo de paralelismo".The number specified in the max degree of parallelism server configuration option.
  • El número de CPU que aún no han superado el umbral de trabajo realizado para subprocesos de trabajo de SQL ServerSQL Server.The number of CPUs not already over a threshold of work performed for SQL ServerSQL Server worker threads.

Por ejemplo, en un equipo con ocho CPU, donde el grado máximo de paralelismo se ha definido en 6, no se generan más de seis subprocesos de trabajo en paralelo para una operación de índice.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. Si cinco de las CPU del equipo han excedido el umbral de trabajo de SQL ServerSQL Server cuando se crea un plan de ejecución de índice, el plan de ejecución especificará únicamente tres subprocesos de trabajo en paralelo.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.

Entre las fases principales de una operación de índice en paralelo se incluyen lo siguiente:The main phases of a parallel index operation include the following:

  • Un subproceso de trabajo de coordinación recorre la tabla rápida y aleatoriamente para calcular la distribución de las claves de índice.A coordinating worker thread quickly and randomly scans the table to estimate the distribution of the index keys. El subproceso de trabajo de coordinación establece los límites de clave que crearán un número de rangos con clave igual al grado de operaciones en paralelo, donde cada rango con clave se estima para cubrir números similares de filas.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. Por ejemplo, si hay cuatro millones de filas en la tabla y el grado de paralelismo es 4, el subproceso de trabajo de coordinación determinará los valores de clave que delimitan cuatro conjuntos de filas con un millón de filas en cada uno.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. Si no se pueden establecer suficientes intervalos de clave para utilizar todas las CPU, el grado de paralelismo se reduce en consecuencia.If enough key ranges cannot be established to use all CPUs, the degree of parallelism is reduced accordingly.
  • El subproceso de trabajo de coordinación genera un número de subprocesos de trabajo igual al grado de operaciones en paralelo y espera hasta que estos subprocesos de trabajo finalicen su trabajo.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. Cada subproceso de trabajo recorre la tabla base mediante un filtro que recupera solo filas con valores de clave en el rango asignado al subproceso de trabajo.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. Cada subproceso de trabajo crea una estructura de índice para las filas en su rango con clave.Each worker thread builds an index structure for the rows in its key range. En el caso de un índice con particiones, cada subproceso de trabajo genera un número especificado de particiones.In the case of a partitioned index, each worker thread builds a specified number of partitions. Las particiones no se comparten entre los subprocesos de trabajo.Partitions are not shared among worker threads.
  • Una vez que han terminado los subprocesos de trabajo en paralelo, el subproceso de trabajo de coordinación conecta las subunidades del índice con un único índice.After all the parallel worker threads have completed, the coordinating worker thread connects the index subunits into a single index. Esta fase solo se aplica a operaciones de índice sin conexión.This phase applies only to offline index operations.

Las instrucciones individuales CREATE TABLE o ALTER TABLE pueden tener varias restricciones que requieren la creación de un índice.Individual CREATE TABLE or ALTER TABLE statements can have multiple constraints that require that an index be created. Estas operaciones de creación de índice se llevan a cabo en serie, aunque cada operación de creación de índice puede ser una operación en paralelo en un equipo con varias 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.

Arquitectura de consultas distribuidasDistributed Query Architecture

Microsoft SQL ServerSQL Server admite dos métodos para referenciar orígenes de datos OLE DB heterogéneos en instrucciones de Transact-SQLTransact-SQL:Microsoft SQL ServerSQL Server supports two methods for referencing heterogeneous OLE DB data sources in Transact-SQLTransact-SQL statements:

  • Nombres de servidores vinculadosLinked server names
    Los procedimientos almacenados del sistema sp_addlinkedserver y sp_addlinkedsrvlogin se utilizan para dar un nombre de servidor a un origen de datos OLE DB.The system stored procedures sp_addlinkedserver and sp_addlinkedsrvlogin are used to give a server name to an OLE DB data source. Se puede hacer referencia a los objetos de estos servidores vinculados en instrucciones Transact-SQLTransact-SQL mediante nombres que consten de cuatro elementos.Objects in these linked servers can be referenced in Transact-SQLTransact-SQL statements using four-part names. Por ejemplo, si un nombre de servidor vinculado de DeptSQLSrvr se define en otra instancia de SQL ServerSQL Server, la instrucción siguiente hace referencia a una tabla de ese servidor: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;
    

    El nombre del servidor vinculado también puede especificarse en una instrucción OPENQUERY para abrir un conjunto de filas desde un origen de datos OLE DB.The linked server name can also be specified in an OPENQUERY statement to open a rowset from the OLE DB data source. Se puede hacer referencia a este conjunto de filas del mismo modo que a una tabla en las instrucciones Transact-SQLTransact-SQL.This rowset can then be referenced like a table in Transact-SQLTransact-SQL statements.

  • Nombres de conectores ad hocAd hoc connector names
    Para las referencias poco frecuentes a un origen de datos, las funciones OPENROWSET o OPENDATASOURCE se especifican con la información necesaria para conectarse a un servidor vinculado.For infrequent references to a data source, the OPENROWSET or OPENDATASOURCE functions are specified with the information needed to connect to the linked server. Después, se puede hacer referencia al conjunto de filas del mismo modo que se hace referencia a una tabla en instrucciones 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 utiliza OLE DB para la comunicación entre el motor relacional y el motor de almacenamiento.uses OLE DB to communicate between the relational engine and the storage engine. El motor relacional divide cada instrucción Transact-SQLTransact-SQL en un grupo de operaciones sobre conjuntos de filas OLE DB simples abiertos por el motor de almacenamiento desde las tablas base.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. Esto significa que el motor relacional también puede abrir conjuntos de filas OLE DB simples en cualquier origen de datos OLE DB.This means the relational engine can also open simple OLE DB rowsets on any OLE DB data source.
oledb_storageoledb_storage
El motor relacional utiliza la interfaz de programación de aplicaciones (API) OLE DB para abrir los conjuntos de filas en servidores vinculados, capturar las filas y administrar las transacciones.The relational engine uses the OLE DB application programming interface (API) to open the rowsets on linked servers, fetch the rows, and manage transactions.

Es necesario que haya un proveedor OLE DB en el servidor que ejecute SQL ServerSQL Server por cada origen de datos OLE DB al que se tenga acceso como servidor vinculado.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. El conjunto de operaciones Transact-SQLTransact-SQL que puede utilizarse con un origen de datos OLE DB específico depende de las capacidades del proveedor 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.

Para cada instancia de SQL ServerSQL Server, los miembros del rol fijo de servidor sysadmin pueden habilitar o deshabilitar el uso de nombres de conectores ad hoc para un proveedor OLE DB mediante la propiedad DisallowAdhocAccess de SQL ServerSQL Server.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. Al habilitar el acceso ad hoc, cualquier usuario que haya iniciado sesión en esa instancia puede ejecutar instrucciones Transact-SQLTransact-SQL que contengan nombres de conectores ad hoc, haciendo referencia a cualquier origen de datos de la red al que se pueda obtener acceso mediante el proveedor 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. Para controlar el acceso a orígenes de datos, los miembros del rol sysadmin pueden deshabilitar el acceso ad hoc para ese proveedor OLE DB, limitando así a los usuarios solo a los orígenes de datos a los que se hace referencia mediante nombres de servidores vinculados definidos por los administradores.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. De forma predeterminada, el acceso ad hoc está habilitado para el proveedor OLE DB de SQL ServerSQL Server y deshabilitado para los demás proveedores 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.

Las consultas distribuidas pueden permitir que los usuarios tengan acceso a otro origen de datos (por ejemplo, archivos, orígenes de datos no relacionales como Active Directory, etc.) mediante el contexto de seguridad de la cuenta de Microsoft Windows con la que se ejecuta el servicio SQL ServerSQL Server.Distributed queries can allow users to access another data source (for example, files, non-relational data sources such as Active Directory, and so on) using the security context of the Microsoft Windows account under which the SQL ServerSQL Server service is running. SQL ServerSQL Server suplanta el inicio de sesión de forma apropiada para inicios de sesión de Windows, pero eso no es posible para inicios de sesión de SQL ServerSQL Server.impersonates the login appropriately for Windows logins; however, that is not possible for SQL ServerSQL Server logins. Esto puede permitir potencialmente que un usuario de consultas distribuidas obtenga acceso a un origen de datos para el que no tiene permisos, pero para el que la cuenta con la que el servicio SQL ServerSQL Server se está ejecutando sí tiene permisos.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. Utilice sp_addlinkedsrvlogin para definir los inicios de sesión específicos con acceso autorizado al servidor vinculado correspondiente.Use sp_addlinkedsrvlogin to define the specific logins that are authorized to access the corresponding linked server. Este control no está disponible para nombres ad hoc; por tanto, tenga precaución a la hora de habilitar el proveedor OLE DB para el acceso ad hoc.This control is not available for ad-hoc names, so use caution in enabling an OLE DB provider for ad-hoc access.

Cuando es posible, SQL ServerSQL Server inserta operaciones relacionales como combinaciones, restricciones, proyecciones, ordenaciones y operaciones "group by" al origen de datos 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 no explora de forma predeterminada la tabla base en SQL ServerSQL Server y realiza las operaciones relacionales por sí mismo.does not default to scanning the base table into SQL ServerSQL Server and performing the relational operations itself. SQL ServerSQL Server consulta el proveedor de OLE DB para determinar el nivel de gramática de SQL que admite y, en función de esa información, inserta tantas operaciones relacionales como sea posible en el proveedor.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 especifica un mecanismo para que un proveedor OLE DB devuelva estadísticas que indiquen cómo se distribuyen los valores de clave en el origen de datos 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. Esto permite que el optimizador de consultas de SQL ServerSQL Server analice mejor el patrón de datos del origen de datos según los requisitos de cada instrucción Transact-SQLTransact-SQL, lo que mejora su capacidad a la hora de generar planes de ejecución óptimos.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.

Mejoras de procesamiento de consultas en las tablas e índices con particionesQuery Processing Enhancements on Partitioned Tables and Indexes

En SQL Server 2008SQL Server 2008, se ha perfeccionado el rendimiento del procesamiento de las consultas en tablas con particiones en muchos planes paralelos, se ha modificado la forma de representación de los planes paralelos y en serie, y se ha mejorado la información sobre la creación de particiones que los planes de ejecución en tiempo de compilación y en tiempo de ejecución proporcionan.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. En este tema se describen estas mejoras y se proporcionan consejos sobre la interpretación de los planes de ejecución de consultas sobre tablas e índices con particiones, así como las prácticas recomendadas para la mejora del rendimiento de las consultas en objetos con particiones.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.

Nota

Las tablas e índices con particiones solo se admiten en las ediciones Enterprise, Developer y Evaluation de SQL ServerSQL Server.Partitioned tables and indexes are supported only in the SQL ServerSQL Server Enterprise, Developer, and Evaluation editions.

Nueva operación de búsqueda orientada a particionesNew Partition-Aware Seek Operation

En SQL ServerSQL Server, se ha cambiado la representación interna de una tabla con particiones para que la tabla parezca que es para el procesador de consultas un índice de varias columnas, con PartitionID como columna inicial.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 es una columna calculada oculta usada internamente para representar el valor de ID de la partición que contiene una fila específica.PartitionID is a hidden computed column used internally to represent the ID of the partition containing a specific row. Por ejemplo, suponga que la tabla T, definida como T(a, b, c), tiene una partición en la columna a y un índice clúster en la columna 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. En SQL ServerSQL Server, esta tabla con particiones se trata internamente como una tabla sin particiones con el esquema T(PartitionID, a, b, c) y un índice agrupado en la clave compuesta (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). De esta manera, el Optimizador de consultas puede realizar operaciones de búsqueda basadas en PartitionID sobre cualquier tabla o índice con particiones.This allows the Query Optimizer to perform seek operations based on PartitionID on any partitioned table or index.

Ahora, la eliminación de particiones se lleva a cabo durante esta operación de búsqueda.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 (como la columna inicial lógica) y posiblemente otras columnas de clave de índice, para a continuación realizar una búsqueda de segundo nivel con una segunda condición sobre una o más columnas adicionales para cada valor distinto devuelto por la operación de búsqueda de primer nivel.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. De esta manera, esta operación, denominada búsqueda selectiva, permite al optimizador de consultas realizar una operación de búsqueda o examen en función de una condición con el fin de determinar las particiones a las cuales se va a obtener acceso junto con una operación Index Seek de segundo nivel en el seno de ese operador, que devolverá las filas de las particiones que cumplan con una condición diferente.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. Por ejemplo, considere la siguiente consulta.For example, consider the following query.

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

En este ejemplo, suponga que la tabla T, definida como T(a, b, c), tiene una partición en la columna a y un índice clúster en la columna 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. La siguiente función define los límites de la partición para la tabla 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);

Para llevar a cabo la consulta, el procesador de consultas realiza una operación de búsqueda de primer nivel para localizar todas las particiones que cumplan con la condición 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. Se identifican así las particiones a las cuales se va a tener acceso.This identifies the partitions to be accessed. A continuación, el procesador lleva a cabo en cada partición identificada una búsqueda de segundo nivel sobre el índice agrupado de la columna b con el fin de localizar las filas que cumplan con la condición T.b = 2 y T.a < 10.Within each partition identified, the processor then performs a second-level seek into the clustered index on column b to find the rows that meet the condition T.b = 2 and T.a < 10.

La siguiente ilustración es una representación lógica de la operación de búsqueda selectiva.The following illustration is a logical representation of the skip scan operation. En ella se muestra la tabla T con datos en las columnas a y b.It shows table T with data in columns a and b. Las particiones están numeradas del 1 al 4; las líneas discontinuas representan los límites de las particiones.The partitions are numbered 1 through 4 with the partition boundaries shown by dashed vertical lines. Una operación de búsqueda de primer nivel en el particionamiento (no representado en la ilustración) ha determinado que las particiones 1, 2 y 3 cumplen con la condición de búsqueda impuesta por el particionamiento definido para la tabla y con el predicado de la columna 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. Es decir, T.a < 10.That is, T.a < 10. La línea curva representa el camino recorrido por la fase de búsqueda de segundo nivel de la operación de búsqueda selectiva.The path traversed by the second-level seek portion of the skip scan operation is illustrated by the curved line. Básicamente, la operación de búsqueda selectiva realiza en cada una de esas particiones una búsqueda de las filas que cumplan con la condición b = 2.Essentially, the skip scan operation seeks into each of these partitions for rows that meet the condition b = 2. El costo total de la operación de búsqueda selectiva es el mismo que el de tres búsquedas por índices independientes.The total cost of the skip scan operation is the same as that of three separate index seeks.

skip_scan

Visualización de la información del particionamiento en los planes de ejecución de consultasDisplaying Partitioning Information in Query Execution Plans

Los planes de ejecución de consultas en tablas e índices con particiones pueden examinarse mediante las instrucciones Transact-SQLTransact-SQL SET (SET SHOWPLAN_XML o SET STATISTICS XML), o mediante la salida gráfica del plan de ejecución en 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. Por ejemplo, puede ver el plan de ejecución en tiempo de compilación haciendo clic en Mostrar plan de ejecución estimado en la barra de herramientas del editor de consultas y el plan en tiempo de ejecución haciendo clic en Incluir plan de ejecución real.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.

Estas herramientas le proporcionarán la siguiente información:Using these tools, you can ascertain the following information:

  • Las operaciones como scans, seeks, inserts, updates, mergesy deletes que acceden a tablas o índices con particiones.The operations such as scans, seeks, inserts, updates, merges, and deletes that access partitioned tables or indexes.
  • Las particiones a las que tiene acceso la consulta.The partitions accessed by the query. Por ejemplo, el número total de particiones a las que se ha tenido acceso y los intervalos de particiones contiguas a los que se ha tenido acceso están disponibles en los planes de ejecución en tiempo de ejecución.For example, the total count of partitions accessed and the ranges of contiguous partitions that are accessed are available in run-time execution plans.
  • Cuándo se utiliza la operación de búsqueda selectiva en una operación de búsqueda o de recorrido para recuperar datos de una o más particiones.When the skip scan operation is used in a seek or scan operation to retrieve data from one or more partitions.

Mejoras en la información sobre particionesPartition Information Enhancements

SQL ServerSQL Server proporciona una mejor información acerca del particionamiento tanto para los planes de ejecución en tiempo de compilación como para los planes de ejecución en tiempo de ejecución.provides enhanced partitioning information for both compile-time and run-time execution plans. Los planes de ejecución proporcionan ahora la siguiente información:Execution plans now provide the following information:

  • Un atributo Partitioned opcional que indica que un operador, como seek, scan, insert, update, mergeo delete, se ejecuta en una tabla con particiones.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.
  • Un nuevo elemento SeekPredicateNew con un subelemento SeekKeys que incluye PartitionID como columna de clave de índice principal y condiciones de filtro que especifican búsquedas de intervalos en 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. La presencia de dos subelementos SeekKeys indica el uso de una operación de búsqueda selectiva sobre PartitionID .The presence of two SeekKeys subelements indicates that a skip scan operation on PartitionID is used.
  • Información de resumen que proporciona el número total de particiones a las que se ha tenido acceso.Summary information that provides a total count of the partitions accessed. Esta información está disponible únicamente en los planes en tiempo de ejecución.This information is available only in run-time plans.

Para ver cómo aparece esta información en la salida gráfica tanto del pan de ejecución como del Plan de presentación XML, considere la siguiente consulta sobre la tabla con particiones 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. Esta consulta actualiza datos en dos particiones.This query updates data in two partitions.

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

La siguiente ilustración muestra las propiedades del operador Clustered Index Seek en el plan de ejecución en tiempo de compilación para esta consulta.The following illustration shows the properties of the Clustered Index Seek operator in the compile-time execution plan for this query. Para ver la definición de la tabla fact_sales y la definición de la partición, consulte el "Ejemplo" en este tema.To view the definition of the fact_sales table and the partition definition, see "Example" in this topic.

clustered_index_seek

Atributo PartitionedPartitioned Attribute

Cuando un operador como Index Seek se ejecuta sobre una tabla o índice con particiones, el atributo Partitioned aparece en los planes de tiempo de compilación y de ejecución y tiene asignado el valor 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). El atributo no se muestra cuando su valor es False (0).The attribute does not display when it is set to False (0).

El atributo Partitioned puede aparecer en los siguientes operadores físicos y lógicos:The Partitioned attribute can appear in the following physical and logical operators:

  • Table Scan
  • Index Scan
  • Index Seek
  • Insert
  • Update
  • Delete
  • Merge

Como puede apreciarse en la ilustración previa, este atributo se muestra en las propiedades del operador en el que está definido.As shown in the previous illustration, this attribute is displayed in the properties of the operator in which it is defined. En la salida del Plan de presentación XML, este atributo aparece como Partitioned="1" en el nodo RelOp del operador en el que está definido.In the XML Showplan output, this attribute appears as Partitioned="1" in the RelOp node of the operator in which it is defined.

Predicado de nueva búsquedaNew Seek Predicate

En la salida del Plan de presentación XML, el elemento SeekPredicateNew aparece en el operador en el que está definido.In XML Showplan output, the SeekPredicateNew element appears in the operator in which it is defined. Puede contener hasta un máximo de dos apariciones del subelemento SeekKeys .It can contain up to two occurrences of the SeekKeys sub-element. El primer elemento SeekKeys especifica la operación de búsqueda de primer nivel a nivel de identificador de partición del índice lógico.The first SeekKeys item specifies the first-level seek operation at the partition ID level of the logical index. Es decir, esta búsqueda determina las particiones a las que se debe tener acceso para satisfacer las condiciones de la consulta.That is, this seek determines the partitions that must be accessed to satisfy the conditions of the query. El segundo elemento SeekKeys especifica la parte correspondiente a la búsqueda de segundo nivel de la operación de búsqueda selectiva que se produce en cada partición identificada en la búsqueda de primer nivel.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.

Información de resumen de particionesPartition Summary Information

En los planes de ejecución de tiempo de ejecución, la información de resumen de particiones proporciona el número total y la identidad de las particiones a las que se ha tenido acceso.In run-time execution plans, partition summary information provides a count of the partitions accessed and the identity of the actual partitions accessed. Esta información puede utilizarse para comprobar que la consulta tiene acceso a las particiones correctas y que todas las demás particiones no se consideran.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.

Se proporciona la siguiente información: Actual Partition County Partitions Accessed.The following information is provided: Actual Partition Count, and Partitions Accessed.

Actual Partition Count es el número total de particiones a las que la consulta ha tenido acceso.Actual Partition Count is the total number of partitions accessed by the query.

Partitions Accessed, en la salida del Plan de presentación XML, es la información de resumen de partición que aparece en el nuevo elemento RuntimePartitionSummary en el nodo RelOp del operador en el que está definido.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. El siguiente ejemplo muestra los contenidos del elemento RuntimePartitionSummary , que indica que se ha tenido acceso a un total de dos particiones (particiones 2 y 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>

Visualización de la información de la partición mediante otros métodos de ShowplanDisplaying Partition Information by Using Other Showplan Methods

Los métodos del Plan de presentación SHOWPLAN_ALL, SHOWPLAN_TEXTy STATISTICS PROFILE no notifican la información de partición que se describe en este tema, con la siguiente excepción.The Showplan methods SHOWPLAN_ALL, SHOWPLAN_TEXT, and STATISTICS PROFILE do not report the partition information described in this topic, with the following exception. Como parte del predicado SEEK , las particiones a las que se va a tener acceso están identificadas por un predicado de intervalo definido sobre la columna calculada que representa el identificador de la partición.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. En el ejemplo siguiente se muestra el predicado SEEK para un operador Clustered Index Seek .The following example shows the SEEK predicate for a Clustered Index Seek operator. Se obtiene acceso a las particiones 2 y 3, y el operador de búsqueda filtra las filas que cumplen con la condición 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)

Interpretación de planes de ejecución para montones con particionesInterpreting Execution Plans for Partitioned Heaps

Un montón con particiones se trata como un índice lógico sobre el identificador de partición.A partitioned heap is treated as a logical index on the partition ID. La eliminación de una partición en un montón con particiones se representa en un plan de ejecución como un operador Table Scan con un predicado SEEK sobre el identificador de partición.Partition elimination on a partitioned heap is represented in an execution plan as a Table Scan operator with a SEEK predicate on partition ID. El siguiente ejemplo muestra la información de Showplan proporcionada:The following example shows the Showplan information provided:

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

Interpretación de planes de ejecución para combinaciones por colocaciónInterpreting Execution Plans for Collocated Joins

La combinación colocada puede darse cuando dos tablas presentan particiones que usan funciones de partición iguales o equivalentes y las columnas de partición de ambos lados de la combinación se especifican en la condición de combinación de la consulta.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. El optimizador de consultas puede generar un plan en el que las particiones de cada tabla que tengan los mismos identificadores de partición se combinen de forma independiente.The Query Optimizer can generate a plan where the partitions of each table that have equal partition IDs are joined separately. Las combinaciones por colocación pueden ser más rápidas que las no colocadas, puesto que requieren menos memoria y tiempo de procesamiento.Collocated joins can be faster than non-collocated joins because they can require less memory and processing time. El Optimizador de consultas elige un plan no colocado o colocado en función de las estimaciones de costos.The Query Optimizer chooses a non-collocated plan or a collocated plan based on cost estimates.

En un plan colocado, la combinación Nested Loops lee una o varias particiones de índice o tabla combinada de la parte interna.In a collocated plan, the Nested Loops join reads one or more joined table or index partitions from the inner side. Los números en el interior de los operadores Constant Scan representan los números de partición.The numbers within the Constant Scan operators represent the partition numbers.

Cuando se generan planes paralelos para combinaciones por colocación para índices o tablas con particiones, aparece un operador Parallelism entre los operadores de combinación Constant Scan y Nested 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. En este caso, los distintos subprocesos de trabajo de la parte exterior de la combinación leen y trabajan en una partición diferente.In this case, multiple worker threads on the outer side of the join each read and work on a different partition.

La siguiente ilustración muestra un plan de consulta paralela para una combinación por colocación.The following illustration demonstrates a parallel query plan for a collocated join.
colocated_join

Estrategia de ejecución de consulta paralela para objetos con particionesParallel Query Execution Strategy for Partitioned Objects

El procesador de consultas utiliza una estrategia de ejecución paralela para consultas que seleccionan en objetos con particiones.The query processor uses a parallel execution strategy for queries that select from partitioned objects. Como parte de la estrategia de ejecución, el procesador de consultas determina las particiones de tabla requeridas para la consulta y la proporción de subprocesos de trabajo que se han de asignar a cada partición.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. En la mayoría de los casos, el procesador de consultas asigna a cada partición un número igual, o casi igual, de subprocesos de trabajo y, después, ejecuta la consulta en paralelo en las particiones.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. En los párrafos siguientes se explica la asignación de subprocesos de trabajo con más detalle.The following paragraphs explain worker thread allocation in greater detail.

subproceso de trabajo 1

Si el número de subprocesos de trabajo es menor que el número de particiones, el procesador de consultas asigna cada subproceso de trabajo a una partición diferente, pero deja inicialmente una o más particiones sin un subproceso de trabajo asignado.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. Cuando un subproceso de trabajo termina de ejecutarse en una partición, el procesador de consultas lo asigna a la partición siguiente hasta que cada partición tenga asignado un solo subproceso de trabajo.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. Este es el único caso en el que el procesador de consultas reasigna subprocesos de trabajo a otras particiones.This is the only case in which the query processor reallocates worker threads to other partitions.
Muestra el subproceso de trabajo reasignado después de finalizar.Shows worker thread reassigned after it finishes. Si el número de subprocesos de trabajo es igual al número de particiones, el procesador de consultas asigna un subproceso de trabajo a cada partición.If the number of worker threads is equal to the number of partitions, the query processor assigns one worker thread to each partition. Cuando un subproceso de trabajo finaliza, no se reasigna a otra partición.When a worker thread finishes, it is not reallocated to another partition.

subproceso de trabajo 2

Si el número de subprocesos de trabajo es mayor que el número de particiones, el procesador de consultas asigna un número igual de subprocesos de trabajo a cada partición.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. Si el número de subprocesos de trabajo no es un múltiplo exacto del número de particiones, el procesador de consultas asigna un subproceso de trabajo adicional a algunas particiones para que se usen todos los subprocesos de trabajo disponibles.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. Tenga en cuenta que si solo hay una partición, todos los subprocesos de trabajo se asignarán a esa partición.Note that if there is only one partition, all worker threads will be assigned to that partition. En el diagrama siguiente hay cuatro particiones y 14 subprocesos de trabajo.In the diagram below, there are four partitions and 14 worker threads. Cada partición tiene asignados tres subprocesos de trabajo, y dos particiones tienen un subproceso de trabajo adicional, para un total de 14 asignaciones de subprocesos de trabajo.Each partition has 3 worker threads assigned, and two partitions have an additional worker thread, for a total of 14 worker thread assignments. Cuando un subproceso de trabajo finaliza, no se reasigna a otra partición.When a worker thread finishes, it is not reassigned to another partition.

subproceso de trabajo 3

Aunque los ejemplos anteriores sugieren una manera sencilla de asignar subprocesos de trabajo, la estrategia real es más compleja y considera otras variables que se producen durante la ejecución de la consulta.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. Por ejemplo, si la tabla tiene particiones y un índice agrupado en la columna A, y una consulta tiene la cláusula de predicado WHERE A IN (13, 17, 25), el procesador de consultas asignará uno o más subprocesos de trabajo a cada uno de estos tres valores de búsqueda (A=13, A=17 y A=25) en lugar de a cada partición de tabla.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. Solo es necesario ejecutar la consulta en las particiones que contienen estos valores, y si se da la circunstancia de que todos estos predicados de búsqueda están en la misma partición de tabla, todos los subprocesos de trabajo se asignarán a la misma partición de tabla.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.

He aquí otro ejemplo: supongamos que la tabla tiene cuatro particiones en la columna A con puntos de límite (10, 20, 30), un índice en la columna B y la consulta tiene una cláusula de predicado 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). Dado que las particiones de tabla se basan en los valores de A, los valores de B pueden producirse en cualquiera de las particiones de tabla.Because the table partitions are based on the values of A, the values of B can occur in any of the table partitions. Por lo tanto, el procesador de consultas buscará cada uno de los tres valores de B (50, 100, 150) en cada una de las cuatro particiones de tabla.Thus, the query processor will seek for each of the three values of B (50, 100, 150) in each of the four table partitions. El procesador de consultas asignará subprocesos de trabajo proporcionalmente para poder ejecutar cada uno de estos 12 exámenes de consulta en paralelo.The query processor will assign worker threads proportionately so that it can execute each of these 12 query scans in parallel.

Particiones de tabla basadas en la columna ATable partitions based on column A Busca la columna B en cada partición de tablaSeeks for column B in each table partition
Partición de tabla 1: A < 10Table Partition 1: A < 10 B=50, B=100, B=150B=50, B=100, B=150
Partición de tabla 2: A >= 10 Y A < 20Table Partition 2: A >= 10 AND A < 20 B=50, B=100, B=150B=50, B=100, B=150
Partición de tabla 3: A >= 20 Y A < 30Table Partition 3: A >= 20 AND A < 30 B=50, B=100, B=150B=50, B=100, B=150
Partición de tabla 4: A >= 30Table Partition 4: A >= 30 B=50, B=100, B=150B=50, B=100, B=150

Procedimientos recomendadosBest Practices

Para mejorar el rendimiento de las consultas que tienen acceso a una cantidad grande de datos de tablas e índices grandes con particiones, recomendamos las siguientes prácticas: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:

  • Distribuya cada partición entre varios discos.Stripe each partition across many disks. Ello resulta especialmente importante al utilizar discos giratorios.This is especially relevant when using spinning disks.
  • Cuando sea posible, utilice un servidor con memoria principal suficiente para alojar en ella las particiones con una mayor frecuencia de acceso o todas las particiones con el fin de reducir el costo de la E/S.When possible, use a server with enough main memory to fit frequently accessed partitions or all partitions in memory to reduce I/O cost.
  • Si no es posible alojar en memoria los datos que está consultando, comprima las tablas e índices.If the data you query will not fit in memory, compress the tables and indexes. De esta manera se reducirá el costo de la E/S.This will reduce I/O cost.
  • Utilice un servidor con procesadores rápidos y tantos núcleos de procesador como pueda permitirse, con el fin de sacar partido de la capacidad de procesamiento de las consultas en paralelo.Use a server with fast processors and as many processor cores as you can afford, to take advantage of parallel query processing capability.
  • Asegúrese de que el servidor dispone de un ancho banda de controlador de E/S suficiente.Ensure the server has sufficient I/O controller bandwidth.
  • Cree un índice clúster en cada tabla grande con particiones para sacar partido de las optimizaciones de examen de los árboles B.Create a clustered index on every large partitioned table to take advantage of B-tree scanning optimizations.
  • Siga los procedimientos recomendados de las notas del producto, en The Data Loading Performance Guide (Guía sobre el rendimiento de carga de datos), cuando cargue grandes volúmenes de datos en tablas con particiones.Follow the best practice recommendations in the white paper, The Data Loading Performance Guide, when bulk loading data into partitioned tables.

EjemploExample

El ejemplo siguiente crea una base de datos de prueba que contiene una única tabla con siete particiones.The following example creates a test database containing a single table with seven partitions. Utilice las herramientas descritas previamente al ejecutar las consultas de este ejemplo para ver la información del particionamiento para los planes de tiempo de compilación y los de tiempo de ejecución.Use the tools described previously when executing the queries in this example to view partitioning information for both compile-time and run-time plans.

Nota

Este ejemplo inserta más de 1 millón de filas en la tabla.This example inserts more than 1 million rows into the table. En función de su hardware, la ejecución de este ejemplo puede tomar varios minutos.Running this example may take several minutes depending on your hardware. Antes de ejecutar este ejemplo, compruebe que tiene más de 1,5 GB de espacio en disco disponible.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

Lecturas adicionalesAdditional Reading

Referencia de operadores lógicos y físicos del plan de presentaciónShowplan Logical and Physical Operators Reference
Eventos extendidosExtended Events
Procedimiento recomendado con el Almacén de consultasBest Practice with the Query Store
Estimación de cardinalidadCardinality Estimation
Procesamiento de consultas inteligentes Intelligent query processing
Prioridad de los operadores Operator Precedence
Planes de ejecución Execution Plans
Centro de rendimiento para el motor de base de datos SQL Server y Azure SQL DatabasePerformance Center for SQL Server Database Engine and Azure SQL Database