[sims] SQL Server CPUs at 100%

[JustinRoberts]

Our users are experiencing intermittent disconnects and crashes when using Sims at peak times.

 

The server is only running two SQL instances, hosting FMS and SIMS databases, a Sims file share and document storage is hosted on another server.

 

We have 1000 students, 100 staff users and 3000 external users connecting via a hosted SLG.

 

We have a reasonably fast SQL server:

 

Database size = 4Gb

Local Users = ~100

 

Processors = 2 x 2.66Ghz Xeon x5550 (Quad hyper threading cpu)

Memory = 16Gb

Operating System = 64 bit 2008r2

SQL System = 64 bit 2008

Network = 4 x gBit Network

 

Document Management Server is installed on another network server running only DMS services.

 

Please can anyone provide a more suitable response than Capita's Second Line advice, "If you Google SQL using 100% cpu you will find loads of forums etc and I believe a Microsoft KB." ?!

 

 

 

[localzuk]

Is SQL the full edition or express?

[JustinRoberts]

Is SQL the full edition or express?

 

Thanks for your reply, its the Full 2008 Edition

[Jay_Jay]

We had a slow down on our Sims server last week, we did not get users being disconnected but certain reports were crashing when run, I ended up detaching the Sims database and attaching again, then restarted the server and all was running fine, our Sims database is about 5Gb in size.

[localzuk]

I've found our SQL server hits 100% when running certain tasks - such as importing Common Transfer Files, and when people are using Assessment Manager en-masse.

 

What activities are your staff doing at these times?

 

Have you tried looking in SQL Management Studio at active processes etc... to see what's running?

[JustinRoberts]

I've found our SQL server hits 100% when running certain tasks - such as importing Common Transfer Files, and when people are using Assessment Manager en-masse.

 

What activities are your staff doing at these times?

 

Have you tried looking in SQL Management Studio at active processes etc... to see what's running?

 

Hi

 

Our CPU is maxing out so often it is difficult to identify any pattern at the moment. I have used the Management suite to see what is happening but I haven't seen anything untoward.

Is there a particular process fault / pattern I should be looking for?!

[NorthernSands]

@JustinRoberts: Have a look at my post at the end of this page http://www.edugeek.net/forums/mis-systems/69947-sims-server-hardware-large-school-2.html and the one on the following page in the same thread. The first post might help determine if the CPU is being used by the SIMS SQL process or something else. The second post gives a description of Hyper-Threading and why I think you should turn it off. If you have the Turbo functionality enabled, I've seen recommendations to turn that off as well for a SQL server.

 

Pete

[sjatkn]

I frequently see our SQL at close to 100% CPU. I have managed to find that it was how our database was initially installed. We have the OS, SIMS DB, temp DB and master DB all on the same drive spindles. Using perfmon it is possible to see massive IO loads on the drive. I plan to replace the server this summer budget permitting! Then I will install it in a more performance minded way. There is a reindex patch which helps a little.

[GeneralDreedle]

Justin, we are experiencing things slowing down, reports grinding to a halt, people getting thrown off etc. I reckon it started after the spring upgrade and rebooting the server every 2 weeks or so is all that fixes it for us.

 

Could a big fat memory leak or some dodgy processor-intensive loop have been introduced in the upgrade?

 

(We have 8Gb database, 4Gb RAM, poorer processor than yours, the rest similar)

[vikpaw]

Although you have relatively few users, maybe a combo of concurrent usage with multiple homepage panels would cripple the system. Although the homepage panels have segregated synch times, the one time they all pop at once is when you first log in. I know that at 8am my server cries like a teething baby, when the whole upper school logs in to do registers.

 

Even if you have another underlying issue, many people are recommending disabling homepage panels and if you already have it set up, you'll have to manually go round and remove it from staff accounts. Perhaps it's time to do an audit. Many of my users after an INSET session on the panels, ended up with lots that they didn't need, and didn't remove them.

[SHimmer45]

I've found our SQL server hits 100% when running certain tasks - such as importing Common Transfer Files, and when people are using Assessment Manager en-masse.

 

What activities are your staff doing at these times?

 

Have you tried looking in SQL Management Studio at active processes etc... to see what's running?

 

we have had this in the past so have asked that no major imports or exports are carried out during the school day (9am-3pm) to prevent disruption to registration etc.

[localzuk]

Hi

 

Our CPU is maxing out so often it is difficult to identify any pattern at the moment. I have used the Management suite to see what is happening but I haven't seen anything untoward.

Is there a particular process fault / pattern I should be looking for?!

 

If you look at the activity monitor for the SQL server in management suite, it will give you a list of connected users etc.. It will give you a rough idea of who is doing what, so you could ask those people what they're doing at that time to see if specific tasks are causing the problems.

[Butuz]

Wow - with the top spec of your server I wouldn't expect any bottlenecks whatsoever with only a 5gb database and 100 users.

 

I would definately be looking at the hosted SLG side of it because SIMS on its own should fly on that hardware for a school three times the size.

 

One thing worth checking on the server is that your sqlservr.exe process should be using about the same amount of mem usage as the size of your database so in your case it should be using around 5gb. If it's using much less than there may need to be a setting or two changed to allow it to use more of your 16gb ram.

 

Butuz

[zag]

We have virtually an identical setup including the SLG and cpu can peak at 90% quite often.

 

I guess it is the SLG that causes the problems.

 

Have you tried reducing the amount of RAM on the server? SQL server just eats anything it can get which might impact on cpu perfomance in some way.

 

This screenshot shows cpu at the height of the day during registration if thats any use.

 

[jinnantonnixx]

SQL server will use all the RAM it can get, but it will release it on demand (up to pre-defined limits). It's very much like 'balloon' memory. More here.

 

Anyway, if you're OK with SQL scripting, this little scriplet will show you a quick and dirty table of the CPU hogs on your system. It's a modification of the memory-hog script. Bear in mind it's a cumulative total - to do it properly, you'll need to dump the results to a table, run it again after a while then dump that to another table and compare the CPU total. Anyway, here it is:

 

use master
go

Select db.name as 'Database',
spid,
pr.status,
cmd,
nt_username,
pr.loginame,
hostname,
program_name,
cpu
from sysprocesses pr (nolock)
join sysdatabases db (nolock)
on pr.dbid = db.dbid
order by pr.cpu desc

[jinnantonnixx]

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.

[localzuk]

SQL server will use all the RAM it can get, but it will release it on demand (up to pre-defined limits). It's very much like 'balloon' memory. More here.

 

Anyway, if you're OK with SQL scripting, this little scriplet will show you a quick and dirty table of the CPU hogs on your system. It's a modification of the memory-hog script. Bear in mind it's a cumulative total - to do it properly, you'll need to dump the results to a table, run it again after a while then dump that to another table and compare the CPU total. Anyway, here it is:

 

use master
go

Select db.name as 'Database',
   spid,
   pr.status,
   cmd,
   nt_username,
   pr.loginame,
   hostname,
   program_name,
   cpu
from sysprocesses pr (nolock)
join sysdatabases db (nolock)
on pr.dbid = db.dbid
order by pr.cpu desc

 

Interesting command! It shows that FMS uses a heck of a lot more CPU usage than SIMS users do.

[jinnantonnixx]

Don't forget it's cumulative! If something's ticking over 24/7 it will all add up....

[localzuk]

Don't forget it's cumulative! If something's ticking over 24/7 it will all add up....

 

Indeed, but it still shows that FMS chews through a lot - we have one FMS user in today, and they've used about 20x the CPU compared with all the SIMS users logged in put together.

[jinnantonnixx]

Crikey. We don't use FMS but it seems like a busy character from what you say.

[jinnantonnixx]

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

 

 

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

[jinnantonnixx]

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.