Início / dba / Perguntas / 18936
Accepted
James Lupolt
Asked: 2012-06-08 02:04:42 +0800 CST

Estatísticas de uso de recursos por login

  • 772

Existe uma maneira de obter estatísticas de uso de recursos por login no SQL Server?

Idealmente, gostaria de ter uma versão sys.dm_exec_sessionsque rastreie o uso de todas as sessões, em vez de apenas as conectadas no momento. Então eu poderia apenas executar algo assim:

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

Obviamente, isso é executado atualmente, mas mostra apenas as estatísticas das sessões conectadas no momento.

sql-server dmv

1 respostas

  1. Best Answer

    Se alguém estiver curioso, acabei fazendo isso. Isso me dá alguma coisa, mas não estou realmente convencido de que os dados sejam bons o suficiente para serem melhores do que nenhum dado.

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

relate perguntas