none
% de Processador sendo utilizado

    Frage

  • Pessoal, estou com uma dúvida, preciso de um SELECT que colete o % de processador que o SQL-Server esta sando. 

    Agradeço desde já.

    Dienstag, 13. Februar 2018 10:59

Alle Antworten

  • Olá Vinicius Lana

    Dá uma olhada nas querys abaixo e verifique se alguma lhe ajuda:

    SELECT * FROM sys.sysprocesses
    
    SELECT * FROM sys.sysperfinfo
    
    SELECT  
    req.session_id,
    req.status,
    req.command,
    req.cpu_time,
    req.total_elapsed_time
    FROM sys.dm_exec_requests req 

     

    Se a resposta for relevante ou tenha resolvido seu problema, marque como útil/resposta!

    Rafael Almeida
    Microsoft Developer .NET
    Microsoft Certified Professional
    Development Leader at JAMSOFT Informática
    Email: ralms@ralms.net
    Blog -  GitHub  -  LinkedIn -  Twitter

    Dienstag, 13. Februar 2018 13:26
  • Vinícius,

    Veja estes outros exemplos:

    SELECT 
      DES.SESSION_ID,
      DES.CPU_TIME,
      DES.READS,
      DES.WRITES,
      DES.LOGICAL_READS,
      DES.ROW_COUNT,
      DER.SESSION_ID,
      DES.STATUS,
      DES.HOST_NAME,
      DES.PROGRAM_NAME,
      DES.LOGIN_NAME,
      DES.ORIGINAL_LOGIN_NAME, 
      DEC.CLIENT_NET_ADDRESS,
      DEC.AUTH_SCHEME,
      DEC.NET_TRANSPORT,
      SUBSTRING(T.[TEXT], DER.[STATEMENT_START_OFFSET] / 2, 
    	COALESCE(NULLIF(DER.[STATEMENT_END_OFFSET], - 1) / 2, 2147483647)) AS COMANDO
    FROM
     SYS.DM_EXEC_SESSIONS AS DES 
      INNER JOIN SYS.DM_EXEC_REQUESTS DER
       ON DER.BLOCKING_SESSION_ID = DES.SESSION_ID
      INNER JOIN SYS.DM_EXEC_CONNECTIONS DEC  
       ON DEC.SESSION_ID = DES.SESSION_ID
      INNER JOIN SYS.DM_EXEC_REQUESTS DER2
       ON DER2.SESSION_ID = DES.SESSION_ID
       CROSS APPLY SYS.DM_EXEC_SQL_TEXT(DER.[SQL_HANDLE]) AS T
    GO

    -- Abrir nova query e executar --
    SET STATISTICS PROFILE ON
    GO
    
    
    SELECT  session_id ,
            node_id ,
            physical_operator_name ,
            SUM(row_count) row_count ,
            SUM(estimate_row_count) AS estimate_row_count ,
            IIF(COUNT(thread_id) = 0, 1, COUNT(thread_id)) [Threads] ,
            CAST(SUM(row_count) * 100. / SUM(estimate_row_count) AS DECIMAL(30, 2)) [% Complete] ,
            CONVERT(TIME, DATEADD(ms, MAX(elapsed_time_ms), 0)) [Operator time] ,
            DB_NAME(database_id) + '.' + OBJECT_SCHEMA_NAME(QP.object_id,
                                                            qp.database_id) + '.'
            + OBJECT_NAME(QP.object_id, qp.database_id) [Object Name]
    FROM    sys.dm_exec_query_profiles QP
    GROUP BY session_id ,
            node_id ,
            physical_operator_name ,
            qp.database_id ,
            QP.OBJECT_ID ,
            QP.index_id
    ORDER BY session_id ,
            node_id
    GO

    SELECT spid, 
           QRY.[text] AS Consulta 
    FROM sysprocesses AS P
    CROSS APPLY sys.dm_exec_sql_text(P.sql_handle) AS QRY
    WHERE P.open_tran <> 0


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    Mittwoch, 14. Februar 2018 11:32
    Moderator