ms sql server 2014 - in-memory columnstore index - haciendo un almacén de datos
DESCRIPTION
Las características "In Memory" es la tendencia más de perspectiva en el área de alto rendimiento. Los Índices de ColumnStore es uno de las tales características, y aún con sus limitaciones, pueden aceleran a veces sus consultas! ¿Cómo obtener más de esta característica? ¿En qué situaciones debemos usarlos? ¿Qué mecanismos internos ayudan a lograr eso? Usted puede obtener respuestas a estas y otras preguntas que se haya generado en algún momento en esta sesión.TRANSCRIPT
MS SQL Server 2014In Memory ColumnStore Indexes - Haciendo un Almacén de Datos
José Redondo
DPA SolidQ | SDP Bits America Colombia | SQL Server MVP
Correo: [email protected] | Twitter: @redondoj | Blog: redondoj.wordpress.com
Expositor
Jose Redondo
DPA, SolidQ – Specialist Data Platform, BITS Americas
Colombia
MCP | MCTS – MS SQL Server; MTA – DAF | MS SQL Server
MVP
MS SQL Server 2014In Memory ColumnStore Indexes - Haciendo un Almacén de Datos
AGENDA
Introducción
Implementación & Mantenimiento
Arquitectura & Funcionamiento
Compresión de datos
Modo de procesamiento por lotes
Un aproximado de un volumen de datos
administrado por un Almacén de Datos (DW)
0% 10% 20% 30% 40% 50%
No sabe
Mas de 10TB
3-10TB
1-3TB
Menos de 1TB
Hace 3 años Hoy
Como Microsoft SQL Server responde a esta oportunidad?
Tecnología Microsoft “In-Memory”
Qué es In-Memory?
Tecnologías implícitas en In-Memory
Componentes involucrado
xVelocity
Hardware de ultima
generación
Memoria (NUMA)
Múltiple núcleos CPU
Motor de análisis de datos
PowerPivot
SQL Server Analysis Services
ColumnStore Index
SQL Server Database Engine
Tecnologías implícitas en In-Memory
MS SQL Server 2012
MS SQL Server 2014
MS SQL Server Parallel Data Warehouse (v1 –
v2)
Escenarios comunes para acelerar las consultas
en los Almacenes de Datos
MS SQL Server 2012 introdujo dos innovaciones
dirigidas para cargas de trabajo en el ámbito de
almacenamiento de datos (Data Warehouse)
Índices de almacenamiento columnar (ColumnStore
Index)
Modo de procesamiento por lotes vectorizado (Batch
“vectorized” Processing Mode)
MS SQL Server 2012
Introducción
Qué es ColumnStore Index?
Una tecnología para
almacenar, recuperar y
administrar datos utilizando
un formato de datos
columnares
Los datos son
comprimidos, almacenados
y administrados como una
colección de columnas
parcialmente
Qué es ColumnStore Index?
Podemos usar los índices de
almacenamiento columnar para
responder a una consulta como
cualquier otro tipo de índice de datos
El optimizador de consultas
considera los índices de
almacenamiento columnar como
origen de datos para acceder a los
mismos considerando otros índices
al crear un plan de consulta
Qué es ColumnStore Index?
Un índice de almacenamiento
columnar son datos
organizados lógicamente
como una tabla con filas y
columnas, y físicamente
almacenados en un formato
de datos columnares.
Beneficios
Forma parte de la nueva tecnología llamada xVelocity
Ejecución de consultas 10x
Hasta 10x en el incremento del funcionamiento de las consultas
comparado con el almacenamiento tradicional orientada basado
en filas, al almacenar y comprimir datos por columnas
Compresión de datos 7x
Hasta 7x sobre el tamaño de datos
sin comprimir, siendo utilizando menos
lectura de los mismos al traerlos
comprimidos en memoria para luego, al
utilizar el volumen de datos reducidos para
el procesamiento en la memoria
Ver el Índice Agrupado (Clustered)
ColumnStore como el estándar para el
almacenamiento de grandes almacenes de
tablas de hechos, y esperar a que se
utilicen en la mayoría de los escenarios de
Data Warehousing
Donde aplicarlos?
Apunte de Microsoft desde MSDN
Mejoras
Hacer tablas actualizables
Disponibilidad en la modificación de esquemas
Más tipos de datos incluidos
Soporte en la ejecución de modos mixtos
Más operaciones son soportadas para el modo por lotes
Diccionarios globales mejorados para la compresión de los
segmentos de datos
Soporte de compresión de datos archival
Soporte nativo Seek y Spill para Bulk Insert masivos
Implementación & Mantenimiento
Características claves
Índices de Almacenamiento Columnar (Clustered)Agrupados Añadido como nueva característica en MS SQL Server
2014
Índice de Almacenamiento Columnar (Nonclustered)No Agrupado Añadido como nueva característica en MS SQL Server
2012
Los Índices de Almacenamiento Columnar NOnecesitan ningún tipo de hardware para ejecutarse
Índice de Almacenamiento Columnar
(Nonclustered) No Agrupado No es necesario incluir todas las columnas
de la tabla
Requiere de almacenamiento para guardaruna copia de las columnas en el índice
Puede combinarse con otros índices delatabla de datos
Usa compresión para AlmacenamientoColumnar La compresión no es configurable
Físicamente, no almacena columnasordenadas En cambio, almacena los datos para mejorar la
compresión y el rendimiento
Índice de Almacenamiento Columnar
(Clustered) Agrupado Disponible en las Ediciones Enterprise y
Developer de SQL Server 2014
Incluye todas las columnas de la tabla y es el
método para almacenar toda la tabla
Es el único índice sobre la tabla
No puede combinarse con cualquier otros
índice
Usa la compresión para el almacenamiento
columnar
La compresión no es configurable
Físicamente, no almacena columnas
ordenadas
En cambio, almacena los datos para mejorar
tanto la compresión así como el rendimiento
Crear un Índice de Almacenamiento
Columnar
Tipo de datos no compatibles
VARCHAR(MAX), NVARCHAR(MAX)
NTEXT, TEXT, IMAGE
DECIMAL (y NUMERIC) con precisión mayor de 18 dígitos
DATETIMEOFFSET, con escala mayor de 2
ROWVERSION (y TIMESTAMP)
SQL_VARIANT
Tipos de CLR (Por ejemplo: Tipos HIERARCHYID y SPATIAL)
XML
Características no compatibles
Columnas Sparse
Columnas calculadas
Columnas Incluidas
Vistas o Vistas Indexadas
No pueden ordenarse por ASC o DESC
Replicación
FileStream
Change Tracking y Change Data Capture
Nivel de Aislamiento Soportado
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
READ_COMMITED_SNAPSHOT
Aplicando eficientemente los índices de
almacenamiento columnar Poner los índices de almacenamiento columnar
únicamente en tablas grandes Típicamente, están ubicadas en las tablas de hechos
del almacén de datos, pero no las tablas de dimensiones
Si tenemos en nuestros entornos tablas de dimensiones muy grandes, que almacenan varios de millones de registros, entonces deberíamos aplicar un índice de almacenamiento columnar así como cualquier otro índice en dicha tabla
Incluir todas las columnas de la tabla en el índice de almacenamiento columnar Si no lo diseñamos de esta manera, tendremos el
inconveniente de no beneficiar dicha consulta (O cualquier consulta) puesto que no aplica referencia alguna al escenario en general
Aplicando eficientemente los índices de
almacenamiento columnar Estructurar de las consultas como uniones
('Joins') estrella con agrupaciones y agregacionestanto como sea posible Evitar generar uniones con tablas grandes
Generar uniones de tablas de Hechos grandes auna o mas con tablas de Dimensiones pequeñasutilizando el estándar inners join
Utilice un enfoque de modelado dimensional tantocomo sea posible para que nuestros datos puedanpermitírseles estructurar todas sus consultas eneste escenario
Utilizar las mejores prácticas para laadministración de las estadísticas así como lasconsultas mismas Esto es MUY independiente a la tecnología de los
índices de almacenamiento columnar
Utilizar eficientemente las estadísticas en estecontexto con el fin de evitar errores en el diseño delas mismas, y así obtener un excelente rendimiento
Leyendo la metadata de los Índices de
Almacenamiento Columnar Sys.column_store_dictionaries
Contiene una fila por cada Diccionariousado con el optimizador de memoria dexVelocity en el índices dealmacenamiento columnar
Sys.column_store_segments Contiene una fila por cada columna en
un índice de almacenamiento columnar
Sys.column_store_row_groups Proporciona información sobre el índice
de almacenamiento columnar agrupado(Clustered) sobre una base porsegmento (per-segment)
Muy útil para determinar qué registrosagrupados tienen un alto porcentaje defilas eliminadas, conllevando conesto, que debe ser reconstruido dichoíndice
Comando DBCC CSIndex
Comando DBCC no documentado
Soportado en MS SQL Server 2012 y posterior
Similar a DBCC PAGE para Índices de Almacenamiento Columnar
• rowsetid
• HoBT o PartitionID desde sys.column_store_segments
• columnid
• column_id desde sys.column_store_segments
• rowgroupid
• segment_id desde sys.column_store_segments
• object_type
• 1 = Segmento
• 2 = Diccionario
• print_option
• Los valores validos son 0,1 y 2
• En estado Beta en la actualidad
Arquitectura & Funcionamiento
Almacenamiento Columnar vs. Heap y B-
TreeD
ato
s a
lma
ce
na
do
s c
om
o R
eg
istr
os
Da
tos a
lma
ce
na
do
s c
om
o C
olu
mn
as
Beneficios del Almacenamiento
Columnar
Bajo consumo de memoria. Tasas de compresión alta mejoran el rendimiento de las consultas mediante el uso de un bajo uso en la memoria
existente. A su vez, puede mejorar el rendimiento de una consulta porque SQL Server puede realizar másoperaciones de consultas y datos en memoria (In-Memory)
I/O se reduce ampliamente Las consultas frecuentemente selecciona únicamente unas pocas columnas de una tabla, con lo cual reduce el total
de I/O hacia y desde los medios físicos obteniendo rentabilidad en los recursos existente en el escenario a ejecutarse
Reducción del uso de la CPU La tecnología de ejecución de consultas avanzadas, procesa "Partes" de las columnas llamadas "Lotes" de una
manera simplificada, conllevando a la reducción del uso de la CPU
Términos Claves – Primera Parte
Rowgroup (Grupos de Registros)
Es un grupo de filas que están
comprimidos en formato de
Almacenamiento Columnar al mismo
tiempo
Cada columna en el rowgroup (Grupo de
Registro) se comprime y se almacenan
por separado en los medios físicos
Cada rowgroup contiene un segmento
de la columna para cada columna de la
tabla
Los RowGroups define los valores de
columna que se encuentran en cada
segmento de la columna
Términos Claves – Segunda Parte
Column Segment (Segmento de
Columna)
Es la unidad de almacenamiento básico para
un índice de almacenamiento columnar
Es un grupo de columnas con valores
comprimido y físicamente almacenados en
conjunto en los medios físicos
Cada columna esta compuesta de uno o
varios segmentos de la columna
Cuando SQL Server comprime un
rowgroup, comprime cada columna dentro de
la rowgroup como segmento de una columna
Términos Claves – Tercera Parte
ColumnStore
(Almacén Columnar) Son datos que lógicamente
está organizados comouna tabla con filas ycolumnas
Físicamente almacenadosen un formato de datoscolumnares
Las columnas se divide ensegmentos y se almacenancomo segmentos de lacolumna comprimida
Términos Claves – Cuarta Parte
Rowstore (Almacén deRegistros) Un rowstore son datos que se
encuentran organizados tanto porfilas así como columnas para luegoser físicamente almacenados en unformato de datos row-wise
Esta ha sido la manera tradicionalpara almacenar los datos en tablasrelacionales
Términos Claves – Quinta Parte
Deltastore Es una tabla de RowStore (Almacén de
registro) que contiene todos los registros
hasta que el número de las filas sea
suficientemente grande como para ser
trasladado al almacén columnar
(ColumnStore)
Los registros acumulados en cada DeltaStore
se almacenarán hasta que el número de filas
sea el máximo permitido en un rowgroup
Para cada almacén columnar (ColumnStore)
puede haber múltiples DeltaStores
Para una tabla con particiones, hay uno o más DeltaStores para cada partición
Los DeltaStore se encuentran en el formato de registro tradicional de índices (B-Trees)
Es muy costoso para consultar los segmentos columnares comprimidos
Cada DeltaStore contiene 1.048.576 filas, cuando llega a ser convertido en almacenamiento columnar(ColumnStore)
Terminología en gráfico
Ejemplo de Índice ColumnarID_FechaFactura Id_Productos Id_Tiendas Id_Region Cantidad TotalVentas
20080615 2023 100 1 8 4.500,00
20080615 1756 105 3 5 2.500,00
20080615 4520 105 8 6 6.300,00
20080615 1756 109 1 2 4.580,00
20080615 3652 112 9 8 6.500,00
20080615 2023 102 4 4 4.100,00
20080615 7542 102 3 7 4.200,00
20080616 2023 105 1 8 9.800,00
20080616 1756 109 3 6 9.650,00
20080616 4520 102 8 2 6.525,00
20080616 3652 100 7 8 7.500,00
20080616 2023 112 1 4 6.540,00
20080616 2023 105 1 6 7.512,00
20080617 1756 112 3 2 9.641,00
20080617 2023 105 7 8 3.298,00
20080617 7542 102 9 4 1.278,00
Ejemplo de Índice Columnar
ID_FechaFactura Id_Productos Id_Tiendas Id_Region Cantidad TotalVentas
20080615 2023 100 1 8 4.500,00
20080615 1756 105 3 5 2.500,00
20080615 4520 105 8 6 6.300,00
20080615 1756 109 1 2 4.580,00
20080615 3652 112 9 8 6.500,00
20080615 2023 102 4 4 4.100,00
20080615 7542 102 3 7 4.200,00
20080616 2023 105 1 8 9.800,00
ID_FechaFactura Id_Productos Id_Tiendas Id_Region Cantidad TotalVentas
20080616 1756 109 3 6 9.650,00
20080616 4520 102 8 2 6.525,00
20080616 3652 100 7 8 7.500,00
20080616 2023 112 1 4 6.540,00
20080616 2023 105 1 6 7.512,00
20080617 1756 112 3 2 9.641,00
20080617 2023 105 7 8 3.298,00
20080617 7542 102 9 4 1.278,00
Paso 1 – Partición Horizontalmente (Crear un Row Groups)
~1M
registros
Ejemplo de Índice Columnar
Paso 2 – Partición Verticalmente (Crear Segmentos)
Ejemplo de Índice Columnar
Paso 3 – Comprimir cada Segmento
Algunos segmentos
serán comprimidos
mas que otros
Ejemplo de Índice Columnar
Paso 4 – Leer los datos
Insert Es insertado en uno de los Delta Stores en sesión
Deletes Si la fila eliminada se encuentra
en el interior de un RowGroup,
entonces los datos borrados del
Bitmap se actualizan con el identificador
del registro de la fila respectiva
Si la fila eliminada esta en sesión
dentro de un DeltaStore, entonces
el proceso de eliminación de dicho
registro se ejecuta en el índice B-Tree
Updates Como se entiende e interpreta una
actualización representada en su
acción de eliminar e insertar
Como ejecutar operaciones básicas
Como se crean los DeltaStore?
Comandos INSERT, DELETE y MERGE
Que no apliquen el comando BULK
INSERT API
Excluyendo INSERT . . . SELECT . . .
BULK INSERT (Tamaño regular)
Tamaño promedio de Bach de registros
de 100.000, insertándose como una
DeltaStore
Alrededor de 100.000 filas se crea en un
segmento comprimido
Pero en un Índice de Almacenamiento
Columnar Agrupado se fundamenta esta
en segmentos de 100k filas al ser
suboptimizadas
El tamaño ideal del lote es de 1.000.000
registros
Tupla Motriz
Cuando un DeltaStore . . . Alcanza el tamaño máximo de 1,048,576
registros
Ha sido cerrado
Y estará disponible para la Tupla Motriz alser comprimida
La Tupla Motriz Crea grandes y óptimos segmentos de
registros
Esto no ha sido diseñado para serreemplazados por la recreación de índices
Se ejecuta cada 5 minutos en segundo plano
Ejecución On-Demand ALTER INDEX . . . REORGANIZED
ALTER INDEZ . . . REBUILD
Tupla Motriz
Delta
(Registro)
Store
Column Store
Tu
pla
Mo
triz
Consumo de Memoria
En MS SQL Server 2014 El grado de paralelismo (DOP) estará variando de la misma forma
que SQL Server pueda estar cambiando en el consumo dememoria en un momento dado basándose en los recursosactualmente disponibles
Esto significa que algunos de los hilos de ejecución podrían inclusoponerse en espera, con el fin de mantener estable el sistema
Memoria concedida como requerimiento en MB =
(((4.2 * COLNUM) + 68) * DOP) + (CHRCOL * 34)
COLNUM = Números de columnas en el Índice de Almacenamiento Columnar
DOP = Grado de Paralelismo
CHRCOL = Números de columnas de texto en el Índice de Almacenamiento Columnar
Error 8657 o 8658 Estos errores acontecen cuando falla la concesión inicial de la memoria
Considere cambiar los ajustes del Gobernador de Recursos para permitir que lainstrucción de CREATE INDEX pueda acceder a más memoria La configuración predeterminada para el Gobernador de Recursos limita una consulta en el
pool predeterminado al 25% de memoria disponible
Incluso, si el servidor se encuentra inactivo
Esto es cierto, incluso si no ha activado la Gobernador de Recursos
Error 701 o 802 Se pueden obtener estos errores, si la memoria se agote tardíamente durante la
ejecución
La única forma de solucionar estos errores en este caso serían: Para reducir el Grado de Paralelismo (DOP) explícitamente cuando se crea el Índice
Para reducir la concurrencia en las consultas, o añadir más memoria
Errores de Memoria durante la Creación del
Índice de Almacenamiento Columnar
Eliminando BitMap
Un almacenamiento que contieneinformación sobre los registrosborrados dentro de los segmentos
La representación de la memoria esun BitMap
Almacenada en el disco como un B-Tree Contiene identificadores de las filas
eliminadas
Consultado de manera regular Para evitar devolver las filas que ya fueron
eliminadas
Almacenamiento de los Índices de
Almacenamiento Columnar
Ilustración sobre como un Índice de Almacenamiento Columnar es creado y almacenado
El conjunto de filas se divide en grupos de fila que se convierten en los segmentos de la columna y diccionarios que luego se almacenan utilizando almacenamiento Blob de SQL Server
Qué son los Diccionarios?
Ampliamente utilizado en el Almacenamiento Columnar
De manera eficiente, codifica los tipos de datos de gran tamaño como
cadenas de caracteres
Los valores almacenados en los segmentos de la columna serán solo números de
entrada en el diccionario, y los valores reales son los que se almacenan en el
diccionario
Eficiente compresión de datos para valores repetidos Pero produce malos resultados si los valores son muy distintos (En realidad, aumenta
el almacenamiento requerido)
Esto es lo que hace que se incremente el tamaño de las columnas (cadena decaracteres) con valores distintos, siendo muy malos candidatos para los Índices deAlmacenamiento Columnar
Los Índices de Almacenamiento Columnar contienen diccionarios separados paracada columna, y las columnas con valores de cadena contienen dos (2) tipos dediccionarios como se explicaran a continuación:
Diccionarios
Diccionario Primario (Global) Este es un diccionario global que es usado
por todos los segmentos de una columna
Diccionario Secundario (Local) Este es un diccionario de desbordamiento
para la entradas que no aplicaba en losdiccionarios primarios
Esta puede ser compartida por diversossegmentos de una columna: La relaciónentre diccionarios y segmentos decolumna es de uno a muchos
sys.column_store_dictionaries Puede encontrarse información sobre los
diccionarios utilizados por un almacéncolumnar en esta DMV
Compresión de datos
Compresión de datosEspacio utilizado en GB (101 millones de registros por tabla)
0
5
10
15
20
0.0 5.0 10.0 15.0 20.0
Tabla con indexación ordinaria
Tabla con indexación ordinaria (Con compresión de página)
Tabla con ningún índice
Tabla con ningún índice (Con compresión de página)
Tabla con índice de almacenamiento columnar
Almacenamiento columnar agrupado
Espacio utilizado = Table space + Index space
Compresión “Modo Archival”
Nuevo en MS SQL Server 2014 Puede ser aplicado en una tabla o una partición
Proporciona una compresión amplia de 37% a67% El aumento de la compresión depende de los datos
Proceso transparente
Comprime los datos Blob antes de almacenarlos endisco
La compresión de archivo “Archival” se implementacomo una capa de compresión extra al proceso, yaque transparentemente son comprimidos los datos enel disco
Utiliza el algoritmo XPress8 Es una variante interna de compresión denominada
LZ77 generada por Microsoft en los años 70
Trabaja con multiples hilos
Utiliza un máximo de flujo de datos de 64KB
Comparación del Modo de Compresión
Archivo
Nombre de Base de
Datos
Tamaño del dato
(GB)
Ratio de compresión
% de Compresión en Modo Archivo
GZIP
No Si
PDVSA95,4 5,84 9,33 4,85
MACMA 41,3 2,2 3,65 3,08
ASODIAM 47,1 3,0 5,27 5,1
SHELL 1,3 5,41 10,37 8,07
INTER Network 14,7 6,92 16,11 11,93
GM Foods1,0 23,8 70,4 43,3
El cuadro anterior muestra los diferentes escenarios de compresión de datos alcanzadas con y sin compresión de archivo de
datos para varios conjuntos de datos reales
Modo de procesamiento por lotes
Presentado por primera vez en MS SQL Server 2012
Utiliza un nuevo modelo de iterador para le
procesamiento de datos de “Un Lote a la Vez” en
lugar de “Una Fila a la Vez”
Un lote típicamente es representado por 1000 registros de
datos
Cada columna dentro de un lote se almacena como un
vector en un área separada de la memoria, ósea que el
procesamiento en modo por lote es basado en vectores
Utiliza algoritmos que están optimizados para el
rendimiento del aumento de memoria y CPU multinucleos que se encuentran en hardware modernos
El procesamiento en modo por lotes expande los costos de acceso a metadatos y otros tipos de gastos para todas las
filas de un lote, en lugar de pagar el costo por cada fila
Así mismo, funciona con datos comprimidos cuando sea posible y elimina algunos de los operadores de intercambio
utilizados por el procesamiento en modo de fila
El resultado es obtener un mejor y mas eficiente paralelismo, y una ejecución mas rápida y optima
Modo de procesamiento por lotes
Modo de procesamiento por lotesM
S S
QL S
erv
er
2012 S
P1
Modo de procesamiento por lotesM
S S
QL S
erv
er
2012 S
P1
Modo de procesamiento por lotesM
S S
QL S
erv
er
2012 S
P1
Modo de procesamiento por lotesM
S S
QL S
erv
er
2012 S
P1
Modo de procesamiento por lotesM
S S
QL S
erv
er
2014 C
TP
2
Modo de procesamiento por lotesM
S S
QL S
erv
er
2014 C
TP
2
Modo de procesamiento por lotes
MS
SQ
L S
erv
er
2014 C
TP
2
DEMO
PREGUNTAS & RESPUESTAS
MS SQL Server 2014In Memory ColumnStore Indexes - Haciendo un Almacén de Datos
Jose Redondo – MS SQL Server MVPCorreo: [email protected]
Twitter: @redondoj
Blog: redondoj.wordpress.com
Los invitamos al
Muchas gracias por su participación