Comportamento "estranho" utilizando Identity - intevalo nos valores da sequência após restart
Nos últimos meses tenho trabalhado, dentro de um dos meus clientes, mais próximo do time de DBAs que são mais focados na administração física dos ambientes e, talvez por isso, não tenho recebido muitas perguntas dos desevolvedores que trabalham nessa mesma empresa.
Há algumas semanas, porém, um desenvolvedor me trouxe uma situação interessante que eu não consegui explicar o motivo logo de cara. Talvez porque, como eu mencionei, há alguns meses tenho focado menos em desenvolvimento, ou talvez porque a grande maioria das tabelas que gerenciamos hoje em dia possuem bem mais do que 1000 registros.
O cliente me encaminhou o seguinte report do Connect e disse que após um Failover uma das tabelas com uma coluna identity saltou de 63 para 1001.
Ele já me apontou de cara um Trace Flag, o 272, pois disse ter pesquisado o assunto e que com esse Trace Flag esse comportamento não acontecia. Ao mesmo tempo ele achou estranho e disse suspeitar de um bug no produto.
Bem, mais interessado do que no TF em si, já que se há um Trace Flag eu já entendo que se trata de uma alteração do comportamento padrão, eu queria entender o porque esse comportamento deveria ser então esperado.
A primeira coisa, e talvez a mais óbvia, que eu fiz, foi ler a documentação sobre o Identity, e vi algo que me chamou a atenção e até então eu realmente desconhecia.
“Consecutive values after server restart or other failures – SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use its own mechanism to generate key values. Using a sequence generator with the NOCACHE option can limit the gaps to transactions that are never committed.”
https://technet.microsoft.com/en-in/library/ms186775.aspx
Aqui já percebi uma coisa sobre o caso que ele me trouxe: Não, isso não é um bug, como ele havia sugerido inicialmente, e sim um comportamento esperado, apenas confirmando minhas suspeitas. Ao mesmo tempo isso talvez seja considerado um comportamente bem estranho para alguns.
Relendo a documentação do SQL 2008 R2 por exemplo, há apenas um alerta com relação a possíveis gaps na sequência do identity, sempre causados por deleção de registros, algo esperado, mas não por uma falha, ou até mesmo um simples restart como nesse cenário.
Testando o comportamento, percebi que ele, de fato, acontece. Veja que interessante essa sequência após o restart da minha instância 2012.
A pergunta agora é: Em que situação esse comportamento, até então um pouco bizarro, faz sentido? Bem, seguindo a documentação do SQL 2012, aqui está, talvez, a principal razão:
"SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart ... These restrictions are part of the design in order to improve performance.".
Analisando mais a fundo, a minha explicação final é de que a partir da versão 2012 o SQL Server utiliza um cache de 1000 entradas para colunas identity. Ou seja, há 1000 valores reservados que já estão em memória, ou seja, em cache, para que o desempenho seja superior em caso de novas escritas. Caso não seja utilizado todos os valores dentro daquele cache, no caso de um restart por exemplo, a sequência é reiniciada, porém como se trata de um cache de 1000 registros, não seria possível reinciciar o mesmo a partir do valor 0, já que existem registros inseridos, então ele reinicia, automaticamente, a partir do primeiro valor do próximo intervalo, de 1001 a 2000, por exemplo.
Existem dois "workarounds" para esse comportamento, que mais uma vez, é assim by design:
- Habilitar a TF272, como mencionei no começo, nos parâmetros de inicialização da instância
- Utilizar o SEQUENCE, introduzido no SQL 2012, para tabelas que contém menos de 1000 registros
Qual o impacto de se utilizar o TF272?
Considerando que o cache de 1000 valores para colunas identity tem o objetivo de aumentar o desempenho durante a inserção de novos registros, habilitar o TF anula essa otimização do produto. Pra ser sincero, testando e simulando o desempenho das minhas escritas, eu não senti muita diferença, porém se você considerar milhares de inserções por minuto, talvez o comportamento padrão seja, de fato, benéfico.
Um abraço,
Thiago Caserta.