¿Por qué la presencia de la clave primaria en la tabla mejora significativamente el rendimiento de los índices de almacén de columnas?

Question

Apr 03, 2015, 03:15 PM

¿Por qué la presencia de la clave primaria en la tabla mejora significativamente el rendimiento de los índices de almacén de columnas?

Estaba tratando de ver el tipo de ganancias de rendimiento que los índices de almacenamiento en columna pueden proporcionar en una tabla. La tabla tiene aproximadamente 3,7 millones de filas, 11 columnas y se almacena como un montón (es decir, sin una clave primaria). Creo un índice de almacén de columnas en la tabla y ejecuto la siguiente consulta:

SELECT 
    [Area], [Family],
    AVG([Global Sales Value]) AS [Average GlobalSalesValue],
    COUNT([Projected Sales])
FROM 
    dbo.copy_Global_Previous5FullYearSales
WHERE 
    [Year] > 2012  
GROUP BY 
    [Area], [Family]

La declaración de crear tabla es la siguiente:

CREATE TABLE [dbo].[copy_Global_Previous5FullYearSales]
(
    [SBU] [NVARCHAR](10) NULL,
    [Year] [INT] NULL,
    [Global Sales Value] [MONEY] NULL,
    [Area] [NVARCHAR](50) NULL,
    [Sub Area] [NVARCHAR](50) NULL,
    [Projected Sales] [MONEY] NULL,
    [Family] [NVARCHAR](50) NULL,
    [Sub Family 1] [NVARCHAR](50) NULL,
    [Sub Family 2] [NVARCHAR](50) NULL,
    [Manufacturer] [NVARCHAR](40) NULL,
    [rowguid] [UNIQUEIDENTIFIER] NOT NULL,
    [ID] [INT] IDENTITY(1,1) NOT NULL,

    PRIMARY KEY CLUSTERED ([ID] ASC)
        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
              IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
              ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Las ganancias de rendimiento que obtengo del índice de almacén de columnas en este caso son insignificantes. La consulta con el índice de almacén de columnas es casi tan lenta como la consulta original sin un índice, en algunos casos, incluso más lenta, aunque también se utiliza el modo por lotes.

Sorprendentemente, cuando creo una clave primaria cada vez mayor: ID en la tabla existente y reconstruyo el índice de almacenamiento de columnas, obtengo una mejora de 15X en el tiempo de CPU y una mejora de 3X en el tiempo transcurrido.

No entiendo cómo la adición de una clave primaria podría afectar el rendimiento de la consulta para los índices de almacenamiento de columnas que almacenan los datos en un formato comprimido de todos modos. Además, las claves primarias solo cambian el orden de las páginas que, en este caso, no serían ninguna.

A continuación se muestra el plan de ejecución.