0
Shares
0
0
0

Los índices columnares son un nuevo tipo de índices disponibles en SQL Server 2012 que incorporan el almacenamiento columnar al motor relacional.

Dejando de lado los múltiples inconvenientes y limitaciones de estos índices, voy a centrarme en analizar el impacto de rendimiento teniendo en cuenta los puntos clave que se destacan de este tipo de índices:

 

  • Solo las columnas necesarias para la consulta son accedidas.
  • Las columnas están comprimidas lo cual reduce la cantidad de lecturas
  • El plan de ejecución en batches reduce el consumo de CPU

Vamos a utilizar la base de datos AdventureWorksDW2012 y comenzaremos por analizar el índice columnar que viene como ejemplo en BOL:

 

CREATE NONCLUSTERED COLUMNSTORE INDEX [csindx_FactResellerSales] 
ON [FactResellerSales] 
( 
    [ProductKey], 
    [OrderDateKey], 
    [DueDateKey], 
    [ShipDateKey], 
    ResellerKey, 
    [EmployeeKey], 
    [PromotionKey], 
    [CurrencyKey], 
    [SalesTerritoryKey], 
    [SalesOrderNumber], 
    [SalesOrderLineNumber], 
    [RevisionNumber], 
    [OrderQuantity], 
    [UnitPrice], 
    [ExtendedAmount], 
    [UnitPriceDiscountPct], 
    [DiscountAmount], 
    [ProductStandardCost], 
    [TotalProductCost], 
    [SalesAmount], 
    [TaxAmt], 
    [Freight], 
    [CarrierTrackingNumber], 
    [CustomerPONumber], 
    [OrderDate], 
    [DueDate], 
    [ShipDate] 
);

 

Podemos ver que pese a ser un índice no cluster, han incluido todas las columnas de la tabla. La razón que gana peso para esta decisión es que únicamente podemos tener un índice columnar por tabla.

Si lanzamos una consulta que el optimizador considera apropiada veremos como el nuevo operador ColumnStore IndexScan entra en juego:

SELECT SalesTerritoryKey, SUM(ExtendedAmount) AS SalesByTerritory 
FROM FactResellerSales 
GROUP BY SalesTerritoryKey;
Rendimiento con índices columnares

Si comparamos la estimación de costes de esta consulta comparada con otra en la que forzamos el uso del índice cluster vemos que, efectivamente, el uso del índice columnar parece ser mucho más eficiente:

Rendimiento con índices columnares

Si comparamos los tiempos de ejecución vemos que la consulta con el índice columnar es más rápida, realiza menos lecturas y consume menos CPU:

(10 row(s) affected) 
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'FactResellerSales'. Scan count 1, logical reads 49, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: 
   CPU time = 16 ms,  elapsed time = 17 ms. 
(10 row(s) affected) 
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'FactResellerSales'. Scan count 1, logical reads 2982, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 SQL Server Execution Times: 
   CPU time = 31 ms,  elapsed time = 26 ms.

Veamos qué ocurre si creamos un índice de cobertura sobre las columnas utilizadas:

CREATE INDEX IX_SOLID ON FACTRESELLERSALES(SALESTERRITORYKEY,EXTENDEDAMOUNT)

En este caso, ya el optimizador nos estima que la operación con el índice de cobertura va a ser tanto o más rápida que con el índice columnar:

Rendimiento con índices columnares

El resultado de la ejecución de esta última query muestra que estamos prácticamente a la par en el consumo de CPU y en la duración aunque el número de lecturas sigue siendo considerablemente mayor:

(10 row(s) affected) 
Table 'FactResellerSales'. Scan count 1, logical reads 290, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: 
   CPU time = 16 ms,  elapsed time = 20 ms.

 

Si probamos estas mismas queries en un escenario de caché fría (DBCC dropcleanbuffers) veremos que la diferencia en tiempos aumenta a favor del índice columnar:

(10 row(s) affected) 
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'FactResellerSales'. Scan count 1, logical reads 52, physical reads 3, read-ahead reads 71, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: 
   CPU time = 16 ms,  elapsed time = 22 ms.
(10 row(s) affected) 
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'FactResellerSales'. Scan count 1, logical reads 2982, physical reads 2, read-ahead reads 2972, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: 
   CPU time = 31 ms,  elapsed time = 93 ms.
(10 row(s) affected) 
Table 'FactResellerSales'. Scan count 1, logical reads 290, physical reads 0, read-ahead reads 287, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: 
   CPU time = 16 ms,  elapsed time = 33 ms.

Otro factor que debemos considerar es que también es posible comprimir los índices no columnares mediante compresión de fila o de página. Veamos que ocurre cuando comprimimos el índice cluster y el índice de cobertura con compresión de página:

ALTER INDEX [ix_solid] ON [dbo].[FactResellerSales] REBUILD PARTITION = ALL WITH 
(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, 
DATA_COMPRESSION = PAGE) 
GO 
ALTER INDEX [PK_FactResellerSales_SalesOrderNumber_SalesOrderLineNumber] ON [dbo].[FactResellerSales] REBUILD PARTITION = ALL WITH 
(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, 
DATA_COMPRESSION = PAGE) 
GO

Con compresión activa los resultados con caché fría son los siguientes:

 

(10 row(s) affected) 
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'FactResellerSales'. Scan count 1, logical reads 52, physical reads 4, read-ahead reads 71, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: 
   CPU time = 16 ms,  elapsed time = 23 ms.
(10 row(s) affected) 
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'FactResellerSales'. Scan count 1, logical reads 370, physical reads 0, read-ahead reads 358, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: 
   CPU time = 47 ms,  elapsed time = 43 ms.
(10 row(s) affected) 
Table 'FactResellerSales'. Scan count 1, logical reads 92, physical reads 1, read-ahead reads 90, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: 
   CPU time = 16 ms,  elapsed time = 33 ms.

Vemos que la compresión nos ayuda a reducir las lecturas y el tiempo total en el caso del índice cluster mientras que en el índice de cobertura, al ser menos páginas a leer, realmente la diferencia no es apreciable.

Para poder apreciar un poco mejor las diferencias de tiempos y de consumos de CPU vamos a inflar un poco la tabla ejecutando 5 veces este insert que duplica el número de registros con cada ejecución:

insert into FactResellerSales 
SELECT [ProductKey] 
      ,[OrderDateKey] 
      ,[DueDateKey] 
      ,[ShipDateKey] 
      ,[ResellerKey] 
      ,[EmployeeKey] 
      ,[PromotionKey] 
      ,[CurrencyKey] 
      ,[SalesTerritoryKey] 
      ,(select count(*) from [FactResellerSales]) + row_number() over (order by SalesOrderNumber) 
      ,[SalesOrderLineNumber] 
      ,[RevisionNumber] 
      ,[OrderQuantity] 
      ,[UnitPrice] 
      ,[ExtendedAmount] 
      ,[UnitPriceDiscountPct] 
      ,[DiscountAmount] 
      ,[ProductStandardCost] 
      ,[TotalProductCost] 
      ,[SalesAmount] 
      ,[TaxAmt] 
      ,[Freight] 
      ,[CarrierTrackingNumber] 
      ,[CustomerPONumber] 
      ,[OrderDate] 
      ,[DueDate] 
      ,[ShipDate] 
  FROM [dbo].[FactResellerSales] 
  go 5

 

Ahora que tenemos ya casi 2 millones de filas volveremos a recrear el índice columnar (tuvimos que borrarlo para poder insertar datos) y desfragmentaremos el resto de índices. Repetiremos las pruebas realizadas anteriormente con caché fría y caliente así como con compresión o sin compresión de página.

Al aumentar el número de registros vemos que las tres operaciones se ejecutan con planes paralelos:

Rendimiento con índices columnares
La siguiente tabla resume los valores obtenidos:

Rendimiento con índices columnares

Ahora vamos a testear otra consulta que, en principio, parece muy óptima también para un índice columnar, un cálculo de un total acumulado:

Rendimiento con índices columnares

La primera diferencia que vemos es que no se está considerando un plan paralelo para el índice columnar. La siguiente tabla resume los valores obtenidos:

Rendimiento con índices columnares

Podemos ver que en este escenario el índice columnar ha ofrecido peores tiempos de respuesta. Desgraciadamente el trace flag no documentado 8649 no nos ayuda en este caso ya que parece no afectar a los planes con índices columnares. Si modificamos manualmente el “cost threshold for parallelism” y lo disminuimos, podemos obtener un plan paralelo con un tiempo de respuesta comparable con el del índice de cobertura:

Table 'FactResellerSales'. Scan count 7, logical reads 744, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: 
   CPU time = 547 ms,  elapsed time = 195 ms.

Por tanto podemos extraer las siguientes conclusiones:

* Los índices columnares son más eficientes cuanta más lectura física se ve obligado el servidor a ejecutar para resolver la consulta. El algoritmo de compresión del columnar es bastante más eficiente que el algoritmo de compresión de página.

* Con la tendencia a mayor cantidad de memoria en los servidores no es inusual ver servidores con 512 GB o más de memoria. Esto hace que el porcentaje de acierto de página sea muy elevado. En este escenario, un índice de cobertura puede ser más eficiente que un columnar para algunas consultas que utilizan agregados

* En base a los resultados obtenidos consideramos que una estrategia mixta podría dar los mejores resultados que una que se incline solo hacia una alternativa.

Esta estrategia mixta consistiría en el uso de una tabla particionada + indices columnares (para datos históricos, de solo lectura) junto a una tabla no particionada con índices no columnares (para datos no históricos, de lectura/escritura). Ambas tablas podrían unificarse en una única vista para facilitar las consultas que accedan tanto a datos históricos como a datos no históricos.

La parte histórica contaría con un conjunto reducido de índices no columnares (índice cluster, PK e índices no clustered imprescindibles) más un índice columnar que incluya todas las columnas que se utilicen en consultas históricas. La parte no histórica contaría con el conjunto de índices necesarios para la operativa no histórica (índice cluster, PK, índices sobre FKs y todos índices no clustered necesarios).

0 Shares:
Share 0
Tweet 0
Share 0

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

— Previous article

El experto en Busines Intelligence tendrá trabajo

Next article —

Accediendo a Facebook con Data Explorer

You May Also Like