[SIMS / SQL2008] Show us your Perfmon!

Attachment 10740

Hey People,


Would anyone mind comparing SQL 2008 perfmon?

running sims

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

hi MAtt, this is taken at busist point of day, CPU = xeon 2.ghz ( four cores) x3220.
Just wondering what else people have...

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 ;)

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-sy...tml#post698678

thanks jinnantonnixx
Does This mean anything to anyone?
I;m guessing it just havsn't got eh CPU required but i maybe wrong Attachment 10755

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)

Code:

---

-- 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='        < paste your query sample here >
        < leave all spaces and line breaks alone >
        < its fine for a query to be on several lines >'



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.

On second thoughts, it's probably more straightforward for you to run the query I wrote here: http://www.edugeek.net/forums/mis-sy...tml#post698678

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

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

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

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

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.

Code:

---

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.

Attachment 10768

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

And datafile usage

Attachment 10769

Butuz

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,.