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.

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

@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 LupoltJun 7 '12 at 16:26

1 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