Resource Usage Statistics by Login

Question

Is there a way to get resource usage statistics by login in SQL Server?

Ideally, I'd like to have a version of sys.dm_exec_sessions that tracks usage for all sessions, rather than just the currently-connected ones. Then I could just run something like this:

select login_name, sum(reads) as reads, 
       sum(writes) as writes, 
       sum(cpu_time) as cpu_time, 
       count (login_name) as sessions 
from sys.dm_Exec_Sessions 
where login_name is not null 
group by login_name

Obviously this runs currently, but only shows statistics for currently-connected sessions.

Answer 1

If anyone's curious, I ended up doing this. It gives me something, but I'm not really convinced that the data is good enough to be better than no data.

-- Set up table
-- Put this somewhere other than TempDB if you want it to persist across restarts
USE TempDB
DROP TABLE Sessions
CREATE TABLE Sessions (last_request_end_time datetime, login_name varchar(100), reads int,  
writes int, cpu_time bigint)

-- Insert info about sessions from sys.dm_exec_sessions
INSERT INTO Sessions (last_request_end_time, login_name, reads, writes, cpu_time)
SELECT last_request_end_time, login_name, reads, writes, cpu_time 
FROM sys.dm_exec_sessions

/* 
In a loop, every 100ms insert data from sys.dm_exec_sessions where
last_request_end_time is greater than what has already been written
to the sessions table. 

This data will be inaccurate in that very short-lived
sessions might never get counted, and long-running sessions will get 
activity counted multiple times. As a result, you might get very different results
depending on what the WAITFOR delay is set to.

Optionally run this part as an Agent job.
*/

WHILE (2 > 1)
BEGIN
INSERT INTO Sessions (last_request_end_time, login_name, reads, writes, cpu_time)
SELECT last_request_end_time, login_name, reads, writes, cpu_time 
FROM sys.dm_exec_sessions 
WHERE last_request_end_time > (SELECT MAX(last_request_end_time) FROM Sessions)
WAITFOR DELAY '00:00:00.100';
END

-- After exiting the loop (stop Agent job, manually abort, etc) report on results
SELECT login_name, 
SUM(reads) AS reads, 
SUM(writes) AS writes, 
SUM(cpu_time) AS cpu_time, 
COUNT (login_name) AS session_count
FROM TempDB.dbo.Sessions 
GROUP BY login_name