Recently our SQL Server is getting bogged down by something. I was wondering what is the best way to check what could be causing the problem by querying the database. This is the best I've found so far:
SELECT
SPID = s.spid
, BlockingSPID = s.blocked
, DatabaseName = DB_NAME(s.dbid)
, ProgramName = s.program_name
, [Status] = s.[status]
, LoginName = s.loginame
, ObjectName = OBJECT_NAME(objectid, s.dbid)
, [Definition] = CAST([text] AS VARCHAR(MAX))
FROM sys.sysprocesses s
CROSS APPLY sys.dm_exec_sql_text (sql_handle)
WHERE
s.spid > 50
ORDER BY
DatabaseName
, loginName
