title: CPU Time spent for each sql server session using DMVs?
body: <p>Is there a way to get the CPU Time for each session or for every open connection in SQL Server 2005 using DMVs? I have kind of tried various combinations of  sys.dm_exec_query_stats, sys.dm_exec_connections, sys.dm_exec_sessions. Am i missing some other obvious way to get this same information? My environment is a SQL Server 2005 with about 100 databases where one database takes about 40% CPU (found using query below) and I am trying to figure out whats (number of connections, what queries, etc..) is taking it upto 40%.</p>
  
  <p>This script gives CPU time for each Database. I just need to break it down to Session OR at least at the Connection level.</p>
  
  <pre><code>WITH DB_CPU_Stats
  AS
  (SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], 
    SUM(total_worker_time) AS [CPU_Time_Ms]
   FROM sys.dm_exec_query_stats AS qs
   CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] 
                FROM sys.dm_exec_plan_attributes(qs.plan_handle)
                WHERE attribute = N'dbid') AS F_DB
   GROUP BY DatabaseID)
  SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
         DatabaseName, [CPU_Time_Ms], 
         CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) 
         OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
  FROM DB_CPU_Stats
  WHERE DatabaseID &gt; 4 -- system databases
  AND DatabaseID &lt;&gt; 32767 -- ResourceDB
  ORDER BY row_num
  </code></pre>
  
  <p>Appreciate if you could provide a working sample. </p>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>Modified the query above - Is this good enough? Just want someone to validate...</p>
  
  <pre><code>WITH DB_CPU_Stats
  AS
  (SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], sql_handle,
    SUM(total_worker_time) AS [CPU_Time_Ms]
   FROM sys.dm_exec_query_stats AS qs
   CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] 
                FROM sys.dm_exec_plan_attributes(qs.plan_handle)
                WHERE attribute = N'dbid') AS F_DB
   GROUP BY DatabaseID, sql_handle
  )
  
  
  SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
         DatabaseName, [CPU_Time_Ms],  text, count(1) as Connections,
         CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) 
         OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
  FROM DB_CPU_Stats
  cross apply sys.dm_exec_sql_text(sql_handle) C
  join sys.dm_exec_connections CON on CON.most_recent_sql_handle = sql_handle
  WHERE DatabaseID &gt; 4 -- system databases
  AND DatabaseID &lt;&gt; 32767 -- ResourceDB
  GROUP BY CPU_Time_Ms, DatabaseName, text
  ORDER BY row_num
  </code></pre>
  
  <p>Is the most_recent_handle of dm_exec_connections, supposed to map to sql_handle of dm_exec_query_text?</p>