Is there an equivalent to 'mysqladmin processlist' for SQL Server?

Question

I've been trying to formulate a query to help myself identify resource-heavy queries/database/users using SQL Server and haven't gotten it down quite yet. I want to build a query that will do what 'mysqladmin processlist' would do for MySQL.

I've referred to this related question but haven't gotten what I really need from it. I'm using sp_who, sp_who2 and queries like this:

select master.dbo.sysprocesses.loginame, 
count(master.dbo.sysprocesses.loginame)
from master.dbo.sysprocesses
group by master.dbo.sysprocesses.loginame

The problem always is that one of these tools doesn't give me everything I need. My goal would be to have a query that would be of this format:

LOGIN, DATABASE, QUERY, CPU, MEM, etc.

If anyone knows how to do this, I would appreciate the help. If anyone has any SQL Server DBA cheatsheets that would be great, too.

Answer 1

If you bust out sp_who2, you could extract the fields that you're interested in:

select    
  spid
 ,status 
 ,hostname
 ,program_name
 ,cmd
 ,cpu
 ,physical_io
 ,blocked
 ,dbid
 ,convert(sysname, rtrim(loginame))
        as loginname

      from sys.sysprocesses with (nolock)
      order by cpu desc

Answer 2

Does it have to be done with a sproc call? SQL Server Management Studio (the link is for the express edition, but a full install of SQL Server already has it) has an "Activity Monitor" feature which lists exactly what you want.

Other than that,

EXECUTE sp_who2

Gives you exactly what you asked for: Login, DBName, Command, CPUTime, DiskIO, are all there...

If you want the exact command that a SPID is executing, you can use the

DBCC INPUTBUFFER(spid)

command (sp_who2 just tells you whether it's a DELETE, SELECT, etc)