I have needed to monitor the SQL servers a little more than usual. I am mostly concerned about memory and CPU usage. By properly setting our program names in the SQL connection string, I am able to find the offending applications a little easier. There are times, that I set the application and routine in the connection string when the offending process is not so obvious.
Here is my script that I use to view the offending SQL processes
SQL Query using SysProcess
use master
declare @memlimit as int, @cpulimit as int
set @memlimit = 100 — Memory Filter Limit
set @cpulimit = 200 — CPU Filter Limit
select spid, login_time, last_batch, hostname, program_name, memusage,cpu
from sysprocesses
where ( memusage > @memlimit or cpu > @cpulimit )
Once I have a list of offending processes, I seek and fix the code has the issue. If the process is a closed application like the BlackBerry® Mobile Data Server Connection Service, I check for updates or in rare cases set up a scheduled task to restart the service.