Guía para el procesamiento de consultas para tablas de Memory-Optimized

In-Memory OLTP presenta tablas optimizadas para memoria y procedimientos almacenados compilados de forma nativa en SQL Server. En este artículo se proporciona información general sobre el procesamiento de consultas para tablas optimizadas para memoria y procedimientos almacenados compilados de forma nativa.

En el documento se explica cómo se compilan y ejecutan las consultas en tablas optimizadas para memoria, entre las que se incluyen:

• Canalización de procesamiento de consultas en SQL Server para tablas basadas en disco.

• Optimización de consultas; el rol de estadísticas en tablas optimizadas para memoria, así como directrices para solucionar problemas de planes de consulta incorrectos.

• Uso de Transact-SQL interpretado para acceder a tablas optimizadas para el uso de memoria.

• Consideraciones sobre la optimización de consultas para el acceso a tablas optimizadas para memoria.

• Compilación y procesamiento de procedimientos almacenados compilados nativamente.

• Estadísticas que el optimizador usa para la estimación de costos.

• Formas de corregir planes de consulta incorrectos.

Consulta de ejemplo

El siguiente ejemplo se usará para ilustrar los conceptos de procesamiento de consultas descritos en este artículo.

Consideramos dos tablas, Cliente y Pedido. El siguiente script de Transact-SQL contiene las definiciones de estas dos tablas y índices asociados, en su formato basado en disco (tradicional):

CREATE TABLE dbo.[Customer] (  
  CustomerID nchar (5) NOT NULL PRIMARY KEY,  
  ContactName nvarchar (30) NOT NULL   
)  
GO  
  
CREATE TABLE dbo.[Order] (  
  OrderID int NOT NULL PRIMARY KEY,  
  CustomerID nchar (5) NOT NULL,  
  OrderDate date NOT NULL  
)  
GO  
CREATE INDEX IX_CustomerID ON dbo.[Order](CustomerID)  
GO  
CREATE INDEX IX_OrderDate ON dbo.[Order](OrderDate)  
GO  

Para construir los planes de consulta que se muestran en este artículo, las dos tablas se rellenaron con datos de ejemplo de la base de datos de ejemplo Northwind, que puede descargar de Northwind y pubs Sample Databases for SQL Server 2000.

Tenga en cuenta la siguiente consulta, que combina las tablas Customer y Order y devuelve el identificador del pedido y la información del cliente asociada:

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID  

El plan de ejecución estimado que muestra SQL Server Management Studio es el siguiente:

Plan de consulta para combinar tablas basadas en disco.

Acerca de este plan de consulta:

• Las filas de la tabla Customer se recuperan del índice agrupado, que es la estructura de datos principal y tiene los datos de tabla completos.

• Los datos de la tabla Order se recuperan mediante el índice no agrupado en la columna CustomerID. Este índice contiene la columna CustomerID, que se usa para la combinación y la columna de clave principal OrderID, que se devuelve al usuario. Devolver columnas adicionales de la tabla Order requeriría búsquedas en el índice agrupado para la tabla Order.

• El operador Inner Join lógico se implementa mediante el operador Merge Joinfísico . Los otros tipos de combinación física son Nested Loops y Hash Join. El operador Merge Join aprovecha el hecho de que ambos índices están ordenados en la columna de combinación CustomerID.

Considere una ligera variación en esta consulta, que devuelve todas las filas de la tabla Order, no solo OrderID:

SELECT o.*, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID  

El plan estimado para esta consulta es:

Plan de consulta para una combinación hash de tablas basadas en disco.

En esta consulta, las filas de la tabla Order se recuperan mediante el índice agrupado. El Hash Match operador físico ahora se usa para el Inner Join. El índice agrupado en Order no se ordena en CustomerID, por lo que Merge Join requeriría un operador de ordenación, lo que afectaría al rendimiento. Observe el costo relativo del Hash Match operador (75%) en comparación con el costo del Merge Join operador en el ejemplo anterior (46%). El optimizador habría considerado el Hash Match operador también en el ejemplo anterior, pero concluyó que el Merge Join operador dio un mejor rendimiento.

Procesamiento de consultas de SQL Server para tablas de Disk-Based

En el diagrama siguiente se describe el flujo de procesamiento de consultas en SQL Server para consultas ad hoc:

Canalización de procesamiento de consultas de SQL Server.

En este escenario:

1. El usuario emite una consulta.

2. El analizador y algebrizer crean un árbol de consulta con operadores lógicos basados en el texto Transact-SQL enviado por el usuario.

3. El optimizador crea un plan de consulta optimizado que contiene operadores físicos (por ejemplo, unión de bucles anidados). Después de la optimización, el plan se puede almacenar en la memoria caché del plan. Este paso se omite si la memoria caché del plan ya contiene un plan para esta consulta.

4. El motor de ejecución de consultas procesa una interpretación del plan de consulta.

5. Para cada operador de búsqueda de índice, escaneo de índice y escaneo de tabla, el motor de ejecución solicita filas de las estructuras de índice y tabla correspondientes de los métodos de acceso.

6. Los métodos de acceso recuperan las filas de las páginas de índice y páginas de datos del grupo de búferes y cargan páginas del disco en el grupo de búferes según sea necesario.

Para la primera consulta de ejemplo, el motor de ejecución solicita filas en el índice agrupado en Customer y el índice no agrupado en Order from Access Methods. Los métodos de acceso recorren las estructuras de índices B-tree para recuperar las filas solicitadas. En este caso, todas las filas se recuperan como las llamadas de plan para exámenes de índice completos.

Acceso Transact-SQL interpretado a tablas de Memory-Optimized

Transact-SQL lotes ad hoc y procedimientos almacenados también se conocen como Transact-SQL interpretados. Interpretado hace referencia al hecho de que el motor de ejecución de consultas interpreta el plan de consulta para cada operador del plan de consulta. El motor de ejecución lee el operador y sus parámetros y realiza la operación.

El Transact-SQL interpretado se puede usar para acceder a tablas optimizadas para memoria y tablas basadas en disco. En la ilustración siguiente se muestra el procesamiento de consultas para Transact-SQL interpretados el acceso a tablas optimizadas para memoria:

Canalización de procesamiento de consultas para Transact-SQL acceso interpretado a tablas optimizadas para memoria.

Como se muestra en la ilustración, la canalización de procesamiento de consultas permanece prácticamente sin cambios:

• El analizador y el algebrizador construyen el árbol de consulta.

• El optimizador crea el plan de ejecución.

• El motor de ejecución de consultas interpreta el plan de ejecución.

La principal diferencia con la canalización de procesamiento de consultas tradicional (figura 2) es que las filas de las tablas optimizadas para memoria no se recuperan del grupo de búferes mediante métodos de Access. En su lugar, las filas se recuperan de las estructuras de datos en memoria a través del motor OLTP de In-Memory. Las diferencias en las estructuras de datos hacen que el optimizador elija planes diferentes en algunos casos, como se muestra en el ejemplo siguiente.

El siguiente script de Transact-SQL contiene versiones optimizadas para memoria de las tablas Order y Customer, mediante índices hash:

CREATE TABLE dbo.[Customer] (  
  CustomerID nchar (5) NOT NULL PRIMARY KEY NONCLUSTERED,  
  ContactName nvarchar (30) NOT NULL   
) WITH (MEMORY_OPTIMIZED=ON)  
GO  
  
CREATE TABLE dbo.[Order] (  
  OrderID int NOT NULL PRIMARY KEY NONCLUSTERED,  
  CustomerID nchar (5) NOT NULL INDEX IX_CustomerID HASH(CustomerID) WITH (BUCKET_COUNT=100000),  
  OrderDate date NOT NULL INDEX IX_OrderDate HASH(OrderDate) WITH (BUCKET_COUNT=100000)  
) WITH (MEMORY_OPTIMIZED=ON)  
GO  

Considere la misma consulta ejecutada en tablas optimizadas para memoria:

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID  

El plan estimado es el siguiente:

Plan de consulta para combinar tablas optimizadas para memoria.

Observe las siguientes diferencias con el plan de la misma consulta en tablas basadas en disco (figura 1):

• Este plan contiene un examen de tabla en lugar de un examen de índice agrupado para la tabla Customer:

  • La definición de la tabla no contiene un índice agrupado.

  • Los índices agrupados no se admiten en tablas con optimización para memoria. En su lugar, todas las tablas optimizadas para memoria deben tener al menos un índice no clúster y todos los índices de las tablas optimizadas para memoria pueden acceder eficazmente a todas las columnas de la tabla sin tener que almacenarlas en el índice o hacer referencia a un índice agrupado.

• Este plan contiene un Hash Match en lugar de un Merge Join. Los índices en las tablas "Order" y "Customer" son índices hash y, por tanto, no están ordenados. Un Merge Join requeriría operadores de ordenación que reducirían el rendimiento.

Procedimientos almacenados compilados de forma nativa

Los procedimientos almacenados compilados de forma nativa son Transact-SQL procedimientos almacenados compilados en código de máquina, en lugar de interpretados por el motor de ejecución de consultas. El script siguiente crea un procedimiento almacenado compilado de forma nativa que ejecuta la consulta de ejemplo (en la sección Consulta de ejemplo).

CREATE PROCEDURE usp_SampleJoin  
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
AS BEGIN ATOMIC WITH   
(  TRANSACTION ISOLATION LEVEL = SNAPSHOT,  
  LANGUAGE = 'english')  
  
  SELECT o.OrderID, c.CustomerID, c.ContactName   
FROM dbo.[Order] o INNER JOIN dbo.[Customer] c   
  ON c.CustomerID = o.CustomerID  
  
END  

Los procedimientos almacenados compilados de forma nativa se compilan en tiempo de creación, mientras que los procedimientos almacenados interpretados se compilan en el primer momento de ejecución. (Una parte de la compilación, especialmente el análisis y la algebrización, tienen lugar en la creación. Sin embargo, para los procedimientos almacenados interpretados, la optimización de los planes de consulta tiene lugar en la primera ejecución). La lógica de recompilación es similar. Los procedimientos almacenados compilados de forma nativa se vuelven a compilar en la primera ejecución del procedimiento si se reinicia el servidor. Los procedimientos almacenados interpretados se recompilan si el plan ya no está en la caché de planes. En la tabla siguiente se resumen los casos de compilación y recompilación para los procedimientos almacenados compilados e interpretados de forma nativa:

	Compilado de forma nativa	Interpretado
Compilación inicial	En el momento de creación.	En la primera ejecución.
Recompilación automática	Tras la primera ejecución del procedimiento después de reiniciar una base de datos o servidor.	En el reinicio del servidor. O bien, el desalojo de la memoria caché del plan, por lo general debido a los cambios de esquema o estadísticas, o por presión de memoria.
Recompilación manual	No está soportado. La solución consiste en quitar y volver a crear el procedimiento almacenado.	Utilice sp_recompile. Puede expulsar manualmente el plan de la caché, por ejemplo a través de DBCC FREEPROCCACHE. También puede crear el procedimiento almacenado WITH RECOMPILE y el procedimiento almacenado se volverá a compilar en cada ejecución.

Compilación y procesamiento de consultas

En el diagrama siguiente se muestra el proceso de compilación para procedimientos almacenados compilados de forma nativa:

Compilación nativa de procedimientos almacenados.

El proceso se describe como,

1. El usuario emite una CREATE PROCEDURE instrucción a SQL Server.

2. El analizador y el algebrizador crean el flujo de procesamiento para el procedimiento, así como árboles de consultas para las consultas Transact-SQL en el procedimiento almacenado.

3. El optimizador crea planes de ejecución de consultas optimizados para todas las consultas del procedimiento almacenado.

4. El compilador OLTP de In-Memory toma el flujo de procesamiento con los planes de consulta optimizados incrustados y genera una DLL que contiene el código máquina para ejecutar el procedimiento almacenado.

5. El archivo DLL generado se carga en la memoria.

La invocación de un procedimiento almacenado compilado de forma nativa se traduce en llamar a una función en el archivo DLL.

Ejecución de procedimientos almacenados compilados de forma nativa.

La invocación de un procedimiento almacenado compilado de forma nativa se describe de la manera siguiente:

1. El usuario emite EXECinstrucción usp_myproc.

2. El analizador extrae el nombre y los parámetros del procedimiento almacenado.

   Si la instrucción se preparó, por ejemplo, mediante sp_prep_exec, el analizador no necesita extraer el nombre del procedimiento y los parámetros en tiempo de ejecución.

3. El entorno de ejecución de In-Memory OLTP localiza el punto de entrada DLL para el procedimiento almacenado.

4. El código de máquina en el archivo DLL se ejecuta y los resultados de se devuelven al cliente.

Detección de parámetros

Los procedimientos almacenados interpretados Transact-SQL se compilan en la primera ejecución, a diferencia de los procedimientos almacenados compilados de forma nativa, que se compilan en tiempo de creación. Cuando se compilan procedimientos almacenados interpretados en la invocación, el optimizador usa los valores de los parámetros proporcionados para esta invocación al generar el plan de ejecución. Este uso de parámetros durante la compilación se denomina examen de parámetros.

El examen de parámetros no se usa para compilar procedimientos almacenados compilados de forma nativa. Todos los parámetros del procedimiento almacenado se consideran tener valores DESCONOCIDOS. Al igual que los procedimientos almacenados interpretados, los procedimientos almacenados compilados de forma nativa también admiten la OPTIMIZE FOR sugerencia. Para obtener más información, consulte Sugerencias de consulta (Transact-SQL).

Recuperación de un plan de ejecución de consultas para procedimientos almacenados compilados de forma nativa

El plan de ejecución de consultas para un procedimiento almacenado compilado de forma nativa se puede recuperar mediante el plan de ejecución estimado en Management Studio o mediante la opción SHOWPLAN_XML de Transact-SQL. Por ejemplo:

SET SHOWPLAN_XML ON  
GO  
EXEC dbo.usp_myproc  
GO  
SET SHOWPLAN_XML OFF  
GO  

El plan de ejecución generado por el optimizador de consultas consta de un árbol con operadores de consulta en los nodos y hojas del árbol. La estructura del árbol determina la interacción (el flujo de filas de un operador a otro) entre los operadores. En la vista gráfica de SQL Server Management Studio, el flujo va de derecha a izquierda. Por ejemplo, el plan de consulta de la figura 1 contiene dos operadores de escaneo de índice, que suministran filas a un operador de combinación por mezcla. El operador merge join proporciona filas a un operador select. El operador select devuelve finalmente las filas al cliente.

Operadores de consulta en procedimientos almacenados compilados de forma nativa

En la tabla siguiente se resumen los operadores de consulta admitidos en procedimientos almacenados compilados de forma nativa:

Operador	Consulta de ejemplo
Seleccionar	SELECT OrderID FROM dbo.[Order]
INSERTAR	INSERT dbo.Customer VALUES ('abc', 'def')
ACTUALIZACIÓN	UPDATE dbo.Customer SET ContactName='ghi' WHERE CustomerID='abc'
ELIMINAR	DELETE dbo.Customer WHERE CustomerID='abc'
Escalar de cálculo	Este operador se usa tanto para funciones intrínsecas como para conversiones de tipos. No todas las funciones y conversiones de tipos se admiten en procedimientos almacenados compilados de forma nativa. SELECT OrderID+1 FROM dbo.[Order]
Combinación de bucles anidados	Nested Loops es el único operador de combinación admitido en procedimientos almacenados compilados de forma nativa. Todos los planes que contienen combinaciones usarán el operador de bucles anidados, incluso si el plan de la misma consulta ejecutada como interpretado Transact-SQL contiene una unión hash o una unión de mezcla. SELECT o.OrderID, c.CustomerID FROM dbo.[Order] o INNER JOIN dbo.[Customer] c
Ordenar	SELECT ContactName FROM dbo.Customer ORDER BY ContactName
Mejores	SELECT TOP 10 ContactName FROM dbo.Customer
Ordenación Prioritaria	La TOP expresión (el número de filas que se van a devolver) no puede superar los 8000 filas. Serán menos si en la consulta también hay operadores de combinación y agregación. Las combinaciones y la agregación suelen reducir el número de filas que se van a ordenar, en comparación con el recuento de filas de las tablas base. SELECT TOP 10 ContactName FROM dbo.Customer ORDER BY ContactName
Agregación de flujo	Tenga en cuenta que el operador Hash Match no se admite para la agregación. Por lo tanto, toda la agregación en procedimientos almacenados nativamente compilados usa el operador Stream Aggregate, incluso si el plan de la misma consulta en la consulta interpretada Transact-SQL usa el operador Hash Match. SELECT count(CustomerID) FROM dbo.Customer

Estadísticas y combinaciones de columnas

SQL Server mantiene estadísticas sobre los valores de las columnas de clave de índice para ayudar a calcular el costo de determinadas operaciones, como el examen de índices y las búsquedas de índice. ( SQL Server también crea estadísticas en columnas de clave que no son de índice si las crea explícitamente o si el optimizador de consultas las crea en respuesta a una consulta con un predicado). La métrica principal de la estimación de costos es el número de filas procesadas por un único operador. Tenga en cuenta que para las tablas basadas en disco, el número de páginas a las que accede un operador determinado es significativa en la estimación de costos. Sin embargo, como el recuento de páginas no es importante para las tablas optimizadas para memoria (siempre es cero), esta discusión se centra en el recuento de filas. La estimación comienza con los operadores de búsqueda y exploración de índice en el plan y, a continuación, se extiende para incluir los otros operadores, como el operador de unión. El número estimado de filas que va a procesar un operador de combinación se basa en la estimación de los operadores de índice, búsqueda y examen subyacentes. Para el acceso interpretado Transact-SQL a las tablas optimizadas para memoria, puede observar el plan de ejecución real para ver la diferencia entre los recuentos de filas estimados y reales para los operadores del plan.

Para el ejemplo de la figura 1,

• La exploración del índice agrupado en Cliente ha estimado 91; mientras que el real es 91.

• El escaneo del índice no agrupado en CustomerID ha estimado 830; real 830.

• El operador Merge Join ha estimado un valor de 815; real 830.

Las estimaciones de los escaneos de índice son precisas. SQL Server mantiene el recuento de filas para las tablas basadas en disco. Las estimaciones de los escaneos completos de tabla e índice siempre resultan precisas. La estimación de la unión también es bastante precisa.

Si estas estimaciones cambian, también cambian las consideraciones de costos para diferentes alternativas de plan. Por ejemplo, si uno de los lados de la combinación tiene un recuento estimado de filas de 1 o solo unas pocas filas, el uso de combinaciones de bucles anidados es menos costoso.

A continuación se muestra el plan de la consulta:

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID  

Después de eliminar todas las filas excepto una de la tabla *Customer*:

Con relación a este plan de consulta:

• El operador Hash Match se ha reemplazado por un operador de unión física de bucles anidados.

• La exploración completa de índice en IX_CustomerID se ha reemplazado por una búsqueda de índice. Esto dio lugar a examinar 5 filas, en lugar de las 830 filas necesarias para el examen de índice completo.

Estadísticas y cardinalidad para tablas de Memory-Optimized

SQL Server mantiene estadísticas de nivel de columna para tablas optimizadas para memoria. Además, mantiene el recuento real de filas de la tabla. Sin embargo, a diferencia de las tablas basadas en disco, las estadísticas de las tablas optimizadas para memoria no se actualizan automáticamente. Por lo tanto, las estadísticas deben actualizarse manualmente después de cambios significativos en las tablas. Para obtener más información, vea Estadísticas para tablas de Memory-Optimized.

Véase también

Memory-Optimized Tablas