[sims] SQL2008 VERY SLOW FOR ALL USERS

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

[powdarrmonkey]

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

 

Er... I'm supporting your opinion.

[glennda]

Er... I'm supporting your opinion.

 

I know - but i meant with people with esx!

[teejay]

As long as they are set up properly and have supported hardware, you'll get absolutely naff all difference in performance these days between KVM, VMWare, Xen etc, purely because all the work is done by the processor virtualisation extensions. You do get variations down to other hardware components, such as NICS, SANs etc, but that happens just as much with all of them and I have seen VMWare knock spots off KVM on some hardware and vice versa.

[Darylrese]

Here is a screen shot:

 

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

[Darylrese]

Strange isn't it. I sat with admin staff last night after applying the patch reccomended by Capita, rebooted the server and tried a report again and still just as slow.

 

We only have 4GB of RAM, this isn't a problem i assume? Others i have spoken to say we should have atleast 6 - 10.

[Darylrese]

"Your current compatibility level is 100."

 

No problem mate we are too. It's a nightmare. Even TASC / Capita and the local authorities are out of ideas.

[Darylrese]

I know the video quality is very poor but it gives you an idea of whats happening (if you change it to 480p or 720p its much clearer):http://www.youtube.com/watch?v=GP3S_wUkZo4 Edited October 5, 2010 by Darylrese

[Chris01]

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

 

I am IT Manager in a school and we are getting exactly the same issue following upgrade to SQL 2008 in the summer. Do you know if your teachers use the Home Page for Achievement and Behaviour? Also are you a member of a consortium sharing data within SIMS?

[glennda]

How much of your database is sitting in the ram? We had the same problems and have now migrated to a x64 server with 10gb ram and then the entire database is sitting in the ram and it works fine now

[glennda]

I know throwing 10gb at it isnt the problem - Our support team have been through everything they can to see why it what was causing the issue - they said that for the spec of server we had (quad-core 4gb ram server2k3 vm) our database was to big at 6gb (which personally i didn't believe) but this issue only came around after we started using the extra sims behaviour modules. All i was saying was that we have migrated to Server 2008 R2 x64 with 10gb of ram and the database is now running fine (completely sitting in the ram) - one thing we do not have is all the old instances installed plus the extra things that our support team have used in the past and not uninstalled. I will be arranging for our Biostore Database to be transferred to this server along with groupcall exporter. If this then causes a mass slow down again I know where to point the finger. Also our Sims Learning Gateway has not be resetup yet (waiting for LEA team to sort). again if this cause's slowdown I will ask the LEA why there SLG is causing a major slowdown of our systems.

[Chris01]

Our slow sims coincides with a number of things other than the sql upgrade. Capita's partnership exchange server is now working properly and we have shown staff how to set up their home page to show behaviour and achievement. Our server is quad-core 4gb ram server 2k3 and our database is just under 4gb. It shouldn't have any problems. We think we have eliminated the partnership exchange as the culprit and I now have to get staff to remove the behaviour and achievement from their front page. It may well be what is causing the problem. We have done all the re-indexing, looked at hardware, changed the pagefile, optimised performance etc, but our admin staff are tearing their hair out.

[Darylrese]

we had all the IT bods from the council in today and none of them can work it out! We are now looking at reinstalling SQL2008, Moving to a different server entirely (hopefully not!) or getting a conference call with capita and tasc to ask them.

 

Its all still so slow you can't use it.

[Butuz]

What RAID level are you using on the storage for your SIMS server VM? I found Raid 5 / 6 slow on the SIMS server VM as these raid levels give a high write speed penalty. I've been experimenting and have now moved to Raid 10 and the whole server is mutch faster. Am now moving to Tripple Mirrored Raid 10 for ultimate speed:

 

http://www.necam.com/Storage/images/TripleMirror.gif

 

"RAID-Triple Mirror by high-speed RAID engine is adopted. This technology has high speed of RAID-1 and high reliability of RAID-6 at the same time. The business can continue even when double failure occurs to the HDDs of the same RAID. This technology is useful especially for Database and OLTP which require high performance and availability."

 

I'll let you guys know how it goes!

 

Butuz

[Chris01]

I stopped a service that we have which shares data with other members of our consortium and SIMS was ok. As soon as I re-started it, hey presto, right back to square one. I now know what is causing it, but not how to cure it. I will pass it on to Capita.

 

Do you have anything like that, that might be the cause of your problems?

[glennda]

I stopped a service that we have which shares data with other members of our consortium and SIMS was ok. As soon as I re-started it, hey presto, right back to square one. I now know what is causing it, but not how to cure it. I will pass it on to Capita.

 

Do you have anything like that, that might be the cause of your problems?

 

 

Could you let us know the name - if not in public via PM would be great. We have not transferred all the old software to the new box yet - especially a product which does what you mention above.

 

 

On another note we have had our Sims Learning Gateway installed today so I will post back tomorrow or wednesday to say if this has affected it.

Edited October 11, 2010 by glennda

[matt40k]

I stopped a service that we have which shares data with other members of our consortium and SIMS was ok. As soon as I re-started it, hey presto, right back to square one. I now know what is causing it, but not how to cure it. I will pass it on to Capita.

 

Do you have anything like that, that might be the cause of your problems?

 

I think your refering to Partnership Xchange (PX) - there is a discussion on SupportNet about how it basically kills your server if it doesn't have the correct server name entered in the config.

[eddyc]

[*]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?

 

I'm a bit of a novice when it comes to SQL, how do I do this please?

 

Thanks

[glennda]

Just an update on this - the problem seems to be with the Sims Homepage if you are using the graphs (apparently attendance mainly). Nice to see they are on the ball as somebody at a local school here worked this out about 8 weeks ago!

 

From Capita

 

SIMS Performance / Homepage Panels

We have recently been made aware of a small number of customers that have been experiencing slower than expected performance of their SIMS system. On investigation we have found a small number of cases where this has been caused by heavy usage of the SIMS Homepage panels across large numbers of staff. Many of the panels have automatic refreshing of data and therefore, if large numbers of users each have many panels setup for them then this could place a significantly higher load on the SQL Server than was the case before these panels were configured.

 

To help reduce any potential impacts from this, if you are experiencing performance issues we would recommend evaluating the number of SIMS Homepage panels in use by your staff and consider reducing their usage and number of automated refreshes.

We will be updating the Homepage panels in future releases of SIMS to allow more control over the refresh frequency hence reducing the potential load on the SQL Server

Regards,

Jonathan Wood

Technical Product Manager (SIMS)

[irsprint84]

My server running like crap since upgrade just thrown ram at it plus I spoke to sims today knowing there is memory leak for some and be fixed in March

[Rozzer]

Thank god I am not alone

 

Having the exact same issue. I have setup a new VM and installed the reindex patch and still seeing CPU spiking. Lets hope the March update fixes it.

 

Ross