Estou pesquisando uma tabela de relatórios DW que crescerá muito. Para simplificar, vou mostrar a tabela da seguinte forma:
BigTable
--------
TableID INT IDENTITY NOT NULL,
CompanyName NVARCHAR(100) NOT NULL
Cada consulta usará o nome da empresa para consultar em uma partição de dados (não em uma partição física).
Como essa tabela pode conter mais de um bilhão de linhas e cada empresa terá uma distribuição de dados bastante uniforme, as consultas por empresa devem ser o mais rápidas possível. Estou na fase de configurar alguns testes, mas antes de fazê-lo pensei em perguntar e ver se seria uma perda de tempo.
Minha ideia era determinar que, se a partição de dados de cada empresa fosse colocada lado a lado no disco por meio de um índice clusterizado, a recuperação de dados seria mais rápida do que apenas usar um índice não clusterizado para cobrir CompanyName.
Exemplo 1: Aqui está a variação em que a coluna IDENTITY é o PK, mas não CLUSTERED. O CompanayName e TableID se combinam para formar o Índice Clusterizado para que os dados sejam ordenados por empresa no disco.
CREATE TABLE [dbo].[BigTable](
[TableID] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [nvarchar](100) NOT NULL,
CONSTRAINT [PK_BigTable] PRIMARY KEY NONCLUSTERED
(
[TableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [CLUSTERED_ByCompanyName_TableID] ON [dbo].[BigTable]
(
[CompanyName] ASC,
[TableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
E aqui está a maneira tradicional de criar tabelas com índices de cobertura.
CREATE TABLE [dbo].[BigTable](
[TableID] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [nvarchar](200) NOT NULL,
CONSTRAINT [PK_BigTable] PRIMARY KEY CLUSTERED
(
[TableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_ByCompanyName] ON [dbo].[BigTable]
(
[CompanyName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
Alguém sabe imediatamente se haveria alguma melhoria de desempenho a ser obtida usando o primeiro exemplo sobre o segundo exemplo?
EDIT: Estou inclinado a usar um índice clusterizado com a empresa. O TableID é apenas um campo de autoincremento para usar como PK se uma linha precisar de uma referência exclusiva. Eu sinto que as buscas/varreduras de índice agrupadas são mais rápidas do que as buscas/busca(s) de índice.
Eu gostaria que você pudesse particionar facilmente ou fragmentar com base em algo como companyid.
Uma consulta básica seria na forma de
SELECT
SUM(FieldA) OVER (PARTITION BY ...) a,
COUNT(1) OVER (PARTITION BY...) b
...
FROM
BigTable
WHERE
CompanyName = 'NABISCO'
GROUP BY
....
ORDER BY
....
Aqui está uma tentativa de resposta, com base nos comentários.
Em resumo, você diz que sempre filtra o CompanyName para suas consultas.
Ter um índice clusterizado em CompanyName, TableID pode realmente ser benéfico, pois o SQL Server pode navegar nos "dados" para a empresa certa e ler apenas as linhas dessa empresa específica.
Considere usar a compactação de dados. E certifique-se de avaliar a compactação de linha e página. As pessoas tendem a esquecer a compactação de linha, mas considerando sua sobrecarga quase inexistente, pode ser um tipo de compactação muito atraente em alguns casos.
Ter um índice columnstore pode ser ainda mais benéfico. Em parte por causa da taxa de compactação ainda maior em comparação com nenhuma, linha ou compactação de página. Mas também porque é mais provável que você veja o modo de lote para seus operadores no plano de execução. Você pode obter o modo de lote sem índices columnstore em 2019, mas requer nível de compatibilidade de banco de dados de 2019 e Enterprise Edition.
Você deseja cobrir a consulta com o índice columnstore. Ou um não clusterizado que possui todas as colunas de que suas consultas precisam. Ou provavelmente mais atraente no seu caso, um índice columnstore clusterizado - onde agora você também percebe a economia de armazenamento para o índice columnstore.
Um aspecto será como as linhas são dispostas nos grupos de linhas (um grupo de linhas tem cerca de 1 milhão de linhas, dependendo de como você carrega novos dados, etc.). Você deseja "agrupar" isso com base na empresa. Procure a empresa A e, se as linhas da empresa A estiverem limitadas a um pequeno conjunto de rowgroups, agora você poderá obter uma boa eliminação de rowgroup em tempo de execução (também conhecida como eliminação de segmento). O SQL Server tem metadados para o valor mais baixo e mais alto de cada coluna e cada grupo de linhas. Ao criar o índice, você garantirá que o SQL Server "aconteça" ler as linhas na ordem desejada - tendo um índice agrupado por linha nessa coluna e criando o índice columnstore usando CREATE INDEX ... WITH DROP EXISTING ( basicamente convertendo o índice clusterizado de linha em um índice clusterizado de coluna).
Existem limitações em relação à eliminação do grupo de linhas e aos tipos de dados. Acredito que você ainda não tenha eliminação para tipos de dados de string. Ou seja, considere cuidadosamente se esta será uma coluna CompanyName ou CompanyID! A próxima versão está planejada para estender o suporte de tipo para eliminação de rowgroup.
E depois há o aspecto quando você adiciona dados. Adicione um monte de linhas, que provavelmente são para muitos de seus clientes e eles estarão no(s) mesmo(s) grupo(s) de linhas - e esse grupo de linhas agora terá que ser lido para suas próximas consultas. Ou seja, o índice "degradará" ao longo do tempo se você adicionar dados quando se trata de eliminação de rowgroup, deixando você com a decisão de reconstruir o índice (o que novamente é um pouco complicado até agora porque não temos alguma cláusula ORDER) para refazer -agrupar as linhas com base na empresa.