查询谁在SQL Server上运行什么程序
Find Who Is Running What on SQL Server
发布-日期: 2014年5月7日 (评论)
Contents
中文
这是一个快速的脚本,可以让你立即了解谁在运行什么程序。 它会返回一些可能对你有用的东西。 其中包括以下内容:
- 会话ID
- 用户名
- 来自什么设备
- 来自什么应用
- 开始时间
- 正在运行的命令
- CPU上的时间
- 所用内存
- GB读取的数据量
- GB写入的数据量
- 逻辑读取
- 声明的状态
- 完成百分比
- 进程所花时间
- 所剩小时
- 所剩分钟
English
Here’s a quick script to give you an immediate view of who is running what. It will return a number of things that you might find helpful. Those include the following:
- Session ID
- User Name
- From Device
- From Application
- Time Started
- Statement that is running
- Time on CPU
- Memory Usage
- Amount of Data that’s read by GB
- Amount of Data that’s Written by GB
- Logical Reads
- Status of statement.
- Percentage Done
- Time Spent on process
- Hours Remaining
- Minutes Remaining
Logic
use master;
set nocount on
select
sdes.session_id
, 'user name' = sdes.login_name
, 'from device' = sdes.host_name
, 'from applcation' = sdes.program_name
, 'time started' = convert(char, start_time, 9) , 'statement' = command
, 'time on cpu' = sder.cpu_time
, 'database' = sder.db_name(database_id)
, 'memory usage' = sdes.memory_usage
, 'gb data read' = (sder.reads * 8 / 1024 / 1024) -- if under 1gb (0) zero will be listed.
, 'gb data written' = (sder.writes * 8 / 1024 / 1024)-- if under 1gb (0) zero will be listed.
, 'raw logical reads' = sder.logical_reads
, 'status' = sder.status
, 'percent done' = convert(numeric(6,2), sder.percent_complete) --may not be accurate for complex operatons.
, 'time spent' = convert(numeric(10,2), sder.total_elapsed_time /1000.0/60.0) --may not be accurate for complex operatons.
, 'hours remaining' = convert(numeric(10,2), sder.estimated_completion_time /1000.0/60.0/60.0) --may not be accurate for complex operatons.
, 'minutes remaining' = convert(numeric(10,2), sder.estimated_completion_time /1000.0/60.0) --may not be accurate for complex operatons.
, sdest.text
from
sys.dm_exec_requests sder cross apply sys.dm_exec_sql_text(sder.sql_handle) sdest join sys.dm_exec_sessions sdes on sder.session_id = sdes.session_id where
sder.session_id > 50
order by
sder.logical_reads desc
以下是结果的截图。 Here’s a quick screenshot of the results you’ll see.
Build-Info
Build Quality | Build History | ||||
---|---|---|---|---|---|
Author
- 李聪明的数据库 Lee's Clever Data
- Mike的数据库宝典 Mikes Database Collection
- 李聪明的数据库 "Lee Songming"