view sql server work load processes running
As a DBA you might need to keep an eye on current work load of your SQL Server, here is a handy script which you can use to query sysprocesses table in master database and display various information, the sysprocesses table is presented as view in SQL Server 2005.
SELECT
a.name as [Database Name],
Connections = (SELECT COUNT(*)
FROM master..sysprocesses b
WHERE
a.dbid = b.dbid),
[Blocked Processes] = (SELECT COUNT(*)
FROM master..sysprocesses b
WHERE
a.dbid = b.dbid AND
blocked <> 0),
[Total Memory] = ISNULL((SELECT SUM(memusage)
FROM
master..sysprocesses b
WHERE
a.dbid = b.dbid),0),
[Total IO] = ISNULL((SELECT SUM(physical_io)
FROM
master..sysprocesses b
WHERE
a.dbid = b.dbid),0),
[Total CPU] = ISNULL((SELECT SUM(cpu)
FROM
master..sysprocesses b
WHERE
a.dbid = b.dbid),0),
[Total Waittime] = ISNULL((SELECT SUM(waittime)
FROM
master..sysprocesses b
WHERE
a.dbid = b.dbid),0),
[SELECTs] = (SELECT COUNT(*)
FROM master..sysprocesses b
WHERE
a.dbid = b.dbid AND
b.cmd LIKE '%SELECT%'),
[DELETEs] = (SELECT COUNT(*)
FROM master..sysprocesses b
WHERE
a.dbid = b.dbid AND
b.cmd LIKE '%DELETE%'),
[DBCC Commands] = ISNULL((SELECT COUNT(*)
FROM
master..sysprocesses b
WHERE
a.dbid = b.dbid and
b.cmd like '%DBCC%'),0),
[BCP Running] = ISNULL((SELECT COUNT(*)
FROM
master..sysprocesses b
WHERE
a.dbid = b.dbid and
b.cmd like '%BCP%'),0),
[Backups Running] = ISNULL((SELECT COUNT(*)
FROM
master..sysprocesses b
WHERE
a.dbid = b.dbid and
b.cmd LIKE '%BACKUP%'),0)
FROM master.dbo.sysdatabases a WITH (nolock)
WHERE
DatabasePropertyEx(a.name,'Status') = 'ONLINE'
ORDER BY [Database Name]
The above script will count and sum several different processes currently processed by SQL Server database engine. You can add remove items to fit your needs.