SQL Server Top Running Queries by CPU seconds per minute

I have arrived in a new environment. I handed the real application cluster over in a stable condition and have since moved into a new Microsoft environment. In the past week, I've been writing a number of SQL scripts for SQLCMD. The scripts will speed up the process of learning and understanding the environment.

The script below was refurbished for the current environment - it is a TOPCPU script I wrote for another SQL environment around 4 years ago. It follows a similar form to the one I use for Oracle environments. Unfortunately, the code below is indented badly on the page - I will come back to cleaning it up.  The substring is for controlled output through SQLCMD. The script divides the duration of the users session by the amount of CPU seconds consumed and then orders the results by this new figure. It is not 100% perfect but it often sheds light on whats going on inside the system and any session that is out of control will usually spike to the top of the report. It has served me well over the years. It can also be adjusted to report on IO, though through my anecdotal experience, the TOPCPU often reports bad sessions to the top more often than TOPIO.

:setvar NOCOUNT ON

use $(database)
go

print ''
print '___________________________________________________________LOAD UP'

print ''
print '=============================================='
print 'TOP CPU CONSUMING SESSIONS ON : $(database)'
print '=============================================='
print ''
go

     select top 20
            substring(convert(varchar,sp.spid),0,4) sid,
            substring(sp.hostname,0,10) hostname,
            substring(object_name(st.objectid,sp.dbid),0,20) obj_name,            substring(convert(varchar(45),replace(replace(replace(st.text,char(13),''),char(10),''),char(9),'')),0,45) text,
            substring(sp.loginame,0,10) loginame,
            substring(db_name(sp.dbid),0,10) did,
            CASE WHEN sp.blocked > 0 THEN 'YES' ELSE 'NO' END blx,
            substring(sp.lastwaittype,0,15) lastwaittype,
            substring(sp.status,0,10) status,
            substring(sp.program_name,0,15) program_name,            substring(convert(varchar(15),replace(replace(sp.cmd,char(13),''),char(10),'')),0,15) cmd,
            substring(sp.nt_domain,0,10) nt_domain,
            substring(sp.nt_username,0,8) nt_username,
            convert(varchar(6),datediff(minute,sp.login_time,current_timestamp )) minutes,
            sp.login_time,
            convert(varchar(6),
                    sp.cpu/case 
                             when datediff(minute,sp.login_time,current_timestamp ) <= 0 then 1 
                             else datediff(minute,sp.login_time,current_timestamp ) end) cpu_pm
       from sys.sysprocesses sp (nolock)
cross apply sys.dm_exec_sql_text(sp.sql_handle) as st 
 order by sp.cpu/case 
                   when datediff(minute,sp.login_time,current_timestamp ) <= 0 then 1 
                   else datediff(minute,sp.login_time,current_timestamp ) end  desc
go

print ''
print '_________________________________________________________COMPLETED'
print ''

Results below - obfuscated owing to the sensitive nature of the business. The script was setup on a screen resolution at 1920x1080 - the output may be jumbled on something smaller.