Síguenos en: YouTube

Prueba de rendimiento con SQL Server 2016

Aquí tenéis una prueba de rendimiento de los índices columnares (Columstore) en soluciones Business Intelligence.

José Miguel Calzada
José Miguel Calzada es responsable de Sistemas y Business Intelligence en Consultoría Certia.
30/05/2016

Prueba-rendimiento-con-SQL-Server-2016

Tras mucho leer sobre la eficiencia de los índices columnares (Columstore) cuando se usan con consultas de agregados típicas de soluciones Business Intelligence me he decidido a hacer una prueba.


Para ello he tomado una tabla procedente de una solución ya en producción desarrollada sobre SQL Server 2008; los datos de la tabla sin índices ni claves eran estos:


CREATE TABLE [dbo].[Avisos](

 [AvisoKey] [int] IDENTITY(1,1) NOT NULL,

[HoraAviso] [datetime] NULL, [HoraRecepcionAviso] [datetime] NULL,

[TiempoLlegadaAviso] [bigint] NULL [CodTarjeta] [nvarchar](10) NULL,

[Matricula] [nvarchar](10) NULL,

[NomPersona] [nvarchar](60) NULL,

[NumMensaje] [int] NULL,

[Mensaje] [nvarchar](60) NULL,

[ValPresencia] [int] NULL,

[TextoPresencia] [nvarchar](50) NULL,

[TipoMensaje] [int] NULL,

[NomLector] [nvarchar](40) NULL,

[TipoDispositivo] [int] NULL,

[CodEmpresa] [nvarchar](10) NULL,

[CodTarjetaPortatil] [nvarchar](10) NULL,

[Oculto] [nvarchar](1) NULL,

[TecnologiaLector] [nvarchar](1) NULL,

[CodigoTarjetaVehiculo] [nvarchar](10) NULL,

[Extension] [int] NULL) 

ON [PRIMARY]

Espacio de datos: 6.015,477 MB

Espacio de índices: 0,023 MB

Número de filas: 24.587.384


La consulta que he empleado para la prueba es esta:

SET STATISTICS TIME ON

SELECT TOP 5 Mensaje, SUM(TiempoLlegadaAviso), AVG(TiempoLlegadaAviso)

FROM dbo.Avisos

WHERE TiempoLlegadaAviso > 5 AND TipoMensaje=1

GROUP BY Mensaje  

 

He ido ejecutando sucesivamente la consulta en cuatro estados diferentes de indización obteniendo los siguientes resultados:

1 - Tabla sin índices:

Espacio de datos: 6.015,477 MB

Espacio de índices: 0,023 MB

Tiempo de CPU = 10985 ms

Tiempo total = 11100 ms

2 - Tabla con una clave primaria:  

ALTER TABLE dbo.Avisos ADD CONSTRAINT PK_Avisos PRIMARY KEY CLUSTERED (AvisoKey)

Espacio de datos: 6.017,742 MB

Espacio de índices: 9,734 MB

Tiempo de CPU = 10562 ms

Tiempo total = 10778 ms

3 - Tabla con un índice agrupado creado para optimizar la consulta:  

ALTER TABLE dbo.Avisos DROP CONSTRAINT PK_Avisos CREATE CLUSTERED INDEX CI_Avisos ON dbo.Avisos(TipoMensaje)

Espacio de datos: 6.163,758 MB

Espacio de índices: 18,383 MB

Tiempo de CPU = 9094 ms

Tiempo total = 9178 ms

4 - Tabla con un índice no agrupado columnar creado para cubrir la consulta:  

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Avisos ON dbo.Avisos(AvisoKey, NomLector, TiempoLlegadaAviso, TipoMensaje, Mensaje)

Espacio de datos: 6.358,148 MB

Espacio de índices: 212,773 MB

Tiempo de CPU = 47 ms

Tiempo total = 121 ms


Las sucesivas creaciones y borrado de índices se han hecho sin reorganizarlos ni optimizar las estructuras de la tabla de manera alguna. Podemos ver en las cifras de arriba que el espacio empleado por la tabla crece a medida que se crean nuevos índices aunque no lo hace en exceso.



La parte interesante viene en los tiempos empleados en la consulta; podemos ver que la sola creación de una clave primaria y su índice agrupado asociado proporciona un incremento aproximado del 3% en la velocidad de ejecución. No es mucho aunque se trata de un índice, digamos, de uso genérico. En el segundo caso, hemos creado un índice agrupado en una de las columnas del filtro (WHERE) de la consulta; en concreto de aquella para la cual buscamos un valor concreto (TipoMensaje). Para ello hemos tenido que eliminar la clave primaria ya que sólo podemos tener un índice agrupado. En este caso la mejora en la velocidad de ejecución es del 15% sobre la obtenida con sólo la clave primaria.

Finalmente, sin eliminar el índice agrupado, creamos un índice columnar no agrupado que cubra la consulta... y los resultados son espectaculares. La mejora es de un 99%. Llegados a este punto uno me he preguntado si todo el mérito se debía al índice columnar, así que he borrado el índice agrupado y los resultados han sido estos. Espacio de datos: 6.367,406 MB Espacio de índices: 203,68 MB Tiempo de CPU = 781 ms Tiempo total = 986 ms Un empeoramiento del 814%. En cifras absolutas sigue siendo un buen resultado pero, como se ha visto, mejorable.




Temas: SQL Server , Business Intelligence
Enlaces de interés
Articulos relacionados
Configuración en SQL Server de un servidor vinculado a Oracle
Primer vídeo de una nueva lista de reproducción dedicada a SQL Server de nuestro canal YouTube
Sistema de Fechas para soluciones Business Intelligence
Cómo construir un sistema de control de tiempos para nuestra solución BI potente, flexible y bajo nuestro completo control.
¿Qué es el Business Intelligence?
La solución informática que nos proporciona el conocimiento sobre cómo funciona nuestra empresa y que nos habilita para mejorar su funcionamiento.