Consultas lentas no SQL Server
Algumas dicas de como identificar queries lentas no SQL Server
Ao trabalhar com banco de dados, pode ser necessário investigar problemas pontuais de lentidão. Estes problemas podem ser causados por vários motivos, como excesso de acessos, hardware insuficiente ou problemas na modelagem e indexação dos dados. Identificar consultas lentas no SQL Server e tentar descobrir porque o banco de dados está lento pode ser algo muito desafiador.
As consultas a seguir podem ajudar a identificar problemas de lentidão no SQL Server.
O primeiro exemplo seria mostrar o login, host, e a utilização de memória e CPU de cada sessão conectada ao servidor.
SELECT
session_id,
login_time,
host_name,
program_name,
client_interface_name,
login_name,
status,
cpu_time,
memory_usage,
last_request_start_time,
last_request_end_time,
transaction_isolation_level,
lock_timeout,
deadlock_priority
FROM
sys.dm_exec_sessions
WHERE
login_name NOT IN ('sa', 'AUTORIDADE NTSISTEMA', 'NT AUTHORITYSYSTEM')
O exemplo a seguir permite mostrar a quantidade de conexões ativas por login
SELECT
DB_NAME(dbid) as BancoDeDados,
COUNT(dbid) as QtdeConexoes,
loginame as Login
FROM
sys.sysprocesses
WHERE
dbid > 0
GROUP BY
dbid, loginame
O próximo exemplo permite listar quais as conexões ativas.
SELECT
DB_NAME(dbid) as BancoDeDados,
COUNT(dbid) as QtdeConexoes,
loginame as Login
FROM
sys.sysprocesses
WHERE
dbid > 0
GROUP BY
dbid, loginame
As consultas anteriores permitiram listas dados sintéticos em tempo real. Agora vamos ver como podemos listar as consultas mais lentas que foram executadas no servidor. Esta é uma query para verificar performance no SQL Server. No exemplo abaixo, vamos listar as 10 consultas mais lentas.
SELECT TOP 10
total_worker_time/execution_count AS Avg_CPU_Time
,execution_count
,total_elapsed_time/execution_count as AVG_Run_Time
,(SELECT
SUBSTRING(text,statement_start_offset/2,(CASE
WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2
ELSE statement_end_offset
END -statement_start_offset)/2
) FROM sys.dm_exec_sql_text(sql_handle)
) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY Avg_CPU_Time DESC
--OUTRAS OPCOES
--ORDER BY AVG_Run_Time DESC
--ORDER BY execution_count DESC
Para ver quais jobs estão em execução
SELECT
F.session_id,
A.job_id,
C.name AS job_name,
F.login_name,
F.[host_name],
F.[program_name],
A.start_execution_date,
CONVERT(VARCHAR, CONVERT(VARCHAR, DATEADD(ms, ( DATEDIFF(SECOND, A.start_execution_date, GETDATE()) % 86400 ) * 1000, 0), 114)) AS time_elapsed,
ISNULL(A.last_executed_step_id, 0) + 1 AS current_executed_step_id,
D.step_name,
H.[text]
FROM
msdb.dbo.sysjobactivity A WITH(NOLOCK)
LEFT JOIN msdb.dbo.sysjobhistory B WITH(NOLOCK) ON A.job_history_id = B.instance_id
JOIN msdb.dbo.sysjobs C WITH(NOLOCK) ON A.job_id = C.job_id
JOIN msdb.dbo.sysjobsteps D WITH(NOLOCK) ON A.job_id = D.job_id AND ISNULL(A.last_executed_step_id, 0) + 1 = D.step_id
JOIN (
SELECT CAST(CONVERT( BINARY(16), SUBSTRING([program_name], 30, 34), 1) AS UNIQUEIDENTIFIER) AS job_id, MAX(login_time) login_time
FROM sys.dm_exec_sessions WITH(NOLOCK)
WHERE [program_name] LIKE 'SQLAgent - TSQL JobStep (Job % : Step %)'
GROUP BY CAST(CONVERT( BINARY(16), SUBSTRING([program_name], 30, 34), 1) AS UNIQUEIDENTIFIER)
) E ON C.job_id = E.job_id
LEFT JOIN sys.dm_exec_sessions F WITH(NOLOCK) ON E.job_id = CAST(TRY_CONVERT( BINARY(16), SUBSTRING(F.[program_name], 30, 34), 1) AS UNIQUEIDENTIFIER) AND E.login_time = F.login_time
LEFT JOIN sys.dm_exec_connections G WITH(NOLOCK) ON F.session_id = G.session_id
OUTER APPLY sys.dm_exec_sql_text(most_recent_sql_handle) H
WHERE
A.session_id = ( SELECT TOP 1 session_id FROM msdb.dbo.syssessions WITH(NOLOCK) ORDER BY agent_start_date DESC )
AND A.start_execution_date IS NOT NULL
AND A.stop_execution_date IS NULL
Para listar os processos que estão ativos, você pode utilizar a consulta a seguir:
Select spid as Processo, hostname as HostComputador , loginame as usuario , status as status , blocked as BloqueadoPor , cmd as TipoComando , program_name as NomeAplicativo
from master..sysprocesses
where status in (‘runnable’, ‘suspended’)
order by BloqueadoPor desc, status, Processo
Se algum processo estiver bloqueado, o id do processo responsavel aparece na coluna BloqueadoPor. Para cancelar o processo que está causando o problema, você pode executar o seguinte comando
EXEC KILL <>
Uma outra maneira de identificar processos que estão causando travamento é utilizar os comandos
EXEC SP_WHO
ou
EXEC SP_WHO
Ambos fornecem informações sobre os usuários atuais, sessões e processos. O SP_WHO retorna informaçoes sobre o ID do processo da sessão (SPID), o ID do contexto de execução (ECID), o status do processo, o ID que estã bloqueando o processo (se houver bloqueio), o nome do banco, login, usuário e host associado com o processo, eo o tipo de comando que está sendo executado.
O SP_WHO2 é similar ao comando anterior, mas retorna informações adicionais como Disk IO, tempo de CPU e horário da última execução.
Como ambos retornam o ID do processo, você pode usar o comando KILL para eliminar o processo que está causando problemas.