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.

  • 4
    I think you're going to need to output sys.dm_exec_sessions to a table to get historical info.
    – JNK
    Jun 7 '12 at 12:23
  • 2
    You could follow Tim Ford's technique (he did this for indexes but the concept is the same for tracking/trending any DMV really): mssqltips.com/sqlservertip/1749/… you could also look at Management Data Warehouse - which I'm pretty sure supports custom snapshots / collections for queries like the one you showed - if you're on SQL Server 2008 (you didn't specify version) but I don't think this feature is getting any more development. Jun 7 '12 at 12:36
  • 1
    @AaronBertrand Why isn't it getting more development? Because it's perfect or because they're deprecating it? Hmmm, I think MDW is extremely useful. Jun 7 '12 at 13:02
  • @Shark Primarily because the third party tools are way better, and they realized how much resources it takes to make that solution useful. And don't forget about System Center, which is their own quasi-competing product for uploading performance / configuration data "to the cloud." Jun 7 '12 at 14:22
  • Thanks, I decided to go with the approach of copying sys.dm_exec_sessions to a table. This is problematic in that I'm unsure how to do this without missing some data or counting some data twice. I'll post an answer with my ugly hack. Jun 7 '12 at 16:26

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
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)
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';

-- 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.