Guía de arquitectura de procesamiento de consultas

Artículo
08/18/2023

Se aplica a:SQL Server Azure SQL Database Azure SQL Managed Instance

El motor de base de datos de SQL Server procesa consultas en diversas arquitecturas de almacenamiento de datos como tablas locales, tablas con particiones y tablas distribuidas entre varios servidores. En las secciones siguientes se explica cómo SQL Server procesa consultas y optimiza la reutilización de consultas mediante el almacenamiento en caché del plan de ejecución.

Modos de ejecución

El motor de base de datos de SQL Server puede procesar las instrucciones Transact-SQL mediante dos modos de procesamiento distintos:

Ejecución del modo de fila
Ejecución del modo por lotes

Ejecución del modo de fila

La ejecución del modo de fila es un método de procesamiento de consultas que se usa con tablas RDBMS tradicionales, donde los datos se almacenan en formato de fila. 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. A partir de cada fila que se lee, SQL Server recupera las columnas necesarias para el conjunto de resultados, tal y como hace referencia una instrucción SELECT, un predicado JOIN o un predicado de filtro.

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.

Ejecución del modo por lotes

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"). 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. 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.

Cuando se introdujo por primera vez, la ejecución del modo por lotes estaba estrechamente integrada con el formato de almacenamiento de almacén de columnas y optimizada para este. Sin embargo, a partir de SQL Server 2019 (15.x) y en Azure SQL Database, la ejecución del modo por lotes ya no requiere índices de almacén de columnas. Para obtener más información, consulte Modo por lotes en el almacén de filas.

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. El resultado es un mayor paralelismo y un rendimiento más rápido.

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. SQL Server lee solo las columnas necesarias para el resultado, como se hace referencia a ella mediante una instrucción SELECT, un predicado JOIN o un predicado de filtro. Para más información sobre los índices de almacén de columnas, vea Arquitectura de los índices de almacén de columnas.

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.

Procesamiento de instrucciones SQL

La forma más básica de ejecutar instrucciones Transact-SQL en SQL Server consiste en procesar una única instrucción Transact-SQL. 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.

Precedencia del operador lógico

Cuando en una instrucción se usa más de un operador lógico, primero se evalúa NOT, luego AND y, finalmente, OR. Los operadores aritméticos y bit a bit se tratan antes que los operadores lógicos. Para más información, vea Prioridad de operador.

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.

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. La siguiente consulta busca solamente los productos en los modelos 20 y 21 que sean rojos.

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. No hay ninguna reducción significativa del rendimiento que sea achacable al uso de paréntesis. El siguiente ejemplo se lee mejor que el ejemplo original, aunque ambos son sintácticamente iguales.

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

Optimizar instrucciones SELECT

Una SELECT instrucción no es de procedimiento; no indica los pasos exactos que el servidor de base de datos debe usar para recuperar los datos solicitados. 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. Este proceso se denomina optimizar la instrucción SELECT . El componente que lo hace se denomina optimizador de consultas. 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. La salida del Optimizador de consultas es un plan de ejecución de consultas, en ocasiones denominado plan de consulta o simplemente plan de ejecución. El contenido de un plan de ejecución se describe con más detalle más adelante en este artículo.

En el siguiente diagrama se muestran las entradas y salidas del optimizador de consultas durante la optimización de una única instrucción SELECT:

Diagram of the Query processor I/O.

Una instrucción SELECT define únicamente los siguientes elementos:

El formato del conjunto de resultados. Este elemento se especifica principalmente en la lista de selección. Sin embargo, también afectan a la forma final del conjunto de resultados otras cláusulas como ORDER BY y GROUP BY .
Las tablas que contienen los datos de origen. Se especifica en la cláusula FROM .
Cómo se relacionan las tablas de forma lógica para la instrucción SELECT . 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.
Las condiciones que deben cumplir las filas de las tablas de origen para satisfacer los requisitos de la instrucción SELECT . Se especifican en las cláusulas WHERE y HAVING .

Un plan de ejecución de consulta es una definición de los siguientes elementos:

La secuencia en la que se tiene acceso a las tablas de origen.
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. 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. Las demás secuencias en las que el servidor de base de datos podría tener acceso a las tablas son:
TableC, TableB, TableAo
TableB, TableA, TableCo
TableB, TableC, TableAo
TableC, TableA, TableB
Los métodos que se usan para extraer los datos de cada tabla.
Por lo general, hay métodos diferentes para tener acceso a los datos de cada tabla. Si solo se necesitan unas cuantas filas con valores de clave específicos, el servidor de la base de datos puede utilizar un índice. 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. 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. 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.
Los métodos que se usan para realizar cálculos, y cómo filtrar, agregar y ordenar los datos de cada tabla.
A medida que se tiene acceso a los datos desde las tablas, existen distintos métodos para realizar cálculos sobre ellos (por ejemplo, calcular valores escalares), para agregarlos y ordenarlos tal como se define en el texto de la consulta, por ejemplo, cuando se usa una cláusula GROUP BY o ORDER BY, y cómo filtrarlos, por ejemplo, cuando se usa una cláusula WHERE o HAVING.

El proceso de selección de un plan de ejecución entre varios planes posibles se conoce como optimización. El optimizador de consultas es uno de los componentes más importantes del motor de base de datos. 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. Por ejemplo, se pueden dar a dos constructoras planos idénticos para una casa. 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.

El optimizador de consultas de SQL Server es un optimizador basado en costos. Cada plan de ejecución posible tiene asociado un costo en términos de la cantidad de recursos del equipo que se utilizan. El optimizador de consultas debe analizar los planes posibles y elegir el de menor costo estimado. Algunas instrucciones SELECT complejas tienen miles de planes de ejecución posibles. En estos casos, el optimizador de consultas no analiza todas las combinaciones posibles. En lugar de esto, utiliza algoritmos complejos para encontrar un plan de ejecución que tenga un costo razonablemente cercano al mínimo posible.

El optimizador de consultas de SQL Server no elige solo el plan de ejecución con el menor costo de recursos; elige el plan que devuelve los resultados al usuario con un costo razonable en los recursos y que devuelve los resultados más rápido. 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. El optimizador de consultas de SQL Server usará un plan de ejecución paralelo para devolver resultados si la carga en el servidor no se verá afectada negativamente.

El optimizador de consultas de SQL Server se basa en las estadísticas de distribución cuando calcula los costos de recursos de diferentes métodos para extraer información de una tabla o índice. Se mantienen estadísticas de distribución para las columnas y los índices, y se conserva información sobre la densidad¹ de los datos subyacentes. Esto se usa para indicar la selectividad de los valores de una columna o un índice determinado. 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). Un índice del NIV es más selectivo que un índice del fabricante, porque NIV tiene una densidad inferior a la del fabricante. 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. Para obtener más información sobre las densidades, vea Estadísticas.

¹ 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. A medida que disminuye la densidad, aumenta la selectividad de un valor.

El optimizador de consultas de SQL Server es importante porque permite que el servidor de bases de datos se ajuste dinámicamente a las condiciones cambiantes de la base de datos sin necesidad de entrada de un programador o administrador de bases de datos. Esto permite a los programadores centrarse en la descripción del resultado final de la consulta. Pueden confiar en que el optimizador de consultas de SQL Server creará un plan de ejecución eficaz para el estado de la base de datos cada vez que se ejecuta la instrucción.

Nota:

SQL Server Management Studio tiene tres opciones para mostrar los planes de ejecución:

El plan de ejecución estimado, que es el plan compilado, generado por el optimizador de consultas.
El plan de ejecución real, que es lo mismo que el plan compilado más su contexto de ejecución. Esto incluye información en tiempo de ejecución disponible después de que se complete la ejecución, como advertencias de ejecución, o en versiones más recientes del motor de base de datos, el tiempo transcurrido y la CPU usados durante la ejecución.
Las estadísticas de consulta activa, que es lo mismo que el plan compilado más su contexto de ejecución. Esto incluye información en tiempo de ejecución durante el progreso de la ejecución y se actualiza cada segundo. La información en tiempo de ejecución incluye, por ejemplo, el número real de filas que fluyen a través de los operadores.

Procesar una instrucción SELECT

Los pasos básicos que utiliza SQL Server para procesar una única instrucción SELECT incluyen lo siguiente:

El analizador examina la instrucción SELECT y la divide en unidades lógicas como palabras clave, expresiones, operadores e identificadores.
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.
El optimizador de consultas analiza diferentes formas de acceso a las tablas de origen. Después, selecciona la serie de pasos que devuelven los resultados de la forma más rápida con el menor número de recursos posible. El árbol de la consulta se actualiza para registrar esta serie exacta de pasos. La versión final y optimizada del árbol de la consulta se denomina plan de ejecución.
El motor relacional comienza a ejecutar el plan de ejecución. 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.
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.

Evaluación de expresiones y plegado constante

SQL Server evalúa algunas expresiones constantes al principio para mejorar el rendimiento de las consultas. Es lo que se conoce como doblado de constantes. Una constante es un literal de Transact-SQL, como 3, 'ABC', '2005-12-31', 1.0e3 o 0x12345678.

Expresiones plegables

SQL Server usa el plegado constante con los siguientes tipos de expresiones:

Expresiones aritméticas, como 1 + 1 y 5 / 3 * 2, que solo contienen constantes.
Expresiones lógicas, como 1 = 1 y 1 > 2 AND 3 > 4, que solo contienen constantes.
Funciones integradas que SQL Server considera plegables, incluidas CAST y 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. Las funciones no deterministas no pueden plegarse. Excepto algunas excepciones, las funciones deterministas integradas pueden doblarse.
Métodos deterministas de tipos definidos por el usuario clR y funciones definidas por el usuario CLR con valores escalares deterministas (a partir de SQL Server 2012 (11.x)). Para obtener más información, vea Doblado constante para las funciones y métodos definidos por el usuario CLR.

Nota:

Los tipos de objetos grandes constituyen una excepción. Si el tipo de salida del proceso de plegado es un tipo de objeto grande (text,ntext, image, nvarchar(max), varchar(max), varbinary(max) o XML), SQL Server no plega la expresión.

Expresiones nofoldables

El resto de tipos de expresiones no pueden plegarse. En concreto, los siguientes tipos de expresiones no pueden plegarse:

Expresiones no constantes como una expresión cuyo resultado dependa del valor de una columna.
Expresiones cuyos resultados dependan de una variable o parámetro locales, como @x.
Funciones no deterministas.
Funciones de Transact-SQL definidas por el usuario¹.
Expresiones cuyos resultados dependan de la configuración de idioma.
Expresiones cuyos resultados dependan de las opciones SET.
Expresiones cuyos resultados dependan de las opciones de configuración del servidor.

¹ Antes de SQL Server 2012 (11.x), las funciones y los métodos definidos por el usuario clR con valores escalares deterministas no eran plegables.

Ejemplos de expresiones constantes plegables y nofoldables

Considere la consulta siguiente:

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 PARAMETERIZATION opción de base de datos no está establecida FORCED en para esta consulta, la expresión 117.00 + 1000.00 se evalúa y reemplaza por su resultado, 1117.00, antes de compilar la consulta. Entre las ventajas de este doblado de constantes figuran las siguientes:

La expresión no tiene que evaluarse repetidamente en tiempo de ejecución.
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.

Por otro lado, si dbo.f es una función escalar definida por el usuario, la expresión dbo.f(100) no se plega porque SQL Server no plega las expresiones que implican funciones definidas por el usuario, aunque sean deterministas. Para obtener más información sobre la parametrización, consulte Parametrización forzada más adelante en este artículo.

Evaluación de expresiones

Además, algunas expresiones cuyas constantes no se pliegan, pero cuyos argumentos se conocen en tiempo de compilación tanto si se trata de parámetros como de constantes, se evalúan mediante el estimador (de cardinalidad) del tamaño del conjunto de resultados que forma parte del optimizador durante la optimización.

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. Los siguientes operadores también se evalúan en tiempo de compilación si se conocen todas sus entradas:

Operadores aritméticos: +, -, *, /, unarios -
Operadores lógicos: AND, OR, NOT
Operadores de comparación:<, >, <=, >=, <>, 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.

Ejemplos de evaluación de expresiones en tiempo de compilación

Observe este procedimiento almacenado:

USE AdventureWorks2022;
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. La expresión @d+1 no está plegada constantemente, porque @d es un parámetro . Sin embargo, el valor del parámetro ya se conoce durante la optimización. 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.

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.

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

Cuando la SELECT instrucción de MyProc2 está optimizada en SQL Server, no se conoce el valor de @d2 . 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).

Procesar otras instrucciones

Los pasos básicos descritos para procesar una instrucción SELECT se aplican a otras instrucciones Transact-SQL, como INSERT, UPDATE y DELETE. Las instruccionesUPDATE y DELETE deben identificar el conjunto de filas que se van a modificar o eliminar. 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 . Las instrucciones UPDATE e INSERT pueden contener instrucciones SELECT incrustadas que proporcionan los valores de los datos que se van a actualizar o insertar.

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.

Tablas de trabajo

Es posible que el motor relacional tenga que generar una tabla de trabajo para realizar una operación lógica que se especifica en una instrucción Transact-SQL. Las tablas de trabajo son tablas internas que se utilizan para almacenar resultados intermedios. Se generan para determinadas consultas GROUP BY, ORDER BYo UNION . Por ejemplo, si una cláusula ORDER BY hace referencia a columnas que no están cubiertas por índices, es posible que el motor relacional tenga que generar una tabla de trabajo para ordenar el conjunto de resultados en el orden solicitado. 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. Las tablas de trabajo se generan en tempdb y se eliminan automáticamente cuando ya no se necesitan.

Visualización de la resolución

El procesador de consultas de SQL Server trata las vistas indexadas y no indexadas de manera diferente.

Las filas de una vista indizada se almacenan en la base de datos con el mismo formato que una tabla. Si el optimizador de consultas decide utilizar una vista indizada en un plan de consulta, ésta recibe el mismo tratamiento que la tabla base.
Solo se almacena la definición de una vista no indizada, y no las filas de la vista. 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-SQL que hace referencia a la vista no indexada.

La lógica usada por el optimizador de consultas de SQL Server para decidir cuándo usar una vista indizada es similar a la lógica que se usa para decidir cuándo usar un índice en una tabla. Si los datos de la vista indexada cubren toda o parte de la instrucción SQL y el optimizador de consultas determina que un índice de la vista es la ruta de acceso menos costosa, el optimizador de consultas elegirá el índice independientemente de si se hace referencia a la vista por su nombre en la consulta.

Si una instrucción Transact-SQL hace referencia a una vista no indizada, el analizador y el optimizador de consultas analizarán el origen de la instrucción Transact-SQL y de la vista, y las resolverán en un plan de ejecución único. No hay un plan para la instrucción Transact-SQL y un plan independiente para la vista.

Por ejemplo, considere la vista siguiente:

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

En función de esta vista, ambas instrucciones Transact-SQL realizan las mismas operaciones en las tablas base y producen el mismo resultado:

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

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

La característica del plan de presentación de SQL Server Management Studio muestra que el motor relacional genera el mismo plan de ejecución para estas dos instrucciones SELECT .

Uso de sugerencias con vistas

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. En ese caso, la consulta devuelve un error. Por ejemplo, considere la siguiente vista que contiene una sugerencia de tabla en su definición:

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

Ahora supongamos que usted ingresa la siguiente consulta:

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. No obstante, la expansión de la vista también mostrará la sugerencia NOLOCK en Person.Address. Dado que las sugerencias SERIALIZABLE y NOLOCK entran en conflicto, la consulta resultante es incorrecta.

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 .

Las sugerencias pueden propagarse por los niveles de las vistas anidadas. Por ejemplo, supongamos que una consulta aplica la sugerencia HOLDLOCK a una vista v1. Cuando se expande v1 , observamos que la vista v2 forma parte de su definición. La definición dev2incluye una sugerencia NOLOCK en una de sus tablas base. Sin embargo, esta tabla también hereda la sugerencia HOLDLOCK de la consulta de la vista v1. Dado que las sugerencias NOLOCK y HOLDLOCK entran en conflicto, se produce un error en la consulta.

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. Por ejemplo, la siguiente consulta realiza una selección entre tres tablas y una vista:

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:

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.

Resolución de índices en vistas

Al igual que con cualquier índice, SQL Server decide usar una vista indizada en su plan de consulta solo si el optimizador de consultas determina que es beneficioso hacerlo.

Las vistas indexadas se pueden crear en cualquier edición de SQL Server. En algunas ediciones de algunas versiones anteriores de SQL Server, el optimizador de consultas considera automáticamente la vista indizada. En algunas ediciones de algunas versiones anteriores de SQL Server, para usar una vista indizada, se debe usar la NOEXPAND sugerencia de tabla. Antes de SQL Server 2016 (13.x) Service Pack 1, el uso automático de una vista indexada por parte del optimizador de consultas solo se admite en determinadas ediciones de SQL Server. Dado que todas las ediciones admiten el uso automático de una vista indizada. Azure SQL Database e Instancia administrada de Azure SQL también admiten el uso automático de vistas indizadas sin especificar la NOEXPAND sugerencia.

El optimizador de consultas de SQL Server usa una vista indizada cuando se cumplen las condiciones siguientes:

Estas opciones de sesión se establecen en 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.
El optimizador de consultas busca una coincidencia entre las columnas de índice de vista y los elementos de la consulta, como las siguientes:
- Predicados de condiciones de búsqueda de la cláusula WHERE
- Operaciones de combinación
- Funciones de agregado
- CláusulasGROUP BY
- Referencias de tabla
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.
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.

Nota:

Las sugerencias READCOMMITTED y READCOMMITTEDLOCK siempre se consideran distintas en este contexto, independientemente del nivel de aislamiento de transacción actual.

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. No ha de especificarse ningún otro elemento para que la consulta utilice una vista indizada.

Una consulta no tiene que hacer referencia explícitamente a una vista indizada en la FROM cláusula para que el optimizador de consultas use la vista indizada. 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. El optimizador de consultas puede elegir la vista cuando contiene 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.

El optimizador de consultas trata FROM como vista estándar cualquier vista indexada a la que se haga referencia en la cláusula. Expande la definición de la vista en la consulta al inicio del proceso de optimización. A continuación se realiza la coincidencia de vista indizada. 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. El Optimizador de consultas elige la alternativa de menor costo.

Uso de sugerencias con vistas indizadas

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. Sin embargo, debe dejar que sea el optimizador de consultas el que determine dinámicamente los mejores métodos de acceso para cada consulta. Limite la utilización de EXPAND y NOEXPAND a casos específicos en los que se haya comprobado que el rendimiento mejora significativamente.

La opción EXPAND VIEWS determina que el Optimizador de consultas no use ninguno de los índices de la vista en toda la consulta.
Cuando se especifica NOEXPAND en una vista, el Optimizador de consultas considera la posibilidad de usar cualquiera de los índices definidos en la vista. Cuando se especifica NOEXPAND con la cláusula INDEX() opcional, el Optimizador de consultas está obligado a usar los índices especificados. NOEXPAND solo se puede especificar para una vista indizada y no se puede especificar para una vista no indizada. Antes de SQL Server 2016 (13.x) Service Pack 1, el uso automático de una vista indexada por parte del optimizador de consultas solo se admite en determinadas ediciones de SQL Server. Dado que todas las ediciones admiten el uso automático de una vista indizada. Azure SQL Database e Instancia administrada de Azure SQL también admiten el uso automático de vistas indizadas sin especificar la NOEXPAND sugerencia.

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. Si la consulta que compone la vista contiene sugerencias de tabla, éstas se propagan a las tablas subyacentes. (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. La mayoría de las veces, estas sugerencias coinciden entre sí, porque se han heredado directamente de la vista. 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, esta consulta no es apta para buscar coincidencias con una vista indizada. Si las INDEXsugerencias , PAGLOCK, TABLOCKXROWLOCK, , UPDLOCKo XLOCK se aplican a las tablas a las que se hace referencia en la consulta después de la expansión de la vista, la consulta no es apta para la coincidencia de vistas indizada.

Si una sugerencia de tabla en forma de referencias a INDEX (index_val[ ,...n] ) una vista en una consulta y no especifica también la NOEXPAND sugerencia, se omite la sugerencia de índice. Para especificar el uso de un determinado índice, 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. 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. Para más información, consulte Resolución de vistas. Esta propagación no se aplica a las sugerencias de combinación. Solo se aplican en su posición original en la consulta. El optimizador de consultas no tiene en cuenta las sugerencias de combinación al hacer coincidir consultas con vistas indizadas. Si un plan de consulta usa una vista indizada que coincide con parte de una consulta que contiene una sugerencia de combinación, la sugerencia de combinación no se usa en el plan.

No se permiten sugerencias en las definiciones de vistas indizadas. En los modos de compatibilidad 80 y superiores, SQL Server pasa por alto las sugerencias incluidas en definiciones de vistas indexadas al mantenerlas o al ejecutar consultas que utilizan dichas vistas. Aunque el uso de sugerencias en definiciones de vista indizada no producirá un error de sintaxis en modo de compatibilidad 80, se omiten.

Para obtener más información, vea Sugerencias de tabla (Transact-SQL).

Resolución de vistas con particiones distribuidas

El procesador de consultas de SQL Server optimiza el rendimiento de las vistas con particiones distribuidas. 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.

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

El Procesador de consultas usa en primer lugar OLE DB para recuperar las definiciones de la restricción CHECK de cada tabla miembro. Esto permite al procesador de consultas asignar la distribución de valores clave a las tablas miembro.
El procesador de consultas compara los intervalos de clave especificados en la cláusula WHERE de una instrucción Transact-SQL con el mapa que muestra cómo se distribuyen las filas en las tablas miembro. 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-SQL. 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.

Por ejemplo, considere un sistema en el que una Customers tabla tiene particiones entre Server1 (CustomerID de 1 a 3299999), Server2 (CustomerID de 3300000 a 6599999) y Server3 (CustomerID de 6600000 a 9999999).

Tome como ejemplo el plan de ejecución que se crea para esta consulta ejecutada en 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.

El procesador de consultas SQL Server también puede crear lógica dinámica en planes de ejecución de consultas para instrucciones Transact-SQL cuyos valores clave no se conocen cuando se tiene que crear el plan. Tome como ejemplo este procedimiento almacenado:

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

SQL Server no puede predecir qué valor de clave proporcionará el @CustomerIDParameter parámetro cada vez que se ejecute el procedimiento. Dado que no se puede predecir el valor de clave, el procesador de consultas tampoco puede predecir a qué tabla miembro tendrá que tener acceso. Para tratar este caso, SQL Server crea un plan de ejecución con lógica condicional, lo que se conoce como 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. 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:

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 Server genera ese tipo de planes dinámicos de ejecución, incluso para consultas sin parámetros. El Optimizador puede parametrizar una consulta de modo que el plan de ejecución pueda volver a usarse. 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, y tendrá que utilizar los filtros dinámicos en el plan de ejecución.

Procedimiento almacenado y ejecución del desencadenador

SQL Server almacena únicamente el origen de procedimientos almacenados y desencadenadores. La primera vez que se ejecuta un procedimiento almacenado o un desencadenador, el origen se compila en un plan de ejecución. 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. Si el plan ha quedado anticuado en la memoria, se genera uno nuevo. Este proceso es similar al que sigue SQL Server para procesar todas las instrucciones Transact-SQL. La principal ventaja de rendimiento que tienen los procedimientos almacenados y los desencadenadores en SQL Server en comparación con lotes de Transact-SQL dinámico, es que las instrucciones Transact-SQL siempre son las mismas. Por lo tanto, el motor relacional los hace coincidir fácilmente con los planes de ejecución existentes. El procedimiento almacenado y los planes del desencadenador se reutilizan fácilmente.

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. Esto proporciona mayor flexibilidad para volver a utilizar los planes de ejecución de los procedimientos almacenados y desencadenadores.

Almacenamiento en caché y reutilización del plan de ejecución

SQL Server tiene un bloque de memoria que se utiliza para almacenar planes de ejecución y búferes de datos. 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. La parte del bloque de memoria que se usa para almacenar los planes de ejecución se denomina caché de planes.

La caché de planes tiene dos almacenes para todos los planes compilados:

El almacén de caché Planes de objeto (OBJCP) se usa para los planes relacionados con objetos persistentes (procedimientos almacenados, funciones y desencadenadores).
El almacén de caché Planes SQL (SQLCP) se usa para los planes relacionados con consultas con parámetros automáticos, dinámicas o preparadas.

En la consulta siguiente se proporciona información sobre el uso de memoria de estos dos almacenes de caché:

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

Nota:

La caché de planes tiene dos almacenes adicionales que no se usan para almacenar planes:

El almacén de caché Árboles enlazados (PHDR) se usa para las estructuras de datos utilizadas durante la compilación del plan para vistas, restricciones y valores predeterminados. Estas estructuras se conocen como Árboles enlazados o Árboles algebraicos.
El almacén de caché Procedimientos almacenados extendidos (XPROC) se usa para los procedimientos predefinidos del sistema, como sp_executeSql o xp_cmdshell, que se definen mediante un archivo DLL, no mediante instrucciones Transact-SQL. La estructura en caché solo contiene el nombre de la función y el del archivo DLL en el que se implementa el procedimiento.

Los planes de ejecución de SQL Server tienen los siguientes componentes principales:

Plan compilado (o plan de consulta)
El plan de consulta generado por el proceso de compilación es principalmente una estructura de datos reentrante de solo lectura usada por cualquier número de usuarios. Almacena información sobre:
- Operadores físicos que implementan la operación descrita por operadores lógicos.
- El orden de estos operadores, que determina el orden en el que se accede a los datos, se filtran y se agregan.
- El número de filas estimadas que fluyen a través de los operadores.
  
  Nota:
  
  En versiones más recientes del motor de base de datos, también se almacena información sobre los objetos de estadísticas que se usaron para la estimación de cardinalidad.
- Qué objetos de compatibilidad se deben crear, como tablas de trabajo o archivos de trabajo en tempdb. En el plan de consulta no se almacena ningún contexto de usuario ni información en tiempo de ejecución. 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. La copia en paralelo cubre todas las ejecuciones en paralelo, sin tener en cuenta el grado de paralelismo.
Contexto de ejecución
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. Esta estructura de datos se conoce como contexto de ejecución. Las estructuras de datos de contexto de ejecución se reutilizan, pero su contenido no. Si otro usuario ejecuta la misma consulta, las estructuras de datos se reinicializan con el contexto del nuevo usuario.

Al ejecutar una instrucción Transact-SQL en SQL Server, el motor de base de datos busca primero en la caché de planes para comprobar la existencia de un plan de ejecución para la misma instrucción Transact-SQL. La instrucción Transact-SQL se considera existente si coincide literalmente con una instrucción Transact-SQL ejecutada anteriormente con un plan en caché, carácter por carácter. SQL Server vuelve a usar cualquier plan existente que encuentra para ahorrar la sobrecarga de volver a compilar la instrucción Transact-SQL. Si no existe ningún plan de ejecución, SQL Server genera un nuevo plan de ejecución para la consulta.

Nota:

Los planes de ejecución de algunas instrucciones Transact-SQL no se almacenan en la caché de planes, como, por ejemplo, instrucciones de operaciones masivas que se ejecutan en almacén de filas, o instrucciones que contienen literales de cadena de más de 8 KB. Estos planes solo existen mientras se ejecuta la consulta.

SQL Server tiene un algoritmo eficiente que permite encontrar cualquier plan de ejecución existente para una determinada instrucción Transact-SQL. En la mayor parte de los sistemas los recursos mínimos que utiliza para esta búsqueda son menores que los recursos que se ahorran por poder utilizar de nuevo los planes existentes en lugar de compilar cada instrucción Transact-SQL.

Los algoritmos que hacen coincidir las instrucciones Transact-SQL nuevas con los planes de ejecución existentes no usados de la caché de planes requieren que todas las referencias a objetos sean completas. Por ejemplo, supongamos que Person es el esquema predeterminado para el usuario que ejecuta las instrucciones SELECT siguientes. Aunque en este ejemplo no es necesario que la Person tabla esté completa para ejecutarse, significa que la segunda instrucción no coincide con un plan existente, pero la tercera coincide:

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

Cambiar cualquiera de las siguientes opciones SET para una ejecución determinada afectará a la capacidad de reutilizar planes, ya que el motor de base de datos realiza un plegado constante y estas opciones afectan a los resultados de estas expresiones:

ANSI_NULL_DFLT_OFF

FORCEPLAN

ARITHABORT

DATEFIRST

ANSI_PADDING

NUMERIC_ROUNDABORT

ANSI_NULL_DFLT_ON

LANGUAGE

CONCAT_NULL_YIELDS_NULL

DATEFORMAT

ANSI_WARNINGS

QUOTED_IDENTIFIER

ANSI_NULLS

NO_BROWSETABLE

ANSI_DEFAULTS

Almacenar en caché varios planes para la misma consulta

Las consultas y los planes de ejecución son identificables de forma única en el motor de base de datos, de forma muy similar a una huella digital:

El hash de plan de consulta es un valor hash binario calculado en el plan de ejecución de una consulta concreta y se usa para identificar de forma única planes de ejecución de consulta similares.
El hash de consulta es un valor hash binario calculado en el texto Transact-SQL de una consulta y se usa para identificar las consultas de forma única.

Un plan compilado se puede recuperar de la caché de planes mediante un identificador de plan, que es un identificador transitorio que permanece constante solo mientras el plan permanece en la caché. El identificador del plan es un valor hash derivado del plan compilado del lote completo. El identificador del plan de un plan compilado permanece sin cambios incluso si se vuelven a compilar una o varias instrucciones del lote.

Nota:

Si se ha compilado un plan para un lote en lugar de una sola instrucción, el plan para instrucciones individuales del lote se puede recuperar mediante el identificador del plan y los desplazamientos de la instrucción. La DMV sys.dm_exec_requests contiene las columnas statement_start_offset y statement_end_offset de cada registro, que hacen referencia a la instrucción que se ejecuta actualmente de un lote en ejecución o un objeto almacenado. Para obtener más información, consulte sys.dm_exec_requests (Transact-SQL). La DMV sys.dm_exec_query_stats también contiene estas columnas para cada registro, que hacen referencia a la posición de una instrucción dentro de un lote o un objeto almacenado. Para más información, vea sys.dm_exec_query_stats (Transact-SQL).

El texto Transact-SQL real de un lote se almacena en un espacio de memoria independiente de la caché de planes denominado caché de SQL Manager (SQLMGR). El texto Transact-SQL de un plan compilado se puede recuperar de la caché de SQL Manager mediante un identificador SQL, que es un identificador transitorio que permanece constante solo mientras al menos un plan que le haga referencia permanezca en la caché de planes. El identificador SQL es un valor hash derivado del texto del lote completo y se garantiza que es único para cada lote.

Nota:

Al igual que un plan compilado, el texto de Transact-SQL se almacena por lote, incluidos los comentarios. El identificador SQL contiene el hash MD5 del texto del lote completo y se garantiza que es único para cada lote.

En la consulta siguiente se proporciona información sobre el uso de memoria de la caché de SQL Manager:

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

Hay una relación 1:N entre un identificador SQL y los identificadores de plan. Esa condición se produce cuando la clave de caché para los planes compilados es diferente. Esto puede deberse a un cambio en las opciones SET entre dos ejecuciones del mismo lote.

Observe el siguiente procedimiento almacenado:

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

SET ANSI_DEFAULTS ON
GO

EXEC usp_SalesByCustomer 10
GO

Compruebe lo que se puede encontrar en la caché de planes mediante la consulta siguiente:

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

El conjunto de resultados es el siguiente:

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

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

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

Ahora ejecute el procedimiento almacenado con otro parámetro, pero sin otros cambios en el contexto de ejecución:

EXEC usp_SalesByCustomer 8
GO

Vuelva a comprobar lo que se puede encontrar en la caché de planes. El conjunto de resultados es el siguiente:

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

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

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

Observe que usecounts ha aumentado a 2, lo que significa que el mismo plan almacenado en caché se ha reutilizado tal cual, porque se han reutilizado las estructuras de datos del contexto de ejecución. Ahora cambie la opción SET ANSI_DEFAULTS y ejecute el procedimiento almacenado con el mismo parámetro.

SET ANSI_DEFAULTS OFF
GO

EXEC usp_SalesByCustomer 8
GO

Vuelva a comprobar lo que se puede encontrar en la caché de planes. El conjunto de resultados es el siguiente:

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

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

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

Observe que ahora hay dos entradas en la salida de la DMV sys.dm_exec_cached_plans:

La usecounts columna muestra el valor 1 del primer registro, que es el plan ejecutado una vez con SET ANSI_DEFAULTS OFF.
La usecounts columna muestra el valor 2 en el segundo registro, que es el plan ejecutado con SET ANSI_DEFAULTS ON, porque se ejecutó dos veces.
El otro valor memory_object_address hace referencia a una entrada diferente del plan de ejecución en la caché de planes. Pero el valor sql_handle es el mismo para ambas entradas porque hacen referencia al mismo lote.
- La ejecución con ANSI_DEFAULTS establecido en OFF tiene un nuevo valor plan_handle y está disponible para su reutilización en las llamadas que tengan el mismo conjunto de opciones SET. El nuevo identificador de plan es necesario porque el contexto de ejecución se ha reinicializado debido al cambio de las opciones SET. Pero esto no desencadena una recompilación: las dos entradas hacen referencia al mismo plan y a la misma consulta, como se demuestra en los mismos valores de query_plan_hash y query_hash.

Esto significa que hay dos entradas del plan en caché que se corresponden al mismo lote, y subraya la importancia de asegurarse de que la caché de planes que afecta a las opciones SET sea la misma, cuando se ejecutan varias veces las mismas consultas, para optimizar la reutilización del plan y mantener el tamaño de la caché de planes al mínimo necesario.

Sugerencia

Un problema común es que distintos clientes pueden tener otros valores predeterminados para las opciones SET. Por ejemplo, una conexión realizada a través de SQL Server Management Studio establece QUOTED_IDENTIFIER automáticamente en ON, mientras que SQLCMD establece en QUOTED_IDENTIFIER OFF. La ejecución de las mismas consultas desde estos dos clientes dará como resultado varios planes (como se ha descrito en el ejemplo anterior).

Eliminación de planes de ejecución de la caché de planes

Los planes de ejecución permanecen en la caché de planes mientras haya suficiente memoria para almacenarlos. Cuando existe presión de memoria, el motor de base de datos de SQL Server usa un enfoque basado en costos para determinar qué planes de ejecución se van a quitar de la memoria caché del plan. Para tomar una decisión basada en costos, el motor de base de datos de SQL Server aumenta y disminuye una variable de costo actual para cada plan de ejecución según los siguientes factores.

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. Después, cada vez que un proceso de usuario hace referencia a un plan de ejecución, restablece el costo actual al costo de compilación original; para los planes de ejecución ad hoc, el proceso de usuario aumenta el costo actual. Para todos los planes, el valor máximo del costo actual es el costo de compilación original.

Cuando existe presión de memoria, el motor de base de datos de SQL Server responde quitando los planes de ejecución de la memoria caché del plan. Para determinar qué planes quitar, el motor de base de datos de SQL Server examina repetidamente el estado de cada plan de ejecución y quita los planes cuando su costo actual es cero. Un plan de ejecución con un costo actual cero no se quita automáticamente cuando existe presión de memoria; solo se quita cuando el motor de base de datos de SQL Server examina el plan y el costo actual es cero. Al examinar un plan de ejecución, el motor de base de datos de SQL Server inserta el costo actual hacia cero al reducir el costo actual si una consulta no usa actualmente el plan.

El motor de base de datos de SQL Server examina repetidamente los planes de ejecución hasta que se han quitado suficientes para satisfacer los requisitos de memoria. Mientras existe presión de memoria, un plan de ejecución puede ver su costo incrementado y reducido más de una vez. Cuando ya no existe presión de memoria, el motor de base de datos de SQL Server deja de reducir el costo actual de los planes de ejecución sin usar y todos los planes de ejecución permanecen en la memoria caché del plan, incluso si su costo es cero.

El motor de base de datos de SQL Server usa el monitor de recursos y los subprocesos de trabajo de usuario para liberar memoria de la memoria caché del plan en respuesta a la presión de memoria. 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. El monitor de recursos quita los planes de ejecución de la caché de planes cuando hay presión de memoria global. 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é.

El tamaño máximo de todas las memorias caché es una función del tamaño del grupo de búferes y no puede superar la memoria máxima del servidor. 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.

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. Aplican las directivas del tamaño máximo de la memoria caché única y de las entradas máximas de la memoria caché única.

En los ejemplos siguientes se muestra qué planes de ejecución se quitan de la caché de planes:

Se suele hacer referencia a un plan de ejecución como si su costo nunca llegara a ser cero. El plan permanece en la memoria caché del plan y no se quita a menos que haya presión de memoria y el costo actual sea cero.
Se inserta un plan de ejecución ad hoc y no se hace referencia de nuevo antes de que exista presión de memoria. Dado que los planes ad hoc se inicializan con un costo actual de cero, cuando el motor de base de datos de SQL Server examina el plan de ejecución, verá el costo actual cero y quitará el plan de la memoria caché del plan. El plan de ejecución ad hoc permanece en la memoria caché del plan con un costo actual cero cuando la presión de memoria no existe.

Para quitar manualmente un único plan o todos los planes de la memoria caché, utilice DBCC FREEPROCCACHE. También se puede usar DBCC FREESYSTEMCACHE para borrar cualquier memoria caché, incluida la caché de planes. A partir de SQL Server 2016 (13.x), para ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE borrar la memoria caché del procedimiento (plan) de la base de datos en el ámbito.

Los cambios en algunos parámetros de configuración mediante sp_configure y reconfigure también harán que se eliminen los planes de la caché de planes. Puede encontrar la lista de estos parámetros de configuración en la sección Comentarios del artículo DBCC FREEPROCCACHE. Este tipo de cambios de configuración registrarán el siguiente mensaje informativo en el registro de errores:

SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

Volver a compilar planes de ejecución

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. SQL Server detecta los cambios que invalidan un plan de ejecución y marcan el plan como no válido. Después, debe volver a compilarse un nuevo plan para la próxima conexión que ejecute la consulta. Las condiciones que hacen que un plan no sea válido son:

Los cambios realizados en una tabla o vista a las que hace referencia la consulta (ALTER TABLE y ALTER VIEW).
Los cambios realizados en un procedimiento único, que descartaría todos los planes de dicho procedimiento de la memoria caché (ALTER PROCEDURE).
Cambios en los índices que utilizan el plan de ejecución.
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.
Quitar un índice que utiliza el plan de ejecución.
Una llamada explícita a 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).
Para tablas con desencadenadores, si el número de filas de las tablas insertadas o eliminadas crece significativamente.
Ejecutar un procedimiento almacenado mediante la opción WITH RECOMPILE .

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.

En las versiones de SQL Server anteriores a 2005, cada vez que una instrucción dentro de un lote provoca la recompilación, todo el lote, ya sea enviado a través de un procedimiento almacenado, desencadenador, lote ad hoc o instrucción preparada, se recompiló. A partir de SQL Server 2005 (9.x), solo se vuelve a compilar la instrucción dentro del lote que desencadena la recompilación. Además, hay tipos adicionales de recompilaciones en SQL Server 2005 (9.x) y versiones posteriores debido a su conjunto de características expandido.

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. Por lo tanto, estas sanciones se evitan para las demás instrucciones del lote que no tienen que volver a compilarse.

El evento extendido sql_statement_recompile (xEvent) envía informes de recompilaciones de nivel de instrucción. Este xEvent se produce cuando un lote de cualquier tipo requiere una recompilación de nivel de instrucción. Esto incluye procedimientos almacenados, desencadenadores, lotes ad hoc y consultas. Los lotes se pueden enviar a través de varias interfaces, como sp_executesql, SQL dinámico, Métodos de preparación o Métodos Execute. 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. La tabla siguiente contiene las posibles razones:

Esquema modificado

Estadísticas modificadas

Compilación diferida

Opción SET modificada

Tabla temporal modificada

Conjunto de filas remoto modificado

Permiso FOR BROWSE modificado

Entorno de notificación de consultas modificado

Vista con particiones modificada

Opciones de cursor modificadas

OPTION (RECOMPILE) solicitado

Plan parametrizado vaciado

Plan que afecta a la versión de la base de datos modificado

Directiva que fuerza el plan del almacén de consultas modificada

Error al forzar el plan del almacén de consultas

Falta el plan en el almacén de consultas

Nota:

En las versiones de SQL Server en las que no hay xEvents disponibles, se puede usar el evento de seguimiento SP:Recompile de SQL Server Profiler para crear informes de recompilaciones a nivel de instrucción.

El evento de seguimiento SQL:StmtRecompile también informa de recompilaciones de instrucciones, y se puede usar para realizar el seguimiento de las recompilaciones y depurarlas.

Mientras que SP:Recompile solo genera para procedimientos almacenados y desencadenadores, SQL:StmtRecompile genera para procedimientos almacenados, desencadenadores, lotes ad hoc, lotes que se ejecutan mediante sp_executesql, consultas preparadas y SQL dinámico. 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. Los códigos se describen aquí.

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.

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. 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. 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. Dado que estas tablas se crean en tempdb, la recompilación de consultas que tienen acceso a ellas depende de la configuración de AUTO_UPDATE_STATISTICS en tempdb.

En SQL Server anterior a 2005, las consultas continúan recompilando en función de los cambios de cardinalidad en las tablas insertadas y eliminadas del desencadenador DML, incluso cuando esta configuración es OFF.

Reutilización de parámetros y planes de ejecución

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.

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 .

La única diferencia entre las dos instrucciones SELECT siguientes son los valores que se comparan en la cláusula WHERE :

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

SELECT *
FROM AdventureWorks2022.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 . Aunque el objetivo es que SQL Server reconozca siempre que las instrucciones generan esencialmente el mismo plan y reutilizan los planes, SQL Server a veces no detecta esto en instrucciones complejas de Transact-SQL.

Separar las constantes de la instrucción Transact-SQL mediante parámetros ayuda al motor relacional a reconocer los planes duplicados. Puede utilizar los parámetros de varias maneras:

En Transact-SQL, utilice sp_executesql:

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

Este método se recomienda para las scripts de Transact-SQL, procedimientos almacenados o desencadenadores que generan dinámicamente instrucciones SQL.

Con ADO, OLE DB y ODBC, utilice marcadores de parámetros. 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. Por ejemplo, podría hacer lo siguiente en una aplicación ODBC:
- Utilice SQLBindParameter para enlazar una variable de tipo entero al primer marcador de parámetros en una instrucción SQL.
- Coloque el valor entero en la variable.
- Ejecute la instrucción y especifique el marcador de parámetros (?):
```
SQLExecDirect(hstmt,
  "SELECT *
  FROM AdventureWorks2022.Production.Product
  WHERE ProductSubcategoryID = ?",
  SQL_NTS);
```
El proveedor de SQL Server Native Client OLE DB y el controlador de SQL Server Native Client ODBC incluidos en SQL Server usan sp_executesql para enviar instrucciones a SQL Server cuando se utilizan marcadores de parámetros en las aplicaciones.
Para diseñar procedimientos almacenados, que utilizan parámetros por diseño.

Si no compila explícitamente parámetros en el diseño de las aplicaciones, también puede confiar en el optimizador de consultas de SQL Server para parametrizar automáticamente determinadas consultas mediante el comportamiento predeterminado de la parametrización simple. 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.

Cuando se habilita la parametrización forzada, se puede producir la parametrización simple. Por ejemplo, la consulta siguiente no se puede parametrizar según las reglas de parametrización forzada:

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

Sin embargo, se puede parametrizar según las reglas de parametrización simple. Cuando la parametrización forzada se intenta pero falla, después se sigue intentando la parametrización simple.

Parametrización simple

En SQL Server, el uso de parámetros o marcadores de parámetros en instrucciones Transact-SQL aumenta la capacidad del motor relacional para que coincida con nuevas instrucciones Transact-SQL con los planes de ejecución compilados anteriormente existentes.

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 .

Si una instrucción Transact-SQL se ejecuta sin parámetros, SQL Server parametriza la instrucción internamente para aumentar las posibilidades de hacerla coincidir con un plan de ejecución existente. Este proceso se denomina parametrización simple. En las versiones de SQL Server anteriores a 2005, el proceso se denominaba parametrización automática.

Considere esta instrucción:

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

El valor 1 del final de la instrucción puede especificarse como un parámetro. 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. Debido a esta parametrización simple, SQL 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:

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

SELECT * FROM AdventureWorks2022.Production.Product
WHERE ProductSubcategoryID = 4;

Al procesar instrucciones Transact-SQL complejas, el motor relacional puede tener dificultades para determinar qué expresiones pueden parametrizarse. Para aumentar la capacidad del motor relacional de hacer coincidir instrucciones Transact-SQL complejas con planes de ejecución existentes y sin usar, especifique explícitamente los parámetros mediante marcadores de sp_executesql parámetros o .

Nota:

Cuando los +operadores aritméticos , -*, /, o % se usan para realizar la conversión implícita o explícita de valores constantes int, smallint, tinyint o bigint en los tipos de datos float, real, decimal o numérico, SQL Server aplica reglas específicas para calcular el tipo y la precisión de los resultados de la expresión. Sin embargo, estas reglas varían en función de si la consulta se parametriza o no. Por lo tanto, expresiones similares utilizadas en consultas pueden, en ciertos casos, producir resultados diferentes.

En el comportamiento predeterminado de parametrización simple, SQL Server parametriza una clase relativamente pequeña de consultas. 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. 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.

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.

Sugerencia

Cuando se usa una solución de asignación relacional de objetos (ORM), como Entity Framework (EF), es posible que las consultas de aplicación como árboles de consulta LINQ manuales o determinadas consultas SQL sin procesar no se parametricen, lo que afecta a la nueva utilización del plan y a la capacidad de realizar un seguimiento de las consultas en el Almacén de consultas. Para más información, consulte Almacenamiento en caché y parametrización de consultas de EF y Consultas SQL sin formato de EF.

Parametrización forzada

Puede reemplazar el comportamiento predeterminado de parametrización simple de SQL Server si especifica que, con algunas limitaciones, todas las instrucciones SELECT, INSERT, UPDATEy DELETE de una base de datos incluyan parámetros. La parametrización forzada se habilita al establecer la opción PARAMETERIZATION en FORCED en la instrucción ALTER DATABASE . 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. 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.

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. Las excepciones son los literales que aparecen en las siguientes construcciones de consulta:

InstruccionesINSERT...EXECUTE .
Instrucciones incluidas en los cuerpos de procedimientos almacenados, desencadenadores o funciones definidas por el usuario. SQL Server ya vuelve a utilizar planes de consultas para estas rutinas.
Instrucciones preparadas que ya incluyen parámetros en la aplicación del lado cliente.
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 . Si el método aparece en un contexto en el que sus argumentos no se parametrizarían, el resto de la instrucción se parametriza.
Instrucciones dentro de un cursor de Transact-SQL. (Las instruccionesSELECT contenidas en cursores de la API incluyen parámetros).
Construcciones de consulta desaprobadas.
Cualquier instrucción que se ejecuta en el contexto de ANSI_PADDING o ANSI_NULLS establecido en OFF.
Instrucciones que contienen más de 2.097 literales aptos para parametrización.
Instrucciones que hacen referencia a variables, como WHERE T.col2 >= @bb.
Instrucciones que contienen la sugerencia de consulta RECOMPILE .
Instrucciones que contienen una cláusula COMPUTE .
Instrucciones que contienen una cláusula WHERE CURRENT OF .

Además, las siguientes cláusulas de consulta no incluyen parámetros. En estos casos, solo las cláusulas no están parametrizadas. Otras cláusulas de la misma consulta podrían ser candidatas para la parametrización forzada.

El <select_list> de cualquier instrucción SELECT. Esto incluye listas SELECT de subconsultas y SELECT listas dentro de instrucciones INSERT.
Instrucciones SELECT de subconsulta que aparezcan dentro de una instrucción IF .
Las cláusulas TOP, TABLESAMPLE, HAVING, GROUP BY, ORDER BY, OUTPUT...INTO o FOR XML de una consulta.
Argumentos, directos o como subexpresiones, a OPENROWSET, OPENQUERY, OPENDATASOURCE, OPENXML, o a cualquier operador FULLTEXT .
Los argumentos pattern y escape_character de una cláusula LIKE .
El argumento de estilo de una cláusula CONVERT .
Constantes de tipo entero dentro de una cláusula IDENTITY .
Constantes especificadas mediante la sintaxis de extensiones ODBC.
Expresiones que admiten el doblado de constantes y son argumentos de los operadores +, -, *, / y %. Al considerar la idoneidad para la parametrización forzada, SQL Server considera que una expresión se puede plegar constantemente cuando se cumple alguna de las condiciones siguientes:
- No aparecen columnas, variables ni subconsultas en la expresión.
- La expresión contiene una cláusula CASE .
Argumentos para cláusulas de sugerencias de consulta. 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 number de la sugerencia de consulta MAXRECURSION.

La parametrización se produce a nivel de instrucciones Transact-SQL individuales. En otras palabras, las instrucciones individuales de un lote incluyen parámetros. Tras la compilación, una consulta con parámetros se ejecuta en el contexto del lote en el que se envió originalmente. Si se almacena en caché un plan de ejecución para una consulta, puede determinar si la consulta se parametrizó haciendo referencia a la columna sql de la sys.syscacheobjects vista de administración dinámica. 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).

Nota:

Los nombres de parámetros son arbitrarios. Los usuarios o las aplicaciones no deben basarse en un determinado orden de nombres. Además, lo siguiente puede cambiar entre versiones de SQL Server y actualizaciones de Service Pack: nombres de parámetro, elección de literales parametrizados y espaciado en el texto con parámetros.

Tipos de datos de parámetros

Cuando SQL Server parametriza literales, los parámetros se convierten a los siguientes tipos de datos:

Literales enteros cuyo tamaño se ajustaría en caso contrario dentro del tipo de datos int parametrizar en int. Literales enteros más grandes que son partes de predicados que implican cualquier operador de comparación (incluye <, <=, =>!<!><>>=!=ANYSOMEALLBETWEENy IN) parametrizar con 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 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. Los literales numéricos de punto fijo que no forman parte de predicados que incluyen operadores de comparación se parametrizan a numéricos cuyas precisión y escala son lo suficientemente grandes como para admitir su tamaño.
Los literales numéricos de punto flotante se parametrizan a 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.
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.
Los literales de tipo binario se parametrizan a varbinary(8000) si el literal se ajusta a 8000 bytes. Si tiene más de 8000 bytes, se convierte a varbinary(max).
Los literales de tipo money se parametrizan a money.

Directrices para usar la parametrización forzada

Tenga en cuenta lo siguiente al establecer la opción PARAMETERIZATION en FORCED:

La parametrización forzada, en efecto, cambia las constantes literales de una consulta a parámetros al compilar una consulta. Por tanto, puede que el optimizador de consultas elija planes menos adecuados para las consultas. 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. Puede que también elija planes menos adecuados para consultas formuladas en tablas con particiones y vistas con particiones distribuidas. La parametrización forzada no se debe usar para entornos que dependen en gran medida de vistas indexadas e índices en columnas calculadas. Por lo general, los administradores de bases de datos experimentados solo deben usar la PARAMETERIZATION FORCED opción después de determinar que hacerlo no afecta negativamente al rendimiento.
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.
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. 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.
El establecimiento de la opción PARAMETERIZATION es una operación en línea que requiere bloqueos no exclusivos de la base de datos.
El valor actual de la opción PARAMETERIZATION se mantiene al volver a adjuntar o restaurar una base de datos.

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. 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. Se utilizanguías de planes con este fin.

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.

Preparar instrucciones SQL

El motor relacional de SQL Server proporciona compatibilidad completa para preparar las instrucciones Transact-SQL antes de que se ejecuten. Si una aplicación necesita ejecutar una instrucción Transact-SQL varias veces, puede utilizar la API de bases de datos para lo siguiente:

Preparar la instrucción una vez. Esto compila la instrucción Transact-SQL en un plan de ejecución.
Ejecutar el plan de ejecución compilado previamente cada vez que necesite ejecutar la instrucción. Esto evita tener que volver a compilar la instrucción Transact-SQL después de la primera ejecución. Las funciones y los métodos de la API controlan la preparación y la ejecución de las instrucciones. No forma parte del lenguaje Transact-SQL. El modelo de preparación y ejecución para ejecutar instrucciones Transact-SQL es compatible con el proveedor OLE DB del cliente nativo de SQL Server y con el controlador ODBC del cliente nativo de SQL Server. En una solicitud de preparación, el proveedor o el controlador envían la instrucción a SQL Server con una solicitud para preparar la instrucción. SQL Server compila un plan de ejecución y devuelve un identificador para ese plan al proveedor o al controlador. En una solicitud de ejecución, el proveedor o el controlador envían al servidor una solicitud para ejecutar el plan asociado al identificador.

Las instrucciones preparadas no se pueden usar para crear objetos temporales en SQL Server. Las instrucciones preparadas no pueden hacer referencia a procedimientos almacenados del sistema que crean objetos temporales, como tablas temporales. Estos procedimientos deben ejecutarse directamente.

El uso excesivo del modelo de preparación y ejecución puede reducir el rendimiento. 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. El hecho de preparar y ejecutar una instrucción Transact-SQL que solo se ejecuta una vez requiere un recorrido de ida y vuelta adicional en la red: uno para preparar la instrucción y otro para ejecutarla.

Preparar una instrucción es más eficaz si se utilizan marcadores de parámetros. 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 . Hay dos maneras en que la aplicación puede llevarlo cabo.

En la primera, la aplicación puede ejecutar una consulta independiente para cada producto que se solicita:

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

En la segunda, la aplicación hace lo siguiente:

Prepara una instrucción que contiene un marcador de parámetros (?):

SELECT * FROM AdventureWorks2022.Production.Product
WHERE ProductID = ?;

Enlaza una variable de programa al marcador de parámetros.
Cada vez que se necesite información de productos, llena la variable enlazada con el valor de clave y ejecuta la instrucción.

La segunda forma es más eficaz cuando la instrucción se ejecuta más de tres veces.

En SQL 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 Server reutiliza los planes de ejecución. SQL Server dispone de algoritmos eficientes para hacer corresponder las instrucciones Transact-SQL actuales con los planes de ejecución generados para ejecuciones anteriores de la misma instrucción Transact-SQL. Si una aplicación ejecuta varias veces una instrucción Transact-SQL con marcadores de parámetros, SQL 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). El modelo de preparación y ejecución sigue teniendo estas ventajas:

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-SQL.
La aplicación puede controlar cuándo se crea el plan de ejecución y cuándo se vuelve a utilizar.
El modelo de preparación y ejecución se puede transportar a otras bases de datos, incluidas las versiones anteriores de SQL Server.

Confidencialidad de parámetros

La confidencialidad de los parámetros, también conocida como "detección de parámetros", hace referencia a un proceso por el que SQL Server "examina" los valores de parámetro actuales durante la compilación o la recompilación, y lo pasa al optimizador de consultas para que se puedan usar para generar planes de ejecución de consultas potencialmente más eficaces.

Los valores de parámetros se examinan durante la compilación o la recompilación de los siguientes tipos de lotes:

Procedimientos almacenados
Consultas enviadas mediante sp_executesql
Consultas preparadas

Para obtener más información sobre cómo solucionar problemas de detección de parámetros incorrectos, consulte:

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. 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. En concreto, en el caso de los parámetros no se examinan los valores que acompañan a la llamada de invocación del lote.

Procesamiento de consultas en paralelo

SQL 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). Dado que SQL 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.

Durante la optimización de una consulta, SQL Server busca operaciones de consulta o índice que podrían beneficiarse de la ejecución en paralelo. Para estas consultas, SQL Server inserta operadores de intercambio en el plan de ejecución de consultas para preparar su ejecución en paralelo. 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. 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.

Importante

Algunas construcciones impiden la capacidad de SQL Server de usar paralelismo en todo el plan de ejecución, o partes o en el plan de ejecución.

Entre las construcciones que impiden el paralelismo se incluyen las siguientes:

UDF escalares
Para obtener más información sobre las funciones escalares definidas por el usuario, vea Crear funciones definidas por el usuario. A partir de SQL Server 2019 (15.x), el motor de base de datos de SQL Server tiene la capacidad de insertar estas funciones y desbloquear el uso del paralelismo durante el procesamiento de consultas. Para obtener más información sobre la inserción de UDF escalares, vea Procesamiento de consultas inteligente en bases de datos SQL.
Remote Query
Para obtener más información sobre Remote Query, vea Referencia de operadores lógicos y físicos del plan de presentación.
Cursores dinámicos
Para obtener más información sobre los cursores, vea DECLARE CURSOR.
Consultas recursivas
Para obtener más información sobre la recursión, vea Instrucciones para definir y usar expresiones de tabla comunes recursivas y Recursion in T-SQL (La recursión en T-SQL).
Funciones con valores de tabla de múltiples instrucciones (MSTVF)
Para obtener más información sobre las MSTVF, vea Creación de funciones definidas por el usuario (motor de base de datos).
Palabra clave TOP
Para obtener más información, vea TOP (Transact-SQL).

Un plan de ejecución de consultas puede contener el atributo NonParallelPlanReason en el elemento QueryPlan , que describe por qué no se usó el paralelismo. Los valores de este atributo incluyen:

Valor NonParallelPlanReason	Descripción
MaxDOPSetToOne	El grado máximo de paralelismo está establecido en 1.
EstimatedDOPIsOne	El grado estimado de paralelismo es 1.
NoParallelWithRemoteQuery	El paralelismo no se admite para las consultas remotas.
NoParallelDynamicCursor	Los planes paralelos no se admiten para los cursores dinámicos.
NoParallelFastForwardCursor	Los planes paralelos no se admiten para los cursores de avance rápido.
NoParallelCursorFetchByBookmark	Los planes paralelos no se admiten para los cursores que capturan por marcador.
NoParallelCreateIndexInNonEnterpriseEdition	La creación de índices en paralelo no se admiten para Enterprise Edition.
NoParallelPlansInDesktopOrExpressEdition	Los planes paralelos no se admiten para Desktop y Express Edition.
NonParallelizableIntrinsicFunction	La consulta hace referencia a una función intrínseca que no se puede paralelizar.
CLRUserDefinedFunctionRequiresDataAccess	El paralelismo no se admite para una UDF de CLR que requiere acceso a datos.
TSQLUserDefinedFunctionsNotParallelizable	La consulta hace referencia a una función definida por el usuario de T-SQL que no era paralelizable.
TableVariableTransactionsDoNotSupportParallelNestedTransaction	Las transacciones de variables de tabla no admiten transacciones anidadas paralelas.
DMLQueryReturnsOutputToClient	La consulta DML devuelve la salida al cliente y no es paralelizable.
MixedSerialAndParallelOnlineIndexBuildNotSupported	La combinación de planes de serie y paralelos no se admite para una sola compilación de índice en línea.
CouldNotGenerateValidParallelPlan	Error al comprobar el plan paralelo; se realizará una conmutación por recuperación a la opción de serie.
NoParallelForMemoryOptimizedTables	El paralelismo no se admite para las tablas OLTP en memoria a las que se hace referencia.
NoParallelForDmlOnMemoryOptimizedTable	El paralelismo no se admite para DML en una tabla OLTP en memoria.
NoParallelForNativelyCompiledModule	El paralelismo no se admite para los módulos compilados de forma nativa a los que se hace referencia.
NoRangesResumableCreate	Error al generar intervalos para una operación de creación reanudable.

Tras la inserción de operadores de intercambio, el resultado es un plan de ejecución de consultas en paralelo. Un plan de ejecución de consultas en paralelo puede usar más de un subproceso de trabajo. Un plan de ejecución en serie, usado por una consulta no paralela (en serie), solo usa un subproceso de trabajo para su ejecución. 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.

El grado de paralelismo (DOP) determina el número máximo de CPU que se usan; no significa el número de subprocesos de trabajo que se usan. El límite de DOP se establece por tarea. No es un límite por solicitud ni por consulta. Esto significa que durante una ejecución de consultas en paralelo, una sola solicitud puede generar varias tareas que se asignan a un programador. Se pueden usar más procesadores que los especificados por MAXDOP de manera simultánea en un momento dado de la ejecución de la consulta, cuando se ejecutan varias tareas de forma simultánea. Para más información, consulte la guía de arquitectura de subprocesos y tareas.

El optimizador de consultas de SQL Server no usa un plan de ejecución paralelo para una consulta si se cumple alguna de las condiciones siguientes:

El plan de ejecución serie es trivial o no supera el umbral de costo para la configuración del paralelismo.
El plan de ejecución serie tiene un costo de subárbol estimado inferior al de cualquier plan de ejecución paralelo explorado por el optimizador.
La consulta contiene operadores escalares o relacionales que no se pueden ejecutar en paralelo. 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.

Nota:

El costo total estimado del subárbol de un plan paralelo puede ser menor que el umbral de costo para la configuración del paralelismo. Esto indica que el costo total estimado del subárbol del plan serie lo superó y se eligió el plan de consulta con el menor costo total estimado del subárbol.

Grado de paralelismo (DOP)

SQL 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). Para ello utiliza los siguientes criterios:

Si SQL Server se ejecuta en un equipo que tiene más de un microprocesador o CPU, como un equipo de multiprocesamiento simétrico (SMP). Solo los equipos con más de una CPU pueden utilizar consultas en paralelo.
Si hay suficientes subprocesos de trabajo disponibles. Cada operación de consulta o índice requiere que se ejecute un determinado número de subprocesos de trabajo. 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. Cuando no se puede cumplir el requisito de subproceso de trabajo del plan paralelo para un grado específico de paralelismo, el motor de base de datos de SQL Server reduce automáticamente o abandona completamente el plan paralelo en el contexto de carga de trabajo especificado. Es entonces cuando ejecuta un plan en serie (un subproceso de trabajo).
El tipo de operación de consulta o índice ejecutado. 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. Por ejemplo, las combinaciones de tablas grandes, agregaciones importantes y la ordenación de grandes conjuntos de resultados son buenos candidatos. 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. Para distinguir entre las consultas que se benefician del paralelismo y las que no se benefician, el motor de base de datos de SQL Server compara el costo estimado de ejecutar la operación de consulta o índice con el umbral de costo para el valor de paralelismo . 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.
Si hay un número suficiente de filas que se van a procesar. Si el optimizador de consultas determina que el número de filas es demasiado bajo, no introduce operadores de intercambio para distribuir las filas. Por lo tanto, los operadores se ejecutan en serie. 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.
Si las estadísticas de distribución actuales están disponibles. Si no es posible el grado más alto de paralelismo, los grados inferiores se consideran antes de que se abandone el plan paralelo. Por ejemplo, al crear un índice agrupado en una vista, no se pueden evaluar las estadísticas de distribución, ya que el índice agrupado aún no existe. En este caso, el motor de base de datos de SQL Server no puede proporcionar el grado más alto de paralelismo para la operación de índice. Sin embargo, algunos operadores, como sorting y scannig, se siguen beneficiando de la ejecución en paralelo.

Nota:

Las operaciones de índices en paralelo están únicamente disponibles en las ediciones Enterprise, Developer y Evaluation de SQL Server.

En tiempo de ejecución, el motor de base de datos de SQL Server determina si la carga de trabajo actual del sistema y la información de configuración descritas anteriormente permiten la ejecución en paralelo. Si se garantiza la ejecución en paralelo, el motor de base de datos de SQL Server determina el número óptimo de subprocesos de trabajo y distribuye la ejecución del plan paralelo entre esos subprocesos de trabajo. 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. El motor de base de datos de SQL Server 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 memoria caché del plan. 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.

Los operadores de actualización y eliminación de un plan de ejecución de consultas en paralelo se ejecutan en serie, pero la cláusula WHERE de una instrucción UPDATE o DELETE se puede ejecutar en paralelo. Los cambios reales de los datos se aplican en serie a la base de datos.

Hasta SQL Server 2012 (11.x), el operador insert también se ejecuta en serie. Sin embargo, la parte SELECT de una instrucción INSERT se puede ejecutar en paralelo. Los cambios reales de los datos se aplican en serie a la base de datos.

A partir de SQL Server 2014 (12.x) y el nivel de compatibilidad de base de datos 110, la SELECT … INTO instrucción se puede ejecutar en paralelo. Otras formas de operadores de inserción funcionan de la misma manera que se describe para SQL Server 2012 (11.x).

A partir de SQL Server 2016 (13.x) y el nivel de compatibilidad de la base de datos 130, la INSERT … SELECT instrucción se puede ejecutar en paralelo al insertar en montones o índices de almacén de columnas agrupados (CCI) y mediante la sugerencia TABLOCK. Las inserciones en las tablas temporales locales (identificadas por el prefijo #) y las tablas temporales globales (identificadas por prefijos ##) también se habilitan para el paralelismo mediante la sugerencia TABLOCK. Para más información, vea INSERT (Transact-SQL).

Los cursores estáticos y los dinámicos pueden llenarse mediante planes de ejecución en paralelo. Sin embargo, el comportamiento de los cursores dinámicos solo puede proporcionarse mediante la ejecución en serie. El optimizador de consultas siempre genera un plan de ejecución en serie para una consulta que es parte de un cursor dinámico.

Invalidación de grados de paralelismo

El grado de paralelismo establece el número de procesadores que se van a utilizar en la ejecución de planes paralelos. Esta configuración se puede establecer en varios niveles:

En el nivel de servidor, mediante la opción de configuración de servidor máximo grado de paralelismo (MAXDOP).
Se aplica a: SQL Server

Nota:

SQL Server 2019 (15.x) presenta recomendaciones automáticas para establecer la opción de configuración del servidor MAXDOP durante el proceso de instalación. La interfaz de usuario del programa de instalación permite aceptar la configuración recomendada o introducir su propio valor. Para obtener más información, vea la página Configuración del Motor de base de datos: MaxDOP.
En el nivel de carga de trabajo, con la opción de configuración del grupo de cargas de trabajo de Resource Governor MAX_DOP.
Se aplica a: SQL Server
En el nivel de base de datos, mediante la configuración de ámbito de base de datosMAXDOP.
Se aplica a: SQL Server y Azure SQL Database
Nivel de instrucción de consulta o índice, con la sugerencia de consultaMAXDOP o la opción de índice MAXDOP. 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. De este modo es posible equilibrar los recursos utilizados por una operación de índice con los de usuarios simultáneos.
Se aplica a: SQL Server and Azure SQL Database

Establecer la opción grado máximo de paralelismo en 0 (valor predeterminado) permite a SQL Server usar todos los procesadores disponibles hasta un máximo de 64 procesadores en una ejecución de plan paralelo. Aunque SQL Server establece un destino en tiempo de ejecución de 64 procesadores lógicos cuando la opción MAXDOP está establecida en 0, se puede establecer manualmente un valor diferente si es necesario. Si se establece MAXDOP en 0 para consultas o índices, SQL Server puede utilizar todos los procesadores disponibles, hasta un máximo de 64, para las consultas o los índices especificados en una ejecución de planes en paralelo. MAXDOP no es un valor aplicado para todas las consultas paralelas, sino un destino provisional para todas las consultas aptas para paralelismo. 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.

Sugerencia

Para obtener más información, consulte recomendaciones de MAXDOP para obtener instrucciones sobre cómo configurar MAXDOP en el servidor, la base de datos, la consulta o el nivel de sugerencia.

Ejemplo de consulta paralela

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. Esta consulta muestra el número de dichos pedidos agrupados por orden de prioridad y de manera ascendente.

Este ejemplo utiliza una tabla y nombres de columnas teóricos.

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:

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:

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

Diagram of a parallel plan.

El plan en paralelo contiene tres operadores Parallelism. 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. Esto produce varios flujos exclusivos. 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. Ambos reparten el tipo de intercambio. Es decir, solo reconstruyen los datos de los flujos que producen el mismo número de flujos en la salida que en la entrada. Este número de flujos equivale al grado de paralelismo.

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. De este modo, los mismos valores de L_ORDERKEY finalizan en el mismo flujo de salida. 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.

El operador Parallelism situado encima del operador Index Seek reparte sus flujos de entrada con el valor de O_ORDERKEY. Dado que su entrada no está ordenada en los O_ORDERKEY valores de columna y esta es la columna de combinación en el Merge Join operador , el operador Sort entre el paralelismo y los operadores Merge Join asegúrese de que la entrada está ordenada para el Merge Join operador en las columnas de combinación. El operador Sort, al igual que el operador Merge Join, se ejecuta en paralelo.

El primer operador Parallelism recopila los resultados de varios flujos en un solo flujo. 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. Como este plan tiene dos segmentos de intercambio y un grado de paralelismo igual a 4, usa ocho subprocesos de trabajo.

Para obtener más información sobre los operadores usados en este ejemplo, vea la Referencia de operadores lógicos y físicos del plan de presentación.

Operaciones de índice paralelas

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.

Nota:

Las operaciones de índice paralelo solo están disponibles en Enterprise Edition, a partir de SQL Server 2008 (10.0.x).

SQL Server usa los mismos algoritmos para determinar el grado de paralelismo (el número total de subprocesos de trabajo independientes que se van a ejecutar) para las operaciones de índice que para otras consultas. 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 . 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.

Cuando el motor de base de datos de SQL Server compila un plan de ejecución de índices, el número de operaciones paralelas se establece en el valor más bajo de entre los siguientes:

El número de microprocesadores o CPU en el equipo.
El número especificado en la opción de configuración del servidor "grado máximo de paralelismo".
Número de CPU que aún no superan un umbral de trabajo realizado para subprocesos de trabajo de SQL Server.

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. Si cinco de las CPU del equipo superan el umbral de trabajo de SQL Server cuando se compila un plan de ejecución de índice, el plan de ejecución especifica solo tres subprocesos de trabajo paralelos.

Entre las fases principales de una operación de índice en paralelo se incluyen lo siguiente:

Un subproceso de trabajo de coordinación recorre la tabla rápida y aleatoriamente para calcular la distribución de las claves de índice. 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. 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. Si no se pueden establecer intervalos de claves suficientes para usar todas las CPU, el grado de paralelismo se reduce en consecuencia.
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. 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. Cada subproceso de trabajo crea una estructura de índice para las filas en su rango con clave. En el caso de un índice con particiones, cada subproceso de trabajo genera un número especificado de particiones. Las particiones no se comparten entre los subprocesos de trabajo.
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. Esta fase solo se aplica a operaciones de índice sin conexión.

Las instrucciones individuales CREATE TABLE o ALTER TABLE pueden tener varias restricciones que requieren la creación de un índice. 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.

Arquitectura de consultas distribuidas

Microsoft SQL Server admite dos métodos para hacer referencia a orígenes de datos OLE DB heterogéneos en instrucciones Transact-SQL:

Nombres de servidores vinculados
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. Se puede hacer referencia a los objetos de estos servidores vinculados en instrucciones Transact-SQL mediante nombres que consten de cuatro partes. Por ejemplo, si un nombre de servidor vinculado de DeptSQLSrvr se define en otra instancia de SQL Server, la instrucción siguiente hace referencia a una tabla de ese servidor:
```
SELECT JobTitle, HireDate
FROM DeptSQLSrvr.AdventureWorks2022.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. Se puede hacer referencia a este conjunto de filas del mismo modo que a una tabla en las instrucciones Transact-SQL.
Nombres de conectores ad hoc
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. A continuación, se puede hacer referencia al conjunto de filas del mismo modo que se hace referencia a una tabla en instrucciones Transact-SQL:
```
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
      'c:\MSOffice\Access\Samples\Northwind.mdb';'Admin';'';
      Employees);
```

SQL Server utiliza OLE DB para la comunicación entre el motor relacional y el motor de almacenamiento. El motor relacional divide cada instrucción Transact-SQL en un grupo de operaciones sobre conjuntos de filas OLE DB simples abiertos por el motor de almacenamiento desde las tablas base. Esto significa que el motor relacional también puede abrir conjuntos de filas OLE DB simples en cualquier origen de datos OLE DB.

Diagram of OLE DB 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.

Es necesario que haya un proveedor OLE DB en el servidor que ejecute SQL Server por cada origen de datos OLE DB al que se tenga acceso como servidor vinculado. El conjunto de operaciones Transact-SQL que puede utilizarse con un origen de datos OLE DB específico depende de las funcionalidades del proveedor OLE DB.

Para cada instancia de SQL Server, los miembros del sysadmin rol fijo de servidor pueden habilitar o deshabilitar el uso de nombres de conector ad hoc para un proveedor OLE DB mediante la propiedad SQL Server DisallowAdhocAccess . Cuando se habilita el acceso ad hoc, cualquier usuario que haya iniciado sesión en esa instancia puede ejecutar instrucciones Transact-SQL que contengan nombres de conector ad hoc, haciendo referencia a cualquier origen de datos de la red a la que se pueda acceder mediante ese proveedor OLE DB. Para controlar el acceso a los orígenes de datos, los miembros del rol pueden deshabilitar el sysadmin acceso ad hoc para ese proveedor OLE DB, lo que limita a los usuarios solo a esos orígenes de datos a los que hacen referencia los nombres de servidor vinculados definidos por los administradores. De forma predeterminada, el acceso ad hoc está habilitado para el proveedor OLE DB de SQL Server y deshabilitado para todos los demás proveedores OLE DB.

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 Server. SQL Server suplanta el inicio de sesión adecuadamente para los inicios de sesión de Windows; sin embargo, esto no es posible para los inicios de sesión de SQL Server. Esto puede permitir que un usuario de consulta distribuida acceda a otro origen de datos para el que no tenga permisos, pero la cuenta con la que se ejecuta el servicio SQL Server tiene permisos. Utilice sp_addlinkedsrvlogin para definir los inicios de sesión específicos con acceso autorizado al servidor vinculado correspondiente. Este control no está disponible para los nombres ad hoc, por lo que debe tener cuidado al habilitar un proveedor OLE DB para el acceso ad hoc.

Cuando es posible, SQL Server inserta operaciones relacionales como combinaciones, restricciones, proyecciones, ordenaciones y operaciones "group by" al origen de datos OLE DB. SQL Server no examina de forma predeterminada la tabla base en SQL Server y realiza las propias operaciones relacionales. SQL Server consulta el proveedor de OLE DB para determinar el nivel de gramática SQL que admite y, en función de esa información, inserta tantas operaciones relacionales como es posible en el proveedor.

SQL 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. Esto permite que el optimizador de consultas de SQL Server analice mejor el patrón de datos del origen de datos según los requisitos de cada instrucción Transact-SQL, lo que mejora su capacidad a la hora de generar planes de ejecución óptimos.

Mejoras de procesamiento de consultas en tablas e índices con particiones

SQL Server 2008 (10.0.x) mejoró el rendimiento del procesamiento de consultas en tablas con particiones para muchos planes paralelos, cambia la forma en que se representan los planes paralelos y serie y mejoró la información de creación de particiones proporcionada tanto en los planes de ejecución en tiempo de compilación como en tiempo de ejecución. En este artículo se describen estas mejoras, se proporcionan instrucciones sobre cómo interpretar los planes de ejecución de consultas de tablas e índices con particiones y proporciona procedimientos recomendados para mejorar el rendimiento de las consultas en objetos con particiones.

Nota:

Hasta SQL Server 2014 (12.x), las tablas e índices con particiones solo se admiten en las ediciones SQL Server Enterprise, Developer y Evaluation. A partir de SQL Server 2016 (13.x) SP1, también se admiten tablas e índices con particiones en SQL Server Standard Edition.

Nueva operación de búsqueda compatible con particiones

En SQL Server, se cambia 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, siendo PartitionID la columna inicial. PartitionID es una columna calculada oculta usada internamente para representar el valor de ID de la partición que contiene una fila específica. 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. En SQL 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). De esta manera, el Optimizador de consultas puede realizar operaciones de búsqueda basadas en PartitionID sobre cualquier tabla o índice con particiones.

Ahora, la eliminación de particiones se lleva a cabo durante esta operación de búsqueda.

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. 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. Por ejemplo, considere la siguiente consulta.

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. La siguiente función define los límites de la partición para la tabla T:

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. Se identifican así las particiones a las cuales se va a tener acceso. 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.

La siguiente ilustración es una representación lógica de la operación de búsqueda selectiva. En ella se muestra la tabla T con datos en las columnas a y b. Las particiones están numeradas del 1 al 4; las líneas discontinuas representan los límites de las particiones. 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. Es decir, 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. 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. El costo total de la operación de búsqueda selectiva es el mismo que el de tres búsquedas por índices independientes.

Diagram showing how skip scan works.

Mostrar información de creación de particiones en los planes de ejecución de consultas

Los planes de ejecución de consultas en tablas e índices con particiones pueden examinarse utilizando las instrucciones SET de Transact-SQL SET SHOWPLAN_XML o SET STATISTICS XML, o mediante la salida gráfica del plan de ejecución en SQL Server Management Studio. Por ejemplo, puede mostrar el plan de ejecución en tiempo de compilación seleccionando Mostrar plan de ejecución estimado en la barra de herramientas del Editor de consultas y el plan en tiempo de ejecución seleccionando Incluir plan de ejecución real.

Estas herramientas le proporcionarán la siguiente información:

Las operaciones como scans, seeks, inserts, updates, mergesy deletes que acceden a tablas o índices con particiones.
Las particiones a las que tiene acceso la consulta. 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.
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.

Mejoras en la información de partición

SQL 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. Los planes de ejecución proporcionan ahora la siguiente información:

Un atributo Partitioned opcional que indica que un operador, como seek, scan, insert, update, mergeo delete, se ejecuta en una tabla con particiones.
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. La presencia de dos subelementos SeekKeys indica el uso de una operación de búsqueda selectiva sobre PartitionID .
Información de resumen que proporciona el número total de particiones a las que se ha tenido acceso. Esta información está disponible únicamente en los planes en tiempo de ejecución.

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. Esta consulta actualiza datos en dos particiones.

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

En la siguiente ilustración, se muestran las propiedades del operador Clustered Index Seek en el plan de ejecución del entorno de ejecución para esta consulta. Para ver la definición de la fact_sales tabla y la definición de partición, vea "Ejemplo" en este artículo.

Diagram of a clustered index seek.

Atributo particionado

Cuando un operador como Index Seek se ejecuta en una tabla o índice con particiones, el Partitioned atributo aparece en el plan en tiempo de compilación y en tiempo de ejecución, y se establece True en (1). El atributo no se muestra cuando se establece False en (0).

El atributo Partitioned puede aparecer en los siguientes operadores físicos y lógicos:

Table Scan
Index Scan
Index Seek
Insertar
Actualización
Eliminar
Merge

Como puede apreciarse en la ilustración previa, este atributo se muestra en las propiedades del operador en el que está definido. 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.

Nuevo predicado seek

En la salida del Plan de presentación XML, el elemento SeekPredicateNew aparece en el operador en el que está definido. Puede contener hasta dos apariciones del SeekKeys subelemento. 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. Es decir, esta búsqueda determina las particiones a las que se debe tener acceso para satisfacer las condiciones de la consulta. 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.

Información de resumen de particiones

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

Se proporciona la siguiente información: Actual Partition County Partitions Accessed.

Actual Partition Count es el número total de particiones a las que la consulta ha tenido acceso.

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. 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).

<RunTimePartitionSummary>
    <PartitionsAccessed PartitionCount="2" >
        <PartitionRange Start="2" End="3" />
    </PartitionsAccessed>
</RunTimePartitionSummary>

Mostrar información de partición mediante otros métodos showplan

Los métodos SHOWPLAN_ALLShowplan , SHOWPLAN_TEXTy STATISTICS PROFILE no notifican la información de partición descrita en este artículo, con la siguiente excepción. 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. En el ejemplo siguiente se muestra el predicado SEEK para un operador Clustered Index Seek . 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.

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

Un montón con particiones se trata como un índice lógico sobre el identificador de partición. 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. El siguiente ejemplo muestra la información de Showplan proporcionada:

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

Interpretación de planes de ejecución para combinaciones intercaladas

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. 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. Las combinaciones por colocación pueden ser más rápidas que las no colocadas, puesto que requieren menos memoria y tiempo de procesamiento. El Optimizador de consultas elige un plan no colocado o colocado en función de las estimaciones de costos.

En un plan colocado, la combinación Nested Loops lee una o varias particiones de índice o tabla combinada de la parte interna. Los números en el interior de los operadores Constant Scan representan los números de partición.

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 . En este caso, los distintos subprocesos de trabajo de la parte exterior de la combinación leen y trabajan en una partición diferente.

La siguiente ilustración muestra un plan de consulta paralela para una combinación por colocación.

Diagram of a colocated join.

Estrategia de ejecución de consultas paralelas para objetos con particiones

El procesador de consultas utiliza una estrategia de ejecución paralela para consultas que seleccionan en objetos con particiones. Como parte de la estrategia de ejecución, el procesador de consultas determina las particiones de tabla necesarias para la consulta y la proporción de subprocesos de trabajo que se van a asignar a cada partición. 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. En los párrafos siguientes se explica la asignación de subprocesos de trabajo con más detalle.

Diagram of a worker thread, part 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. 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. Este es el único caso en el que el procesador de consultas reasigna subprocesos de trabajo a otras particiones.

Muestra el subproceso de trabajo reasignado después de finalizar. 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. Cuando finaliza un subproceso de trabajo, no se reasigna a otra partición.

Diagram of a worker thread, part 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. 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 poder usar todos los subprocesos de trabajo disponibles. Si solo hay una partición, todos los subprocesos de trabajo se asignarán a esa partición. En el diagrama siguiente hay cuatro particiones y 14 subprocesos de trabajo. 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. Cuando finaliza un subproceso de trabajo, no se reasigna a otra partición.

Diagram of a worker thread, part 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. 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. 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.

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). 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. 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. El procesador de consultas asignará subprocesos de trabajo proporcionalmente para poder ejecutar cada uno de estos 12 exámenes de consulta en paralelo.

Particiones de tabla basadas en la columna A	Busca la columna B en cada partición de tabla
Partición de tabla 1: A < 10	B=50, B=100, B=150
Partición de tabla 2: A >= 10 AND A < 20	B=50, B=100, B=150
Partición de tabla 3: A >= 20 AND A < 30	B=50, B=100, B=150
Partición de tabla 4: A >= 30	B=50, B=100, B=150

Procedimientos recomendados

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:

Distribuya cada partición entre varios discos. Ello resulta especialmente importante al utilizar discos giratorios.
Cuando sea posible, use un servidor con suficiente memoria principal para ajustarse a las particiones a las que se accede con frecuencia, o todas las particiones en memoria, para reducir el costo de E/S.
Si los datos que consulta no caben en la memoria, comprima las tablas e índices. De esta manera se reducirá el costo de la E/S.
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.
Asegúrese de que el servidor dispone de un ancho banda de controlador de E/S suficiente.
Cree un índice clúster en cada tabla grande con particiones para sacar partido de las optimizaciones de examen de los árboles B.
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.

Ejemplo

El ejemplo siguiente crea una base de datos de prueba que contiene una única tabla con siete particiones. 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.

Nota:

Este ejemplo inserta más de 1 millón de filas en la tabla. En función de su hardware, la ejecución de este ejemplo puede tomar varios minutos. Antes de ejecutar este ejemplo, compruebe que tiene más de 1,5 GB de espacio en disco disponible.

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

Guía de arquitectura de procesamiento de consultas

Modos de ejecución

Ejecución del modo de fila

Ejecución del modo por lotes

Procesamiento de instrucciones SQL

Precedencia del operador lógico

Optimizar instrucciones SELECT

Procesar una instrucción SELECT

Evaluación de expresiones y plegado constante

Expresiones plegables

Expresiones nofoldables

Ejemplos de expresiones constantes plegables y nofoldables

Evaluación de expresiones

Ejemplos de evaluación de expresiones en tiempo de compilación

Procesar otras instrucciones

Tablas de trabajo

Visualización de la resolución

Uso de sugerencias con vistas

Resolución de índices en vistas

Uso de sugerencias con vistas indizadas

Resolución de vistas con particiones distribuidas

Procedimiento almacenado y ejecución del desencadenador

Almacenamiento en caché y reutilización del plan de ejecución

Almacenar en caché varios planes para la misma consulta

Eliminación de planes de ejecución de la caché de planes

Volver a compilar planes de ejecución

Reutilización de parámetros y planes de ejecución

Parametrización simple

Parametrización forzada

Tipos de datos de parámetros

Directrices para usar la parametrización forzada

Preparar instrucciones SQL

Confidencialidad de parámetros

Procesamiento de consultas en paralelo

Grado de paralelismo (DOP)

Invalidación de grados de paralelismo

Ejemplo de consulta paralela

Operaciones de índice paralelas

Arquitectura de consultas distribuidas

Mejoras de procesamiento de consultas en tablas e índices con particiones

Nueva operación de búsqueda compatible con particiones

Mostrar información de creación de particiones en los planes de ejecución de consultas

Mejoras en la información de partición

Atributo particionado

Nuevo predicado seek

Información de resumen de particiones

Mostrar información de partición mediante otros métodos showplan

Interpretación de planes de ejecución para montones con particiones

Interpretación de planes de ejecución para combinaciones intercaladas

Estrategia de ejecución de consultas paralelas para objetos con particiones

Procedimientos recomendados

Ejemplo

Pasos siguientes

Recursos adicionales

Recursos adicionales