Featured

Find Who Is Running What on SQL Server

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:

1. Session ID
2. User Name
3. From Device
4. From Application
5. Time Started
6. Statement that is running
7. Time on CPU
8. Memory Usage
9. Amount of Data that’s read by GB
10. Amount of Data that’s Written by GB
11. Logical Reads
12. Status of statement.
13. Percentage Done
14. Time Spent on process
15. Hours Remaining
16. Minutes Remaining

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s