Forma rápida de verificar queries que estão pesando muito no servidor - Parte I

Oi pessoal! Aí vai um dica muito boa tirada do SQLServerPedia e que já usei algumas vezes para detectar problemas de performance no servidor. Quando tem uma série de consultas onerando o servidor, uma das primeiras coisas que o DBA pensa é em ver os processos em execução e, caso não encontre um padrão, usar o Profiler. O Profiler, para quem não conhece, é uma ferramenta que consegue listar atividades ocorridas no SQL Server, então é possível utilizá-la para verificar o consumo de CPU e I/O de consultas de maneira comparativa.

Existem situações em que usar o Profiler não é uma escolha: a execução do Profiler onera o servidor e pode piorar ainda mais a situação de um servidor sobrecarregado ou, ainda pior, o usuário pode ter só um Management Studio Express e não ter o Profiler instalado para fazer essa verificação. Nestes casos é possível, para SQL Server 2005 e 2008, apelar para as dynamic management views, as famosas DMVs. As DMVs fornecem informações extremamente valiosas para o gerenciamento de um servidor, e entre elas o procedure cache, que é um cache que armazena planos de execução corriqueiramente utilizados. Então, vamos à consulta:

select total_worker_time/execution_count as MediaCPU

, total_worker_time AS TotalCPU

, total_elapsed_time/execution_count as MediaDuration

, total_elapsed_time AS TotalDuration

, total_logical_reads/execution_count as MediaLogicalReads

, total_logical_reads AS TotalLogicalReads

, total_physical_reads/execution_count as MediaPhysicalReads

, total_physical_reads AS TotalPhysicalReads

, execution_count 

, substring(st.text, (qs.statement_start_offset/2)+1

, ((case qs.statement_end_offset  when -1 then datalength(st.text)

else qs.statement_end_offset

end - qs.statement_start_offset)/2) + 1) as txt

, query_plan

from sys.dm_exec_query_stats as qs

cross apply sys.dm_exec_sql_text(qs.sql_handle) as st

cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp

order by 1 desc

Se você quer entender os resultados da query acima, basicamente são apresentados nos oito primeiros campos contadores de média e total para uso de CPU, duração da execução, logical reads (leituras de cache) e physical reads, que é leitura direta de valores no disco. Como os indicadores do SQL são inexatos é melhor tomá-los como medidas relativas. O "order by 1 desc" trará as consultas que tiveram mais peso em processamento, alternando para "order by 5 desc" se teria os mais pesados em logical reads e por aí vai. O execution_count apresenta o número de execuções, txt o corpo da consulta e query_plan acaba por apresentar o plano de execução.

Nas primeiras consultas, utilize um "top" para restringir o número de registros retornados, principalmente se o cache comportar muitos registros. Com essa query já se resolvem muitos problemas. Amanhã vou dissecar a sys.dm_exec_query_stats para que, caso vocês queiram, vocês possam aplicar em outros cenários de monitoramento.

Novamente fico à disposição para quaisquer dúvidas. Se eu não souber, corro atrás. Abraços!

---

P.S.: Um amigo, ex-colega e leitor do blog, Jackson Barbian, sinalizou que não conseguiu executar o script no SQL Server 2005 pois estava apresentando erro de sintaxe. Descobrimos depois que o problema era o modo de compatibilidade do banco de dados com o SQL Server 2000, o que faz com que o CROSS APPLY não funcione. Nesses casos, acesse o banco de dados MASTER ou outro banco de dados e execute a query, pois os dados dela não são sensíveis ao banco de dados selecionado. Valeu Jackson!

Verificando em quais stored procedures e views uma tabela está sendo utilizada

Oi pessoal! Um problema muito frequente que encontro é mapear quais stored procedures e views utilizam uma determinada tabela. Claro, este método não vai capturar usos indiretos, como uma stored procedure que consulta uma view que consulta a tabela desejada, mas sim se o nome da tabela consta no código da stored procedure. Portanto CLR procedures também estão de fora. Mas vamos à query, funciona em 2005 e 2008, ainda vou testar no 2012:

SELECT obj.Name as [Nome da procedure],

sc.TEXT as [Conteudo da procedure]

FROM sys.syscomments sc

INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID

WHERE sc.TEXT LIKE '%TABELA%' AND TYPE = 'P'

Onde está o texto TABELA, você deve colocar o nome da tabela em questão. Se as procedures utilizam a convenção de nomenclatura de schema + tabela (Exemplo: dbo.tabela) você vai achar os registros facilmente. Se o nome da sua tabela estiver contida no nome de outra tabela ou view e vocês não usar essa convenção de nomenclatura, bem, podem pintar alguns resultados errados, mas ainda assim afunila o volume de procedures a se inspecionar. 

Ah, e para pegar views, é barbadinha. Troque a linha:

AND TYPE = 'P'

Por:

AND TYPE = 'V'

E você listará as views onde o nome da tabela é encontrado. Pra manutenção em sistemas, é uma belezinha. Abração!

Script para finalizar processos de usuário provenientes de uma máquina específica em um banco específico

Oi pessoal, tudo bem? Primeiro post e uma dica resultante de um problema no trabalho hoje. Pintou um problema em que uma stored procedure fez um lock inadequado em uma tabela e enfileirou uma série de SELECTs e INSERTs feitos por agentes.

Como esse enfileiramento acabou se tornando muito grande, tendo grande impacto no desempenho de outras aplicações, perguntei ao colega se haveria problema em finalizar estes processos enfileirados e ele disse que não. Pois bem, a única coisa que identificava estes SELECTs e INSERTs eram os servidores a partir dos quais as solicitações eram feitas. Então fiz rapidinho um script para finalizar todos os processos oriundos destes servidores em um banco de dados específico:

declare @output varchar(max);

set @output = ''

select @output = @output + 'kill ' + cast(spid as varchar(max)) + ';' from sys.sysprocesses

where DB_NAME(dbid) = '[NOME DO BANCO]'

and hostname in ('[SERVIDOR1]','[SERVIDOR2]','[SERVIDOR3]')

exec(@output)

Resolveu o problema rapidinho. Caso tenham dúvidas, mandem para cá que tentarei respondê-las. Abraços!