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.
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)