SQL - CLUSTERED COLUMNSTORE INDEX

Buenas tardes a todos.

Estoy tratando de utilizar en SQL CLUSTERED COLUMNSTORE INDEX, mis dudas son:

-En la parte de la Partición que se pone y un ejemplo de como podria quedar la estructura
-Siempre es necesario poner partición?

Sentencia:
CREATE CLUSTERED COLUMNSTORE INDEX index_name
ON [database_name. [schema_name ] . | schema_name . ] table_name
ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n ] ) ]

Yo lo eh ajustado asi:
CREATE CLUSTERED COLUMNSTORE INDEX faily_445_1
ON LADW.t-fact_daily_445
ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n ] ) ]

Cualquier ayuda y/o comentarios son bien recibidos!

Voy a intentar ayudarle, ¿para que desea utilizar el índice column store?

Estamos apunto de realizar una migración de sql server 2008 a 2014 y queremos utilizar el indice column store para mejorar el rendimiento de las consultas y acortar el tiempo de ejecución de procesos ETL, en realidad no tengo mucho conocimiento sobre el tema todavía.
Saludos.

El concepto de COLUMN STORE INDEX no son para tablas de tipo OLTP, son para bases de datos de tipo Datawarehouse (BI).

Comúnmente se utilizan para tablas de SOLO LECTURA

Busca en www.google.com, como hacer uso y donde de COLUMNA STORE INDEX

Hola tengo unas dudas con este mismo tema, estoy intentando usar NonClustered ColumnStore Index en Sql Server 2016, tengo entendido que ahora el Column Store es actuaizable por medio del OLTP.

El punto es que unas consultas me duraban mucho tiempo 6-8mins, apliqué el columnStore y el tiempo se redujo a aproximadamente unos 30segundos, lo cual es muy bueno, pero agregé un registro en el detalle y las mismas consultas pasron a durar más de 7minutos.

Mi escenario es el siguiente:

Tengo 4 tablas (EncVtas con 9millones de registros, DetVtas con 39 Millones de Registros, Productos con 3.500 registros y Restaurantes con 60 Registros)

DESEO Saber y entender porque no obtengo buen rendimiento en mis consultas y como poder optimizarlas.

Hice pruebas con mis consultas cambiando: ,SUM(DetVtas.PreVta * DetVtas.UniVen) AS Total por SUM(DetVtas.PreVta) * sum(DetVtas.UniVen) AS Total , y el rendimiento fue igual.

Los column store generados fueron en EncVtas los campos Descuento y SubTotal, adicional luego agregue NumFactura para el Count()
En DetVtas el Column Store contiene los campos: PreVta y UniVen

Las estructuras de mis tablas son las siguientes:

CREATE TABLE [dbo].[Restaurantes](
[CodRestaurante] [char](2) NOT NULL,
[NomRestaurante] [varchar](30) NULL,
[CodProvincia] [smallint] NULL,
[CodCanton] [smallint] NULL,
CONSTRAINT [PK_Restaurantes] PRIMARY KEY CLUSTERED
(
[CodRestaurante] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[EncVtas](
[CodRest] [char](2) NOT NULL,
[NumFactura] [numeric](10, 0) NOT NULL,
[TipoFactura] [char](2) NULL,
[NumOrden] [varchar](6) NULL,
[CodCliente] [varchar](12) NULL,
[NomCliente] [varchar](30) NULL,
[NumAdicional] [varchar](5) NULL,
[TipoCliente] [char](1) NULL,
[CodCaja] [char](2) NULL,
[CodCajero] [char](2) NULL,
[CodSalonero] [char](2) NULL,
[CodSupervisor] [char](2) NULL,
[FecFactura] [datetime] NOT NULL,
[SubTotal] [money] NOT NULL,
[Descuento] [money] NULL,
[ImptoServicio] [money] NULL,
[ImptoVentas] [money] NULL,
[ImptoVta] [real] NULL,
[Anulada] [int] NULL,
[MotivoAnulacion] [varchar](100) NULL,
[NumCierre] [int] NULL,

CONSTRAINT [PK_EncVtas] PRIMARY KEY CLUSTERED
(
[CodRest] ASC,
[NumFactura] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[DetVtas](
[CodRest] [char](2) NOT NULL,
[NumFactura] [numeric](10, 0) NOT NULL,
[NumLin] [numeric](5, 0) NOT NULL,
[CodPro] [char](5) NULL,
[NomPro] [varchar](50) NULL,
[PreVta] [money] NULL,
[PorDescto] [real] NULL,
[UniVen] [float] NULL,
[Anulada] [int] NULL,
[TipoServicio] [char](1) NULL,
[ImptoVta] [float] NULL,
[Impreso] [bit] NULL,
[CostoPro] [money] NULL,

CONSTRAINT [PK_DetVtas] PRIMARY KEY CLUSTERED
(
[CodRest] ASC,
[NumFactura] ASC,
[NumLin] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Productos](
[CodPro] [char](5) NOT NULL,
[CodCla] [char](2) NOT NULL,
[NomPro] [varchar](50) NOT NULL,
[PreVta] [money] NOT NULL,
[CostoAnt] [money] NULL,
[CostoPro] [money] NULL,
[Exento] [bit] NULL,
CONSTRAINT [PK_ProductosTMP] PRIMARY KEY CLUSTERED
(
[CodPro] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


/* ---------------------------------- */
****
**** Estas son unas de mis consultas que duran mucho ****
**** Repito, solo 1 vez antes de incluir registros en DetVtas, obtuve buen rendimiento, Recupere un Backup, volvi a generar los indices, y mi rendimiento en las consultas es pesimo ***

Consultas usadas.

Declare @FechaInicial date;
Declare @FechaFinal date;

Set @FechaInicial = '01/01/2016';
Set @FechaFinal = '01/04/2016';


/*Ventas por Tipo Servicios, Esta s funciona rapido */
Select top 10 TipoFactura,count(NumFactura) as Transacciones,sum(Subtotal)-sum(Descuento) as Total
from EncVtas INNER JOIN Restaurantes ON EncVtas.CodRest = Restaurantes.CodRestaurante
Where
FecFactura >= @FechaInicial
and FecFactura <= @FechaFinal
and
EncVtas.Anulada=0
group by tipofactura

/*Ventas por 10 Productos Mas Venta esta funciona muy lento */
SELECT TOP (10) Productos.NomPro,SUM(DetVtas.PreVta) * sum(DetVtas.UniVen) AS Total -- SUM(DetVtas.PreVta * DetVtas.UniVen) AS Total
FROM DetVtas INNER JOIN Productos ON DetVtas.CodPro = Productos.CodPro
INNER JOIN EncVtas ON DetVtas.CodRest = EncVtas.CodRest AND DetVtas.NumFactura = EncVtas.NumFactura
INNER JOIN Restaurantes ON EncVtas.CodRest = Restaurantes.CodRestaurante
Where
FecFactura >= @FechaInicial
and FecFactura <= @FechaFinal
and EncVtas.Anulada=0
GROUP BY Productos.NomPro
ORDER BY Total DESC

--Ticket Promedio Ventas
Select TipoFactura, (sum(Subtotal)-sum(Descuento)) / COUNT(NumFactura) as Total
from EncVtas INNER JOIN Restaurantes ON EncVtas.CodRest = Restaurantes.CodRestaurante
Where
FecFactura >= @FechaInicial
and FecFactura <= @FechaFinal
and EncVtas.Anulada=0
group by TipoFactura



** Gracias por cualquier ayuda que me puedan brindar ***

Saludos!!!!!

Gracias por el comentario, ya encontre donde estaba mi error, y si ahora SQL 2016 permite indices columnares sobre el OLTP y ahora estos indices se actualizan automaticamente sin necesidad de re-crear los column sotre index.

He encontrado el problema.

Primero aunque si es cierto que para column store index se usaban tablas de solo lectura, ahora en SQL 2016 han mejorado estas opciones y ahora puede ser aplicables a tablas de tipo OLTP.

Mi solución fue:
1) Eliminar todos los indices y PKs, en las tablas de encabezado y detalle.
2) Crear un Clustered Column Store Index (Antes tenia un NonClustered Column Store y no me funcionaba para lo que quería.)
3) Crear un Indice Secundario Unico con los campos que tenia anteriormente con el Primary Key, esto porque para poder crear un Clustered ColumnStore Index tuve que eliminar el Primary key, por consiguiente para seguir manteniendo la integridad de los datos me resulto muy bien un indice secundario.

Algo que me sirvió mucho fue utilizar los planes de ejecución para visualizar que al principio mis consultas No estaban usando los indices columnares, con esto fue que tuve la idea de borrar todo y comenzar de nuevo pero esta vez usando el Clustered Column Store.

Aquí les dejo una explicacion buena sobre este tema
https://www.youtube.com/watch?v=mikO5gPN5Cw

De momento después de que mis consultas duraban mas de 8minutos, pasaron a durar entre 6 y 8 segundos.

Después de hacer modificaciones o inserciones de datos, el indice se actualiza automaticamente y las consultas siguen igual de rapidas.