Olá amigos,

Já vivenciei muitas situações em minha vida profissional, onde vi relatórios, filtros e consultas extremamente pesadas que deixavam o sistema indisponível por causa do banco de dados. Em verdade, que em todos as vezes por uma consulta mal montada, fosse pelo desenvolvedor ou até mesmo pelo ORM usado no projeto.

A Falta de índices adequados às consultas, inexperiência do desenvolvedor e uma estrutura de banco e hardware não adequadas podem comprometer muito sua aplicação.

Hoje trabalho com sistemas em SaaS e que tem de apresentar uma performance muito significativa para os usuários, tendo que nenhuma resposta, não importe o que seja, demore mais que 20 segundos.

Para isto, temos toda uma estrutura montada que permite que na verdade em 95% do tempo respondamos em menos de 1s, porém como sempre acontecem descuidos dos desenvolvedores, já houveram situações de comandos extremamente pesados serem rodados no servidor, elevando o IO, Processador e Memória, além de gerar Locks que afetavam a performance do sistema. E geralmente isto acontecia de madrugada ou em pleno feriado…

Para isto, criei um comando simples que blinda meu sistema contra estas situações, matando qualquer processo que demore mais de 5 minutos (tempo que aprendi no decorrer dos anos como sendo o que o cliente aceita como falha).

Então precisamos de um comando para listar as transações ativas que estão com mais de 300 segundos em execução (5 minutos):

SELECT [transaction_id], [session_id], [elapsed_time_seconds]
		FROM [DATABASE_NAME].[sys].[dm_tran_active_snapshot_database_transactions]
	Where 
		[elapsed_time_seconds] > 300

Para outras opções de como recuperar as transações, acesse aqui no MSDN.

Jogando esta transição em um cursor, percorremos uma a uma executando o comando de Kill em cada uma delas.

Kill [MY_SPID]

E para completar, usamos a função de envio de e-mail para ficarmos sabendo que houve uma transação que precisou ser abortada. Isto é importante para que saibamos o que está havendo e providenciemos que o problema seja corrigido, seja criando um índice, mudando a aplicação ou o que for. Ficaria então assim:

Declare @ProcessId int, @SPID int, @elapsedTime bigint, @CMD NVARCHAR(MAX)

Declare ProcessToKill Cursor FORWARD_ONLY  For 
	SELECT [transaction_id], [session_id], [elapsed_time_seconds]
		FROM [DATABASE_NAME].[sys].[dm_tran_active_snapshot_database_transactions]
	Where 
		[elapsed_time_seconds] > 300

Open ProcessToKill
	
FETCH NEXT FROM ProcessToKill 
	INTO @ProcessId, @SPID, @elapsedTime

WHILE @@FETCH_STATUS = 0
BEGIN
	Print N'Kill process: ' + Convert(NVARCHAR(32), @ProcessId) + 
						  ' - SPID: ' + Convert(NVARCHAR(32), @SPID) +
						  ' - ElapsedTime: ' + Convert(NVARCHAR(64), @elapsedTime)
	
	set @CMD = N'Kill ' + Convert(NVARCHAR(32), @SPID)				  
	Execute SP_EXECUTESQL @CMD
  
	Exec msdb.dbo.sp_send_dbmail
		@profile_name = 'MyProfile',
		@recipients = 'chalk@100loop.com',
		@subject = 'Long transaction killed', 
		@body = 'A long transaction has been killed by "Kill Orphan Tran" job.', 
		@body_format = 'HTML'
  
	FETCH NEXT FROM ProcessToKill 
		INTO @ProcessId, @SPID, @elapsedTime
END

CLOSE ProcessToKill
DEALLOCATE ProcessToKill

Print N'Finished! If print any error message, kneel and pray'

Agora coloque este comando em um job que execute a cada 5 min e habilite o log em txt para ter o registro das ações.

Com isto, você garante que um único cliente ou desenvolvedor descuidado afete sua operação e de seus clientes. Melhor ter um cliente reclamando que não consegue tirar um relatório com mais de 2 anos de intervalo do que ter todos os clientes parados por 50 minutos enquanto o relatório é gerado.