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.



 5/3/2008


Add Comments
Name: *
Email: *
URL:
Comments: *
 


Advertisements

MySQL HOSTING
MySQL hosting provider, clustering and replication supported. DBA Services Included.

SQL SERVER HOSTING
Reliable, powerful SQL Server 2008 hosting with ASP.NET on Windows 2008 Servers.

SQL Server DBA

I am a SQL Server DBA with almost 9 years of experience in database technologies.

 I am again in process of redesigning this website using ASP.NET 2.0 and AJAX. The main purpose of new SQL DBA website is to offer more features and make it easy for me to update contents on regular basis.