[SIMS] [SIMS / SQL2008] Show us your Perfmon!

[andydis]

LEA are passing this onto capita support, so fingers crossed will get a phonecall soon.

[andydis]

you are going to think im mad but i have been watching SQL actmon all day, and running the script to show who's taking upall the CPU.
I have been ringing users straight away and ask what they where doing in SIMS at the time and they all say the same thing :- "sims is just open and i havn't touched it for a while" ... so is it possible there some kind of sleep function in sims making my CPU max out?
see pic for latest result
sims waiting.jpg

[pubgrub277]

TaskMGr.png

We've notice a small improvement since we replaced our old dell box with a new machine. the 12gig of Ram has certainly helped.

[jinnantonnixx]

I had a look at why some columns show negative CPU results and it's foxing me.
I've modified the code, as in the first version I made a bit of an error by joining on the SPID. The SPID can contain a different process when it dumps the running command so this was a mistake.
Also, I'm ignoring the system databases (so it will filter out the CPU hog query too).

Now, the code joins only on the dataabse name, the login name and the machine host name. This should, in theory, give us accurate results, but I'm still getting some weird results with negative figures. I thought it was an outer join giving us a new result in table B which wasn't in table A, thus giving us a negative value, but this can't be the case now.

Just to check, I did a bit of debugging and found that table B (the later one) had a lower value in the CPU ticks column! Very strange.

So, I still think it's useful, but take the results with a pinch of salt.

-- SQL script to find CPU hogs in SQL server
-- version 2, but still shows negative values :(
-- somethings funny with the CPU tick column. oh well.

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
where name not in ('master', 'msdb', 'model')
order by pr1.spid

-- wait for a period of time
print 'Waiting for a while...'
waitfor delay '00:05: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
where name not in ('master', 'msdb', 'model')
order by pr2.spid

-- debug - check the two tables, later one sometimes shows lower CPU values - weird :(
-- select * from ##cpuhog1
-- select * from ##cpuhog2


-- 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
		 join ##cpuhog2 t2
		on 
			t1.DBName = t2.DBName
			and t1.loginame = t2.loginame
			and t1.hostname = t2.hostname
		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

[jinnantonnixx]

Andy, is your server fully patched and up-to-date?

Things like this can be fixed by updates.

This isn't applicable, but it's an example of the problems you get if your system isn't patched.
You experience low CPU spikes at set intervals even when there is no user activity in SQL Server 2005

[andydis]

jinnan, its sql 2008, however there is no way its fully patched becuase i tried to run SQL 2008 best practice analyser on it, then it asked for best practice framework as a prerequitsie, then it asked for powershell , the server is *supposed* to be fully managed by the LEA . So i do not dare do much with it TBH. ( last week i got such a telling off for rebooting the cisco pix , thats fully managed by the LEA) . Awkward.

[jinnantonnixx]

I'd ask them to run in the updates on the server. There's a fair chance this will improve things.

Microsoft SQL Server Release Services - Site Home - MSDN Blogs