[SIMS] SQL2008 VERY SLOW FOR ALL USERS

[Darylrese]

Hi,

When running a report in PARS it seems to throw up lots of SPID's being blocked. I managed to capture one. The biggest problem seems to be if more than 1 person is running a report at the same time....which happens quite often in our school

[Darylrese]

They did seem to flash up quite quick. I'll wait untill someone says its locked out completely again and then run the report.

Cheers

[DarrenShan]

I'm also having major problems with our SQL 2008 SIMS server being very slow, in our case the SQLSERVR.EXE process is almost always running at 100% CPU usage.

I've followed this thread with interest and performed the tests and blocking reports as already discussed and cannot find any reason.

Running SQL 10.0.1600.22 Standard with SIMS and FMS databases.

Any advice?

[teejay]

I'm also having major problems with our SQL 2008 SIMS server being very slow, in our case the SQLSERVR.EXE process is almost always running at 100% CPU usage.

I've followed this thread with interest and performed the tests and blocking reports as already discussed and cannot find any reason.

Running SQL 10.0.1600.22 Standard with SIMS and FMS databases.

Any advice?

Either the server isn't up to spec for running the required loading, or your database needs reindexing/checking (see my post about patch 6635 earlier in thread).

[DarrenShan]

teejay - I will see if I can hold of the 6635 patch and let you know what I find.

[DarrenShan]

jinnantonnix, I've ran the Activity Monitor and it is showing the following:

Processor: 98%
Waiting Tasks: 5 (max 22)
Database I/O: 0.1 MB/sec (max 2.0)
Batch Req: 5 (Max30)

Looking at the processes it shows me the processes but how can I tell which one(s) are hammering it?

BTW Thanks to everyone's swift replies

[Darylrese]

Hi,

It happened again just now and it just so happens i was looking at the management studio activity monitor and the processes screen filled up with blocking errors. After about 5mins they cleared and users could access SIMs again.

Here is a report, again only 1 entry for some reason even though there were about 10 different SPID's blocking each other.

[Darylrese]

The routine that's causing the blocking is sta_pix_save_historical_membership_block.

Thanks for finding out. I'll give this information to our local council and see if they know what could be causing it.

[Darylrese]

they have now sent us a patch, Patch_14265 which apparently does the same as the other patches people have suggested on here. Will let you know how it goes once we have applied it.

[Darylrese]

Hi,

Just to let you all know we applied the patch this morning and the problems still persist. Taking a register in PARs and setting merits / sanctions seems to be ok but when running any kind of report in SIMS or PARS crashes the system for all users logged on at the time.

[glennda]

We have just moved our sims to a new server with 10gb ram so the whole database can sit in the ram - seems to have made it quicker but still not as much as it should

[glennda]

An SQL server must have good disk performance - disk is crucial to its operation. Every logged operation is written to the log file then the data file, regardless or how much memory you give it so you should never overlook the disk system.

We havn't its still got the same disks hence why i didn't mention it as it wouldn't have made a difference - the drives sit on the san with a fibre connection to them

[glennda]

Its the same as that but we don't use VMware as use KVM which runs in the linux kernel - which in my opinion seems to be quicker then ESX/Vmware server.

The second and third disks for data and logs all are image files which run on the San.

[powdarrmonkey]

Its the same as that but we don't use VMware as use KVM which runs in the linux kernel - which in my opinion seems to be quicker then ESX/Vmware server.

This is fact, not opinion KVM exists for very good reasons.

[glennda]

This is fact, not opinion KVM exists for very good reasons.

I was trying not to start the KVM vs ESX argument!!