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.