Ejemplo en memoria en Azure SQL Managed Instance

  • Artículo

Se aplica a:Azure SQL Managed Instance

Las tecnologías en memoria de Azure SQL Managed Instance permiten mejorar el rendimiento de la aplicación y reducen el coste de la base de datos. Mediante el uso de las tecnologías en memoria de Azure SQL Managed Instance, puede lograr mejoras de rendimiento con diversas cargas de trabajo.

En este artículo se muestran dos ejemplos que ilustran el uso de OLTP en memoria y de los índices del almacén de columnas en Azure SQL Managed Instance.

Para más información, vea:

Si desea ver una demostración más simple, pero más atractiva visualmente, del rendimiento de OLTP en memoria, consulte:

1. Restaurar la base de datos de ejemplo OLTP en memoria

Puede restaurar la base de datos de ejemplo AdventureWorksLT con algunos pasos de T-SQL en SQL Server Management Studio (SSMS). Para obtener más información sobre cómo restaurar una base de datos en SQL Managed Instance, consulte Inicio rápido: Restauración de una base de datos en Azure SQL Managed Instance con SSMS.

Los pasos descritos en esta sección explican cómo puede enriquecer la base de datos AdventureWorksLT con objetos de OLTP en memoria y mostrar las ventajas de rendimiento.

  1. Abre SSMS y conéctate a tu SQL Managed Instance.

    Nota:

    Las conexiones a Azure SQL Managed Instance desde la estación de trabajo local o una máquina virtual de Azure se pueden realizar de forma segura, sin abrir el acceso público. Consulte Inicio rápido: Configuración de una conexión de punto a sitio a Azure SQL Managed Instance desde un entorno local o Inicio rápido: Configuración de una máquina virtual de Azure para conectarse a Azure SQL Managed Instance.

  2. En el Explorador de objetos, haga clic con el botón derecho en su instancia administrada y seleccione Nueva consulta para abrir una nueva ventana de consulta.

  3. Ejecuta la siguiente instrucción de T-SQL, que usa públicamente un contenedor de almacenamiento disponible configurado previamente y una clave de firma de acceso compartido para crear una credencial en la SQL Managed Instance. Con el almacenamiento disponible públicamente, no se requiere ninguna firma SAS.

    CREATE CREDENTIAL [https://mitutorials.blob.core.windows.net/examples/]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE';

  4. Ejecute la instrucción siguiente para restaurar la base de datos de ejemplo AdventureWorksLT.

    RESTORE DATABASE [AdventureWorksLT] 
FROM URL = 'https://mitutorials.blob.core.windows.net/examples/AdventureWorksLT2022.bak';

  5. Ejecute la instrucción siguiente para realizar un seguimiento del estado de la restauración.

    SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete
   , dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE');

  6. Cuando finalice el proceso de restauración, consulte la base de datos AdventureWorksLT en el Explorador de objetos. Puede comprobar si se ha restaurado la base de datos AdventureWorksLT mediante la vista sys.dm_operation_status.

Acerca de los elementos creados optimizados para memoria

Tablas: el ejemplo contiene las siguientes tablas optimizadas para memoria:

  • SalesLT.Product_inmem
  • SalesLT.SalesOrderHeader_inmem
  • SalesLT.SalesOrderDetail_inmem
  • Demo.DemoSalesOrderHeaderSeed
  • Demo.DemoSalesOrderDetailSeed

Puede filtrar para mostrar solo las tablas optimizadas para memoria a través del Explorador de objetos en SSMS. Al hacer clic con el botón derecho en Tablas, vaya a >Filtro>Configuración del filtro>Tiene optimización para memoria. El valor es igual a 1.

O bien puede consultar las vistas de catálogo como:

SELECT is_memory_optimized, name, type_desc, durability_desc
    FROM sys.tables
    WHERE is_memory_optimized = 1;

Procedimiento almacenado compilado de forma nativa: puede inspeccionar SalesLT.usp_InsertSalesOrder_inmem mediante una consulta de la vista de catálogo:

SELECT uses_native_compilation, OBJECT_NAME(object_id), definition
    FROM sys.sql_modules
    WHERE uses_native_compilation = 1;

2. Ejecución de la carga de trabajo de OLTP de ejemplo

La única diferencia entre los dos procedimientos almacenados siguientes es que el primer procedimiento usa las versiones de las tablas optimizadas para memoria, mientras que el segundo procedimiento usa las tablas en disco habituales:

  • SalesLT.usp_InsertSalesOrder_inmem
  • SalesLT.usp_InsertSalesOrder_ondisk

En esta sección verá cómo usar la práctica utilidad ostress.exe para ejecutar los dos procedimientos almacenados a niveles de esfuerzo. Puede comparar el tiempo que tardan en completarse las dos ejecuciones de esfuerzo.

Instalación de ostress y utilidades de RML

Lo ideal sería planear la ejecución de ostress.exe en una máquina virtual de Azure (VM). Crearía una Máquina virtual de Azure en la misma región de Azure que la instancia de SQL Managed Instance. Pero puede ejecutar ostress.exe en la estación de trabajo local en su lugar, siempre y cuando pueda conectarse a la instancia administrada de Azure SQL.

En la VM, o en cualquier host que elija, instale las utilidades de Replay Markup Language (RML). Las utilidades incluyen ostress.exe.

Para más información, consulte:

Script para ostress.exe

En esta sección se muestra el script de T-SQL que se inserta en la línea de comandos de ostress.exe. El script usa elementos creados por el script de T-SQL que instaló antes.

Cuando ejecute ostress.exe, le recomendamos pasar los valores de parámetros diseñados para la carga de trabajo usando estas dos estrategias:

  • Ejecute un gran número de conexiones simultáneas, mediante el uso de -n100.
  • Haga que cada conexión se repita cientos de veces, mediante el uso de -r500.

Pero es posible que quiera comenzar con valores mucho más pequeños, como -n10 y -r50, para garantizar que todo está funcionando.

El siguiente script inserta un pedido de ventas de ejemplo con cinco elementos de línea en las siguientes tablas optimizadas para memoria:

  • SalesLT.SalesOrderHeader_inmem
  • SalesLT.SalesOrderDetail_inmem
DECLARE
    @i int = 0,
    @od SalesLT.SalesOrderDetailType_inmem,
    @SalesOrderID int,
    @DueDate datetime2 = sysdatetime(),
    @CustomerID int = rand() * 8000,
    @BillToAddressID int = rand() * 10000,
    @ShipToAddressID int = rand() * 10000;

INSERT INTO @od
    SELECT OrderQty, ProductID
    FROM Demo.DemoSalesOrderDetailSeed
    WHERE OrderID= cast((rand()*60) as int);

WHILE (@i < 20)
BEGIN;
    EXECUTE SalesLT.usp_InsertSalesOrder_inmem @SalesOrderID OUTPUT,
        @DueDate, @CustomerID, @BillToAddressID, @ShipToAddressID, @od;
    SET @i = @i + 1;
END

Para hacer que la versión _ondisk del script T-SQL anterior sirva para ostress.exe, hay que reemplazar ambas repeticiones de la subcadena _inmem por _ondisk. Estos reemplazos afectan a los nombres de tablas y procedimientos almacenados.

Ejecute de la carga de trabajo de esfuerzo _inmem en primer lugar

Puede usar una ventana del símbolo del sistema de RML para ejecutar la línea de comandos de ostress.exe. Los parámetros de la línea de comandos dirigen ostress para:

  • Ejecutar 100 conexiones simultáneamente (-n100).
  • Hacer que cada conexión ejecute el script de T-SQL 50 veces (-r50).
ostress.exe -n100 -r50 -S<servername>.database.windows.net -U<login> -P<password> -d<database> -q -Q"DECLARE @i int = 0, @od SalesLT.SalesOrderDetailType_inmem, @SalesOrderID int, @DueDate datetime2 = sysdatetime(), @CustomerID int = rand() * 8000, @BillToAddressID int = rand() * 10000, @ShipToAddressID int = rand()* 10000; INSERT INTO @od SELECT OrderQty, ProductID FROM Demo.DemoSalesOrderDetailSeed WHERE OrderID= cast((rand()*60) as int); WHILE (@i < 20) begin; EXECUTE SalesLT.usp_InsertSalesOrder_inmem @SalesOrderID OUTPUT, @DueDate, @CustomerID, @BillToAddressID, @ShipToAddressID, @od; set @i += 1; end"

Para ejecutar la línea de comandos ostress.exe anterior:

  1. Restablezca el contenido de los datos de la base de datos mediante la ejecución del siguiente comando en SSMS para eliminar todos los datos que se insertaron en las ejecuciones anteriores:

    EXECUTE Demo.usp_DemoReset;

  2. Copie el texto de la línea de comandos ostress.exe anterior en el Portapapeles.

  3. Reemplace el <placeholders> de los parámetros -S -U -P -d por los valores reales correctos.

  4. Ejecute la línea de comandos modificada en una ventana del símbolo del sistema de RML.

El resultado es una duración

Cuando finaliza ostress.exe, escribe la duración de la ejecución como la última línea de la salida en la ventana de símbolo del sistema de RML. Por ejemplo, una ejecución de prueba más corta duró aproximadamente 1,5 minutos:

11/12/15 00:35:00.873 [0x000030A8] OSTRESS exiting normally, elapsed time: 00:01:31.867

Restablezca y edite _ondisk y, después, vuelva a ejecutarlo

Una vez que tenga el resultado de la ejecución de _inmem, realice los pasos siguientes para la ejecución de _ondisk:

  1. Restablezca la base de datos mediante la ejecución del siguiente comando en SSMS para eliminar todos los datos que insertó la ejecución anterior:

    EXECUTE Demo.usp_DemoReset;

  2. Edite la línea de comandos de ostress.exe para reemplazar todos los _inmem con _ondisk.

  3. Ejecute ostress.exe por segunda vez y capture el resultado de la duración.

  4. Vuelva a restablecer la base de datos (para la eliminación responsable de lo que puede constituir una gran cantidad de datos de prueba).

Resultados de la comparación esperados

Las pruebas en memoria demostraron tener un rendimiento 9 veces mejor en esta carga de trabajo simplista, con ostress ejecutándose en una máquina virtual de Azure ubicada en la misma región de Azure que la base de datos.

3. Instalación del ejemplo de análisis en memoria

En esta sección, compara los resultados de optimización de infraestructura y de estadísticas cuando usa un índice del almacén de columnas en lugar de un índice de árbol b tradicional.

Para realizar análisis en tiempo real en una carga de trabajo de OLTP, suele ser mejor usar un índice del almacén de columnas no agrupado. Para obtener más información, consulte Guía de índices de almacén de columnas.

Preparación de la prueba de análisis del almacén de columnas

  1. Restaure una base de datos nueva AdventureWorksLT en la instancia administrada de SQL y sobrescriba la base de datos existente que instaló anteriormente mediante WITH REPLACE.

    RESTORE DATABASE [AdventureWorksLT] 
FROM URL = 'https://mitutorials.blob.core.windows.net/examples/AdventureWorksLT2022.bak'
WITH REPLACE;

  2. Copie sql_in-memory_analytics_sample en el Portapapeles.

    • El script T-SQL crea los objetos en memoria necesarios en la base de datos de ejemplo AdventureWorksLT que se creó en el paso 1.
    • El script crea la tabla de dimensiones y dos tablas de hechos. Las tablas de hechos se rellenan con 3,5 millones de filas cada una.
    • El script podría tardar 15 minutos en completarse.

  3. Pegue el script T-SQL en SSMS.exe y, luego, ejecútelo. La palabra clave COLUMNSTORE es crucial en la instrucción CREATE INDEX: CREATE NONCLUSTERED COLUMNSTORE INDEX ...;

  4. Ajuste AdventureWorksLT en el nivel de compatibilidad más reciente, SQL Server 2022 (160): ALTER DATABASE AdventureworksLT SET compatibility_level = 160;

Tablas e índices de almacén de columnas clave

  • dbo.FactResellerSalesXL_CCI es una tabla con un índice de almacén de columnas agrupado que tiene una compresión avanzada a nivel de datos.

  • dbo.FactResellerSalesXL_PageCompressed es una tabla con un índice agrupado equivalente normal, que se comprime solo a nivel de página.

4. Consultas cruciales para comparar el índice de almacén de columnas

Aquí hay varios tipos de consultas de T-SQL que se pueden ejecutar para ver las mejoras de rendimiento. En el paso 2 del script T-SQL, preste atención a estas dos consultas. Difieren solo en una línea:

  • FROM FactResellerSalesXL_PageCompressed AS a
  • FROM FactResellerSalesXL_CCI AS a

Un índice de almacén de columnas en clúster se encuentra en la tabla FactResellerSalesXL_CCI.

El siguiente script de T-SQL imprime las estadísticas de tiempo y actividad de E/S lógicas mediante SET STATISTICS IO y SET STATISTICS TIME para cada consulta.

/*********************************************************************
Step 2 -- Overview
-- Page Compressed BTree table v/s Columnstore table performance differences
-- Enable actual Query Plan in order to see Plan differences when Executing
*/
-- Ensure Database is in 130 compatibility mode
ALTER DATABASE AdventureworksLT SET compatibility_level = 160
GO

-- Execute a typical query that joins the Fact Table with dimension tables
-- Note this query will run on the Page Compressed table, Note down the time
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO

SELECT c.Year
    ,e.ProductCategoryKey
    ,FirstName + ' ' + LastName AS FullName
    ,count(SalesOrderNumber) AS NumSales
    ,sum(SalesAmount) AS TotalSalesAmt
    ,Avg(SalesAmount) AS AvgSalesAmt
    ,count(DISTINCT SalesOrderNumber) AS NumOrders
    ,count(DISTINCT a.CustomerKey) AS CountCustomers
FROM FactResellerSalesXL_PageCompressed AS a
INNER JOIN DimProduct AS b ON b.ProductKey = a.ProductKey
INNER JOIN DimCustomer AS d ON d.CustomerKey = a.CustomerKey
Inner JOIN DimProductSubCategory AS e on e.ProductSubcategoryKey = b.ProductSubcategoryKey
INNER JOIN DimDate AS c ON c.DateKey = a.OrderDateKey
GROUP BY e.ProductCategoryKey,c.Year,d.CustomerKey,d.FirstName,d.LastName
GO
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO


-- This is the same Prior query on a table with a clustered columnstore index CCI
-- The comparison numbers are even more dramatic the larger the table is (this is an 11 million row table only)
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
SELECT c.Year
    ,e.ProductCategoryKey
    ,FirstName + ' ' + LastName AS FullName
    ,count(SalesOrderNumber) AS NumSales
    ,sum(SalesAmount) AS TotalSalesAmt
    ,Avg(SalesAmount) AS AvgSalesAmt
    ,count(DISTINCT SalesOrderNumber) AS NumOrders
    ,count(DISTINCT a.CustomerKey) AS CountCustomers
FROM FactResellerSalesXL_CCI AS a
INNER JOIN DimProduct AS b ON b.ProductKey = a.ProductKey
INNER JOIN DimCustomer AS d ON d.CustomerKey = a.CustomerKey
Inner JOIN DimProductSubCategory AS e on e.ProductSubcategoryKey = b.ProductSubcategoryKey
INNER JOIN DimDate AS c ON c.DateKey = a.OrderDateKey
GROUP BY e.ProductCategoryKey,c.Year,d.CustomerKey,d.FirstName,d.LastName
GO

SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO

En función de la configuración de SQL Managed Instance, puede esperar importantes mejoras de rendimiento para esta consulta mediante el índice de almacén de columnas agrupado en comparación con el índice tradicional.

Contenido relacionado