<p>Is it possible to get a breakdown of CPU utilization <strong>by database</strong>?</p>

<p>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 <code>taskmgr</code>) or each SPID (like <code>spwho2k5</code>), I want to view the total CPU utilization of each database. Assume a single SQL instance.</p>

<p>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 <code>sqlservr.exe</code> CPU load.</p>

[{'answer_id': 28974, 'author': 'Adam', 'author_id': 1341, 'author_profile': 'https://Stackoverflow.com/users/1341', 'pm_score': 4, 'selected': False, 'text': "<p>SQL Server (starting with 2000) will install performance counters (viewable from Performance Monitor or Perfmon).</p>\n\n<p>One of the counter categories (from a SQL Server 2005 install is:)\n - SQLServer:Databases</p>\n\n<p>With one instance for each database.  The counters available however do not provide a CPU % Utilization counter or something similar, although there are some rate counters, that you could use to get a good estimate of CPU.  Example would be, if you have 2 databases, and the rate measured is 20 transactions/sec on database A and 80 trans/sec on database B --- then you would know that A contributes roughly to 20% of the total CPU, and B contributes to other 80%.</p>\n\n<p>There are some flaws here, as that's assuming all the work being done is CPU bound, which of course with databases it's not.  But that would be a start I believe.</p>\n"}, {'answer_id': 488509, 'author': 'Brent Ozar', 'author_id': 26837, 'author_profile': 'https://Stackoverflow.com/users/26837', 'pm_score': 8, 'selected': True, 'text': '<p>Sort of.  Check this query out:</p>\n\n<pre><code>SELECT total_worker_time/execution_count AS AvgCPU  \n, total_worker_time AS TotalCPU\n, total_elapsed_time/execution_count AS AvgDuration  \n, total_elapsed_time AS TotalDuration  \n, (total_logical_reads+total_physical_reads)/execution_count AS AvgReads \n, (total_logical_reads+total_physical_reads) AS TotalReads\n, execution_count   \n, SUBSTRING(st.TEXT, (qs.statement_start_offset/2)+1  \n, ((CASE qs.statement_end_offset  WHEN -1 THEN datalength(st.TEXT)  \nELSE qs.statement_end_offset  \nEND - qs.statement_start_offset)/2) + 1) AS txt  \n, query_plan\nFROM sys.dm_exec_query_stats AS qs  \ncross apply sys.dm_exec_sql_text(qs.sql_handle) AS st  \ncross apply sys.dm_exec_query_plan (qs.plan_handle) AS qp \nORDER BY 1 DESC\n</code></pre>\n\n<p>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.</p>\n\n<p>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:</p>\n\n<pre><code>USE msdb\nDECLARE @StringToExecute VARCHAR(1000)\nSET @StringToExecute = \'SELECT * FROM AdventureWorks.dbo.ErrorLog\'\nEXEC @StringToExecute\n</code></pre>\n\n<p>The query would be executed in MSDB, but it would poll results from AdventureWorks.  Where should we assign the CPU consumption?</p>\n\n<p>It gets worse when you:</p>\n\n<ul>\n<li>Join between multiple databases</li>\n<li>Run a transaction in multiple databases, and the locking effort spans multiple databases</li>\n<li>Run SQL Agent jobs in MSDB that "work" in MSDB, but back up individual databases</li>\n</ul>\n\n<p>It goes on and on.  That\'s why it makes sense to performance tune at the query level instead of the database level.</p>\n\n<p>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.</p>\n\n<p>For more of those, check out the <a href="http://www.toadworld.com/platforms/sql-server/w/wiki/10040.transact-sql-code-library.aspx" rel="noreferrer">T-SQL repository at Toad World\'s SQL Server wiki (formerly at SQLServerPedia)</a>.</p>\n\n<p><em>Updated on 1/29 to include total numbers instead of just averages.</em></p>\n'}, {'answer_id': 509095, 'author': 'Ry Jones', 'author_id': 5453, 'author_profile': 'https://Stackoverflow.com/users/5453', 'pm_score': 1, 'selected': False, 'text': "<p>I think the answer to your question is no.</p>\n\n<p>The issue is that one activity on a machine can cause load on multiple databases. If I have a process that is reading from a config DB, logging to a logging DB, and moving transactions in and out of various DBs based on type, how do I partition the CPU usage?</p>\n\n<p>You could divide CPU utilization by the transaction load, but that is again a rough metric that may mislead you. How would you divide transaction log shipping from one DB to another, for instance? Is the CPU load in the reading or the writing?</p>\n\n<p>You're better off looking at the transaction rate for a machine and the CPU load it causes. You could also profile stored procedures and see if any of them are taking an inordinate amount of time; however, this won't get you the answer you want.</p>\n"}, {'answer_id': 509114, 'author': 'Lieven Keersmaekers', 'author_id': 52598, 'author_profile': 'https://Stackoverflow.com/users/52598', 'pm_score': 0, 'selected': False, 'text': '<p>Take a look at <a href="http://www.sqlsentry.com/" rel="nofollow noreferrer">SQL Sentry</a>. It does all you need and more.</p>\n\n<p>Regards,\nLieven</p>\n'}, {'answer_id': 509201, 'author': 'Guy', 'author_id': 993, 'author_profile': 'https://Stackoverflow.com/users/993', 'pm_score': 0, 'selected': False, 'text': '<p>Have you looked at SQL profiler?</p>\n\n<p>Take the standard "T-SQL" or "Stored Procedure" template, tweak the fields to group by the database ID (I think you have to used the number, you dont get the database name, but it\'s easy to find out using exec sp_databases to get the list)</p>\n\n<p>Run this for a while and you\'ll get the total CPU counts / Disk IO / Wait etc.  This can give you the proportion of CPU used by each database.</p>\n\n<p>If you monitor the PerfMon counter at the same time (log the data to a SQL database), and do the same for the SQL Profiler (log to database), you <em>may</em> be able to correlate the two together.</p>\n\n<p>Even so, it should give you enough of a clue as to which DB is worth looking at in more detail. Then, do the same again with just that database ID and look for the most expensive SQL / Stored Procedures.</p>\n'}, {'answer_id': 10094733, 'author': 'friism', 'author_id': 2942, 'author_profile': 'https://Stackoverflow.com/users/2942', 'pm_score': 3, 'selected': False, 'text': '<p>Here\'s a query that will show the actual database causing high load. It relies on the query cache which might get flushed frequently in low-memory scenarios (making the query less useful).</p>\n\n<pre><code>select dbs.name, cacheobjtype, total_cpu_time, total_execution_count from\n    (select top 10\n        sum(qs.total_worker_time) as total_cpu_time,  \n        sum(qs.execution_count) as total_execution_count, \n        count(*) as  number_of_statements,  \n        qs.plan_handle\n    from  \n        sys.dm_exec_query_stats qs \n    group by qs.plan_handle\n    order by sum(qs.total_worker_time) desc\n    ) a\ninner join \n(SELECT plan_handle, pvt.dbid, cacheobjtype\nFROM (\n    SELECT plan_handle, epa.attribute, epa.value, cacheobjtype\n    FROM sys.dm_exec_cached_plans \n        OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa\n     /* WHERE cacheobjtype = \'Compiled Plan\' AND objtype = \'adhoc\' */) AS ecpa \nPIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("dbid", "sql_handle")) AS pvt\n) b on a.plan_handle = b.plan_handle\ninner join sys.databases dbs on dbid = dbs.database_id\n</code></pre>\n'}, {'answer_id': 31783992, 'author': 'Anvesh', 'author_id': 2281778, 'author_profile': 'https://Stackoverflow.com/users/2281778', 'pm_score': 0, 'selected': False, 'text': '<p>please check this query:</p>\n\n<pre><code>SELECT \n    DB_NAME(st.dbid) AS DatabaseName\n    ,OBJECT_SCHEMA_NAME(st.objectid,dbid) AS SchemaName\n    ,cp.objtype AS ObjectType\n    ,OBJECT_NAME(st.objectid,dbid) AS Objects\n    ,MAX(cp.usecounts)AS Total_Execution_count\n    ,SUM(qs.total_worker_time) AS Total_CPU_Time\n    ,SUM(qs.total_worker_time) / (max(cp.usecounts) * 1.0) AS Avg_CPU_Time \nFROM sys.dm_exec_cached_plans cp \nINNER JOIN sys.dm_exec_query_stats qs \n    ON cp.plan_handle = qs.plan_handle\nCROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st\nWHERE DB_NAME(st.dbid) IS NOT NULL\nGROUP BY DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid),cp.objtype,OBJECT_NAME(objectid,st.dbid) \nORDER BY sum(qs.total_worker_time) desc\n</code></pre>\n'}, {'answer_id': 39525462, 'author': 'Eduard Okhvat', 'author_id': 6838246, 'author_profile': 'https://Stackoverflow.com/users/6838246', 'pm_score': 1, 'selected': False, 'text': '<p>With all said above in mind.<br>\nStarting with SQL Server 2012 (may be 2008 ?) , there is column <em>database_id</em> in <strong>sys.dm_exec_sessions</strong>.<br>\nIt gives us easy calculation of cpu for each database for <strong>currently connected</strong> sessions. If session have disconnected, then its results have gone.</p>\n\n<pre><code>select session_id, cpu_time, program_name, login_name, database_id \n  from sys.dm_exec_sessions \n where session_id &gt; 50;\n\nselect sum(cpu_time)/1000 as cpu_seconds, database_id \n from sys.dm_exec_sessions \ngroup by database_id\norder by cpu_seconds desc;\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/28952', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1690/']