主页 / dba / 问题 / 18936
Accepted
James Lupolt
Asked: 2012-06-08 02:04:42 +0800 CST

登录资源使用统计

  • 772

有没有办法通过登录 SQL Server 来获取资源使用情况统计信息？

理想情况下，我希望有一个版本sys.dm_exec_sessions可以跟踪所有会话的使用情况，而不仅仅是当前连接的会话。然后我可以运行这样的东西：

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

显然，这是当前运行的，但仅显示当前连接会话的统计信息。

sql-server dmv

1 个回答

  1. Best Answer

    如果有人好奇，我最终这样做了。它给了我一些东西，但我并不真正相信数据足够好，总比没有数据好。

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

相关问题