DBAResourceTricks

/*What is running?*/
SELECT
    program_name AS 'Program Name'
  ,count(*) AS 'Running Processes'
 FROM master..sysprocesses
 WHERE 
	ecid=0
 GROUP BY program_name 

 /*CPU processing*/
 SELECT 
    spid 
	,program_name AS 'Program Name'
	,datediff(second,login_time, getdate())  AS 'Connected Seconds'
 FROM master..sysprocesses
 WHERE spid > 50 
 ORDER BY datediff(second,login_time, getdate()) DESC

 /*Top 10 server processes*/
 SELECT TOP 10
    spid
	,blocked
	,convert(varchar(50),db_name(dbid)) AS 'DB Name'
	,cpu AS 'CPU'
	,datediff(second,login_time, getdate()) AS 'Seconds'
	,convert(varchar(16), hostname) AS 'Host'
	,convert(varchar(50), program_name) AS 'Program'
	,convert(varchar(20), loginame) AS 'Login'
 FROM master..sysprocesses
 WHERE 
	 datediff(second,login_time, getdate()) > 0
	 and spid > 50
 ORDER BY 6 DESC

 /*Resource Contraints*/
SELECT
    convert(varchar(50), program_name) AS 'Program Name'
	,count(*) AS 'Client Count'
	,sum(cpu) AS 'CPU'
	,sum(datediff(second, login_time, getdate())) AS 'Seconds'
 FROM master..sysprocesses
 WHERE spid > 50
 GROUP BY 
	convert(varchar(50), program_name)