Tablas en memoria RAM SQL SERVER

 

Buenas tardes queridos lectores, el día de hoy les traigo un post sobre las tablas en memoria en SQL SERVER. Las tablas en memoria comenzaron desde SQL SERVER 2012, y mejoraron en las siguientes versiones. 

Les dejo también un video más explicativo

Las principales ventajas de tener una tabla en memoria es optimizar los recursos y mejorar el performance de la base de datos. Actualmente la memoria se ha abaratado y es sencillo incrementar cada vez más y más la memoria de nuestro servidor. 

Pero basta de teoría, mejor pasemos a la práctica que es lo que realmente nos gusta.

Para crear y utilizar una tabla optimizada para memoria, realizaremos los siguientes pasos:

Agregar un filegroup optimizado para memoria

• Vamos primeramente a agregar un filegroup optimizado para memoria y para eso ejecutaremos el siguiente script

USE [Pruebas]

GO

-- Creamos el filegroup

ALTER DATABASE pruebas

ADD FILEGROUP PruebaMemoryTable CONTAINS MEMORY_OPTIMIZED_DATA;

• En segundo lugar asignamos ese filegroup a una carpeta del sistema, en esa carpeta se realizará una copia de los datos que esten en tabla en memoria, y lo realizaremos con el siguiente script:

--Creamos la carpeta que contendra copia de los datos

ALTER DATABASE Pruebas

ADD FILE (name = 'PruebaMemoryTable1', filename = 'D:\BD\MemoryTable')

TO FILEGROUP PruebaMemoryTable

Este script lo que genera es una carpeta en la ruta especificada donde en caso de configurarla para guardar esquema y datos se haga una copia en esa ubicación

Crear y usar tablas en memoria

Una vez que tenemos el filegroup, y la carpeta contenedora, vamos a crear la tabla optimizada para memoria, hay que tener en cuenta la instrucción Bucket_count. El bucket count es el número de punteros que se van a utilizar, se basa en el número de datos únicos que existen, pero ya lo veremos mas adelante. Es importante mencionar que los datos de ejemplo los tomé de una base de datos del INEGI de códigos postales. Les dejo el link para que la descarguen y lo importen de aquí

Vamos a crear una tabla optimizada para memoria siguiendo el siguiente script:

--Creamos una tabla de codigos postales

CREATE TABLE [dbo].CodigosPostales(

id int identity primary key nonclustered hash with (Bucket_count = 152000), --Cada bucket es un puntero en la memoria

d_codigo nvarchar(10) NULL,

d_asenta nvarchar(max) NULL,

d_tipo_asenta nvarchar(max) NULL,

D_mnpio nvarchar(max) NULL,

d_estado nvarchar(max) NULL,

d_ciudad nvarchar(max) NULL,

d_CP nvarchar(max) NULL,

c_estado nvarchar(max) NULL,

c_oficina nvarchar(max) NULL,

c_CP nvarchar(max) NULL,

c_tipo_asenta nvarchar(max) NULL,

c_mnpio nvarchar(max) NULL,

id_asenta_cpcons nvarchar(max) NULL,

d_zona nvarchar(max) NULL,

c_cve_ciudad nvarchar(max) NULL

) with (memory_optimized=on, durability = Schema_and_data) --La optimizamos para memoria y la durabilidad son schema y datos

GO

Pongamos atención al campo id, ese campo será el campo primary key, el índice será nonclustered y será de tipo hash, el bucket_count será de 15200, ¿porque? porque es el número de punteros que tendrá nuestro índice, en este caso los valores distintos, en el caso de éste índice en particular al ser un id, pues todos son distintos, por lo cual será el valor aproximado del numero de registros de la tabla, veamos con una consulta esto que les comento.

id int identity primary key nonclustered hash with (Bucket_count = 152000), --Cada bucket es un puntero en la memoria

--contar los valores que existen en cpdescarga

select count(*) from cpdescarga

Como vemos en la imagen, existen 151407 registros, es por eso que en el bucket_count yo utilice 152000

Ahora consideremos también la siguiente línea:

with (memory_optimized=on, durability = Schema_and_data) --La optimizamos para memoria y la durabilidad son schema y datos

En esa línea le estamos indicando al motor que esa tabla será en memoria y además la durabilidad será esquema y datos, de este modo si se reinicia la maquina o la instancia, los datos estarán respaldados en la carpeta que les comenté anteriormente. Podemos utilizar solo el esquema, pero en este caso, los datos desaparecerían al reiniciar el servidor o la instancia

Ahora vamos a cargarle datos a la tabla que acabamos de crear y lo haremos desde la tabla que importamos con el archivo que les deje en el link

insert into CodigosPostales

(

d_codigo

,d_asenta

,d_tipo_asenta

,D_mnpio

,d_estado

,d_ciudad

,d_CP

,c_estado

,c_oficina

,c_CP

,c_tipo_asenta

,c_mnpio

,id_asenta_cpcons

,d_zona

,c_cve_ciudad)

select

d_codigo

,d_asenta

,d_tipo_asenta

,D_mnpio

,d_estado

,d_ciudad

,d_CP

,c_estado

,c_oficina

,c_CP

,c_tipo_asenta

,c_mnpio

,id_asenta_cpcons

,d_zona

,c_cve_ciudad

from cpdescarga

En este momento ya tenemos la tabla en memoria con datos, y podemos comprobarlo mediante la siguiente consulta:

select * from CodigosPostales

where d_codigo = '45079'

Como vemos, ya hay datos cargados en la tabla en memoria, sin embargo, no existe un índice y lo podemos confirmar en el plan de ejecución:

Como ven, la consulta me está mostrando un Table Scan, por lo cual crearemos un índice y quedará más claro el concepto del bucket_count.

El índice lo vamos a crear sobre el campo d_codigo, que es el dato que tenemos en el WHERE, el indice en este caso, como habíamos comentado líneas arriba es un índice HASH.

Crear índice HASH en tabla optimizada para memoria

Primeramente obtendremos los datos que existen distintos, es decir, cuantos códigos postales existen en la tabla, y para eso, realizaremos la siguiente consulta:

--Obtenemos los valores unicos

SELECT DISTINCT d_codigo

FROM CODIGOSPOSTALES

Como vemos, existen 31932 códigos postales distintos, por lo cual el bucket_count tendrá que ser similar, pero vamos a realizar una prueba y le pondremos un bucket_count de 1. Esto lo realizaremos de la siguiente manera:

--Creamos un indice hash

alter table CodigosPostales

add index idx_cp hash (d_codigo) with (bucket_count = 1)

En este caso para crear un índice es distinto a la manera tradicional, en este caso hay que alterar la tabla y agregar el índice, el campo será d_codigo para este ejemplo y como bucket_count le pondremos un 1, lo cual sería incorrecto, ya que estamos asignándole un solo puntero para 31932 datos. Veamos el resultado

 Vean como el tiempo que tardo en crear el índice hacia un solo puntero es de más de dos minutos, ahora veamos nuevamente el resultado de la consulta y su plan de ejecución:

select * from CodigosPostales

where d_codigo = '45079'

Ahora vemos un plan de ejecución mucho más limpio, sin embargo el performance no es el óptimo pues, recordemos que apunta hacía un solo puntero, además vemos que la consulta tardó 74 ms en ejecutarse, parece insignificante, pero son pocos datos en realidad. Vamos a borrar el índice y a crearlo con el bucket_count correcto y veamos el resultado

--Borramos el indice Hash

ALTER TABLE [dbo].[CodigosPostales] DROP INDEX [idx_cp]

GO

--Volvemos a crear el indice HASH

alter table CodigosPostales

add index idx_cp hash (d_codigo) with (bucket_count = 32000)

En este caso con el bucket_count correcto, solo tardo ¡1 segundo! en crear el índice, lo cual resulta en un mejor performance como podemos comprobar en el tiempo de ejecución

Hasta aquí el post queridos lectores, les invito a compartir y comentar, así como suscribirse al canal de YouTube. 

Saludos