Mejorando el rendimiento con In-Memory OLTP en SQL Server

Introducción:

SQL Server 2014 introdujo la característica In-Memory OLTP, que proporciona beneficios significativos de rendimiento para ciertas cargas de trabajo. En este artículo, exploraremos algunas opciones adicionales disponibles con la característica In-Memory OLTP para mejorar aún más el rendimiento.

Opción DURABILITY:

Una opción disponible con la característica In-Memory OLTP es la opción DURABILITY de la instrucción CREATE TABLE. Esta opción nos permite elegir si persistir o no los datos en nuestra tabla. Al elegir no persistir los datos, podemos lograr un rendimiento aún mejor. Sin embargo, es importante tener en cuenta que esta opción puede no ser adecuada para todas las aplicaciones debido a requisitos específicos.

Procedimientos almacenados compilados de forma nativa:

Otra opción para mejorar el rendimiento es utilizar procedimientos almacenados compilados de forma nativa. Estos procedimientos almacenados acceden a la tabla utilizando código compilado en lugar de interpretar las instrucciones T-SQL en tiempo de ejecución. Esto puede resultar en mejoras significativas de rendimiento, especialmente cuando se combina con la opción SCHEMA_ONLY.

Configuración de tabla de muestra:

Creemos dos copias de cada tabla para comparar el rendimiento de las instrucciones T-SQL y los procedimientos almacenados compilados de forma nativa. Aquí está el código T-SQL para crear estas tablas:

CREATE TABLE testtable_inmemory ([col1] [int] NOT NULL primary key nonclustered,
                        [col2] [int] NULL,
                        [col3] [int] NULL,
                        [col4] [varchar](50) NULL) WITH (MEMORY_OPTIMIZED=ON);

CREATE TABLE testtable_inmemoryschemaonly ([col1] [int] NOT NULL primary key nonclustered,
                        [col2] [int] NULL,
                        [col3] [int] NULL,
                        [col4] [varchar](50) NULL) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_ONLY);

CREATE TABLE testtable_inmemory2 ([col1] [int] NOT NULL primary key nonclustered,
                        [col2] [int] NULL,
                        [col3] [int] NULL,
                        [col4] [varchar](50) NULL) WITH (MEMORY_OPTIMIZED=ON);

CREATE TABLE testtable_inmemoryschemaonly2 ([col1] [int] NOT NULL primary key nonclustered,
                        [col2] [int] NULL,
                        [col3] [int] NULL,
                        [col4] [varchar](50) NULL) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_ONLY);

Procedimientos almacenados compilados de forma nativa:

También necesitamos crear procedimientos almacenados compilados de forma nativa para acceder a estas tablas. Aquí está el código T-SQL para crear estos procedimientos almacenados:

CREATE PROCEDURE dbo.DeleteColumn(@col1 integer)
with native_compilation, schemabinding, execute as owner
as 
begin atomic with
(transaction isolation level = snapshot,language = N'English')
  DELETE FROM dbo.testtable_inmemory2 WHERE [col1]=@col1
end

CREATE PROCEDURE dbo.DeleteColumnSchemaOnly(@col1 integer)
with native_compilation, schemabinding, execute as owner
as 
begin atomic with
(transaction isolation level = snapshot,language = N'English')
  DELETE FROM dbo.testtable_inmemoryschemaonly2 WHERE [col1]=@col1
end

CREATE PROCEDURE dbo.UpdateColumn(@col1 integer)
with native_compilation, schemabinding, execute as owner
as 
begin atomic with
(transaction isolation level = snapshot,language = N'English')
  UPDATE dbo.testtable_inmemoryschemaonly2 SET col3=col3+10 WHERE [col1]=@col1
end

CREATE PROCEDURE dbo.UpdateColumnSchemaOnly(@col1 integer)
with native_compilation, schemabinding, execute as owner
as 
begin atomic with
(transaction isolation level = snapshot,language = N'English')
  UPDATE dbo.testtable_inmemoryschemaonly2 SET col3=col3+10 WHERE [col1]=@col1
end

CREATE PROCEDURE dbo.SelectColumn(@col1 integer)
with native_compilation, schemabinding, execute as owner
as 
begin atomic with
(transaction isolation level = snapshot,language = N'English')
  SELECT col1,col2,col3 FROM dbo.testtable_inmemory2 WHERE [col1]=@col1
end

CREATE PROCEDURE dbo.SelectColumnSchemaOnly(@col1 integer)
with native_compilation, schemabinding, execute as owner
as 
begin atomic with
(transaction isolation level = snapshot,language = N'English')
  SELECT col1,col2,col3 FROM dbo.testtable_inmemoryschemaonly2 WHERE [col1]=@col1
end

CREATE PROCEDURE dbo.InsertRow(@col1 integer)
with native_compilation, schemabinding, execute as owner
as 
begin atomic with
(transaction isolation level = snapshot,language = N'English')
  INSERT INTO dbo.testtable_inmemory2 (col1, col2, col3, col4) 
       VALUES (@col1,@col1 % 10,@col1,'TEST' + CAST(@col1 AS VARCHAR))
end

CREATE PROCEDURE dbo.InsertRowSchemaOnly(@col1 integer)
with native_compilation, schemabinding, execute as owner
as 
begin atomic with
(transaction isolation level = snapshot,language = N'English')
  INSERT INTO dbo.testtable_inmemoryschemaonly2 (col1, col2, col3, col4) 
       VALUES (@col1,@col1 % 10,@col1,'TEST' + CAST(@col1 AS VARCHAR))
end

Prueba de rendimiento:

Ahora, ejecutemos una prueba de rendimiento cargando datos en cada tabla y midiendo el rendimiento. Cargaremos 200,000 registros en cada tabla utilizando un bucle WHILE. Aquí está el código T-SQL:

DECLARE @val INT
SELECT @val=1
WHILE @val <= 200000
BEGIN  
   INSERT INTO testtable_inmemory (col1, col2, col3, col4) 
       VALUES (@val,@val % 10,@val,'TEST' + CAST(@val AS VARCHAR))
   SELECT @val=@val+1
END

DECLARE @val INT
SELECT @val=1
WHILE @val <= 200000
BEGIN  
   INSERT INTO testtable_inmemoryschemaonly (col1, col2, col3, col4) 
       VALUES (@val,@val % 10,@val,'TEST' + CAST(@val AS VARCHAR))
   SELECT @val=@val+1
END

DECLARE @val INT
SELECT @val=1
WHILE @val <= 200000
BEGIN  
   exec dbo.InsertRow @val;
   SELECT @val=@val+1
END

DECLARE @val INT
SELECT @val=1
WHILE @val <= 200000
BEGIN  
   exec dbo.InsertRowSchemaOnly @val;
   SELECT @val=@val+1
END

Conclusión:

Al utilizar la opción SCHEMA_ONLY y los procedimientos almacenados compilados de forma nativa, podemos lograr mejoras significativas de rendimiento con la característica In-Memory OLTP en SQL Server. Sin embargo, es importante probar estos cambios en su propio entorno para verificar las ganancias de rendimiento.

Click to rate this post!

[Total: 0 Average: 0]

Comprehensive 360 Degree Assessment

Data Replication

Performance Optimization

Data Security

Database Migration

Expert Consultation

Cloud Migration Made Easy

Considering a move to the cloud? Axial SQL brings you proven migration strategies to streamline your transition. Our expert team ensures a smooth, efficient shift, keeping your data safe and accessible. Start your journey to the cloud with confidence!

SQL Performance Optimization

Is your SQL running slower than expected? Don't let sluggish performance hinder your business. Our optimization experts at Axial SQL specialize in tuning your databases for peak performance. Speed up your SQL and supercharge your data processing today!

Database Stability Solutions

Tired of frequent database outages? Discover stability with Axial SQL! Our comprehensive analysis identifies and resolves your database vulnerabilities. Enhance reliability, reduce downtime, and keep your operations running smoothly with our expert guidance.

Expert Database Team Evaluation

Questioning your database team's efficiency? Let Axial SQL provide an expert, unbiased analysis. We assess your team's strategies and workflows, offering insights and improvements to boost productivity. Elevate your database management to new heights!

Data Security Assurance

Concerned about your database security? Axial SQL is here to fortify your data defenses. Our specialized security assessments identify potential risks and implement robust protections. Keep your sensitive data secure and your peace of mind intact with our expert services.

Published on

Mejorando el rendimiento con In-Memory OLTP en SQL Server

Let's work together