Colunas esparsas, tempo de CPU e índices filtrados

Pouca

Ao fazer alguns testes em colunas esparsas, como você faz, houve um revés de desempenho que eu gostaria de saber a causa direta.

DDL

Criei duas tabelas idênticas, uma com 4 colunas esparsas e outra sem colunas esparsas.

--Non Sparse columns table & NC index
CREATE TABLE dbo.nonsparse( ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
                      charval char(20) NULL,
                      varcharval varchar(20) NULL,
                      intval int NULL,
                      bigintval bigint NULL
                      );
CREATE INDEX IX_Nonsparse_intval_varcharval
ON dbo.nonsparse(intval,varcharval)
INCLUDE(bigintval,charval);

-- sparse columns table & NC index

CREATE TABLE dbo.sparse( ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
                      charval char(20) SPARSE NULL ,
                      varcharval varchar(20) SPARSE NULL,
                      intval int SPARSE NULL,
                      bigintval bigint SPARSE NULL
                      );

CREATE INDEX IX_sparse_intval_varcharval
ON dbo.sparse(intval,varcharval)
INCLUDE(bigintval,charval);

DML

Em seguida, inseri cerca de 2540 valores NON-NULL em ambos.

INSERT INTO dbo.nonsparse WITH(TABLOCK) (charval, varcharval,intval,bigintval)
SELECT 'Val1','Val2',20,19
FROM MASTER..spt_values;

INSERT INTO dbo.sparse WITH(TABLOCK) (charval, varcharval,intval,bigintval)
SELECT 'Val1','Val2',20,19
FROM MASTER..spt_values;

Depois, inseri 1M valores NULL em ambas as tabelas

INSERT INTO dbo.nonsparse WITH(TABLOCK)  (charval, varcharval,intval,bigintval)
SELECT TOP(1000000) NULL,NULL,NULL,NULL 
FROM MASTER..spt_values spt1
CROSS APPLY MASTER..spt_values spt2;

INSERT INTO dbo.sparse WITH(TABLOCK) (charval, varcharval,intval,bigintval)
SELECT TOP(1000000) NULL,NULL,NULL,NULL 
FROM MASTER..spt_values spt1
CROSS APPLY MASTER..spt_values spt2;

Consultas

Execução de tabela não esparsa

Ao executar esta consulta duas vezes na tabela não esparsa recém-criada:

SET STATISTICS IO, TIME ON;
SELECT  * FROM dbo.nonsparse
WHERE   1= (SELECT 1) -- force non trivial plan
OPTION(RECOMPILE,MAXDOP 1);

As leituras lógicas mostram 5257 páginas

(1002540 rows affected)
Table 'nonsparse'. Scan count 1, logical reads 5257, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

E o tempo de CPU é de 343 ms

 SQL Server Execution Times:
   CPU time = 343 ms,  elapsed time = 3850 ms.

execução de tabela esparsa

Executando a mesma consulta duas vezes na tabela esparsa:

SELECT  * FROM dbo.sparse
WHERE   1= (SELECT 1) -- force non trivial plan
OPTION(RECOMPILE,MAXDOP 1);

As leituras são mais baixas, 1763

(1002540 rows affected)
Table 'sparse'. Scan count 1, logical reads 1763, physical reads 3, read-ahead reads 1759, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Mas o tempo de CPU é maior, 547 ms .

 SQL Server Execution Times:
   CPU time = 547 ms,  elapsed time = 2406 ms.

Plano de execução de tabela esparsa

plano de execução de tabela não esparsa

Perguntas

Pergunta original

Como os valores NULL não são armazenados diretamente nas colunas esparsas, o aumento no tempo de CPU pode ser devido ao retorno dos valores NULL como um conjunto de resultados? Ou é simplesmente o comportamento observado na documentação ?

Colunas esparsas reduzem os requisitos de espaço para valores nulos ao custo de mais sobrecarga para recuperar valores não nulos

Ou a sobrecarga está relacionada apenas às leituras e armazenamento usado?

Mesmo executando ssms com a opção de descarte de resultados após execução, o tempo de cpu da seleção esparsa foi maior (407 ms) em comparação com a não esparsa (219 ms).

EDITAR

Pode ter sido a sobrecarga dos valores não nulos, mesmo que haja apenas 2540 presentes, mas ainda não estou convencido.

Isso parece ser o mesmo desempenho, mas o fator esparso foi perdido.

CREATE INDEX IX_Filtered
ON dbo.sparse(charval,varcharval,intval,bigintval)
WHERE charval IS NULL  
      AND varcharval IS NULL
      AND intval  IS NULL
      AND bigintval  IS NULL;

CREATE INDEX IX_Filtered
ON dbo.nonsparse(charval,varcharval,intval,bigintval)
WHERE charval IS NULL  
      AND varcharval IS NULL
      AND intval  IS NULL
      AND bigintval  IS NULL;


    SET STATISTICS IO, TIME ON;

SELECT  charval,varcharval,intval,bigintval FROM dbo.sparse WITH(INDEX(IX_Filtered))
WHERE charval IS NULL AND  varcharval IS NULL
                     AND intval  IS NULL
                     AND bigintval  IS NULL
                     OPTION(RECOMPILE,MAXDOP 1);


SELECT  charval,varcharval,intval,bigintval 
FROM dbo.nonsparse WITH(INDEX(IX_Filtered))
WHERE charval IS NULL AND 
                      varcharval IS NULL
                     AND intval  IS NULL
                     AND bigintval  IS NULL
                     OPTION(RECOMPILE,MAXDOP 1);

Parece ter aproximadamente o mesmo tempo de execução:

 SQL Server Execution Times:
   CPU time = 297 ms,  elapsed time = 292 ms.

 SQL Server Execution Times:
   CPU time = 281 ms,  elapsed time = 319 ms.

Mas por que as leituras lógicas são a mesma quantidade agora? O índice filtrado para a coluna esparsa não deveria armazenar nada, exceto o campo de ID incluído e algumas outras páginas que não são de dados?

Table 'sparse'. Scan count 1, logical reads 5785,
Table 'nonsparse'. Scan count 1, logical reads 5785

E o tamanho de ambos os índices:

RowCounts   Used_MB Unused_MB   Total_MB
1000000     45.20   0.06        45.26

Por que são do mesmo tamanho? A escassez foi perdida?

Ambos os planos de consulta ao usar o índice filtrado

Informação extra

select @@version

Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64) 12 de julho de 2019 17:43:08 Copyright (C) 2017 Microsoft Corporation Developer Edition (64 bits) no Windows Server 2012 R2 Datacenter 6.3 (Build 9600: ) (Hipervisor)

Ao executar as consultas e selecionar apenas o campo ID , o tempo de CPU é comparável, com leituras lógicas mais baixas para a tabela esparsa.

Tamanho das mesas

SchemaName  TableName   RowCounts   Used_MB Unused_MB   Total_MB
dbo         nonsparse   1002540     89.54   0.10        89.64
dbo         sparse      1002540     27.95   0.20        28.14

Ao forçar o índice clusterizado ou não clusterizado, a diferença de tempo da CPU permanece.