Imagine that we’re seeing very heavy load on our SQL server, be it CPU or IO and we need to find out which database is the driver.
I’m sure we’ve all been there at some point, sometimes it’s easy to tell. We might only have a handful of databases on the server with one known to be the heaviest utilised. But sometimes things might not be so obvious, there could be a large numbers of databases or no obvious resource hog.
In those instances we need some way to figure out what how much time each database is spending on the CPU if that’s what we’re interested in or perhaps the total number of page reads or writes if IO is our problem.
Unfortunately SQL doesn’t give us this information directly but there is a DMV that we can tap up to work it out, sys.dm_exec_query_stats will give us runtime stats, along with sys.dm_exec_plan_attributes to get the database name.
You should be aware that this DMV hooks into the plan cache so the information is only as good as what’s in the cache. If you’re server has recently restarted or if you’re having memory issues that are forcing plans to get kicked out of cache then the accuracy of this script could be affected, but assuming that the cache is fairly stable it should give you a good idea of who your resource hogs are.
SELECT DB_NAME(CAST(attributes.value AS INT)) AS DatabaseName
, SUM(total_elapsed_time) AS ElapsedTime
, SUM(total_worker_time) AS WorkerTime
, SUM(total_logical_reads) AS LogicalReads
, SUM(total_physical_reads) AS PhysicalReads
, SUM(total_logical_writes) AS Writes
FROM sys.dm_exec_query_stats stats
CROSS APPLY sys.dm_exec_plan_attributes (plan_handle) attributes
WHERE attributes.attribute = 'dbid'
GROUP BY attributes.value
ORDER BY SUM(total_elapsed_time) DESC
I’ve ordered to give the databases in CPU order but obviously if you’re interested in IO you can change the ORDER BY to suit your needs.
This is something that I’ve seen crop up a few times on the forums so I hope you find it useful.
SQL Undercover 