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

[andydis]

 

Hey People,

 

 

Would anyone mind comparing SQL 2008 perfmon?

 

running sims

[matt40k]

Is that normal load? Looks like someone needs faster CPUs (assuming I'm not being stupid... again)

[andydis]

hi MAtt, this is taken at busist point of day, CPU = xeon 2.ghz ( four cores) x3220.

Just wondering what else people have...

[matt40k]

In Suffolk alot of schools have (or had) Dell 1430, fitted with Xeon E5310 - E5335, so it'll give your server a run for your money and they're about 5 yrs old!!

 

Most highs have replaced kit with DL380, dual quad xeon and 12gb+ ram last year - must have been a offer on

[jinnantonnixx]

Try SQL Acitivity Monitor instead of perfmon; it will give you a better view of your SQL operations.

 

 

Launch SQL Management console, then right-click your server in Object Explorer and choose Activity Monitor.

 

High CPU usage can be difficult to troubleshoot, as many things can cause the problem. For instance, if the server is paging a lot, it will cause high CPU load. This can be caused by insufficient memory, or unusable memory from a leak. Or even an intensive process!

Pay attention to the Recent Expensive Queries pane, open this up and see what's chewing up your server.

 

I wrote a little script to look at the highest CPU users over a period of time (I've set two minutes, but change it if you like)

http://www.edugeek.net/forums/mis-systems/78288-sql-server-cpus-100-a-2.html#post698678

Edited July 13, 2011 by jinnantonnixx

[andydis]

thanks jinnantonnixx

Does This mean anything to anyone?

I;m guessing it just havsn't got eh CPU required but i maybe wrong

[jinnantonnixx]

That third row looks like 101 million reads per second! What's going on there?

 

Time to get detective-y....

 

If you right click a nasty query > select 'Edit Query Text'. This will open a new page with the expensive query in all its glory.

 

Next, we need to find out what called it and where it came from.

 

This is a bit tricky, so let's see what we can do...

 

Right, this bit of code will locate a stored procedure from a bit of text you provide. This is quite handy for things like debugging using Profiler.

 

So, copy a significant portion of the query text, and paste it into this query (I highlighted it blue)

 

-- change this
use [your_SIMS_database_name]

declare @Search as nvarchar(1000)


--paste your query sample here don't forget to enclose the whole deal with a single quote.
select @Search='[color="#0000CD"]	< paste your query sample here > 
< leave all spaces and line breaks alone >
< its fine for a query to be on several lines >[/color]'



SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%' + @Search + '%'
GO

 

 

Like I said, it's a bit hit and miss but it should show you what's causing your bottlenecks.

Edited July 13, 2011 by jinnantonnixx

[jinnantonnixx]

On second thoughts, it's probably more straightforward for you to run the query I wrote here: http://www.edugeek.net/forums/mis-systems/78288-sql-server-cpus-100-a-2.html#post698678

 

This will prob give you more useful info, such as user and workstation, from which you can narrow down the likely suspect.

[andydis]

ok have the results, shall i post them or what am i loking for?

[Butuz]

Yeh that reads per second looks wrong.

 

4 cores should be ok for most installations 8 cores would be ideal if your a very heavy user and large school. I am only using 4 3.2Ghz cores on mine and although it does hit 100 fairly often during the day it is only for spit seconds each time and it far from sustained like yours. We have up to 100 concurrent users and run SIMS / FMS / SIMSDISCOVER / Learning Gateway and Partnership exchange so our SIMS server is given quite the beating.

 

Ill post a screeny of mine from registration tomorrow.

 

Butuz

[andydis]

thanks butuz , sounds like a similar setup, would you mind comparing SIMS sizes too? my sims.ldf is 14GB and sims.mdf is 2.7 GB , im sure somethings not right somewhere. going to run that script tomorrow at a busy time to see who is doing what

[jinnantonnixx]

Your LDF is too big. This normally means that either your backup isn't working, or your backup has had problems in the past and your log file hasn't been shunk down to size.

 

Surprisingly, a gynormous log file doesn't put too much strain on the server; it's a file that's written sequentially and doesn't get loaded into memory. Not all of it, anyway!

 

You should make checking your backups a priority, then take a look at the log file.

 

You can see the status of the log file with this query.

 

use [your_SIMS_database]
go

SELECT	Name, Filename,

CONVERT(Decimal(15,2),ROUND(a.Size/128.000,2)) [Currently Allocated Space (MB)],

CONVERT(Decimal(15,2),ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2)) AS [space Used (MB)],

CONVERT(Decimal(15,2),ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)) AS [Available Space (MB)]

FROM dbo.sysfiles a (NOLOCK)

 

Look at the results and see if the space used by the log file is close to the space allocated.

Edited July 13, 2011 by jinnantonnixx

[Butuz]

 

Here's mine over registration period. This isn't as busy as usual here as a fair few kids are out but one thing to note is Logical reads per second never went over 20,000 on any query.

 

Butuz

[Butuz]

And datafile usage

 

 

Butuz

[andydis]

Ok now I know theres a problem looking at the comparison :-(

 

 

Heres my top 10 running the script

 

http://www.helpdesksupport.co.uk/sims.csv

 

 

Server is still very HIGH CPU useage.

 

 

regarding the backup. it has been successful.

However the way i think it is done is :-

 

When you right click the sims database ALL tasks > backup > full backup. I think this is automated daily and then this goes to tape.

 

The LEA is reasponsible for this server and so i am going to ring them to see if i can esculate this as its getting abit beyond me but any extra input is appreciated,.

[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

[pubgrub277]

 

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