SQL Server internals, what is executing on the server now?

I have used Microsoft SQL Server a lot and done some work on data migrations. That means moveing large or huge amount of data between databases and between different schemas. For these kinds of tasks, I normaly prefer writing scripts to do the job and executing the scripts from batch scripts. While executing migration scripts from command line keeping track of what is running now and some information about the status is good to know. Otherwise debugging and improving the scripts are very hard.

SELECT      r.start_time [Start Time],session_ID [SPID],
            DB_NAME(database_id) [Database],
            SUBSTRING(t.text,(r.statement_start_offset/2)+1,
            CASE WHEN statement_end_offset=-1 OR statement_end_offset=0
            THEN (DATALENGTH(t.Text)-r.statement_start_offset/2)+1
            ELSE (r.statement_end_offset-r.statement_start_offset)/2+1
            END) [Executing SQL],
            Status,command,wait_type,wait_time,wait_resource,
            last_wait_type
FROM        sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE       session_id != @@SPID -- don't show this query
AND         session_id > 50 -- don't show system queries
ORDER BY    r.start_time

The script above is what I am using to see what is executing on a M-SQL server now. The scripts use the system tables to get the command executing and also using the session id field to not list itself. System queries are also nice to keep out to not have all the noise that most of the time is not needed to see how the execution is going. If there are more users executing scripts on the server and filtering to a spesific login name may be useful. Then the dm_exec_sessions can be joined into the query above.

select session_id, login_name from sys.dm_exec_sessions

Teis Lindemark

Passionate software developer and beer brewer

Bergen, Norway https://teilin.net