Probando InMemory OLTP (Hekaton) en SQL Server 2014

By il_masacratore on 11 Noviembre, 2013 - 10:32
Base de datos

En la versión 2014 de Sql Server introducirán un nuevo motor en la base de datos que permitirá trabajar con tablas en memoria (inmemory o hekaton, su nombre en clave). Podemos imaginar que eso puede suponer una mejora considerable en el rendimiento si sabemos elegir para este nuevo las tablas adecuadas. Su funcionamiento es lógico y es el que cabe esperar. Según la MSDN, las tablas y sus registros se mantienen principalmente en memoria y una segunda copia se mantiene en disco para disponer de los datos si reiniciásemos la instancia. Tenemos también la posibilidad de elegir el tiempo que tarda en trasladarse un cambio en los datos de la tabla en memoria a la tabla en disco. También existe la posibilidad de crear tablas on-the-fly (non-durable table) y que no se persistan en disco. En cualquier caso hemos de tener en cuenta que en caso de desastre o reinicio de servidor, cualquier dato no trasladado a disco seguro que lo perdemos. Vamos a hacer unas pruebas.

Primero de todo, para poder usar tablas en memoria tenemos crear un grupo de ficheros del tipo adecuado. En este caso, anticipándome al futuro, combinamos uno de cada tipo (el normal por que sí y luego añadimos uno para datos en memoria después):

CREATE DATABASE [InMemoryTest]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'InMemoryTest', FILENAME = 
  N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\InMemoryTest.mdf' ,
  SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON 
( NAME = N'InMemoryTest_log', FILENAME = 
  N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\InMemoryTest_log.ldf' , 
  SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO


ALTER DATABASE InMemoryTest ADD FILEGROUP InMemoryTest_im CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE InMemoryTest ADD FILE (name='InMemoryTest_im1', 
  filename='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\InMemoryTest_im1') 
  TO FILEGROUP InMemoryTest_im

GO

--*** Si os aparece un error de que el procesador no es compatible usando VBox mirar al pie de página

A continuación vamos con nuestras tablas de ejemplo por cada tipo (la de toda la vida, DURABILITY = SCHEMA_ONLY y DURABILITY = SCHEMA_AND_DATA ):

 

-- Creación de la tabla normal (en el fg por defecto)
use InMemoryTest

CREATE TABLE Ventas (
IdVenta int identity PRIMARY KEY NOT NULL
,IdCliente int NOT NULL
,Total int NOT NULL
,FechaVenta date NOT NULL
,TipoVenta char(1) NOT NULL
,INDEX Ventas_FechaVenta NONCLUSTERED (FechaVenta)
)

-- Creación de la tabla en memoria,
solo con estructura en disco
use InMemoryTest

CREATE TABLE InMemoryVentas (
IdVenta int NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000)
,IdCliente int NOT NULL
,Total int NOT NULL
,FechaVenta date NOT NULL
,TipoVenta char(1) NOT NULL
,INDEX InMemoryVentas_FechaVenta
NONCLUSTERED HASH (FechaVenta) WITH (BUCKET_COUNT = 365)
) WITH (MEMORY_OPTIMIZED = ON,
DURABILITY = SCHEMA_ONLY)

-- Creación de la tabla en memoria con
datos y estructura en disco (DURABLE)
use InMemoryTest

CREATE TABLE InMemoryDurableVentas (
IdVenta int NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000)
,IdCliente int NOT NULL
,Total int NOT NULL
,FechaVenta date NOT NULL
,TipoVenta char(1) NOT NULL
,INDEX InMemoryVentas_FechaVenta
NONCLUSTERED HASH (FechaVenta) WITH (BUCKET_COUNT = 365)
) WITH (MEMORY_OPTIMIZED = ON,
DURABILITY = SCHEMA_AND_DATA)

Seguiremos con la carga de datos. Añado el mensage de output para ver cuanto tarda en cada tabla. Cuidado porque en este caso, la segunda tabla no se persiste en disco (DURABILITY = SCHEMA_ONLY) Si reiniciamos la instancia perderemos los datos. La tercera sí la persistimos en disco pero trabajamos con la copia en memoria.

-- Carga de datos para las tres tablas
SET NOCOUNT ON;
USE InMemoryTest

DECLARE @vCont int = 0;
PRINT( CONVERT(varchar, GETDATE(), 114) + N' > Insertando 1000000 en Ventas')

WHILE @vCont < 1000000
BEGIN
SET @vCont += 1;
INSERT INTO Ventas
VALUES (@vCont%11, 9999, DATEADD(DD,-@vCont%365,GETDATE()), @vCont%2)
END
PRINT( CONVERT(varchar, GETDATE(), 114) + N' > Insertados 1000000 en Ventas')

SET @vCont = 0;
PRINT( CONVERT(varchar, GETDATE(), 114) + N' > Insertando 1000000 en InMemoryDurableVentas')

WHILE @vCont < 1000000
BEGIN
SET @vCont += 1;
INSERT INTO InMemoryDurableVentas
VALUES (@vCont, @vCont%11, 9999, DATEADD(DD,-@vCont%365,GETDATE()), @vCont%2)
END

PRINT( CONVERT(varchar, GETDATE(), 114) + N' > Insertados 1000000 en InMemoryDurableVentas')
SET @vCont = 0;

PRINT( CONVERT(varchar, GETDATE(), 114) + N' > Insertando 1000000 en InMemoryVentas')
WHILE @vCont < 1000000
BEGIN
SET @vCont += 1;
INSERT INTO InMemoryVentas
VALUES (@vCont, @vCont%11, 9999, DATEADD(DD,-@vCont%365,GETDATE()), @vCont%2)
END

PRINT( CONVERT(varchar, GETDATE(), 114) + N' > Insertados 1000000 en InMemoryVentas')

En mi caso, la ejecución con de este último script de tabla me muestra que tarda cerca de 14 minutos en cargar la tabla en disco, cerca de 12 minutos la tabla en memoria persistida en disco al completo y apenas 1 solo minuto para la tabla de la que solo tenemos la estructura en disco (pero de la que al reiniciar la instancia perderemos sus datos). Esta prueba ya empieza a mostrar diferencias y sobretodo ventajas para trabajar con tablas en memoria...

 

Ahora vamos a probar y medir algunas consultas que haremos contra cada tipo de tabla. Para ello las ejecutamos previa limpieza de cache y buffers:

USE InMemoryTest;
GO

DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

SET NOCOUNT ON;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

PRINT(N'Sobre Ventas...')
SELECT DISTINCT IdCliente
FROM Ventas
WHERE FechaVenta = '01/01/2013';

PRINT(N'Sobre InMemoryDurableVentas...')
SELECT DISTINCT IdCliente
FROM InMemoryDurableVentas
WHERE FechaVenta = '01/01/2013';

PRINT(N'Sobre InMemoryVentas...')
SELECT DISTINCT IdCliente
FROM InMemoryVentas
WHERE FechaVenta = '01/01/2013';

Mi salida (que adjunto abajo) es la siguiente. No parece muy descriptiva pero ya vemos que el tiempo transcurrido es prometedor ya que pasamos de casi 400ms, a 1 y 0 milisegundos. Prometedor. Podéis probar de desordenar lo selects para modificar el orden si no os lo creéis y veréis que el resultado es muy similar. En mi opinión esto promete.


Sobre Ventas...

Tiempos de ejecución de SQL Server:
Tiempo de CPU = 0 ms, tiempo transcurrido = 0 ms.

Tabla 'Worktable'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0,

lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Tabla 'Workfile'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0,

lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Tabla 'Ventas'. Recuento de exámenes 3, lecturas lógicas 6302, lecturas físicas 1,

lecturas anticipadas 6225, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.


Tiempos de ejecución de SQL Server:
Tiempo de CPU = 250 ms, tiempo transcurrido = 397 ms.

Sobre InMemoryDurableVentas...

Tiempos de ejecución de SQL Server:
Tiempo de CPU = 0 ms, tiempo transcurrido = 0 ms.

Tabla 'Worktable'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0,

lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Tabla 'Workfile'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0,

lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.


Tiempos de ejecución de SQL Server:
Tiempo de CPU = 0 ms, tiempo transcurrido = 2 ms.

Sobre InMemoryVentas...

Tiempos de ejecución de SQL Server:
Tiempo de CPU = 0 ms, tiempo transcurrido = 0 ms.

Tabla 'Worktable'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0,

lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Tabla 'Workfile'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0,

lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.


Tiempos de ejecución de SQL Server:
Tiempo de CPU = 0 ms, tiempo transcurrido = 1 ms.

 

Aunque, en el ejemplo hay trampa, porque si miramos los planes de ejecución veremos que en el caso de la tabla de toda la vida tenemos un index scan mientras que en el caso de las tablas en memoria tenemos index seek (scan vs seek, gana seek) sobre el HASH INDEX. Eso es porque de forma inherente los indices de tablas en memoria ya son punteros directos a los datos de fila parecidos a los covering indexes. Es más, nos propone crearlo.

CREATE NONCLUSTERED INDEX Ventas_FechaVentaIncIdCliente
ON [dbo].[Ventas] ([FechaVenta])
INCLUDE ([IdCliente])

Lo crearemos, volveremos a ejecutar la consulta y el resultado debe ser como el siguiente.

Tiempos de ejecución de SQL Server:
Tiempo de CPU = 0 ms, tiempo transcurrido = 0 ms.

Tabla 'Worktable'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0,

lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Tabla 'Workfile'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0,

lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Tabla 'Ventas'. Recuento de exámenes 1, lecturas lógicas 15, lecturas físicas 0,

lecturas anticipadas 12, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.


Tiempos de ejecución de SQL Server:
Tiempo de CPU = 0 ms, tiempo transcurrido = 10 ms.

Como vemos sigue ganando la tabla en memoria... de momento. Ahora cambiamos el tipo de consulta a una sin igualdad, cambiaremos a una de rango.

USE InMemoryTest;
GO

DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

SET NOCOUNT ON;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

PRINT(N'Sobre Ventas...')
SELECT DISTINCT IdCliente
FROM Ventas
WHERE FechaVenta >= '01/01/2013'
AND FechaVenta < '01/02/2013';

PRINT(N'Sobre InMemoryDurableVentas...')
SELECT DISTINCT IdCliente
FROM InMemoryDurableVentas
WHERE FechaVenta >= '01/01/2013'
AND FechaVenta < '01/02/2013';

PRINT(N'Sobre InMemoryVentas...')
SELECT DISTINCT IdCliente
FROM InMemoryVentas
WHERE FechaVenta >= '01/01/2013'
AND FechaVenta < '01/02/2013';

El resultado es este:


Sobre Ventas...

Tiempos de ejecución de SQL Server:
Tiempo de CPU = 0 ms, tiempo transcurrido = 0 ms.

Tabla 'Worktable'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0,

lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Tabla 'Workfile'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0,

lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Tabla 'Ventas'. Recuento de exámenes 1, lecturas lógicas 362, lecturas físicas 0,

lecturas anticipadas 125, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Tiempos de ejecución de SQL Server:
Tiempo de CPU = 78 ms, tiempo transcurrido = 71 ms.


Sobre InMemoryDurableVentas...

Tiempos de ejecución de SQL Server:
Tiempo de CPU = 0 ms, tiempo transcurrido = 0 ms.

Tabla 'Worktable'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0,

lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Tabla 'Workfile'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0,

lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Tiempos de ejecución de SQL Server:
Tiempo de CPU = 266 ms, tiempo transcurrido = 255 ms.


Sobre InMemoryVentas...

Tiempos de ejecución de SQL Server:
Tiempo de CPU = 0 ms, tiempo transcurrido = 0 ms.

Tabla 'Worktable'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0,

lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Tabla 'Workfile'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0,

lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Tiempos de ejecución de SQL Server:
Tiempo de CPU = 250 ms, tiempo transcurrido = 252 ms.

El resultado ya no mola tanto pero esto se soluciona añadiendole un nuevo indice de rango, más adecuado si esperamos hacerle consultas del tipo. Por desgracia la única manera de hacerlo es recrear la tabla.

 

Mis conclusiones...

...son que este tipo de tablas pueden ser muy útiles pero hay que saber cuando usarlas. Me vale que podamos mantener los dos tipos de grupos de fichero (normales y preparados para persistir en memoria). También creo que en rendimiento puro no hay mucha diferencia en las tablas que están en memoria, entre la que persiste en disco y de la que solo mantenemos la estructura. En todo caso, puede ser interesante investigar el tema de DELAYED_DURABILITY para posponer la propagación de los cambios a disco.

 

 

*** The model of the processor on the system does not support creating filegroups with MEMORY_OPTIMIZED_DATA. This error typically occurs with older processors.

Este error aparece al crear el tipo de filegroup con la clausula MEMORY_OPTIMIZED_DATA si estamos usando por lo menos la versión 4.1 de Oracle VirtualBox. Actualizando la versión podríamos tener suficiente para solventar el problema. Si no ejecutamos lo siguiente en el directorio de VirtualBox:

VBoxManage setextradata [nombre_maquina_virtual] VBoxInternal/CPUM/CMPXCHG16B 1

 

administracion
hekaton
inmemory oltp
SQL Server 2014

Prueba Semrush gratis 14 días!