Por Junior Galvão em 13/04/2017.
Bom dia pessoal, tudo bem?
Véspera de feriado religioso e um dos mais importantes para boa parte da população mundial! Justamente por ser véspera de feriado hoje é um daqueles dias que qualquer profissional da área de banco de dados reza para que nada ocorra de errado próximo ás 17hrs ou mais conhecido como 5 horas da tarde.
Pensando neste tipo de necessário o post de hoje dedicado a sessão Dica do Mês tem o objetivo de ajudar você profissional que esta em apuros neste momento tentando identificar o que pode estar ocorrendo em seu ambiente ou servidor Microsoft SQL Server.
Hoje vou apresentar como você pode no Microsoft SQL Server a partir da versão 2008 R2 identificar quais transações estão realizando o acesso e demandando informações para o Transact – Log, arquivo utilizado por qualquer banco de dados existente em seu servidor ou instância SQL Server para registrar dados relacionados as transações atuais ou já processadas, sendo este mais conhecido como log de transações.
Estão vamos em frente…..
Introdução
Quando se referimos ao log de transações, temos sempre aquele boa e velha definição, “o transact log é o arquivo utilizado pelo banco de dados para registrar o que esta sendo processado por nossas transações, conhecido como o dedo duro”, na verdade o transact-log é bem mais do que isso, trata-se de um dos principais componentes de qualquer banco de dados criado no Microsoft SQL Server que nos permite através do seu uso garantir a continuidade e disponibilidade de nossos dados para diversos cenários de armazenamento e principalmente recuperação de informações.
Durante o processamento de uma ou mais querys o transact-log pode apresentar comportamentos diversas de acordo com o que esta sendo processado, mas principalmente no que se relacionado aos processos de escrita e leitura do log, onde nossas querys podem gerar processos de gravação de dados no transact-log e internamente o SQL Server pode necessitar a acessar outros dados em log para atender outras possíveis demandas.
Estes processos são conhecidos como leitura ou escrita do transact-log, situações que podem impactar aleatoriamente no processamento de nossas querys.
Pensando neste tipo de possibilidade, o post de hoje visa ilustrar e apresentar como através do Management Studio ferramenta de administração e gerenciamento do Microsoft SQL Server podemos em conjunto com as DMVs – Dynamic Management Views listadas abaixo, monitorar, catalogar e identificar quais querys ou transações estão fazendo uso do Transact-Log em possíveis processos de leitura ou escrita do log.
Dynamic Management Views
Para saber mais sobre cada DMV acesse as referências apresentadas no final do post:
- sys.dm_tran_session_transactions;
- sys.dm_tran_active_transactions;
- sys.dm_tran_database_transactions;
- sys.dm_exec_sessions;
- sys.dm_exec_requests; e
- sys.dm_exec_sql_text.
O Cenário
Para tentar ilustrar como podemos realizar de maneira fácil este tipo de monitoramento e identificação, vamos criar um pequeno cenário através do Bloco de Código 1, onde estaremos realizando a criação de um novo banco de dados denominado UsingTransactLog para armazenar a tabela TabelaTransactionLog.
— Bloco de Código 1 —
— Criando o banco de dados —
Create Database UsingTransactLog
Go
— Acessando o banco de dados —
Use UsingTransactLog
Go
— Criando a tabela —
Create Table TabelaTransactLog
(TransactionID Int Default Current_Transaction_ID(),
TransactionUser Varchar(100) Default System_User,
TransactionDate DateTime Default Current_TimeStamp)
Go
Note que nosso tabela TabelaTransactLog possui um simples estrutura composto por três colunas: TransactionID, TransactionUser e TransactionDate, sendo estes elementos básicos utilizados para catalogar o ID da transação em processamento, o usuário conectado ao banco de dados e data/hora do momento da inserção da linha.
Até aqui nada de tão importante, mas importante para podemos simular o uso do log, nosso próximo passo consiste no processamento do Bloco de Código 2, onde estaremos realizando o processamento de uma transação de inserção de 50.000 mil linhas de registros, sendo a operação de insert uma das mais diversas operações que demando processos de escrita ao transact – log, sendo assim, apresento a seguir o Bloco de Código 2:
— Bloco de Código 2 —
Insert Into TabelaTransactLog Default Values
Go 50000
Observe que não estamos passando nenhum tipo de valores para nossas colunas, na verdade estou forçando o SQL Server a utilizar a criação de valores padrões durante o processamento de cada linha de registro através da instrução Default Values (valores padrões), o que vai obrigar nossa instância e ter que gerar valores durante o tempo de processamento e posteriormente registrar em log.
Muito bem, o processamento desta massa de dados, teremos a necessidade de abrir uma nova query para justamente fazer uso do Bloco de Código 3, sendo este o principalmente elemento do nosso cenário, a query que nos permiti monitorar e identificar as transações que estão em tempo real utilizando o transact-log. Então abra uma nova query e log o Bloco de Código 3 apresentado a seguir:
— Bloco de Código 3 —
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Go
SELECT tst.session_id As ‘Sessão’,
es.original_login_name As ‘Login’,
DB_NAME(tdt.database_id) AS ‘Database’,
DATEDIFF(SECOND, tat.transaction_begin_time, GETDATE()) AS ‘Tempo decorrido’,
tdt.database_transaction_log_record_count AS ‘Espaço Utilizado’,
CASE tat.transaction_state
WHEN 0 THEN ‘A transação não foi completamente inicializada ainda…’
WHEN 1 THEN ‘A transação foi inicializada, mas não começou…’
WHEN 2 THEN ‘A transação esta ativa…’
WHEN 3 THEN ‘A transação foi encerrada…’
WHEN 4 THEN ‘Foi iniciado o processo de confirmação sobre o transação distribuída…’
WHEN 5 THEN ‘A transação está em estado preparação e esperando resolução…’
WHEN 6 THEN ‘A transação foi confirmada…’
WHEN 7 THEN ‘A transação esta sendo revertida para o estado anterior…’
WHEN 8 THEN ‘A transação foi revertida para o estado anterior…’
ELSE ‘Estado da transação desconhecido’
END AS ‘Estado da Transação’,
SUBSTRING(TXT.text, ( er.statement_start_offset / 2 ) + 1, ((CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), TXT.text)) * 2
ELSE er.statement_end_offset
END – er.statement_start_offset ) / 2 ) + 1) AS ‘Query Atual’,
TXT.text AS ‘Query relacionada’,
es.host_name As ‘Hostname’,
CASE tat.transaction_type
WHEN 1 THEN ‘Transação Read/Write’
WHEN 2 THEN ‘Transação Read-Only’
WHEN 3 THEN ‘Transação de Sistema’
WHEN 4 THEN ‘Transação distribuída’
ELSE ‘Tipo de Transação desconhecido’
END AS ‘Tipo da Transação’,
tat.transaction_begin_time AS ‘Iniciado em’
FROM sys.dm_tran_session_transactions AS tst INNER JOIN sys.dm_tran_active_transactions AS tat
ON tst.transaction_id = tat.transaction_id
INNER JOIN sys.dm_tran_database_transactions AS tdt
ON tst.transaction_id = tdt.transaction_id
INNER JOIN sys.dm_exec_sessions es
ON tst.session_id = es.session_id
INNER JOIN sys.dm_exec_requests er
ON tst.session_id = er.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) TXT
ORDER BY [Tempo decorrido] DESC
Go
Pois bem, você pode estar querendo saber um pouco mais sobre o que exatamente esta acontecendo ao processar o Bloco de Código 3, pode ficar tranquilo que a seguir vou fazer um pequeno overview sobre este bloco de código, começando pelo conjunto de colunas retornadas por ele:
- Sessão – Apresenta o ID da sessão que esta utilizando o transact-log;
- Login – Nome da conta de login que esta conectada ao SQL Server;
- Database – Nome do banco de dados utilizado pela query que esta acessando o transact-log;
- Tempo decorrido – Representa o tempo de processamento que o query esta demandando para utilizar o transact-log;
- Log Record Count– Identifica a quantidade de registros de log gerados pela query ou transações em processamento;
- Estado da transação – Esta coluna apresenta o estado atual da query em processamento, sendo este formado por um conjunto de valores;
- Query Atual – Apresenta as instruções que estão sendo processadas por cada query;
- Query Relacionada– Apresenta as instruções processadas por querys que podem estar relacionadas com as atuais querys em processamento;
- Hostname – Apresenta o nome dos hosts utilizados por cada query em processamento;
- Tipo de Transação – Identifica se a transação em processamento esta realizando acesso de leitura ou escrita ao log, bem como, se esta query é uma query de sistema ou distribuída; e
- Iniciado em – Apresenta o horário de início do acesso ao transact-log por parte da query em processamento.
Agora que você já conhece um pouco sobre cada coluna que será retornada após o processamento do Bloco de Código 3, vou abordar o uso da instrução Set Transaction Isolation Level declarada no início do código, que possui a finalidade de alterar no nível de isolamento do nosso banco de dados para Read Uncommitted afim de força o SQL Server a descartar ou desconsiderar qualquer possível ocorrência de bloqueios tanto no nível de tabela e registros, mas principalmente no escopo de banco de dados.
Nota: Se você me perguntar que isso é obrigatório ser feito para conseguir identificar as querys que estão utilizando o transact-log eu vou responder NÃO, isso não é obrigatório, mas pode ser considerada uma possível solução para garantir que o Bloco de Código 3 possa ser processado e consultar as dynamic management views na busca das informações catalogadas por ela em tempo de execução.
Ufa, estamos quase lá, agora nos resta fazer a execução do mesmo e observar o retorno dos dados coletados por este bloco de código, com base, na Figura 1 apresentada abaixo:
Figura 1 – Relação de querys em execução utilizando transact-log.
Perfeito, nosso monitoramento funcionou, conseguimos coletar os dados referentes as querys que estão neste momento em processamento fazendo acesso e utilizando o transact-log criado para o banco de dados UsingTransactLog.
Isso não é sensacional, desta forma, chegamos ao final de mais um post dedicado a sessão Dica do Mês, espero que você tenha gostado.
Referências
- https://msdn.microsoft.com/en-us/library/ms188739.aspx
- https://msdn.microsoft.com/pt-br/library/ms174302.aspx
- https://msdn.microsoft.com/en-us/library/ms186957.aspx
- https://msdn.microsoft.com/pt-br/library/ms176013.aspx
- https://msdn.microsoft.com/pt-br/library/ms177648.aspx
- https://msdn.microsoft.com/pt-br/library/ms181929.aspx
- https://technet.microsoft.com/pt-br/library/ms175156(v=sql.105).aspx
Nenhum comentário:
Postar um comentário