Sql-server – Resource Usage Statistics by Login

dmvsql server

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.

Best Answer

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

Related Solutions

Sql-server – SQL Server 2008 query planner failing after index drop & recreate

Well, the issue is now resolved:

While it would seem logical to use filtered indexes (NOT NULL), to reduce database size and as so many sources on the web say, increase performance, the reality it seems is something else entirely.

In layman's terms, SQL Server query planner resolves even your basic inner joins without making any assumptions as to the content of the columns. Even though NULL values do not form a join, they must be included in the column index in order for query planner to use it, unless otherwise specified with predicates such as WHERE joinCol_ID IS NOT NULL. Basically, SQL Server does not use filtered indexes for joins at all, unless the queries themselves are modified to account for the filter value. Instead, it will create new statistics on these columns and / or use a clustered index scan or other indexes including the column, whichever it deems most effective. Using filtered indexes on foreign keys is therefore an absolutely horrid idea.

We still have no idea how months worth of testing this in multiple other environments never produced the same results outside of this one, single DB, but this is the way it's supposed to work. Apparently something that as far as we know is not related to cache, statistics or configurations, caused the production DB to behave differently and correctly detect and use the filtered indexes, while all of the testing environments simply used the old indexes (seeing as the indexes were dropped and recreated with the same name, this seems a valid theory even if there is no real proof).

So the lesson of the story: The web is filled with examples of how underused filtered indexes are, how awesome they can be. But this serious downside never popped up except as a nagging thought in the back of my head saying "if these are so great, then why aren't NULL values filtered out of indexes by default, since they only take up space and only serve a purpose in special circumstances"? Well, now I know why. :)

SQL Server – How to Cache Statistic Data

How about an indexed view?

CREATE VIEW dbo.LoginsByDate
WITH SCHEMABINDING
AS
  SELECT 
    UserID, 
    LoginDate = CONVERT(DATE, LoginDate), 
    LoginCount = COUNT_BIG(*)
  FROM dbo.LoginTable
  GROUP BY UserID, CONVERT(DATE, LoginDate);
GO
CREATE UNIQUE CLUSTERED INDEX IX_LoginsByDate
  ON dbo.LoginsByDate(UserID, LoginDate);
GO

What this does is pre-aggregate the login data for you (you pay the cost at write time), and flattens it into one row per UserID per LoginDate. Depending on how many times a user logs in in a given day, and how often you run these reports, this can be substantially more efficient than calculating those at query time (and much less hassle than running your own nightly report that aggregates the data and stores it somewhere).

Now, to get the total login events by day:

SELECT LoginDate, SUM(LoginCount)
  FROM dbo.LoginsByDate
  GROUP BY LoginDate;

To get the distinct logins by day:

SELECT LoginDate, COUNT(UserID)
  FROM dbo.LoginsByDate
  GROUP BY LoginDate;

Of course if you're looking for individual user activity, you can do that too, and you can easily aggregate the data to get whatever "total unique" really means to you. If UserID = 1 logs in today 4 times and tomorrow twice, is that 1 total unique (unique per entire time frame) or 2 total uniques (unique per day)?

If the former:

SELECT COUNT(DISTINCT UserID)
  FROM dbo.LoginsByDate
  WHERE LoginDate >= 'yyyymmdd'
    AND LoginDate < 'yyyymmdd';

If the latter:

SELECT SUM(c) FROM
(
  SELECT LoginDate, c = COUNT(UserID)
  FROM dbo.LoginsByDate
  WHERE LoginDate >= 'yyyymmdd' 
    AND LoginDate < 'yyyymmdd'
  GROUP BY LoginDate
) AS x;

Best Answer

Related Solutions

Sql-server – SQL Server 2008 query planner failing after index drop & recreate

SQL Server – How to Cache Statistic Data

Related Question