Indices columnares en SQL Server 2012

Enrique Puig DPE – Motor Relacional MAP 2012 – MCPIT SQL Server

epuig@solidq.com

Javier Loria Mentor – Motor Relacional & BI De todo! Es una máquina !

jloria@solidq.com

Introducción Arquitectura

Claves de rendimiento

Restricciones

Escenarios factibles Datawharehouse

Secundarios de reporte

Escritura y uso Deshabilitando índices

Híbrido: Tabla + Vista

Particionado de datos

Conclusiones

Preguntas

Agenda

¿Qué vamos a ver hoy?

Objetivo: Acelerar consultas sobre grandes volúmenes de datos.

Nuevo tipo de índice No es un índice (no es un árbol)

No tiene clave y no permite seeks ni scan range

Solo permite escaneos

Nuevo tipo de almacenamiento Filas vs. Columnas

Xvelocity Almacenamiento columnar

Algoritmos de compresión

Memoria

Fast batch mode

Introducción

¿Que son los índices columnares?

Introducción

Arquitectura

idVenta Producto cantidad Precio

1 Camisa 2 49.99

2 Jersey 3 60.00

3 Zapatos 4 224.75

4 Pantalón 1 35.00

5 Camisa 1 60.00

6 Jersey 4 180.00

7 Zapatos 6 315.50

8 Pantalón 1 48.75

Tabla

Data page 1

1 camisa 49.99 2

2 Jersey 60.00 3

3 Zapatos 224.75 4

4 Pantalón 35.00 1

Data page 2

1 camisa 60.00 1

2 Jersey 180.00 4

3 Zapatos 315.50 6

4 Pantalón 48.75 1

idventa Producto

1

2

3

4

5

6

7

8

Cantidad Precio

camisa

Jersey

Zapatos

Pantalón

camisa

Jersey

Zapatos

Pantalón

2

3

4

1

1

4

6

1

49.99

60.00

224.75

35.00

60.00

180.00

315.50

48.75

Segmentos

XVelocity

Almacenamiento columnar Consultas especifican qué columnas quieren

Ratios de compresión Elevados ELEVADISIMOS!!! Compresiones 10x

Disminuye el número de lecturas

Maximización de la memoria Solo columnas deseadas

Altamente comprimidas

Maximización del paralelismo Fast batch mode

Claves de rendimiento

¿Por qué va tan rápido?

Claves de rendimiento

Almacenamiento en filas

Memoria Disco

Data page 1

1 camisa 49.99 2

2 Jersey 60.00 3

3 Zapatos 224.75 4

4 Pantaló

n 35.00 1

Data page 2

1 camisa 60.00 1

2 Jersey 180.00 4

3 Zapatos 315.50 6

4 Pantaló

n 48.75 1

Data page 1

1 camisa 49.99 2

2 Jersey 60.00 3

3 Zapatos 224.75 4

4 Pantalón 35.00 1

Data page 2

1 camisa 60.00 1

2 Jersey 180.00 4

3 Zapatos 315.50 6

4 Pantalón 48.75 1

Select

idVenta,

Precio

From Tabla

Claves de rendimeinto

Índices columnares

Memoria Disco Select

idVenta,

Precio

From Tabla idventa Producto

1

2

3

4

5

6

7

8

Cantidad Precio

camisa

Jersey

Zapatos

Pantalón

camisa

Jersey

Zapatos

Pantalón

2

3

3

1

5

6

7

8

49.99

60.00

224.75

35.00

49.99

60.00

224.75

35.00

1

2

3

4

5

6

7

8

49.99

60.00

224.75

35.00

49.99

60.00

224.75

35.00

DEMO Rendimiento de índices columnares

Nuevo modo de procesamiento Modo “Batch”

Hasta ahora -> Row by Row Modo “Row”

Aprovecha el paralelismo Consigue muy buenos tiempos

Solo funciona con planes paralelos

Solo se usa con índices columnares

Solo consultas que realizan: Cruces (JOIN)

Filtros (WHERE)

Agregaciones (GROUP BY)

Claves de rendimiento

Fast batch Mode

Modo de procesamiento actual

Válido para sistemas OLTP Se supone poco análisis

Escaneos de pocas filas

Operaciones seek

Llama a la función GetNextRow() Árbol de llamadas

Procesamiento Fila a Fila

GetNextRow() GetNextRow() GetNextRow()

Ejemplo Consulta analítica más intensa

Productos más vendidos

Tabla ventas: 20 Millones de filas

20M + 20M + 500 + 500 = 40.001.000 llamadas

Impacto en rendimiento

Procesamiento Fila a Fila

¿Y para grandes volúmenes de datos?

GetNextRow() GetNextRow() GetNextRow() GetNextRow()

20M Filas 20M Filas 500 Filas 500 Filas

Procesamos “batches” de filas 1000 filas aprox.

No todos operadores implementan el modo batch Conversiones

Batch -> Row

Row -> Batch

Planes de ejecución mixtos Parte en batch y parte en Row

Mejor rendimiento a mayor parte procesada en batch

Cuidado con operadores

OUTER JOIN, IN / NOT IN, EXISTS / NOT EXISTS, DISTINCT

Fast batch mode

SQL Server vuela…

Ejemplo Consulta analítica más intensa

Productos más vendidos

Tabla ventas: 20 Millones de filas

20M /1000 filas = 2000 * 2= 4000 llamadas

4000 + (500+500) = 5000 llamadas

40M / 5000 = 8000 veces menos en llamadas

Mejora el rendimiento

Fast Batch mode

¿Y para grandes volúmenes de datos?

GetNextRow() GetNextRow() GetNextRow() GetNextRow()

20M Filas 20M Filas 500 Filas 500 Filas

DEMO Fast Batch Mode

- Row mode vs. Batch mode performance

- Operators que no implementan batch mode

Batch mode vs. Row Mode y paralelismo

184

3423

7264

11918

0 2000 4000 6000 8000 10000 12000 14000

Columnstore

Sin columnstore

Duration (ms)

Comparacion rendimiento consulta

No paralelo

paralelo

Decimal y/o numeric (precision >18)

Datetimeoffset (precision > 2)

Binary, Varbinary

Image

Text, ntext

Varchar(max), nvarchar(max)

HierarchyID

Timestamp

Uniqueidentifier

Sqlvariant

xml

Restricciones

Tipos de datos

1 único indice columnar No puede ser clustered

No aplica a vistas indexadas

No se pueden filtrar

1024 columnas como máximo

Convierten tablas en modo lectura INSERT, UPDATE, DELETE y MERGE no funcionan

No resulta factible para sistemas OLTP

Restricciones

Limitaciones de tabla

Grandes volúmenes de datos

Sistemas orientados al análisis

Escrituras incrementales Cargas de datos de distintos orígenes

ETL

Idealmente no hay modificaciones Versionado de los hechos

Mayoría de las operaciones son de lectura Consultas TSQL

Reportes

Cargas de cubos

Consultas directas de cubos

ROLAP

DirectQuery

Escenarios donde encaja

DataWharehouse

Servidores OLTP Tablas Sumarizadas

Mantenimiento de tablas con índices columnares

Ventana de mantenimiento (8x5)

Sincronización

Depende del negocio

Servidores destinados a reporting Descargan el sistema OLTP

Sistemas puramente transaccionales

Ejecutan

Todos los informes

Al menos los informes más pesados

Escenarios donde encaja

Reporting

Técnicas de carga

Deshabilitando/Habilitando el índice

1 • Deshabilitar el índice

2 • Insertar/borrar/modificar datos

3 • Reconstruir el índice

DEMO Habilitando/deshabilitando el índice

Técnicas de carga

Particionado de datos

1 • Crear tabla intermedia

2 • Insertar datos

3 • Crear índice columnar

4 • SWITCH a una partición vacía

DEMO Particionado de datos

Técnicas de carga

Hibrido: Tabla + Vista

Tabla

Normal

Tabla

Columnar

SYNC Vista

DEMO Pruebas en escenarios reales

Proporcionan muy buen rendimiento

Mas eficientes con Fast Batch mode

Descartados para aplicar directamente a tablas del OLTP

Más adecuado para Servidores de reporting

Datawharehouse

Consultas ROLAP/Directquery

Estamos todavía en versión 1.0 ¿Quién sabe si se eliminaran restricciones?

Conclusiones

Si quieres disfrutar de las mejores sesiones de

nuestros mentores de España y Latino América,

ésta es tu oportunidad.

http://summit.solidq.com/madrid/

Síguenos: