[sims] SQL2008 VERY SLOW FOR ALL USERS

[Darylrese]

Good Afternoon,

 

Our school are having major problems since upgrading from SQL2005 to SQL2008. Users are reporting both SIMS and PARS are running VERY slowly. When running a simple report it can take upto 20mins or crashes out completely.

 

The sims.ini and connect.ini are correct, the MIS Server is not under stain according to the performance manager in both windows and SQL studio 2008.

 

The problem seems to be fairly random. Sometimes it runs ok and other times grinds to a halt. Users report even the quicker times are slower than they used to be with SQL2005.

 

TASC who make PARS have spent several hours looking at the SQL database and server and can't find anything wrong with it. All patches / updates for both SIMS and PARS have been applied.

 

Any ideas? Its driving us nuts!!!

 

Thanks!

[Banjo]

Is it the express version of SQL and have you virtualised it?

[creese]

We have found lately that Sophos hogs the resources and SQL gets the blame.

[teejay]

Run patch 6635 on the SIMS database, this does a reindex and database check. Running this fixed the same problem for us after upgrade.

[glennda]

Have exactly the same problem - have our sims support team constantly looking at the problem

[superfletch]

Funny I've generally seen SQL2008 improve response times, this is particularly noticeable with FMS. At the moment I'm going out moving it from SQL'05 to SQL'08 every single day in primary schools with varying systems. (As always the very first load time is slow on each workstation when compared with subsequent loads better - SQL '08 does feel quicker though).

 

I agree with Creese as well, if Sophos is what you use for an AV solution it does seem to slow things up a lot, particularly on the times SIMS needs to do a local workstation upgrade. However this was the case under SQL '05 - you just need to add the right exceptions.

 

Go for teejays suggestion too, it increases the size of the DB a tiny amount but usually reaps a benefit.

 

I know you said the Server isn't the issue but some thoughts on it nonetheless:

 

1. Firewall settings - make sure all the ports and the application are specifically defined as exceptions.
   
2. Is the SIMS specific instance of SQL2005 still running? If so stop and disable it, if possible uninstall it.
   
3. Relevant to the above point are the workstations connect.ini's right or is redirection via the 2005 instance being used?
   NB - What I mean here is that you may have changed the main connect.ini (eg - S:\SIMS\Connect.ini) but local ones in "C:\Program Files\SIMS\SIMS .net" might still be hitting the 2005 instance and redirecting from there.
   
4. Is the new 2008 instance you are using express or full and what was the old instance?
   
5. If it is the full SQL, have you looked at the number of cores that SQL '08 is allowed to use for the SIMS specific instance and compared that to your old version?
   
6. Coming off the back of the school hols have you had any major work done at all - are you noticing slower load speeds with any other applications?
   

 

Sorry, this is a long list but I hope it gives you some ideas of where to look. The firewall thing is unlikely as I don't think SIMS would even open if there was anything amiss there.

 

I hope you find the problem.

Edited September 27, 2010 by superfletch
spelling issues

[Darylrese]

Hi,

 

thank-you for your responses. I have run the command exec sp_who2 and we have no entries in the BlkBy column, just -'s

 

I believe we have SQL2008 R2 Standard edition installed.

 

SQL2005 is still installed on the server but not sure without checking if it has been disabled as our local council came in and did the upgrade for us.

 

All the is specified in the local connect.ini is:

[sIMSConnection]

Redirect=O:\SIMS

 

We had a new wireless system put in over the summer holidays (7 access points connected straight to an aruba switch) as well as the upgrade to SQL2008. Nobody seemed to be complaining on the first week back. We also rolled out SP3 for Windows XP over the summer.

 

Where do we download the 6635 patch from please?

 

Thanks

Edited September 28, 2010 by Darylrese

[PiqueABoo]

I believe we have SQL2008 R2 Standard edition installed.

 

I wanted to know which edition? in some code and it wasn't anywhere near as straightforward as I expected e.g. just go look in add/remove or whatever. Ended up connecting to the DB and running this query:

 

select SERVERPROPERTY('productversion'), SERVERPROPERTY ('edition')

[Darylrese]

10.50.1600.1 Standard Edition

[teejay]

Hi,

 

thank-you for your responses. I have run the command exec sp_who2 and we have no entries in the BlkBy column, just -'s

 

I believe we have SQL2008 R2 Standard edition installed.

 

SQL2005 is still installed on the server but not sure without checking if it has been disabled as our local council came in and did the upgrade for us.

 

All the is specified in the local connect.ini is:

[sIMSConnection]

Redirect=O:\SIMS

 

We had a new wireless system put in over the summer holidays (7 access points connected straight to an aruba switch) as well as the upgrade to SQL2008. Nobody seemed to be complaining on the first week back. We also rolled out SP3 for Windows XP over the summer.

 

Where do we download the 6635 patch from please?

 

Thanks

 

You may well find it kicking around on your sims server, search for Patch_6635_Reindex if you haven't got it, contact your support provider for it or if no joy pm me.

[Darylrese]

Thanks, I have done a search and cant find it anywhere. We are asking our provider for it.

 

I am using Microsoft SQL Server Management Studio and we have all sorts of processes being blocked by each other. See screen shot below. This is when the system comes to a halt:

 

http://i394.photobucket.com/albums/pp24/Miltoncross/blockedprocesses.jpg

Edited September 28, 2010 by Darylrese

[Darylrese]

sorry i have corrected the link...should now work.

 

Cheers

[Darylrese]

It looks like locks are your problem.

From your picture, it seems SPID 86 is being blocked by SPID 110. Can you see what SPID 110 is doing? If it's being blocked by SPID 86 you have a deadlock.

 

Hi,

 

The screenshot doesn't show it but there are several other SPID's blocking each other aswell. As we speak there arn't any SPID's blocking each other...it just seems to all happen at once then sort itself out after 10mins or so...but for this duration it crashes everyones SIMS and PARS causing them to log off.

[Darylrese]

yes will do. If i can grab a screen shot when it next happens i will post it on here. We are just giving Capita and our local council a ring to see what they say

[Darylrese]

Certainly will. Thank-you for your help so far.

[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

 

http://i394.photobucket.com/albums/pp24/Miltoncross/blockingtransactions.jpg

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

 

http://i394.photobucket.com/albums/pp24/Miltoncross/blockingerrorswednesday.jpg

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