Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
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. –  Aaron Bertrand 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. –  Thomas Stringer 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." –  Aaron Bertrand 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. –  James Jun 7 '12 at 16:26
add comment

1 Answer

up vote 1 down vote accepted

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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