数据库的CPU使用率? [英] CPU utilization by database?

问题描述

是否可以按数据库对CPU利用率进行细分?

Is it possible to get a breakdown of CPU utilization by database?

理想情况下，我正在寻找SQL Server的任务管理器类型的接口，但我不想查看每个PID(例如taskmgr)或每个SPID(例如spwho2k5)的CPU使用率，每个数据库的CPU总利用率.假设有一个SQL实例.

I'm ideally looking for a Task Manager type interface for SQL server, but instead of looking at the CPU utilization of each PID (like taskmgr) or each SPID (like spwho2k5), I want to view the total CPU utilization of each database. Assume a single SQL instance.

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

I realize that tools could be written to collect this data and report on it, but I'm wondering if there is any tool that lets me see a live view of which databases are contributing most to the sqlservr.exe CPU load.

推荐答案

排序.检查此查询:

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作业中那样定期运行此命令，并将结果插入表中以确保数据在重启后仍然存在.

This will get you the queries in the plan cache in order of how much CPU they've used up. You can run this periodically, like in a SQL Agent job, and insert the results into a table to make sure the data persists beyond reboots.

阅读结果时，您可能会意识到为什么我们不能将数据直接关联回单个数据库.首先，通过执行以下操作，单个查询还可以隐藏其真正的数据库父级:

When you read the results, you'll probably realize why we can't correlate that data directly back to an individual database. First, a single query can also hide its true database parent by doing tricks like this:

USE msdb
DECLARE @StringToExecute VARCHAR(1000)
SET @StringToExecute = 'SELECT * FROM AdventureWorks.dbo.ErrorLog'
EXEC @StringToExecute

该查询将在MSDB中执行，但它将轮询AdventureWorks的结果.我们应该在哪里分配CPU消耗?

The query would be executed in MSDB, but it would poll results from AdventureWorks. Where should we assign the CPU consumption?

当您出现以下情况时，情况会变得更糟

It gets worse when you:

• 在多个数据库之间加入
• 在多个数据库中运行事务，锁定工作跨越多个数据库
• 在MSDB中运行可以在MSDB中工作"的SQL Agent作业，但备份单个数据库

它持续不断.这就是为什么在查询级别而不是数据库级别进行性能调整很有意义的原因.

It goes on and on. That's why it makes sense to performance tune at the query level instead of the database level.

在SQL Server 2008R2中，Microsoft引入了性能管理和应用程序管理功能，这些功能使我们可以将单个数据库打包在可分发和可部署的DAC包中，它们有望使功能更易于管理单个数据库及其数据库的性能.应用程序.但是，它仍然无法满足您的需求.

In SQL Server 2008R2, Microsoft introduced performance management and app management features that will let us package a single database in a distributable and deployable DAC pack, and they're promising features to make it easier to manage performance of individual databases and their applications. It still doesn't do what you're looking for, though.

有关更多信息，请查看 Toad World的SQL Server Wiki(以前是SQLServerPedia)上的T-SQL存储库.

For more of those, check out the T-SQL repository at Toad World's SQL Server wiki (formerly at SQLServerPedia).

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

这篇关于数据库的CPU使用率?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！