Oh, when you say 100% CPU use is that all CPUs? It's normal to see a spike on a single processor graph, as sometimes a long-running, intensive process just can't be threaded.
Don't forget it's cumulative! If something's ticking over 24/7 it will all add up....
Crikey. We don't use FMS but it seems like a busy character from what you say.
I've written some more code which gives a snapshot of CPU-usage, ordered by user.
It's set to snapshot over 2 minutes (change the value in the 'waitfor' line).
It sort of works, but there's some bug which shows negative values at the end of the list. Don't know why, but I'm going home now so no time to fix it.
Educuppa to whoever can find the bug....
Code:-- SQL script to find CPU hogs in SQL server use master go -- drop temp tables if they exist IF OBJECT_ID('tempdb..##cpuhog1','U') IS NOT NULL DROP TABLE ##cpuhog1 IF OBJECT_ID('tempdb..##cpuhog2','U') IS NOT NULL DROP TABLE ##cpuhog2 -- build first table Select db1.name as 'DBName', spid, pr1.status, cmd, nt_username, pr1.loginame, hostname, program_name, cpu into ##cpuhog1 from sysprocesses pr1 (nolock) join sysdatabases db1 (nolock) on pr1.dbid = db1.dbid order by pr1.cpu desc -- wait for a period of time print 'Waiting for a while...' waitfor delay '00:02:00' -- 'hh:mm:ss', you choose the delay -- build second table Select db2.name as 'DBName', spid, pr2.status, cmd, nt_username, pr2.loginame, hostname, program_name, cpu into ##cpuhog2 from sysprocesses pr2 (nolock) join sysdatabases db2 (nolock) on pr2.dbid = db2.dbid order by pr2.cpu desc --select t1.DBName from ##cpuhog1 t1 -- two tables made, calulate the differences between them and order by CPU select t1.DBName as 'Database Name', t1.spid, t1.status, t1.cmd as 'Command', t1.nt_username as 'Windows Username', t1.loginame as 'SQL Login', t1.hostname as 'Machine Name', t1.program_name, t2.cpu - t1.cpu as CPUDiff from ##cpuhog1 t1 right outer join ##cpuhog2 t2 on t1.spid = t2.spid order by CPUDiff desc -- drop temp tables if they exist IF OBJECT_ID('tempdb..##cpuhog1','U') IS NOT NULL DROP TABLE ##cpuhog1 IF OBJECT_ID('tempdb..##cpuhog2','U') IS NOT NULL DROP TABLE ##cpuhog2
Might be a fault with the join if some process die during the snapshot - hence negative values. It should still give the correct results, though.
There are currently 1 users browsing this thread. (0 members and 1 guests)