Bienvenido a la última parte de esta serie sobre cómo mejorar el rendimiento en SQL Server. En los posts anteriores, discutimos cómo utilizar tablas optimizadas para memoria para reemplazar variables de tabla, tablas temporales globales y tablas temporales locales. Hoy, nos enfocaremos en reemplazar tablas temporales locales y buscar mejoras en el rendimiento.

Las tablas temporales locales se utilizan comúnmente en el desarrollo para almacenar datos temporales durante una transacción. Con SQL Server 2016, podemos aprovechar las tablas optimizadas para memoria para mejorar potencialmente el rendimiento. Consideremos un caso simple en el que se utiliza una tabla temporal local dentro de un procedimiento almacenado para insertar 300 registros.

Dado que las tablas temporales locales existen dentro de una sesión, debemos modificar nuestra tabla optimizada para memoria para cumplir con este requisito. Cada sesión solo debe poder leer y modificar sus propios datos de sesión. Sin embargo, las tablas optimizadas para memoria funcionan como tablas de usuario normales, por lo que debemos agregar restricciones para limitar su uso.

Aquí tienes un ejemplo de una tabla temporal local:

CREATE PROCEDURE sp_localtemp
AS
SET NOCOUNT ON
BEGIN
    DROP TABLE IF EXISTS #localtemp
    CREATE TABLE #localtemp (
        ColA INT NOT NULL,
        ColB NVARCHAR(4000)
    );
    
    BEGIN TRAN
    DECLARE @i INT = 0;
    
    WHILE @i < 300
    BEGIN
        INSERT #localtemp VALUES (@i, N'xyz');
        SET @i += 1;
    END;
    
    COMMIT
END

Utilizando la herramienta ostress, simulamos 100 conexiones, cada una ejecutándose 1000 veces, lo que resulta en un total de 100,000 ejecuciones. Tomó un total de 27 segundos.

Como se discutió anteriormente, las tablas optimizadas para memoria utilizadas para reemplazar tablas temporales locales requieren restricciones adicionales para garantizar que solo la sesión pueda ver y modificar sus datos. Esto se logra creando una función junto con una política de seguridad.

-- Se puede utilizar una única función de filtro para todas las tablas temporales a nivel de sesión
CREATE FUNCTION dbo.fn_SessionFilter (@session_id smallint)
RETURNS TABLE WITH SCHEMABINDING, NATIVE_COMPILATION
AS
RETURN
    SELECT 1 AS fn_SessionFilter
    WHERE @session_id = @@spid;
    
GO

DROP TABLE IF EXISTS dbo.memtemp1
GO

CREATE TABLE dbo.memtemp1 (
    ColA INT NOT NULL,
    ColB NVARCHAR(4000),
    session_id SMALLINT NOT NULL DEFAULT (@@spid),
    INDEX IX_session_id (session_id),
    CONSTRAINT CHK_temp1_session_id CHECK (session_id = @@spid)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);

GO

-- Agregar una política de seguridad para habilitar el filtrado en session_id para cada tabla temporal
CREATE SECURITY POLICY dbo.secFilter
ADD FILTER PREDICATE dbo.fn_SessionFilter(session_id)
ON dbo.memtemp1
WITH (STATE = ON);

GO

DROP PROCEDURE IF EXISTS sp_memtemp1
GO

CREATE PROCEDURE sp_memtemp1
AS
BEGIN
    SET NOCOUNT ON
    
    DELETE FROM dbo.memtemp1;
    
    BEGIN TRAN
    DECLARE @i INT = 0;
    
    WHILE @i < 300
    BEGIN
        INSERT dbo.memtemp1 (ColA, ColB) VALUES (@i, N'xyz');
        SET @i += 1;
    END;
    
    COMMIT
END
GO

Con el mismo número de ejecuciones por 100 conexiones, solo tomó 7 segundos. Esto representa un aumento de rendimiento cercano a 4 veces en comparación con el uso de tablas temporales locales. Si bien no es tan significativo como el caso de uso anterior, aún proporciona una mejora sustancial en el rendimiento.

Como con cualquier cambio de código, es importante probar esto en tu propio entorno para asegurarte de que funcione como se espera. Espero que esta serie te haya ayudado a aprovechar SQL Server 2016 para mejorar el rendimiento en tu propia carga de trabajo.