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
--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
--Obtenemos los valores unicos
SELECT DISTINCT d_codigo
FROM CODIGOSPOSTALES
--Creamos un indice hash
alter table CodigosPostales
add index idx_cp hash (d_codigo) with (bucket_count = 1)
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'
--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)
No hay comentarios.:
Publicar un comentario