logo标签列表

数据库的CPU利用率是什么意思？

sql-servermonitoring
31

是否可以按数据库获取CPU利用率的详细信息？

我希望能够像任务管理器一样查看 SQL 服务器的界面，但不是查看每个 PID 的 CPU 利用率（如 taskmgr），也不是每个 SPID 的 CPU 利用率（如 spwho2k5），而是想要查看每个数据库的总 CPU 利用率。假设只有一个 SQL 实例。

我知道可以编写工具来收集并报告这些数据，但我想知道是否有任何工具可以让我实时查看哪些数据库对 sqlservr.exe 的 CPU 负载作出了最大贡献。

8个回答
91
有点像。看一下这个查询：
SELECT total_worker_time/execution_count AS AvgCPU  
, total_worker_time AS TotalCPU
, total_elapsed_time/execution_count AS AvgDuration  
, total_elapsed_time AS TotalDuration  
, (total_logical_reads+total_physical_reads)/execution_count AS AvgReads 
, (total_logical_reads+total_physical_reads) AS TotalReads
, execution_count   
, SUBSTRING(st.TEXT, (qs.statement_start_offset/2)+1  
, ((CASE qs.statement_end_offset  WHEN -1 THEN datalength(st.TEXT)  
ELSE qs.statement_end_offset  
END - qs.statement_start_offset)/2) + 1) AS txt  
, query_plan
FROM sys.dm_exec_query_stats AS qs  
cross apply sys.dm_exec_sql_text(qs.sql_handle) AS st  
cross apply sys.dm_exec_query_plan (qs.plan_handle) AS qp 
ORDER BY 1 DESC

这将按CPU使用量的顺序获取计划缓存中的查询。您可以定期运行此操作，例如在SQL Agent作业中，并将结果插入表中，以确保数据在重新启动后仍然存在。
当您阅读结果时，您可能会意识到为什么我们无法直接将该数据与单个数据库相关联。首先，单个查询还可以通过执行以下技巧来隐藏其真实的数据库父项：
USE msdb
DECLARE @StringToExecute VARCHAR(1000)
SET @StringToExecute = 'SELECT * FROM AdventureWorks.dbo.ErrorLog'
EXEC @StringToExecute

这个查询将在MSDB中执行，但它会从AdventureWorks中获取结果。我们应该把CPU消耗分配到哪里？

当你：

  • 在多个数据库之间加入
  • 在多个数据库中运行事务，并且锁定工作跨越多个数据库
  • 在MSDB中运行SQL Agent作业，在MSDB中“工作”，但备份单个数据库

问题就变得更糟了。这就是为什么在查询级别而不是在数据库级别进行性能调整是有意义的。

在SQL Server 2008R2中，微软引入了性能管理和应用程序管理功能，使我们可以将单个数据库打包成可分发和可部署的DAC包，并承诺提供功能，使管理单个数据库及其应用程序更容易。尽管如此，它仍无法满足您的要求。

有关更多信息，请查看Toad World's SQL Server wiki（以前在SQLServerPedia）的T-SQL存储库。

更新于1/29，包括总数而不仅仅是平均值。

2
请原谅我的无知，AvgCPU和AvgDuration的单位是什么？ - Portman
1
毫秒数适用于CPU时间和执行时间，它们可能不同，因为查询可能会等待IO、锁定、客户端等。我将修改查询以获取总数。（我本来想说英尺-磅，但我无法保持虚拟的严肃表情。） - Brent Ozar
15

SQL Server（从2000开始）会安装性能计数器（可从Performance Monitor或Perfmon查看）。

其中一个计数器类别（来自SQL Server 2005安装）是： - SQLServer：Databases

每个数据库都有一个实例。然而，可用的计数器并没有提供CPU％利用率计数器或类似的内容，尽管有一些速率计数器，可以用来获得CPU的良好估算。例如，如果您有2个数据库，并且在数据库A上测量的速率为20个交易/秒，在数据库B上为80个交易/秒，则您将知道A大约贡献了总CPU的20％，而B则贡献了另外的80％。

这里存在一些缺陷，因为这假设所有工作都是CPU绑定的，但显然在数据库中不是这样。但我认为这是一个好的开始。

6

以下是一个查询，可以显示导致高负载的实际数据库。它依赖于查询缓存，但在低内存情况下可能会频繁刷新（使查询结果不那么有用）。

select dbs.name, cacheobjtype, total_cpu_time, total_execution_count from
    (select top 10
        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
    ) a
inner join 
(SELECT plan_handle, pvt.dbid, cacheobjtype
FROM (
    SELECT plan_handle, epa.attribute, epa.value, cacheobjtype
    FROM sys.dm_exec_cached_plans 
        OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
     /* WHERE cacheobjtype = 'Compiled Plan' AND objtype = 'adhoc' */) AS ecpa 
PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("dbid", "sql_handle")) AS pvt
) b on a.plan_handle = b.plan_handle
inner join sys.databases dbs on dbid = dbs.database_id
1
考虑到上述内容，从SQL Server 2012（可能是2008？）开始，在sys.dm_exec_sessions中有一个名为database_id的列。
它可以为我们提供每个当前连接会话的数据库CPU的简单计算。如果会话已断开，则其结果已消失。
select session_id, cpu_time, program_name, login_name, database_id 
  from sys.dm_exec_sessions 
 where session_id > 50;

select sum(cpu_time)/1000 as cpu_seconds, database_id 
 from sys.dm_exec_sessions 
group by database_id
order by cpu_seconds desc;
1

我认为你问题的答案是否定的。

问题在于一台机器上的一个活动可能会对多个数据库造成负载。如果我有一个进程正在从配置数据库读取，记录到日志数据库，并根据类型将事务移入和移出各种数据库，那么如何分配CPU使用率？

您可以按交易负载划分CPU利用率，但这又是一个粗略的指标，可能会误导您。例如，您如何将一个数据库中的交易日志传送到另一个数据库中？读取或写入时的CPU负载？

最好查看机器的交易速率和它所引起的CPU负载。您还可以对存储过程进行分析，并查看其中是否有任何花费过多时间的过程；但是，这不会给您想要的答案。

1
请检查这个查询：
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
0

看一下SQL Sentry。它可以满足你的所有需求，甚至更多。

祝好， Lieven

0

你有看过SQL Profiler吗？

拿标准的"T-SQL"或者“存储过程”模板，调整字段以按数据库ID分组（我想你必须使用数字，因为你得不到数据库名称，但是可以使用exec sp_databases获取列表轻松找出）。

运行一段时间后，你将得到总CPU计数/磁盘IO/等待等信息。这可以给你每个数据库使用CPU的比例。

如果同时监视PerfMon计数器（将数据记录到SQL数据库），并对SQL Profiler执行相同操作（记录到数据库），你可能能够将两者相关联。

即使如此，它也足以让你了解哪个数据库值得更详细地查看。然后，只针对该数据库ID再次执行相同的操作，并查找最昂贵的SQL / 存储过程。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接