Dado que estou usando o banco de dados OLTP AdventureWorks2016 , por que o histograma de estatísticas para o índice PK_TransactionHistory_TransactionIDna tabela Production.TransactionHistorycontém apenas 3 "buckets" de histograma quando há 113k valores distintos nessa coluna?
Um exemplo abaixo:
USE AdventureWorks2016
/* ensure statistics are as accurate as they can be */
UPDATE STATISTICS Production.TransactionHistory WITH FULLSCAN
então podemos olhar para o histograma atualizado
/* look at the statistics for the primary key column */
DBCC SHOW_STATISTICS (
'Production.TransactionHistory',
'PK_TransactionHistory_TransactionID')
WITH HISTOGRAM;
e vejo a saída:
Observe os IDs de transação máximo e mínimo:
SELECT MIN(TransactionID) FROM Production.TransactionHistory /* 100000 */
SELECT MAX(TransactionID) FROM Production.TransactionHistory /* 213442 */
O SQL Server parece ter criado um "bucket" para o valor máximo, um para o valor mínimo e um para todos os valores intermediários (que ele sabe que são todos distintos)
Observo que se eu remover a chave primária desta tabela
ALTER TABLE Production.TransactionHistory DROP CONSTRAINT PK_TransactionHistory_TransactionID
e, em seguida, insira alguns valores duplicados
INSERT INTO [Production].[TransactionHistory]
(
TransactionID,
[ProductID],
[ReferenceOrderID],
[ReferenceOrderLineID],
[TransactionDate],
[TransactionType],
[Quantity],
[ActualCost],
[ModifiedDate]
)
VALUES
(200001,1,1,1,GETDATE(),'P',1,1,GETDATE()),
(200011,1,1,1,GETDATE(),'P',1,1,GETDATE()),
(200021,1,1,1,GETDATE(),'P',1,1,GETDATE()),
(200031,1,1,1,GETDATE(),'P',1,1,GETDATE())
Atualize as estatísticas na tabela e, em seguida, observe a estatística da coluna (em vez do PK que excluímos)
USE AdventureWorks2016
/* ensure statistics are as accurate as they can be */
UPDATE STATISTICS Production.TransactionHistory WITH FULLSCAN
/* look at the statistics for the primary key column */
DBCC SHOW_STATISTICS (
'Production.TransactionHistory',
'TransactionID')
WITH HISTOGRAM;
Ainda temos dois buckets, embora DISTINCT_RANGE_ROWS tenha sido atualizado de acordo
Por que o SQL Server não faz uso dos 200 "buckets" disponíveis em um histograma aqui? É algo a ver com os recursos necessários para preencher a página de estatísticas de 8 KB e usar todos os 200 buckets significaria que pode ser necessário redefinir quando novos dados são adicionados à tabela?
O histograma neste caso é quase indistinguível de antes de inserir os 4 valores duplicados. Naquela época, a série única e sequencial podia ser completamente descrita em três etapas.
A diferença teria sido linhas de intervalo = 113441 em vez de 113445, linhas de intervalo distintas ainda = 113441 e linhas de intervalo médio = 1 em vez de 1,000035.
Então. Não seria melhor capturar as quatro duplicatas no que pode ser um histograma de slot de até 200 mais NULL?
Não, não necessariamente.
Por quê? Porque as estatísticas do otimizador não são apenas para o momento. As estatísticas do otimizador são válidas até a próxima vez que as estatísticas do otimizador forem atualizadas. Como há mais de 25.000 linhas, o limite de estatísticas automáticas padrão no SQL Server 2016 e em diante é SQRT(1000 * linhas). Nesse caso, o limite é COLMODCTR > 10651,06. Portanto, não há atualização automática até pelo menos 10652 modificações no TransactionId, que já vimos duplicadas. Que valor geral pode indicar 4 duplicatas entre uma série sequencial única que ainda estaria presente dado o próximo limite de estatísticas de atualização automática de 106652 modificações - que podem ser exclusões criando furos na série, duplicatas de alguns ou muitos valores ou um intervalo de números sequenciais únicos começando com anterior max + 1?
As estatísticas do otimizador, como todo o trabalho feito pelo otimizador, não são para alcançar o melhor caso para todas as circunstâncias, independentemente do esforço ou do tempo. Em vez disso, para fornecer um resultado "bom o suficiente", dado o esforço e o tempo, levando em consideração as limitações de modelagem na estimativa de cardinalidade e outros trabalhos do otimizador.
Essa é uma razão pela qual a modelagem de esquema informada por consulta com restrições, índices e estatísticas sempre será importante. Também uma razão pela qual a modelagem de consulta informada pelo esquema, incluindo o formato de código T-SQL e dicas, sempre será importante :-)