CPU utilization by database?

Question

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

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.

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.

Answer 1

Sort of. Check this query out:

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

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

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:

• Join between multiple databases
• Run a transaction in multiple databases, and the locking effort spans multiple databases
• Run SQL Agent jobs in MSDB that "work" in MSDB, but back up individual databases

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

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.

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

Updated on 1/29 to include total numbers instead of just averages.