Which instance of a process is using lots of CPU on the database server

Running this script will tell you which instance of a process is using lots of CPU on the database server. You can use the loginame or hostname columns to identify the person.

 

SELECT TOP 25

       spid

       ,blocked

       ,convert(varchar(10),db_name(dbid)) as DBName

       ,cpu

       ,datediff(second,login_time, getdate()) as Secs

       ,convert(float, cpu / datediff(second,login_time, getdate())) as PScore

       ,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 pscore desc 

Check how many CPUs visible offline & online

--how many CPUs is SQL Server actually using

select cpu_id,scheduler_id,is_online,status from sys.dm_os_schedulers where status = 'VISIBLE ONLINE'

--how many CPUs are not being used

select cpu_id,scheduler_id,is_online,status from sys.dm_os_schedulers where status = 'VISIBLE OFFLINE'

--how many CPUs an instance can see

select cpu_count from sys.dm_os_sys_info