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.

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. 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 <<Id do processo>>

 

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.

 

Quer conferir mais dicas sobre banco de dados? Clique aqui e veja mais dicas!
BINS - Blog SQL

 

Conteúdo sobre banco de dados sem complicação!

MySQL - Referência Rápida

 

SQL Server - Referência Rápida

 

SQL vs Mongo

 

 

 

Veja outros conteúdos que podem ser de seu interesse