Dicas de Tuning – Criação de Índice – Warning! The maximum key length is X bytes

Fala pessoal,

Nesse post vou mostrar um caso de criação de índice que pode ser bem perigoso e causar problemas na sua aplicação!!! Não se engane pela mensagem ser apenas um WARNING, é uma cilada BINO!!!

“Warning! The maximum key length for a nonclustered index is X bytes.“

Create Index:

Criar índices é algo bem comum para quem trabalha com performance de banco de dados. Eles são muito importantes para deixar suas queries mais rápidas e são eles que fazem MÁGICA como os exemplos abaixo. No primeiro, tivemos uma redução de quase 1,5 BILHÃO de leituras! Já no segundo, o consumo da query foi reduzido para quase ZERO, tudo isso graças a criação de índices!

Um cenário bem comum é o cliente informar que está com lentidão em uma determinada query. Dito isso, alguém (o DBA ou outro responsável) irá fazer uma análise, validar as colunas que estão sendo utilizadas e criar um índice com elas para que a query fique mais rápida, mas é aí que mora o PERIGO!!!

Vou fazer um exemplo para demonstrar esse problema na prática. Primeiro, vamos criar uma tabela “test”, com uma coluna “col” do tipo VARCHAR(2000) e outra coluna “othercol” do tipo BIT.

USE Teste

if(object_id('test') is not null) drop table test

create table test (
      col varchar(2000)
    , otherCol bit -- This column will take a byte out of the index below, pun intended
);

USE Teste

if(object_id('test') is not null) drop table test

create table test (

col varchar(2000)

, otherCol bit -- This column will take a byte out of the index below, pun intended

);

Agora vamos criar um “simples” índice com essas duas colunas.

create nonclustered index test_index on test (col, otherCol);

1	create nonclustered index test_index on test (col, otherCol);

O índice será criado normalmente, mas será exibido a mensagem de WARNING abaixo. Nesse ponto, muita gente irá desconsiderar esse WARNING e é aí que a TRETA começa.

Warning! The maximum key length for a nonclustered index is 1700 bytes.

The index ‘test_index’ has maximum length of 2001 bytes.

For some combination of large values, the insert/update operation will fail.

Segue abaixo um print da estrutura da tabela “test”. Como podemos observar, o índice “test_index” foi criado normalmente.

Agora vamos fazer alguns testes. Esse primeiro irá executar com sucesso.

insert into test select cast(replicate('x', 1699) as varchar(1699)), 0; -- Success

1	insert into test select cast(replicate('x', 1699) as varchar(1699)), 0; -- Success

Contudo, se executarmos o INSERT ou o UPDATE abaixo, vamos ter o seguinte ERRO:

insert into test select cast(replicate('y', 1700) as varchar(1700)), 0; -- Fail

update test 
set col = cast(replicate('y', 1700) as varchar(1700))
where otherCol = 0

insert into test select cast(replicate('y', 1700) as varchar(1700)), 0; -- Fail

update test

set col = cast(replicate('y', 1700) as varchar(1700))

where otherCol = 0

Msg 1946, Level 16, State 3, Line 28

Operation failed. The index entry of length 1701 bytes for the index ‘test_index’ exceeds the maximum length of 1700 bytes for nonclustered indexes.

Ou seja, ao criar um simples índice nós afetamos seriamente a aplicação, pois ela não vai conseguir fazer INSERT/UPDATE com registros “grandes”.

Agora vamos voltar no WARNING e revisar alguns conceitos.

“Warning! The maximum key length for a nonclustered index is 1700 bytes. “

Na documentação oficial da Microsoft, temos algumas informações muito importantes sobre os índices:

https://learn.microsoft.com/pt-br/sql/t-sql/statements/create-index-transact-sql?view=sql-server-ver16

Até 32 colunas podem ser combinadas em uma única chave de índice composto.
Todas as colunas de uma chave de índice composto devem estar na mesma tabela ou exibição.
O tamanho máximo permitido de valores de índice combinados é de 900 bytes para um índice clusterizado ou de 1.700 para um índice não clusterizado. Os limites são 16 colunas e 900 bytes para versões anteriores a Banco de Dados com o SQL Server 2016 (13.x).

Ou seja, existe um limite que no meu caso é de 1.700 bytes para as colunas que fazem parte da chave do índice não clusterizado! Como tentei criar o índice com a coluna “col” que possui o tamanho de 2000 bytes, foi gerado o WARNING.

“The index ‘test_index’ has maximum length of 2001 bytes. “

Nessa parte, ele informa o total de bytes das colunas que selecionei para a criação do índice.

col (varchar(2000)) + othercol (bit) = 2001 bytes

“For some combination of large values, the insert/update operation will fail.”

Por fim, ele informa que para algumas combinações de valores grandes, as operações de INSERT/UPDATE podem falhar, conforme mostrei no exemplo acima.

Segue mais duas referências que me ajudaram a entender melhor esse assunto:

https://stackoverflow.com/questions/22172251/warning-the-maximum-key-length-is-900-bytes-the-index-has-maximum-length-of-10

https://stackoverflow.com/questions/12717317/900-byte-index-size-limit-in-character-length

Conclusão:

Como podemos ver, criar um índice não é só selecionar as colunas que estão sendo utilizadas e pronto.

Antes de criar um índice, devemos validar os tipos e tamanhos das colunas para evitar problemas futuros. Para fazer essa validação, selecione o nome da tabela e pressione ALT + F1 ou execute o comando abaixo:

USE Teste

EXEC sp_help 'test'

USE Teste

EXEC sp_help 'test'

Feito isso, valide as colunas que serão utilizadas como chave do índice e some os valores da coluna “Length” para ver se não irão extrapolar o limite!

Espero que tenha gostado e que isso também possa ser útil no seu dia a dia. Até o próximo post!

Me siga no LinkedIn e YouTube para ficar por dentro das novidades.

Abraço,

Luiz Vitor França Lima

Consultor SQL Server

Create Index:

Conclusão:

Compartilhe isso:

Deixe uma respostaCancelar resposta