Como encontrar o uso da CPU para o banco de dados do SQL Server 2005

Question

Como encontrar o uso da CPU para o SQL Server 2005 Server

    

Answer 1

Do artigo do MSDN sobre Solucionando problemas de desempenho no SQL Server 2005

The following query gives you a high-level view of which currently cached batches or procedures are using the most CPU. The query aggregates the CPU consumed by all statements with the same plan__handle (meaning that they are part of the same batch or procedure). If a given plan_handle has more than one statement, you may have to drill in further to find the specific query that is the largest contributor to the overall CPU usage.

select top 50  
    sum(qs.total_worker_time) as total_cpu_time,  
    sum(qs.execution_count) as total_execution_count, 
    count(*) as  number_of_statements,  
    qs.plan_handle  
from  
    sys.dm_exec_query_stats qs 
group by qs.plan_handle 
order by sum(qs.total_worker_time) desc

    

Answer 2

Use os contadores de desempenho link

    

Answer 3

Mais alguns:

• WMI
• Contadores de desempenho
• SNMP

Answer 4

Algumas maneiras vêm à mente

• Gerenciador de tarefas
• Process Explorer
• Get-Process sqlservr |select CPU
• WMI
• Contadores de desempenho via PerfMon

Answer 5

Usando sys.dm_exec_query_stats, você pode encontrar usos de cpu em diferentes objetos do SQL Server. Abaixo está um script:

SELECT 
    DB_NAME(st.dbid) AS DatabaseName
    ,OBJECT_SCHEMA_NAME(st.objectid,dbid) AS SchemaName
    ,cp.objtype AS ObjectType
    ,OBJECT_NAME(st.objectid,dbid) AS Objects
    ,MAX(cp.usecounts)AS Total_Execution_count
    ,SUM(qs.total_worker_time) AS Total_CPU_Time
    ,SUM(qs.total_worker_time) / (max(cp.usecounts) * 1.0) AS Avg_CPU_Time 
FROM sys.dm_exec_cached_plans cp 
INNER JOIN sys.dm_exec_query_stats qs 
    ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE DB_NAME(st.dbid) IS NOT NULL
GROUP BY DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid),cp.objtype,OBJECT_NAME(objectid,st.dbid) 
ORDER BY sum(qs.total_worker_time) desc

Neste resultado do script, você pode encontrar tipos de objetos e cálculos diferentes para o tempo da CPU.