Nova série: Perguntas intrigantes do SQL Server

Bom dia pessoal.

Algumas coisas na vida são curiosas, quando você está aprendendo uma nova tecnologia as dúvidas te parecem complexas, mas usualmente são simples e com uma resposta direta, você entende e aceita.
Depois que você se aprofunda na tecnologia, as mesmas perguntas podem ter tantos desdobramentos interessantes e variáveis que afetam sua resposta, que eventualmente você se vê pego pensando em exceções e complexidades inerente ao problema, que podem nem interessar a quem perguntou.

Como consultor e instrutor sempre encaramos perguntas e várias vezes, depois que ouvi o questionamento, fico com o dilema na cabeça: será que o "aluno" está pensando nas variáveis X, Y e Z? Devo responder de forma direta "funciona assim e pronto" ou problematizar um pouco mais detalhando vários cenários? Na primeira abordagem eu corro o risco de ser superficial decepcionando o ouvinte, na segunda eu posso viajar tanto que farei o público dormir ou pensar que estou sob efeito de drogas. Um dilema usual, que acabo normalmente seguindo pelo segundo caminho… paciência.

Como eu já brinco com o SQL Server faz um tempinho, acho que sou capaz de sugerir situações obtusas para tantos cenários e funcionalidades que resolvi colocar um pouco para fora. Quem sabe um brainstorm com vocês não deixa a coisa mais divertida?


Modelo de alocação no SQL Server

Sempre que ensino sobre o modelo de alocação do SQL Server eu falo:

As primeiras oito páginas de um índice (também heap) são alocadas em extents mistos e a partir da nona página um extent uniforme é alocado para o índice. Tudo isso é controlado pela GAM/SGAM e
a IAM, que possui oito slots para essas páginas "soltas" em sua estrutura. Analisamos com DBCC PAGE e tudo fica claro.

Depois eu menciono sobre o trace flag 1118, dizendo que ele muda o modelo de alocação da instância, partindo direto para a alocação de um extent uniforme, sem utilizar as páginas dos extents mistos. A vantagem é uma menor contenção na SGAM, em contrapartida você pode estar perdendo um pouco de espaço alocado e sem dados.


Vamos aos questionamentos...

Q: Habilitar ou não o trace flag 1118?

R: O SQL Server 2005 trouxe otimizações para a TempDB (http://technet.microsoft.com/en-us/library/cc966545.aspx), então a princípio você não precisaria mais da trace flag, mas ela continua funcional e ainda é usada pelo time de suporte em casos extremos (http://blogs.msdn.com/psssql/archive/2008/12/17/sql-server-2005-and-2008-trace-flag-1118-t1118-usage.aspx).
Vemos resposta similar também a essa pergunta enviada para o Bob Dorr de um conhecido ninja aqui do Brasil (http://blogs.msdn.com/psssql/archive/2009/06/04/sql-server-tempdb-number-of-files-the-raw-truth.aspx).


Q: Por que o SQL Server não acaba o modelo de alocação misto?

Eu me faço essa pergunta com frequência. Qual o ganho desse modelo para bancos de dados de usuário? Potencialmente você vai jogar fora 56K com tabelas muito pequenas (de uma página). Tá legal, se eu tiver 5000 tabelas com essa característica, então vou desperdiçar 273 MB. Valeria a pena? Me parece que sim.

Será que existe outro motivo, além do ganho de espaço, para manter esse modelo? Não me vem nada de imediato a mente...

Já na TempDB o impacto dessa alteração pode ser bem maior, minimizando uma possível contenção que ainda pode acontecer, mas potencialmente fazendo com que seu banco temporário cresça muito mais, pois a probabilidade de existirem muitos objetos pequenos simultaneamente é maior. O problema de espaço versus o problema de contenção me parece a chave aqui, qual ganha?

O que você acha desse assunto?
O modelo ainda é útil?

Correndo um enorme risco de cometer alguma injustiça e falar besteira, deixo alguns palpites.

- O modelo de alocação original fazia mais sentido no passado, hoje o ganho de espaço parece irrisório. - Porém os ganhos em mudar o modelo de alocação também não serão grandes, então porque sair tocando em uma peça tão fundamental e sensível da engine? Acho que a chance de trazer mais problemas é maior do que os ganhos.
- Pensando nisso, o time de produto prefere manter o modelo de alocação atual e otimizar onde costuma doer mais, a TempDB. Então vemos novos e inteligentes artifícios para minimizar o impacto de workloads que utilizam muito nosso banco temporário.

Pensando nisso, por mais que o modelo não me pareca tão útil, pensando como o dono do produto eu provavelmente não gastaria tempo da minha equipe para tocar nesse código, e sim para introduzir outras novidades.

Faz sentido o que eu disse para vocês? Seria gostoso conversar com o time de produto e entender algumas motivações, como essa.

Como referência deixo aqui dois artigos muito bons: http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-TF-1118.aspx. Detalhe: adorei a parte em que o Paul fala da quantidade de arquivos da TempDB, sempre falo isso nos meus treinamentos e agora tenho uma fonte mais confiável. :-)
http://sqlblog.com/blogs/linchi_shea/archive/2007/08/07/reduce-the-contention-on-tempdb-with-trace-flag-1118-to-enable-or-not-to-enable.aspx

Os KBs conhecidos:
http://support.microsoft.com/kb/328551/en-us
http://support.microsoft.com/kb/936185/en-us

Esse foi o primeiro da série "Perguntas intrigantes do SQL Server". Espero que tenha gostado.

PS: era para ter sido um post rápido. Acho que estou precisando de colocar essas idéias para fora com mais frequência.

[]s
Luciano Caixeta Moreira - {Luti}
Chief Innovation Officer
Sr. Nimbus Serviços em Tecnologia Ltda
luciano.moreira@srnimbus.com.br
www.twitter.com/luticm