Inicio esse post com uma parte do artigo do MSDN explicando o que é uma TEMPDB.
O banco de dados do sistema tempdb é um recurso global disponível a todos os usuários conectados a uma instância de SQL Server e é usado para manter o seguinte:- Os objetos de usuário temporários criados explicitamente como: tabelas temporárias globais ou locais, procedimentos armazenados temporários, variáveis de tabela ou cursores.
- Objetos internos criados por Mecanismo de Banco de Dados do SQL Server, por exemplo, tabelas de trabalho para armazenar resultados intermediários para spool ou classificação.
- Versões de linha geradas por transações de modificação de dados em um banco de dados que usa a leitura de confirmados usando transações de isolação de controle de versão de linha ou isolação de instantâneo.
- As versões de linhas geradas por meio de transações de modificação de dados para recursos como: operações de índice on-line, vários conjuntos de resultados ativos (MARS) e gatilhos AFTER.
Muitas vezes em determinados momentos, você DBA pode se deparar com o um alarme que o database TEMPDB, que é um recurso de armazenamento global temporário esta próximo de atingir 100% de ocupação.
Isso é um problema, mas que pode ser resolvido com algumas ações:
Passo 1 – Identificar a query ofensora
A melhor forma de identificar é executando uma query de monitoramento que retorne as informações que você precisa para a analise. Para isso execute no console:
select top 10 t1.session_id, t1.request_id, t3.hostname, t3.loginame, t3.program_name, db_name (t3.dbid) as dbname, t1.task_alloc * (8.0/1024.0) as Alocado_MB, --qtd de paginas t1.task_dealloc * (8.0/1024.0)as Desalocado_MB, --qtd de paginas (SELECT SUBSTRING(text, t2.statement_start_offset/2 + 1, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),text)) * 2 ELSE statement_end_offset END - t2.statement_start_offset)/2) FROM sys.dm_exec_sql_text(t2.sql_handle)) AS query_text, (SELECT query_plan from sys.dm_exec_query_plan(t2.plan_handle)) as query_plan from (Select session_id, request_id, sum(internal_objects_alloc_page_count + user_objects_alloc_page_count) as task_alloc, sum (internal_objects_dealloc_page_count + user_objects_dealloc_page_count) as task_dealloc from sys.dm_db_task_space_usage group by session_id, request_id) as t1, sys.dm_exec_requests as t2, sys.sysprocesses as t3 where t3.loginame <> '' and t1.session_id = t2.session_id and (t1.request_id = t2.request_id) and t1.session_id = t3.spid and t1.session_id > 50 order by t1.task_alloc DESC
O importante nesse caso é analisar o que essa query esta fazendo e o quanto está alocando no momento do pico.
Aguarde alguns segundos e execute novamente, e nesse caso, analise o próximo campo que será o quanto esta liberando de espaço. O que isso quer dizer:
Na verdade se a query esta usando muito espaço, mas ao executar novamente verifica-se que o valor livre esta crescendo, quer dizer que essa query já fez o que precisava e gradativamente esta devolvendo esse espaço a TEMPDB. Nesse caso somente aguarde que a TEMPDB será liberada em breve e o alarme vai parar. O ultimo campo não exibido aqui é o plano de execução.
Caso a query continue crescendo e consumindo um espaço exorbitante da TEMPDB a ação correta a tomar é a seguinte:
Passo 2 – Analizar as configurações da TEMPDB
A TEMPDB está setada com um valor X pré-supondo que é o necessário para o armazenamento necessário das transações.
Para verificar essas informações clique com o botão direito no database TEMPDB > Properties, como mostra a figura abaixo.
Clique em files, como mostra a figura abaixo:
O arquivo lógico que precisamos analisar chama-se TEMPLOG, verifique para qual caminho fisico que ele esta apontando, que nesse caso é o *E:* (Não exibido aqui, mas fica ao lado da imagem abaixo) e clique no botão ao lado das informações de tamanho como mostra a figura a seguir
Nesse momento será exibidos os parâmetros de crescimento do templog, como mostra a figura aseguir.
Caso haja um crescimento de informações, ela irá crescer gradativamente, desde que o parâmetro AutoGrowth esteja setado.
No parâmetro File Growth está definido como será esse crescimento, nesse caso a cada 1GB.
E por fim analisar o parâmetro Maximum File Size, que define o valor máximo total que esse database pode crescer fisicamente no disco, nesse caso 20GB.
Antes de alterar qualquer parâmetro é importante saber o quanto espaço temos livre no disco em que o database esta armazenado, como vimos anteriormente.
Caso o disco tenha espaço aumente o valor máximo, gradativamente a cada 10GB, até que seja sanado o problema. Depois, quando tudo terminar volte o valor, pois essa é apenas uma medida paliativa.
Passo 3 – Estudando a query ofensiva e sugerindo tunning para melhoria.
Aumentar tamanho de disco, na verdade é uma medida de emergência.
O que deve ser feito com calma é um estudo da query ofensiva e resolver definitivamente o problema. Para isso é necessário analizar o plano de execução dessa query para entender onde esta o auto consumo do banco.
Para isso clique no link que o aparece no resultado da execução da query de monitoramento ( query mostrada no inicio desse documento ).
Será exibido uma pagina em xml com o plano de execução, como mostra a figura a seguir:
Esse é o plano de execução da query ofensiva. O SQL Server automaticamente detecta onde esta o problema e sugere uma ação de “tunning” para melhorar a query. Essa sugestão é mostrada no cabeçalho da página como mostra o topo da imagem.
Ao executar a query de monitoramento sabemos qual host, login e programa esta executando a tal query. Entre em contato com o departamento responsável por essa query notificando o auto consumo.
Passo 4 – Matando o SQL Ofensor !!!
Quando nada resolver … é só matar essa query.
Para isso é necessário primeiro descobri o SPID dessa query com o comando a seguir.
SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name'
Agora é só matar o processo com o comando:
kill SPID
Passo 5 – Algumas referência.
Artigo Completo MSDN Sobre TEMPDB
http://www.red-gate.com/specials/Grant.htm
Juarez Silva, DBA Senior que contribuiu com o desenvolvimento do script de monitoração.
Espero ter ajudado um pouco …
Abraços
Kenia Milene
Tweet to @keniamilene
