[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 SIMS Server Hardware for a large School 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.

simssqlserver.jpg

[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