Currently Running SQL

Some queries to get you started on determining what is currently running on your SQL Server.

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

Show databases and read and write stats

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time,
req.writes,
req.reads,
db.name
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
JOIN sys.databases db on req.database_id = db.database_id

We call this one sp_who4

SELECT 
  er.Session_ID
  ,er.Blocking_session_id Blocked_By
  ,er.Start_Time
  ,er.Total_Elapsed_Time / 1000 Tot_Secs
  ,er.Status
  ,er.Command
  ,db_name(er.database_id) [Database] 
  ,user_name(er.User_ID) [User]
  ,es.Login_Name
  ,er.Wait_Type
  ,er.Wait_Time / 1000 Wait_Sec
  ,er.Last_Wait_Type
  ,er.Wait_Resource
  ,er.Open_Transaction_Count Open_Trans
  ,er.Open_Resultset_Count
  ,er.Percent_Complete
  ,er.CPU_Time / 1000 CPU_Secs
  ,er.Reads
  ,er.Row_Count Rows_Rtrnd
  ,es.Program_Name
  ,es.Host_name
  ,est.text
  ,eqp.query_plan
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) est
OUTER APPLY sys.dm_exec_query_plan(er.plan_handle) eqp
JOIN sys.dm_exec_sessions es
  ON er.Session_id = es.session_id
WHERE er.Session_ID <> @@SPID
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