Nota
O acesso a esta página requer autorização. Pode tentar iniciar sessão ou alterar os diretórios.
O acesso a esta página requer autorização. Pode tentar alterar os diretórios.
Você deve especificar um valor para o BUCKET_COUNT parâmetro ao criar a tabela com otimização de memória. Este tópico faz recomendações para determinar o valor apropriado para o BUCKET_COUNT parâmetro. Se você não puder determinar a contagem correta de buckets, use um índice não clusterizado. Um valor incorreto BUCKET_COUNT , especialmente aquele que é muito baixo, pode afetar significativamente o desempenho da carga de trabalho, bem como o tempo de recuperação do banco de dados. É melhor superestimar a contagem de baldes.
Chaves de índice duplicadas podem diminuir o desempenho com um índice de hash porque as chaves são hashadas para o mesmo bucket, o que faz a cadeia desse bucket aumentar.
Para obter mais informações sobre índices de hash não clusterizados, consulte índices de hash e diretrizes para usar índices em tabelas de Memory-Optimized.
Uma tabela de hash é alocada para cada índice de hash em uma tabela com otimização de memória. O tamanho da tabela de hash alocada para um índice é especificado pelo BUCKET_COUNT parâmetro em CREATE TABLE (Transact-SQL) ou CREATE TYPE (Transact-SQL). A contagem de buckets será arredondada internamente para a próxima potência de dois. Por exemplo, especificar um número de buckets de 300.000 resultará em um número real de buckets de 524.288.
Para obter links para um artigo e um vídeo sobre contagem de buckets, consulte Como determinar a contagem de buckets correta para índices de hash (In-Memory OLTP).
Recomendações
Na maioria dos casos, a contagem de buckets deve estar entre 1 e 2 vezes o número de valores distintos na chave de índice. Se a chave de índice contiver muitos valores duplicados, em média, há mais de 10 linhas para cada valor de chave de índice, use um índice não clusterizado
Você pode nem sempre ser capaz de prever quantos valores uma chave de índice específica pode ter ou terá. O desempenho deverá ser aceitável se o BUCKET_COUNT valor estiver dentro de 5 vezes do número real de valores de chave.
Para determinar o número de chaves de índice exclusivas em dados existentes, use consultas semelhantes aos seguintes exemplos:
Chave primária e índices exclusivos
Como o índice de chave primária é exclusivo, o número de valores distintos na chave corresponde ao número de linhas na tabela. Para obter um exemplo de chave primária sobre (SalesOrderID, SalesOrderDetailID) na tabela Sales.SalesOrderDetail no banco de dados AdventureWorks, emita a seguinte consulta para calcular o número de valores de chave primária distintos, que corresponde ao número de linhas na tabela:
SELECT COUNT(*) AS [row count]
FROM Sales.SalesOrderDetail
Esta consulta mostra uma contagem de linhas de 121.317. Utilize uma contagem de buckets de 240.000 se a contagem de linhas não mudar significativamente. Use uma contagem de buckets de 480.000 se o número de pedidos de vendas na tabela deve quadruplicar.
Índices não exclusivos
Para outros índices, por exemplo, um índice de várias colunas em (SpecialOfferID, ProductID), emita a seguinte consulta para determinar o número de valores exclusivos de chave de índice:
SELECT COUNT(*) AS [SpecialOfferID_ProductID index key count]
FROM
(SELECT DISTINCT SpecialOfferID, ProductID
FROM Sales.SalesOrderDetail) t
Essa consulta retorna uma contagem de chaves de índice para (SpecialOfferID, ProductID) de 484, indicando que um índice não clusterizado deve ser usado em vez de um índice de hash não clusterizado.
Determinando o número de duplicatas
Para determinar o número médio de valores duplicados para um valor de chave de índice, divida o número total de linhas pelo número de chaves de índice exclusivas.
Para o índice de exemplo em (SpecialOfferID, ProductID), isso leva a 121317/484 = 251. Isso significa que os valores de chave de índice têm uma média de 251 e, portanto, deve ser um índice não clusterizado.
Resolução de problemas na contagem de buckets
Para solucionar problemas de contagem de buckets em tabelas com otimização de memória, use sys.dm_db_xtp_hash_index_stats (Transact-SQL) para obter estatísticas sobre os buckets vazios e o comprimento das cadeias de linhas. A consulta a seguir pode ser usada para obter estatísticas sobre todos os índices de hash no banco de dados atual. A consulta pode levar vários minutos para ser executada se houver tabelas grandes no banco de dados.
SELECT
object_name(hs.object_id) AS 'object name',
i.name as 'index name',
hs.total_bucket_count,
hs.empty_bucket_count,
floor((cast(empty_bucket_count as float)/total_bucket_count) * 100) AS 'empty_bucket_percent',
hs.avg_chain_length,
hs.max_chain_length
FROM sys.dm_db_xtp_hash_index_stats AS hs
JOIN sys.indexes AS i
ON hs.object_id=i.object_id AND hs.index_id=i.index_id
Os dois principais indicadores de integridade do índice de hash são:
empty_bucket_percent
empty_bucket_percent indica o número de buckets vazios no índice de hash.
Se empty_bucket_percent for menor que 10%, o número de buckets provavelmente será muito baixo. Idealmente, o empty_bucket_percent deve ser 33% ou maior. Se a contagem de buckets corresponder ao número de valores de chave de índice, cerca de 1/3 dos buckets ficam vazios, devido à distribuição de hash.
avg_chain_length
avg_chain_length indica o comprimento médio das cadeias de linhas nos buckets de hash.
Se avg_chain_length for maior que 10 e empty_bucket_percent for maior que 10%, provavelmente haverá muitos valores de chave de índice duplicados e um índice não clusterizado será mais apropriado. Um comprimento médio de cadeia de 1 é ideal.
Há dois fatores que afetam o comprimento da cadeia:
Duplicatas; todas as linhas duplicadas fazem parte da mesma cadeia de dados na tabela de hash.
Vários valores de chave são mapeados para o mesmo bucket. Quanto menor o número de buckets, mais buckets terão vários valores mapeados neles.
Por exemplo, considere a tabela e o script a seguir para inserir linhas de exemplo na tabela:
CREATE TABLE [Sales].[SalesOrderHeader_test]
(
[SalesOrderID] [uniqueidentifier] NOT NULL DEFAULT (newid()),
[OrderSequence] int NOT NULL,
[OrderDate] [datetime2](7) NOT NULL,
[Status] [tinyint] NOT NULL,
PRIMARY KEY NONCLUSTERED HASH ([SalesOrderID]) WITH ( BUCKET_COUNT = 262144 ),
INDEX IX_OrderSequence HASH (OrderSequence) WITH ( BUCKET_COUNT = 20000),
INDEX IX_Status HASH ([Status]) WITH ( BUCKET_COUNT = 8),
INDEX IX_OrderDate NONCLUSTERED ([OrderDate] ASC),
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
GO
DECLARE @i int = 0
BEGIN TRAN
WHILE @i < 262144
BEGIN
INSERT Sales.SalesOrderHeader_test (OrderSequence, OrderDate, [Status]) VALUES (@i, sysdatetime(), @i % 8)
SET @i += 1
END
COMMIT
GO
O script insere 262.144 linhas na tabela. Ele insere valores exclusivos no índice de chave primária e em IX_OrderSequence. Ele insere muitos valores duplicados no índice IX_Status: o script gera apenas 8 valores distintos.
A saída da consulta de solução de problemas BUCKET_COUNT é a seguinte:
| nome do índice | total_bucket_count | contagem_de_balde_vazio | percentual_de_balde_vazio | comprimento_médio_da_cadeia | comprimento_máximo_da_cadeia |
|---|---|---|---|---|---|
| IX_Estado | 8 | 4 | 50 | 65536 | 65536 |
| IX_OrderSequence | 32768 | 13 | 0 | 8 | 26 |
| PK_SalesOrd_B14003C3F8FB3364 | 262144 | 96319 | 36 | 1 | 8 |
Considere os três índices de hash nesta tabela:
IX_Status: 50% dos buckets estão vazios, o que é bom. No entanto, o comprimento médio da cadeia é muito alto (65.536). Isso indica um grande número de valores duplicados. Portanto, o uso de um índice de hash não clusterizado não é apropriado nesse caso. Em vez disso, um índice não clusterizado deve ser usado.
IX_OrderSequence: 0 por cento dos baldes estão vazios, o que é muito baixo. Além disso, o comprimento médio da cadeia é 8. Como os valores neste índice são exclusivos, isso significa que, em média, 8 valores são mapeados para cada bucket. Contagem de buckets deve ser aumentada. Como a chave de índice tem 262.144 valores exclusivos, a contagem de buckets deve ser de pelo menos 262.144. Se o crescimento futuro for esperado, o número deverá ser maior.
Índice de chave primária (PK__SalesOrder...): 36% dos buckets estão vazios, o que é bom. Além disso, o comprimento médio da cadeia é 1, o que também é bom. Nenhuma alteração é necessária.
Para obter mais informações sobre como solucionar problemas com seus índices de hash com otimização de memória, consulte Solucionar problemas comuns de desempenho com índices de hash Memory-Optimized.
Considerações detalhadas para otimização adicional
Esta seção apresenta outras considerações para otimizar o número de buckets.
Para obter o melhor desempenho para índices de hash, balancee a quantidade de memória alocada para a tabela de hash e o número de valores distintos na chave de índice. Há também um equilíbrio entre o desempenho de consultas pontuais e varreduras de tabela.
Quanto maior o valor da contagem de buckets, mais buckets vazios haverá no índice. Isso tem um impacto no uso da memória (8 bytes por bucket) e no desempenho das verificações de tabela, pois cada bucket é verificado como parte de uma verificação de tabela.
Quanto menor a contagem de buckets, mais valores são atribuídos a um único bucket. Isso diminui o desempenho de pesquisas e inserções de ponto, pois o SQL Server pode precisar percorrer vários valores em um único bucket para localizar o valor especificado pelo predicado de pesquisa.
Se a contagem de buckets for significativamente menor do que o número de chaves de índice exclusivas, muitos valores serão mapeados para cada um dos buckets. Isso degrada o desempenho da maioria das operações DML, especialmente pesquisas de ponto (pesquisas de chaves de índice individuais) e operações de inserção. Por exemplo, você pode ver um desempenho ruim nas consultas SELECT e nas operações UPDATE e DELETE com predicados de igualdade que correspondem às colunas chave do índice na cláusula WHERE. Uma baixa contagem de buckets também afetará o tempo de recuperação do banco de dados, pois os índices são recriados na inicialização do banco de dados.
Valores de chave de índice duplicados
Valores duplicados podem aumentar o impacto no desempenho causado por colisões de hash. Isso geralmente não será um problema se cada chave de índice tiver um número baixo de duplicatas. Mas isso pode ser um problema se a discrepância entre o número de chaves de índice exclusivas e o número de linhas nas tabelas se tornar muito grande.
Todas as linhas com a mesma chave de índice entrarão na mesma cadeia duplicada. Se várias chaves de índice estiverem no mesmo bucket devido a uma colisão de hash, os scanners de índice sempre precisarão verificar a cadeia duplicada completa para o primeiro valor antes de localizarem a primeira linha correspondente ao segundo valor. Chaves duplicadas também tornam mais difícil para a coleta de lixo localizar a linha. Por exemplo, se houver 1.000 duplicatas para qualquer chave e uma das linhas for excluída, o coletor de lixo precisará verificar a cadeia de 1.000 duplicatas para desvincular a linha do índice. Isso é verdadeiro mesmo que a consulta que encontrou a exclusão tenha usado um índice mais eficiente (um índice de chave primária) para localizar a linha, pois o coletor de lixo precisa desvincular de todos os índices
Para índices de hash, há duas maneiras de reduzir o trabalho causado por valores duplicados de chave de índice:
Em vez disso, use um índice não clusterizado. Você pode diminuir as duplicatas adicionando colunas à chave de índice sem exigir nenhuma alteração no aplicativo.
Especifique uma contagem de buckets muito alta para o índice. Por exemplo, de 20 a 100 vezes o número de chaves de índice exclusivas. Isso reduzirá as colisões de hashes.
Tabelas pequenas
Para tabelas menores, a utilização de memória geralmente não é uma preocupação, pois o tamanho do índice será pequeno em comparação com o tamanho geral do banco de dados.
Agora você deve fazer uma escolha com base no tipo de desempenho desejado:
Se as operações críticas de desempenho no índice forem predominantemente buscas pontuais e/ou operações de inserção, será apropriado usar uma contagem maior de buckets para reduzir a probabilidade de colisões de hash. Três vezes o número de linhas ou até mais seria a melhor opção.
Se as verificações de índice completas forem as operações predominantes críticas ao desempenho, use uma contagem de buckets próxima ao número real de valores de chave de índice.
Tabelas Grandes
Para tabelas grandes, a utilização de memória pode se tornar uma preocupação. Por exemplo, com uma tabela de 250 milhões de linhas com 4 índices de hash, cada uma com uma contagem de buckets de um bilhão, a sobrecarga para as tabelas de hash é de 4 índices * 1 bilhão de buckets * 8 bytes = 32 gigabytes de utilização de memória. Ao definir um número de 250 milhões de buckets para cada um dos índices, a sobrecarga total das tabelas de hash será de 8 gigabytes. Observe que isso se soma aos 8 bytes de uso de memória que cada índice adiciona a cada linha individual, que é de 8 gigabytes neste cenário (4 índices * 8 bytes * 250 milhões de linhas).
As verificações de tabela completas geralmente não estão no caminho crítico de desempenho para cargas de trabalho OLTP. Portanto, a opção é entre a utilização de memória versus o desempenho das operações de pesquisa de ponto e inserção:
Se a utilização de memória for uma preocupação, escolha uma contagem de buckets próxima ao número de valores de chave de índice. A contagem de buckets não deve ser significativamente menor do que o número de valores de chave de índice, pois isso afeta a maioria das operações DML, bem como o tempo necessário para recuperar o banco de dados após a reinicialização do servidor.
Ao otimizar o desempenho para consultas pontuais, uma contagem maior de buckets, de duas ou até três vezes o número de valores de índice exclusivos, é apropriada. Uma contagem de buckets mais alta significaria um aumento da utilização da memória e um aumento no tempo necessário para uma verificação de índice completa.