Sql-server – Database – Resource usage statistics

Ever is a big word, but, in general, no, I wouldn't put a varchar(2000) field into an INCLUDE.

And yeah, the way that data is stored at the page level can seriously impact performance of the index, depending on how the index is used.

The thing is, the more rows of data you can cram into a page, the fewer pages have to get accessed, the faster your system is, for the most part. Adding a really large column means less information stored on a page, so, in the event of range seeks or scans, more pages have to be read to retreive the data, seriously slowing stuff down.

To know for sure if this is an issue on your query, or on your system, you'd have to monitor the reads, especially the number of pages that the query uses.

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