Índices de almacén de columnas

  • Artículo

Un índice de almacén de columnas optimizado de memoria xVelocity agrupa y almacena los datos de cada columna y, a continuación, combina todas las columnas para completar el índice de todo el conjunto. Este comportamiento es distinto en los índices tradicionales, que agrupan y almacenan los datos de cada fila y, a continuación, combinan todas las filas para completar el índice del conjunto. En algunos tipos de consultas, el procesador de consultas de SQL Server puede aprovechar el diseño del almacén de columnas para mejorar considerablemente el tiempo de ejecución. El drástico crecimiento del almacenamiento de datos, la ayuda para la toma de decisiones y las aplicaciones de BI han generado una urgencia por leer y procesar conjuntos de datos muy grandes con gran rapidez y convertirlos en información y conocimiento de utilidad. Supone un gran desafío mantener o mejorar este incremento de los volúmenes de datos y las crecientes expectativas de desarrollo a través de mejoras incrementales de rendimiento. SQL Server resulta particularmente apropiada para los conjuntos de datos de almacenamiento de datos comunes. Los índices de almacén de columnas pueden transformar la experiencia de almacenamiento de datos de los usuarios, ya que permite un rendimiento más rápido en las consultas habituales de almacenamiento de datos, como el filtrado, la agregación, la agrupación y la combinación en estrella de consultas.

Contenido

Conceptos básicos

  • Conceptos básicos: Índices de almacén de columnas descritos

  • Conceptos básicos: Restricciones y limitaciones del índice de almacén de columnas

  • Ejemplo de demostración: Índices de almacén de columnas con una tabla con particiones

  • Conceptos básicos: Escenarios típicos del índice de almacén de columnas

  • Conceptos básicos: Optimizaciones de filtros de mapas de bits

Procedimientos recomendados

  • Procedimientos recomendados: Actualizar datos en un índice de almacén de columnas

  • Procedimientos recomendados: Elegir columnas en un índice de almacén de columnas

  • Procedimientos recomendados: Tablas con particiones

Procedimientos

  • Crear un índice de almacén de columnas

  • Determinar el tamaño de un índice de almacén de columnas

  • Solucionar problemas de rendimiento de un índice de almacén de columnas

Conceptos básicos: Índices de almacén de columnas descritos

Los índices de almacén de columnas de Motor de base de datos de SQL Server se pueden usar para acelerar considerablemente el tiempo de procesamiento de las consultas habituales de almacenamiento de datos. Las cargas de trabajo más habituales del almacenamiento de datos tienen que ver con el resumen de grandes cantidades de datos. Las técnicas que se usan normalmente en los sistemas de almacenamiento de datos y de ayuda a la toma de decisiones para mejorar el rendimiento son las tablas de resumen precalculadas, vistas indizadas, cubos OLAP, etc. Aunque pueden acelerar en gran medida el procesamiento de la consulta, estas técnicas pueden ser inflexibles y difíciles de mantener, y deben diseñarse específicamente para cada problema de la consulta.

Por ejemplo, imagine una tabla de hechos F1 con las columnas de clave de dimensión dk1 y dk2. Suponga que M es una función de agregado, por ejemplo, SUM. En lugar de calcular M en la columna dk1 cada vez que se ejecute una consulta que haga referencia a M(dk1), se puede crear y usar una tabla de resumen F2(dk1, M) para que el resultado pueda precalcularse y la consulta se ejecute más rápido. Sin embargo, si se necesita una nueva consulta que haga referencia a M(dk2), tiene que crearse una nueva tabla de resumen F3 (dk2, M) con esta información. Cuando el número de columnas de una tabla aumenta y tiene muchas funciones posibles, este enfoque comienza a resultar difícil de mantener y no resulta fácil cubrir todas las consultas necesarias.

Esta sobrecarga puede ser importante para el usuario. Con los índices de almacén de columnas de SQL Server, los usuarios pueden reducir la sobrecarga de las demás soluciones. Los índices de almacén de columnas también permiten que las consultas calculen los resultados con tanta rapidez que no es necesario precalcularlos.

Las principales características de la tecnología de almacén de columnas de SQL Server son las siguientes:

  • Datos con formato de columna: a diferencia de la organización tradicional de los datos basada en filas (lo que se denomina almacén de filas), en los sistemas de bases de datos en columnas, como SQL Server con índices de almacén de columnas, los datos se agrupan y se almacenan en una sola columna cada vez. El procesador de consultas de SQL Server puede aprovechar el diseño de los nuevos datos para mejorar considerablemente el tiempo de ejecución.

  • Mayor rapidez de los resultados de consulta: los índices de almacén de columnas pueden generar resultados con mayor rapidez por las razones siguientes:

    • Solo deben leerse las columnas necesarias. Por tanto, son menos los datos del disco que se leen en memoria y que se mueven posteriormente de la memoria a la memoria caché del procesador.

    • Las columnas están muy comprimidas. Esto reduce el número de bytes que deben leerse y moverse.

    • La mayoría de las consultas no afectan a todas las columnas de la tabla. Por tanto, muchas columnas nunca se llevarán a la memoria. Esto, combinado con una compresión excelente, mejora el uso del grupo de búferes, lo que reduce el número total de E/S.

    • La tecnología avanzada de ejecución de consultas procesa los fragmentos de columna denominados lotes de forma optimizada, lo que reduce el consumo de CPU.

  • Columnas de clave: el concepto de columnas de clave no está presente en los índices de almacén de columnas, por lo que la limitación del número de columnas de clave (16) de un índice no se aplica a los índices de almacén de columnas.

  • Clave de índice clúster: si una tabla base tiene un índice clúster, todas las columnas de la clave de agrupación en clústeres deben estar presentes en el índice no clúster de almacén de columnas. Si una columna de la clave de agrupación en clústeres no aparece en la instrucción CREATE INDEX, se agregará automáticamente al índice de almacén de columnas.

  • Creación de particiones: los índices de almacén de columnas funcionan con la partición de la tabla. No es necesario realizar ningún cambio en la sintaxis de creación de particiones de la tabla. Un índice de almacén de columnas de una tabla con particiones debe estar alineado por partición con la tabla base. Por tanto, solo se puede crear un índice no clúster de almacén de columnas en una tabla con particiones si la columna de partición es una de las columnas del índice de almacén de columnas.

  • Tamaño de registro: la limitación de tamaño del registro de claves de índice de 900 bytes tampoco se aplica a los índices de almacén de columnas.

  • Procesamiento de consultas: junto con el índice de almacén de columnas, SQL Server incorpora el procesamiento por lotes para aprovechar la orientación por columnas de los datos. La estructura y el procesamiento por lotes del almacén de columnas contribuye a mejorar el rendimiento, pero la investigación de los problemas de rendimiento puede resultar más compleja que si solo estuviera implicado un factor.

  • La tabla no se puede actualizar: en SQL Server 2012, no se pueden actualizar las tablas con índices de almacén de columnas. Para obtener información sobre las posibles soluciones, vea Procedimientos recomendados: actualizar los datos de un índice de almacén de columnas

Si desea consultar la sintaxis para crear un índice de almacén de columnas, vea CREATE COLUMNSTORE INDEX (Transact-SQL).

Tipos de datos

Los tipos de datos empresariales comunes pueden incluirse en un índice de almacén de columnas. Los siguientes tipos de datos pueden incluirse en un índice de almacén de columnas.

  • char y varchar

  • nchar y nvarchar (excepto varchar(max) y nvarchar(max))

  • decimal (y numeric) (excepto con una precisión mayor que 18 dígitos).

  • int, bigint, smallint y tinyint.

  • float (y real)

  • bit

  • money y smallmoney

  • Todos los tipos de datos de fecha y hora (excepto datetimeoffset con escala mayor que 2)

Los siguientes tipos de datos no pueden incluirse en un índice de almacén de columnas:

  • binary y varbinary

  • ntext, text e image

  • varchar(max) y nvarchar(max)

  • uniqueidentifier

  • rowversion (y timestamp)

  • sql_variant

  • decimal (y numeric) con una precisión superior a 18 dígitos

  • datetimeoffset con escala mayor que 2

  • Tipos CLR (hierarchyid y tipos espaciales)

  • xml

Posible reducción del rendimiento

El rendimiento de las consultas de ayuda para la toma de decisiones a menudo mejora cuando se usan índices de almacén de columnas en tablas grandes; sin embargo, algunas consultas (e incluso el conjunto de las cargas de trabajo) puede experimentar un deterioro del rendimiento. Si se usa un enfoque basado en costos, normalmente el optimizador de consultas solo querrá usar un índice de almacén de consultas cuando mejore el rendimiento global de la consulta. Sin embargo, los modelos de costos que el optimizador usa son aproximados y, en ocasiones, el optimizador opta por usar el índice de almacén de columnas de una tabla cuando sería mejor usar un almacén de filas (árbol b o montón) para tener acceso a la tabla. Si esto ocurre, use la sugerencia de consulta IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX o use una sugerencia de índice para dirigir el optimizador a un índice de almacén de filas. El optimizador podría continuar incluyendo información del índice de almacén de columnas. Por tanto, en contadas ocasiones, es posible que esta opción no resuelva el problema de rendimiento. Si el índice de almacén de columnas no favorece al rendimiento de la carga de trabajo y no se pueden usar sugerencias de índice para corregir el problema, quite el índice de almacén de columnas para volver al procesamiento del almacén de filas.

Áreas problemáticas

Los índices de almacén de columnas de SQL Server y el procesamiento de consultas basado en columnas están optimizados para las consultas habituales de almacenamiento de datos, caracterizadas por una tabla de hechos grande y unas tablas de dimensiones entre moderadas y pequeñas combinadas en una configuración de esquema en estrella y, posteriormente, agrupadas y agregadas. Aunque el número de filas de la tabla de hechos es grande, estas consultas suelen devolver un conjunto de resultados comparativamente pequeño porque se agregan los datos. El rendimiento de las consultas que usan el índice de almacén de columnas puede resultar lento cuando una o varias de las condiciones siguientes son ciertas.

  • El conjunto de resultados es grande porque no se agregan los datos. (Devolver un conjunto de resultados grande es intrínsecamente más lento que devolver un pequeño conjunto de resultados).

  • No hay combinación, filtrado ni agregación. En este caso no hay ningún procesamiento por lotes. Por consiguiente, las ventajas del almacén de columnas se limitan a los beneficios derivados de la compresión y lectura de un menor número de columnas.

  • Dos tablas grandes deben combinarse juntas de forma que se creen tablas hash grandes que no quepan en la memoria y deban volcarse a un disco.

  • Se devuelven varias columnas, lo que hace que se recupere una parte mayor del índice de columnas.

  • Una condición de combinación en una tabla con índices de almacén de columnas contiene varias columnas.

Puede usar los métodos descritos anteriormente en esta sección para solucionar la lentitud de procesamiento derivada del uso del almacén de columnas si se produce por alguna de estos motivos.

Arriba

Conceptos básicos: Restricciones y limitaciones del índice de almacén de columnas

Restricciones básicas

Un índice de almacén de columnas:

  • No puede tener más de 1024 columnas.

  • No puede ser clúster. Solo están disponibles los índices no clúster de almacén de columnas.

  • No puede ser un índice único.

  • No se puede crear en una vista o una vista indizada.

  • No puede incluir ninguna columna dispersa.

  • No puede actuar como clave principal ni clave externa.

  • No se puede cambiar utilizando la instrucción ALTER INDEX. En su lugar se debe quitar y volver a crear el índice de almacén de columnas. (Puede usar ALTER INDEX para deshabilitar y volver a generar un índice de almacén de columnas).

  • No se puede crear con la palabra clave INCLUDE.

  • No puede incluir las palabras clave ASC ni DESC para ordenar el índice. Los índices de almacén de columnas se ordenan de acuerdo con los algoritmos de compresión. No se permite la ordenación en el índice. Los valores seleccionados de un índice de almacén de columnas se pueden ordenar por el algoritmo de búsqueda, pero se debe utilizar la cláusula ORDER BY para garantizar la ordenación de un conjunto de resultados.

  • No usa ni mantiene estadísticas a la manera de un índice tradicional.

  • No puede contener una columna con un atributo FILESTREAM. Otras columnas de la tabla que no se emplean en el índice pueden contener el atributo FILESTREAM.

Una tabla con un índice de almacén de columnas no se puede actualizar.

Para solucionar este problema, vea Prácticas recomendadas: Actualizar datos en un índice de almacén de columnas.

Efectos de la limitación de memoria

El procesamiento del almacén de columnas está optimizado para el procesamiento en memoria. SQL Server implementa mecanismos que permiten que los datos y la mayoría de las estructuras de datos puedan volcarse a un disco cuando no haya suficiente memoria disponible. Si hay restricciones de memoria severas, el procesamiento usa el almacén de filas. Puede haber casos en los que el índice de almacén de columnas se elija como método de acceso, pero la memoria sea insuficiente para crear las estructuras de datos necesarias. Al iniciarse como una operación de almacén columnas y establecer el valor predeterminado en una ruta de acceso al código más lenta, puede producirse cierto deterioro del rendimiento en los casos en los que las consultas encuentren una restricción severa de memoria. El requisito de memoria eficiente para una consulta dependerá de la consulta especificada. Para generar un índice de almacén de columnas, se necesitan aproximadamente 8 MB por el número de columnas del índice por el DOP (grado de paralelismo). Por lo general, los requisitos de memoria aumentan en proporción a las columnas en las que se incrementan las cadenas. Por consiguiente, al reducir el DOP se pueden reducir los requisitos de memoria necesarios para generar el índice de almacén de columnas.

La evaluación de algunas expresiones será más rápida que en otras

Cuando se emplea el índice de almacén de columnas, algunas expresiones comunes se evalúan usando un modo por lotes en lugar de usar una fila cada vez. El modo por lotes proporcionará una aceleración de consulta adicional, además de las ventajas que brinda usar un índice de almacén de columnas. No todos los operadores de ejecución de consultas están habilitados para el modo de procesamiento por lotes.

El índice de almacén de columnas no admite SEEK

Si se espera que la consulta devuelva una pequeña fracción de las filas, no es probable que el optimizador seleccione el índice de almacén de columnas (por ejemplo: las consultas de tipo aguja en un pajar). Si se usa la sugerencia de tabla FORCESEEK, el optimizador no tendrá en cuenta el índice de almacén de columnas.

Los índices de almacén de columnas no se pueden combinar con las siguientes características:

  • Compresión de página y fila, y formato de almacenamiento vardecimal (un índice de almacén de columnas ya está comprimido en un formato diferente).

  • Replicación

  • Seguimiento de cambios

  • Captura de datos modificados

  • Secuencia de archivo

Arriba

Ejemplo de demostración: Índices de almacén de columnas con una tabla con particiones

En los ejemplos de este tema se usa una tabla con particiones denominada FactResellerSalesPtnd que se creó en la base de datos de ejemplo AdventureWorksDW2012 . Para probar un índice de almacén de columnas en una tabla con particiones, conéctese a la base de datos AdventureWorksDW2012 y ejecute el código siguiente para crear una versión con particiones de la tabla de hechos.

[!NOTA]

Para obtener información sobre las bases de datos e instrucciones para descargar la base de datos, vea Bases de datos de ejemplo AdventureWorks.

Crear la tabla de FactResellerSalesPtnd

  • Ejecute el código siguiente para crear una versión con particiones de la tabla FactResellerSales denominada FactResellerSalesPtnd.

    USE AdventureWorksDW2012;
GO

CREATE PARTITION FUNCTION [ByOrderDateMonthPF](int) AS RANGE RIGHT 
FOR VALUES (
    20050701, 20050801, 20050901, 20051001, 20051101, 20051201, 
    20060101, 20060201, 20060301, 20060401, 20060501, 20060601, 
    20060701, 20060801, 20060901, 20061001, 20061101, 20061201, 
    20070101, 20070201, 20070301, 20070401, 20070501, 20070601, 
    20070701, 20070801, 20070901, 20071001, 20071101, 20071201, 
    20080101, 20080201, 20080301, 20080401, 20080501, 20080601, 
    20080701, 20080801, 20080901, 20081001, 20081101, 20081201
) 
GO

CREATE PARTITION SCHEME [ByOrderDateMonthRange] 
AS PARTITION [ByOrderDateMonthPF] 
ALL TO ([PRIMARY]) 
GO

-- Create a partitioned version of the FactResellerSales table
CREATE TABLE [dbo].[FactResellerSalesPtnd]( 
    [ProductKey] [int] NOT NULL, 
    [OrderDateKey] [int] NOT NULL, 
    [DueDateKey] [int] NOT NULL, 
    [ShipDateKey] [int] NOT NULL, 
    [CustomerKey] [int] NOT NULL, 
    [EmployeeKey] [int] NOT NULL, 
    [PromotionKey] [int] NOT NULL, 
    [CurrencyKey] [int] NOT NULL, 
    [SalesTerritoryKey] [int] NOT NULL, 
    [SalesOrderNumber] [nvarchar](20) NOT NULL, 
    [SalesOrderLineNumber] [tinyint] NOT NULL, 
    [RevisionNumber] [tinyint] NULL, 
    [OrderQuantity] [smallint] NULL, 
    [UnitPrice] [money] NULL, 
    [ExtendedAmount] [money] NULL, 
    [UnitPriceDiscountPct] [float] NULL, 
    [DiscountAmount] [float] NULL, 
    [ProductStandardCost] [money] NULL, 
    [TotalProductCost] [money] NULL, 
    [SalesAmount] [money] NULL, 
    [TaxAmt] [money] NULL, 
    [Freight] [money] NULL, 
    [CarrierTrackingNumber] [nvarchar](25) NULL, 
    [CustomerPONumber] [nvarchar](25) NULL,
    OrderDate [datetime] NULL,
    DueDate [datetime] NULL,
    ShipDate [datetime] NULL
) ON ByOrderDateMonthRange(OrderDateKey);
GO

-- Using simple or bulk logged recovery mode, and then the TABLOCK 
-- hint on the target table of the INSERT…SELECT is a best practice
-- because it causes minimal logging and is therefore much faster.
ALTER DATABASE AdventureWorksDW2012 SET RECOVERY SIMPLE;
GO

-- Copy the data from the FactResellerSales into the new table
INSERT INTO dbo.FactResellerSalesPtnd WITH(TABLOCK)
SELECT * FROM dbo.FactResellerSales;
GO

-- Create the columnstore index
CREATE NONCLUSTERED COLUMNSTORE INDEX [csindx_FactResellerSalesPtnd]
ON [FactResellerSalesPtnd]
( 
    [ProductKey], 
    [OrderDateKey], 
    [DueDateKey], 
    [ShipDateKey], 
    [CustomerKey], 
    [EmployeeKey], 
    [PromotionKey], 
    [CurrencyKey], 
    [SalesTerritoryKey], 
    [SalesOrderNumber], 
    [SalesOrderLineNumber], 
    [RevisionNumber], 
    [OrderQuantity], 
    [UnitPrice], 
    [ExtendedAmount], 
    [UnitPriceDiscountPct], 
    [DiscountAmount], 
    [ProductStandardCost], 
    [TotalProductCost], 
    [SalesAmount], 
    [TaxAmt], 
    [Freight], 
    [CarrierTrackingNumber], 
    [CustomerPONumber], 
    [OrderDate],
    [DueDate],
    [ShipDate]
);

Ejecute ahora una consulta que aproveche el índice de almacén de columnas y confirme que se usa el almacén de columnas.

Probar el índice de almacén de columnas

  1. Presione Ctrl+M o, en el menú Consulta, seleccione Incluir plan de ejecución real. Esto convierte una representación gráfica del plan de ejecución real utilizado por SQL Server Management Studio.

  2. En la ventana del Editor de consultas, ejecute la siguiente consulta:

    SELECT SalesTerritoryKey, SUM(ExtendedAmount) AS SalesByTerritory
FROM FactResellerSalesPtnd
GROUP BY SalesTerritoryKey;

    En la ventana pestaña Resultados, bajo la pestaña Plan de ejecución, confirme que el plan de consulta seleccionó un examen del índice no clúster csindx_FactResellerSalesPtnd.

    [!NOTA]

    Para obtener más información acerca de los iconos de planes de presentación gráficos, vea Referencia de operadores lógicos y físicos del plan de presentación.

    Arriba

Conceptos básicos: Escenarios típicos del índice de almacén de columnas

El esquema de la base de datos en estrella o copo de nieve normalmente se encuentra en puestos de datos y almacenamientos de datos dimensionales en los que la velocidad de recuperación de datos es más importante que la eficacia de las manipulaciones de datos. La tecnología del almacén de columnas de SQL Server 2012 puede detectar y acelerar las consultas que tienen como destino esquemas en estrella o copo de nieve.

Ejemplos:

[!NOTA]

El procesamiento por lotes no se puede usar en los ejemplos siguientes porque las tablas no son lo suficientemente grandes. El modo de ejecución de lotes, como el procesamiento en paralelo, solo se usa en consultas más costosas.

A: Una consulta de funciones agregadas que combina dos tablas

  • Imagine una consulta de combinación en estrella que calcula qué volumen del producto 215 se vendió cada trimestre. La tabla de hechos denominada FactResellerSalesPtnd tiene particiones en la columna OrderDateKey. Una de las tablas de dimensiones denominadas DimDate está vinculada a la tabla de hechos mediante una clave principal (relación de la clave externa en la clave de fecha).

    SELECT d.CalendarYear,
    d.CalendarQuarter,
    COUNT(*) AS NumberSold
FROM dbo.FactResellerSalesPtnd AS f
    JOIN dbo.DimDate AS d
    ON f.OrderDateKey = d.DateKey
WHERE ProductKey = 215
GROUP BY d.CalendarYear, d.CalendarQuarter
ORDER BY d.CalendarYear, d.CalendarQuarter;

Además, habrá consultas que pueden dirigirse a una sola tabla. En estos casos, SQL Server intenta sacar provecho de la eficacia de la ejecución por lotes y el almacén de columnas para acelerar también la ejecución de las consultas.

B: Una consulta de funciones agregadas simple en una única tabla

  • La tabla de hechos es FactResellerSalesPtnd y esta tabla tiene particiones en la columna OrderDateKey. La consulta siguiente devuelve el número de filas y el número de pedidos.

    SELECT COUNT(*) AS NumberOfRows, 
    COUNT(DISTINCT(f.SalesOrderNumber)) AS NumberOfOrders
FROM dbo.FactResellerSalesPtnd AS f;

    En las consultas de un escenario habitual de almacenamiento de datos, es normal que se produzca un aumento de la velocidad de entre 1,5 veces y 10 veces cuando se usan índices de almacén de columnas y el modo de ejecución por lotes durante la ejecución de consultas. En algunas consultas de combinación en estrella, este aumento de velocidad es mucho mayor.

Conceptos básicos: Optimizaciones de filtros de mapas de bits

Además del diseño de formato de columna de los datos, SQL Server usa filtros de mapas de bits que se pasan al motor de almacenamiento para mejorar el rendimiento durante la ejecución de consultas. Los filtros de mapas de bits aumentan la velocidad de ejecución de consultas reduciendo el número de filas que se incluyen, antes de que se implemente cualquier combinación, y, por tanto, reducen el número de filas que se procesan en el operador de combinación. El mapa de bits se genera en el lado de creación de una combinación hash, pero las comprobaciones reales del mapa de bits se realizan en el lado de sondeo de la combinación hash. Puede observar el uso de filtros de mapas de bits en el plan de ejecución gráfico o xml.

Procedimientos recomendados: Actualizar datos en un índice de almacén de columnas

Las tablas que tienen un índice de almacén de columnas no se pueden actualizar. Hay tres formas de solucionar este problema.

  • Para actualizar una tabla con un índice de almacén de columnas, quite el índice de almacén de columnas, realice las operaciones INSERT, DELETE, UPDATE o MERGE necesarias y, a continuación, vuelva a generar el índice de almacén de columnas.

  • Divida la tabla en particiones de tabla y conmute las particiones. Para realizar una inserción masiva, introduzca los datos en una tabla de ensayo, cree un índice de almacén de columnas en la tabla de ensayo y, a continuación, conmute la tabla de ensayo en una partición vacía. En las actualizaciones, quite una partición de la tabla principal y conmútela por una de la tabla de ensayo, deshabilite o quite el índice de almacén de columnas de la tabla de ensayo, realice las operaciones de actualización, vuelva a generar el índice de almacén de columnas de la tabla de ensayo y, a continuación, conmute de nuevo la tabla de ensayo en la tabla principal.

  • Sitúe los datos estáticos en una tabla principal con un índice de almacén de columnas. Los datos nuevos y los datos recientes que probablemente vayan a modificarse sitúelos en una tabla distinta con el mismo esquema que no tenga un índice de almacén de columnas. Aplique actualizaciones a la tabla con los datos más recientes. Para consultar los datos, vuelva a escribir la consulta como dos consultas: una en cada tabla y, a continuación, combine los dos conjuntos de resultados con UNION ALL. La subconsulta realizada en la tabla principal grande disfrutará de las ventajas del índice de almacén de columnas. Si la tabla que se puede actualizar es mucho más pequeña, la ausencia del índice de almacén de columnas tendrá un efecto menor sobre el rendimiento. Aunque también es posible consultar una vista con el resultado de una operación UNION ALL de las dos tablas, tal vez no perciba una ventaja clara en relación con el rendimiento. El rendimiento dependerá del plan de consulta, que dependerá a su vez de la consulta, los datos y las estimaciones de cardinalidad. La ventaja de usar una vista es que un desencadenador INSTEAD OF de la vista puede desviar las actualizaciones a la tabla que no tiene un índice de almacén de columna y el mecanismo de la vista sería transparente para el usuario y las aplicaciones. Si usa cualquiera de estos enfoques con UNION ALL, pruebe el rendimiento de las consultas habituales y decida si la idoneidad de usar este enfoque contrarresta una eventual pérdida de rendimiento.

[!NOTA]

No cree un índice de almacén de columnas como mecanismo para hacer una tabla de solo lectura. La restricción que impide actualizar las tablas con un índice de almacén de columnas no está garantizada en futuras versiones. Cuando únicamente se necesita un comportamiento de solo lectura, este comportamiento debe aplicarse creando un grupo de archivos de solo lectura y moviendo la tabla a ese grupo de archivos.

Procedimientos recomendados: Elegir columnas en un índice de almacén de columnas

Algunas de las ventajas que brinda un índice de almacén de columnas en relación con el rendimiento se derivan de las técnicas de compresión que reducen el número de páginas de datos que deben leerse y manipularse para procesar la consulta. La compresión funciona mejor en columnas numéricas o de caracteres que tienen una gran cantidad de valores duplicados. Por ejemplo, las tablas de dimensiones pueden tener columnas para códigos postales, ciudades y regiones de ventas. Si en cada ciudad hay muchos códigos postales y si en cada región de ventas hay muchas ciudades, la columna de región de ventas será la que esté más comprimida, la columna de ciudad tendría una compresión algo menor y la columna de código postal sería la menos comprimida. Aunque todas las columnas son buenas candidatas para un índice de almacén de columnas, si se agregara la columna de región de ventas al índice de almacén de columnas, se obtendría el máximo beneficio de la compresión del almacén de columnas, mientras que la columna de código postal sería la menos beneficiada.

Arriba

Procedimientos recomendados: Tablas con particiones

Los índices de almacén de columnas están diseñados para admitir consultas en escenarios de almacenamiento de datos muy grandes, donde la creación de particiones es frecuente. Se recomienda crear particiones cuando los datos de una tabla con un índice de almacén de columnas deben actualizarse periódicamente. Para obtener más información acerca de cómo se actualizan las particiones de un índice de almacén de columnas, vea la sección anterior Procedimientos recomendados: Actualizar los datos de un índice de almacén de columnas.

Arriba

Crear un índice de almacén de columnas

Crear un índice de almacén de columnas es como crear otro índice. Puede crear un índice de almacén de columnas mediante Transact-SQL o con las herramientas gráficas de SQL Server Management Studio.

Crear un índice de almacén de columnas mediante Transact-SQL

  • En una ventana del Editor de consultas, ejecute la instrucción CREATE COLUMNSTORE INDEX. Para obtener un ejemplo, vea Crear la tabla de FactResellerSalesPtnd anteriormente. Para obtener más información, vea CREATE COLUMNSTORE INDEX (Transact-SQL).

Crear un índice de almacén de columnas mediante SQL Server Management Studio

  1. Con Management Studio, use el Explorador de objetos para conectarse a una instancia del Motor de base de datos de SQL Server.

  2. En el Explorador de objetos, expanda la instancia de SQL Server, expanda Bases de datos, expanda una base de datos, expanda una tabla, haga clic con el botón secundario en una tabla, elija Nuevo índice y, a continuación, haga clic en Índice de almacenamiento de columnas no clúster.

  3. En el cuadro de diálogo Nombre de índice, bajo la pestaña General, escriba un nombre para el nuevo índice y haga clic en Agregar.

  4. En el cuadro de diálogo Seleccionar columnas, seleccione las columnas que van a participar en el índice de almacén de columnas y haga clic en Aceptar dos veces para crear el índice.

Determinar el tamaño de un índice de almacén de columnas

Un índice de almacén de columnas se compone de segmentos y diccionarios. En el ejemplo siguiente se muestra cómo se determina el tamaño total de un índice de almacén de columnas (en la tabla de FactResellerSalesPtnd) combinando las columnas on_disk_size de sys.column_store_segments y sys.column_store_dictionaries.

SELECT SUM(on_disk_size_MB) AS TotalSizeInMB
FROM
(
   (SELECT SUM(css.on_disk_size)/(1024.0*1024.0) on_disk_size_MB
    FROM sys.indexes AS i
    JOIN sys.partitions AS p
        ON i.object_id = p.object_id 
    JOIN sys.column_store_segments AS css
        ON css.hobt_id = p.hobt_id
    WHERE i.object_id = object_id('FactResellerSalesPtnd') 
    AND i.type_desc = 'NONCLUSTERED COLUMNSTORE') 
  UNION ALL
   (SELECT SUM(csd.on_disk_size)/(1024.0*1024.0) on_disk_size_MB
    FROM sys.indexes AS i
    JOIN sys.partitions AS p
        ON i.object_id = p.object_id 
    JOIN sys.column_store_dictionaries AS csd
        ON csd.hobt_id = p.hobt_id
    WHERE i.object_id = object_id('FactResellerSalesPtnd') 
    AND i.type_desc = 'NONCLUSTERED COLUMNSTORE') 
) AS SegmentsPlusDictionary

Solucionar problemas de rendimiento de un índice de almacén de columnas

Para determinar si se está usando un índice de almacén de columnas, examine el plan de ejecución de consultas. Cuando se obtiene el máximo beneficio, hay tres elementos presentes.

  • El índice de almacén de columnas está presente en el plan de ejecución de consultas.

    Exploración de índice de almacén de columnas

    Icono del operador INDEX SCAN del almacén de columnas

    Si no se está usando el índice de almacén de columnas y cree que su uso podría beneficiar a la consulta, evalúe el rendimiento de la consulta mientras aplica el uso del índice de almacén de columnas mediante la sugerencia WITH (INDEX(<indexname>)). En el ejemplo siguiente se muestra una consulta con una sugerencia de índice.

    SELECT d.CalendarYear,
    d.CalendarQuarter,
    COUNT(*) AS NumberSold
FROM dbo.FactResellerSalesPtnd AS f WITH (INDEX(csindx_FactResellerSalesPtnd))
    JOIN dbo.DimDate AS d
    ON f.OrderDateKey = d.DateKey
WHERE ProductKey = 215
GROUP BY d.CalendarYear, d.CalendarQuarter
ORDER BY d.CalendarYear, d.CalendarQuarter;

  • Al mover el puntero sobre el icono del índice de almacén de columnas del plan de consulta gráfico, el modo de ejecución real se muestra en forma de lote en lugar de en forma de fila.

  • Hay un icono del operador físico Bitmap en el plan de ejecución gráfico, lo que indica que el filtro de mapas de bits está reduciendo el número de filas antes de una operación de combinación.

    Icono del operador Bitmap

    Icono del operador Bitmap

Arriba

Tareas relacionadas

CREATE COLUMNSTORE INDEX (Transact-SQL)

Contenido relacionado

sys.column_store_dictionaries (Transact-SQL)

sys.column_store_segments (Transact-SQL)