Blog – Fabiano Neves Amorim

Em relação a performance um problema muito comum é a contenção no TempDB.

O Banco de dados TempDB é utilizado para várias operações, ao invés de escrever aqui quais são estas operações eu vou de CTRL-C + CTRL+V do Books Online que é mais facil.

The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:

·         Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.

·         Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.

·         Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.

·         Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

No site do TechNet tem um excelente artigo falando sobre o TempDB no SQL Server 2005, segue o link Working with tempdb in SQL Server 2005.

No artigo acima podemos observar em “Troubleshooting contention caused by to DML operations” que quando temos problema de performance no TempDB é recomendado criar um arquivo de dados para cada processador existente no servidor e habilitar o trace flag 1118.

Obs.: Neste caso um processador Dual Core é considerado como 2 processadores, ou seja, criar 2 arquivos.

Conforme o artigo Q328551 a mesma recomendação também foi feita pela Microsoft para servidores rodando SQL Server 2000.

No TechEd 2007 participei de uma mesa redonda sobre Otimização de Performance com o SQL Server 2005 – Dicas e Truques e discutimos sobre o TraceFlag 1118, e os profissionais da Microsoft presentes reforçaram o que os artigos acima recomendam.

Lembro do Fabricio Catae Premier Field Engineer dizer o seguinte:

·         Por precaução e para não ter futuros problemas nós recomendamos o uso do TraceFlag 1118 e criamos um arquivo do TempDB para cada processador.

Concordo com ele, na duvida se o TempDB está sendo um problema ou não, antes de ter problema habilite o TraceFlag e crie um arquivo para cada processador do servidor.

Antes de continuar a falar sobre isso vamos entender um pouco mais sobre os Extents pois eles tem tudo a ver com o TraceFlag 1118.

No SQL Server existem dois tipos de extents, os extents mistos e extents uniformes.

Assim como o extent uniforme o extent misto tem 64 kb(8 páginas de 8k). A diferença entre eles é que um extent misto pode ter páginas de mais de um objeto diferente. Veja o exemplo abaixo.

Repare que no Extent Misto temos informações de vários objetos, Table2, Index1, Index2 e etc…

O SQL Server usa 2 tipos de controladores para alocação dos extents, o GAM e o SGAM.

·         GAM – Para os extents uniformes o SQL irá usar a Global Allocation Map que é responsável por registrar as alocações dos extents uniformes de todos os objetos do banco de dados. O GAM usa um bit para controlar se o extent está livre ou alocado. Se o bit valer 1 o extent está livre se o bit valor 0 o extent está alocado.

·         SGAM – Para os extents mistos o SQL irá usar a Shared Global Allocation Map como o próprio nome diz “Shared” ele é responsável por registrar quais são os extents mistos e qual deles tem páginas livres para uso. O SGAM também usa um bit para fazer este controle, se o bit for 1 então o extent é misto e tem pelo menos uma página livre para utilização, se o bit for 0 então o extent não é um extent misto ou então todas as páginas já estão utilizadas.

Vamos imaginar o seguinte cenário, um simples insert um uma nova tabela temporária.

Todo objeto criado no banco de dados é iniciado com um extent misto até que a tabela ocupe 8 páginas de dados, sabendo disso então podemos dizer que o SQL irá alocar um extend misto para gravar minha informação nesta tabela temporária. Para chegar neste extent misto e na página onde minha informação será gravada o DataBase Engine usa a controladora SGAM.

Ok já sei que o SQL aloca extents mistos para novos objetos, mas o que isso tem a ver com o TraceFlag e o TempDB?

Tem a ver que quando habilitamos o TraceFlag 1118 o SQL passará a não mais alocar extents mistos e sim extents uniformes, pulando a etapa que usa a SGAM para achar um extent misto.

Podemos observar que o SQL tem o trabalho de alocar extents mistos e conforme a tabela vai crescendo o SQL tem controlar se no extent atual cabe os dados que estão sendo inseridos ou se ele precisa alocar um novo extent,isso irá acontecer quando a tabela ficar maior que 64 k, para fazer este controle o SQL irá utilizar a SGAM e a PFS(controla o espaço livre dentro de uma página). Ao alocar direto um extend uniforme o SQL não precisa toda hora consultar a SGAM e a PFS para verificar se ele necessita alocar outro extent para inserir os dados.

O Luciano Moreira deu um show de explicação sobre como, quando, onde e porque utilizar o TraceFlag 1118 o artigo pode ser lido em Analisando o trace flag 1118.

Um detalhe importante é que recentemente(não tão recente assim) a Microsoft divulgou um artigo dizendo que ao fazer o que ela diz você pode ter problemas de desempenho. O artigo pode ser lido aqui Q936185,

O MVP Linchi Shea escreveu um artigo no seu blog falando sobre esse problema, Reduce the Contention on tempdb with Trace Flag 1118: To Enable, or Not to Enable?

Até a próxima e stay tuned!

______________________________________________________________
Fabiano Neves Amorim (MCP – MCTS – SQL Server)
Análise – NewCon Enterprise
* fabiano@cnpm.com.br – http://fabianosqlserver.spaces.live.com/