Compartir a través de

Ejemplo en memoria en Azure SQL Database

  • Artículo

Se aplica a: Azure SQL Database

Las tecnologías en memoria de Azure SQL Database 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 Database, puede lograr mejoras de rendimiento con diversas cargas de trabajo.

En este artículo, dos ejemplos ilustran el uso de OLTP en memoria y de los índices del almacén de columnas en Azure SQL Database.

Para más información, vea:

Para ver una demostración introductoria de OLTP en memoria, consulte:

1. Instalación del ejemplo de In-Memory OLTP.

La base de datos de ejemplo AdventureWorksLT se puede crear con unos pocos pasos en Azure Portal. Siga los pasos que se indican en esta sección para añadir objetos OLTP en memoria a la base de datos de AdventureWorksLT y mostrar las ventajas de rendimiento.

Pasos de instalación

  1. En Azure Portal, cree una base de datos de nivel Premium (DTU) o Crítico para la empresa (núcleo virtual) en un servidor lógico. Establezca el Origen en la base de datos de ejemplo AdventureWorksLT. Para obtener instrucciones detalladas, consulte Creación de la primera base de datos de Azure SQL Database.

  2. Conectarse a la base de datos con SQL Server Management Studio (SSMS).

  3. Copie el script Transact-SQL de In-Memory OLTP 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.

  4. Pegue el script T-SQL en SSMS.exe y, luego, ejecútelo. La cláusula MEMORY_OPTIMIZED = ON de las instrucciones CREATE TABLE es fundamental. Por ejemplo:

    CREATE TABLE [SalesLT].[SalesOrderHeader_inmem](
    [SalesOrderID] int IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED ...,
    ...
) WITH (MEMORY_OPTIMIZED = ON);

Error 40536

Si recibe el error 40536 cuando ejecuta el script T-SQL, ejecute el siguiente script de T-SQL para comprobar que la base de datos admite objetos en memoria:

SELECT DATABASEPROPERTYEX(DB_NAME(), 'IsXTPSupported');

Un resultado de 0 significa que no se admite OLTP en memoria, mientras que un resultado de 1 significa que se admite. OLTP en memoria está disponible en los niveles Premium (DTU) y Crítico para la empresa (núcleos virtuales) de Azure SQL Database.

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) AS module_name, 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 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 utilidad ostress.exe para ejecutar los dos procedimientos almacenados. Puede comparar el tiempo que tardan en completarse las dos ejecuciones de esfuerzo.

Instalación de ostress y utilidades de RML

Preferiblemente, debe ejecutar ostress.exe en una máquina virtual (VM) de Azure. Tendría que crear una máquina virtual de Azure en la misma región de Azure que la base de datos de AdventureWorksLT. También puede ejecutar ostress.exe en la máquina local si puede conectarse a la base de datos de Azure SQL. Sin embargo, la latencia de red entre la máquina y la base de datos de Azure podría reducir las ventajas de rendimiento de OLTP en memoria.

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, vea:

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

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

  4. De nuevo, restablezca la base de datos.

Resultados de la comparación esperados

Las pruebas de OLTP en memoria demostraron tener un rendimiento nueve veces mejor en esta carga de trabajo simplista, con ostress.exe ejecutándose en una VM 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. Use el Portal de Azure para crear una nueva base de datos AdventureWorksLT a partir del ejemplo. Use cualquier objetivo de servicio que admita índices de almacén de columnas.

  2. Copie sql_in-memory_analytics_sample en el Portapapeles.

    • El script T-SQL crea los objetos 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.
    • En objetivos de servicio más pequeños, el script puede tardar 15 minutos o más 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 vs Columnstore table performance differences
-- Enable actual query plan in order to see Plan differences when executing.
*/
-- Ensure the database uses the latest compatibility level
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 query on a table with a clustered columnstore index (CCI).
-- The comparison numbers are the more pronounced the larger the table is (this is an 11 million row table).
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 una base de datos con el objetivo de servicio P2, puede esperar un rendimiento nueve veces superior, aproximadamente, para esta consulta al usar el índice de almacén de columnas en clúster, en comparación con el índice de almacén de filas tradicional. Con el objetivo de servicio P15, puede esperar, aproximadamente, un rendimiento 57 veces superior gracias al uso de índice de almacén de columnas.

Contenido relacionado