Users reported that using SIMS is very slow & how to improve the performance of SIMS

[genesis]

Hi

 

We have SIMS running on SQL 2012 VMware virtual server.

Processor is: Intel® Xeon® CPU E5-2620 0 @ 2.00Ghz

Installed RAM: 16GB

 

From last 5 months users are reporting that logging in and using SIMS is very slow .

 

To trouble shoot, on SIMS server, I went into the task manager and under Performance- CPU-(The CPU percentage is fluctuating between 75 % and 99% and most of time it stays at 100%). I went into the process and SQL server Windows NT -64 Bit CPU usages is 90%.

 

We are closed for half term and the CPU usage on the SIMS server now is (CPU 6%-10% and Memory 11%). I went into the process and SQL server Windows NT -64 Bit CPU usages is 0%- 2.4%.

 

Not sure how to improve the performance of the server .I don’t know where the underlying problems is and any help to sort this problem would be great.

Thanks in advance.

[kevin_lane]

How big is your database

[genesis]

The Database size is 22Gb

[3s-gtech]

Woooo that's big. What size school? I suspect you need to have a look at shrinking that.

[genesis]

I checked the database through SQL server Management studio-Right click the database-Properties and hope this is the right way of finding the database size.

 

We are a secondary school.

[clareq]

We are a secondary, with 1400 students on roll, and our database is 10Gb.

[PotNoodleTech]

Generally, medium size secondary = 5GB 'ish, large secondary = 10GB ish - 22GB is WAAAAAY too big unless you've got over 3000 pupils?

 

Also - you say 2Ghz virtual CPU - you should have at least 2 VCPU allocated, I would allocate 4. It looks like you only have 1 vcpu? This would be a big bottleneck.

[genesis]

I opened the VSphere console and Edit the SIMS server and under CPU- Number of virtual sockets =1

Number of cores per socket=2

So do i need to increase the Number of virtual sockets to= 2

[splattthecat]

Generally, medium size secondary = 5GB 'ish, large secondary = 10GB ish - 22GB is WAAAAAY too big unless you've got over 3000 pupils?

 

Also - you say 2Ghz virtual CPU - you should have at least 2 VCPU allocated, I would allocate 4. It looks like you only have 1 vcpu? This would be a big bottleneck.

We have several schools where the SIMS database is much larger than we are happy with, caused by the overnight Discover transfer routines, (which in my opinion are unfit for purpose, but Capita are refusing to look at until the Autumn upgrade) and having Data Change Tracking enabled for some third party integration products.

 

The largest database got to 60Gb until Capita produced a patch to shrink the DB. We have a call logged for another school where the DB is 22GB, and the school are unable to make exam entries. Not sure the two things are connected, but as it is Assessment tables that are growing out of control I suspect it may be.

[john]

I opened the V[ATTACH=CONFIG]35997[/ATTACH]Sphere console and Edit the SIMS server and under CPU- Number of virtual sockets =1

Number of cores per socket=2

So do i need to increase the Number of virtual sockets to= 2

 

Woha?! Why so many disks?

 

Also would suggest you read this -http://blogs.vmware.com/vsphere/2014/02/vscsi-controller-choose-performance.html and look to split some disk onto a PVSCSI adapter

 

Also what type of Network Card do you have on that VM https://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1001805 again worth a read and ensure you have the most optimum and suitable model.

 

You should also look at the CPU usage and Ready state on the VM to check it isn't bottle necking due to other VMs on the host, if you give a VM 4CPUs it will wait till it is able to lock 4 threads on to process this adds wait state to VMs and can cause issues so if the box has a high ready state currently then other VMs on the host are hogging resources they probably don't actually need so reduce the vCPU on them to reduce the overall ready state and improve the performance of other boxes.

Edited April 6, 2016 by john

[Esteban_Child_of_the_Sun]

We've found in recent upgrades that the client side is also getting more resource intensive. As a test we put Solid State Drives and upped their RAM to 8GB in some workstations for staff to use and they reported a substantial improvement in performance. One of our schools has a SIMS DB that's over 20GB and they have a physical server with 4 quad core processors and 32GB of RAM (they also run lots of things that integrate with SIMS such as cashless catering etc. on the server) and they haven't reported any performance issues. We offer our schools a SIMS Health Check and part of it is a technical evaluation where in short we test network connectivity from a workstation to the SQL server, the Document Server, the shared drives, etc. Then we check the SQL wait times and workstation performance.

[ChrisERoots]

We've found in recent upgrades that the client side is also getting more resource intensive. As a test we put Solid State Drives and upped their RAM to 8GB in some workstations for staff to use and they reported a substantial improvement in performance. One of our schools has a SIMS DB that's over 20GB and they have a physical server with 4 quad core processors and 32GB of RAM (they also run lots of things that integrate with SIMS such as cashless catering etc. on the server) and they haven't reported any performance issues. We offer our schools a SIMS Health Check and part of it is a technical evaluation where in short we test network connectivity from a workstation to the SQL server, the Document Server, the shared drives, etc. Then we check the SQL wait times and workstation performance.

 

Interesting this, we have been suffering with brutally slow SIMS performance for some time now. As a rough guide, currently staff can wait anywhere between 3-5 minutes for an Address Label report to be done for one year group on our Virtualised Host which is awful. As a test we setup SIMS on a physical server and the time went down to 10-20 seconds. Nobody has come close to being able to assist us in seeing what the actual problem is with our VM but I am close to switching back to the physical box as a real test. I just get the feeling that for some things Physical is the way to go. I always got this impression from when we first installed SIMS on this VM.

[PotNoodleTech]

What was the spec of you're virtual server as compared to the spec on the physycial server, e.g cpus ram disk type/raid configuration etc.

 

Cheers

[ChrisERoots]

What was the spec of you're virtual server as compared to the spec on the physycial server, e.g cpus ram disk type/raid configuration etc.

 

Cheers

 

Here's a quick snap of the current machines. The Document Server files and storage are now on the Physical server to see if that would decrease the times but this has made no real difference in performance.

 

Current VM - Utilises our SAN for storage

 

Physical Server - uses built in storage

[PotNoodleTech]

My gut feeling would be:

 

RAM: Ideally you want the SIMS database to fit inside you're RAM. If you have this facility then SIMS will actually cache the reads and writes to the database in RAM, which is MUCH much faster it then periodically offloads the new data to the disk when needed. If you do not have enough RAM SIMS will use the disk for reads and writes which is really slow and also uses more CPU time. I suspect this is happening as everything is really slow and you're CPUs are maxing out where 4 cores _should_ be enough. So for you I would say 32GB of ram for that virtual machine would be perfect. If you can also find a way of shrinking you're SIMS Database that would be useful.

 

Storage: I would take a long hard look at you're SAN and maybe do some benchmarks if you can as I've seen really expensive SANS in use that were significantly slower than a 5 year old server with £500 worth of SAS disks. A good way of doing it would be to use disk benchmarking software (crystal diskmark, iometer, etc) inside a VM to see what kind of performance the actual VM is seeing.

Edited May 23, 2016 by PotNoodleTech

[ChrisERoots]

My gut feeling would be:

 

RAM: Ideally you want the SIMS database to fit inside you're RAM. If you have this facility then SIMS will actually cache the reads and writes to the database in RAM, which is MUCH much faster it then periodically offloads the new data to the disk when needed. If you do not have enough RAM SIMS will use the disk for reads and writes which is really slow and also uses more CPU time. I suspect this is happening as everything is really slow and you're CPUs are maxing out where 4 cores _should_ be enough. So for you I would say 32GB of ram for that virtual machine would be perfect. If you can also find a way of shrinking you're SIMS Database that would be useful.

 

Storage: I would take a long hard look at you're SAN and maybe do some benchmarks if you can as I've seen really expensive SANS in use that were significantly slower than a 5 year old server with £500 worth of SAS disks. A good way of doing it would be to use disk benchmarking software (crystal diskmark, iometer, etc) inside a VM to see what kind of performance the actual VM is seeing.

 

Thanks for that Butters. I did do some benchmarking of the SAN from numerous VMs in our estate. Can't remember the actual numbers the MIS server came back with the same results as a lot of the other VMs. What I will do though is bench the physical device and see what thats saying in regards to performance. The memory side of things I will take a closer look at though. There's definitely some food for thought there. Thanks for the suggestions.

[RootsSchImp]

Spring cleaning assessment manager could be a productive way of reducing the size of your DB. If you've got a bit of outside DB knowledge I'd recommend:

1/ Export to Excel all relevant Assessment Data (this year and last year if we're being realistic)

2/ Run a report of every result in the system to a text file so you can recover anything should you need to.

3/ Delete all results, aspects and templates then build again fresh and clean.

 

Obvs you'll want to sort your RAM issues first as it sounds like this would be impossible with current server performance.

[SpaceInvader83]

We had issues with our Sims running slower than I would have liked, I went through lots of benchmarking and troubleshooting with help from the forum. In the end I moved our Sims server off our HP Lefthand SAN to locally attached SSD's in Raid 10 on one of the ESX hosts - much improved, still not as quick as i'd like but hey ho that's Sims for you.

[zag]

I was going to say, running a sims server over a SAN is going to create a huge bottleneck. You want local SSD storage really.

 

We have a 10gb database on an SSD based server and it flies when doing reports.

 

I have also noticed that discover is causing a large increase in the db size. Glad someone else is too!

[Homer]

I would bet money that the bottleneck will be storage here - what is the storage backend that the databases are sitting on?

 

Ideally you want the databases on a separate set of spindles with their own LUN/Datastore to get the best performance. We had to deal with it running from shared datastores in my last place and performance could (with reports and logging in) be absolutely awful.

 

Up the vCPU to 4 if possible (but remember licensing implications) and make sure you have at least 8GB of RAM assigned preferably 12-16 and make sure SQL server is capped 2GB below the allocation to prevent it swallowing all of the RAM.

[jinnantonnixx]

I would bet money that the bottleneck will be storage here - what is the storage backend that the databases are sitting on?

 

Ideally you want the databases on a separate set of spindles with their own LUN/Datastore to get the best performance. We had to deal with it running from shared datastores in my last place and performance could (with reports and logging in) be absolutely awful.

 

 

^^^ This.

 

Also make sure your OS, database files, log files and temp files are on separate LUNs. If everything is on one disk, your OS, database reads, log writes and tempDB I/O are all competing for the same I/O channel.

 

Poor Disk Performance, Time Outs, Database and the SQL Server Error Log - Documents

Edited May 26, 2016 by jinnantonnixx

[ranj]

These performance issues you are seeing, the servers are they sitting on a VMware environment? If so what version?

[kennysarmy]

If possible I would like to ensure our SIMS is running optimally with the hardware we have.

 

SIMS is virtualized (VMWare)

 

CPU = 4 vCPU (peaks around 20% daily)

Memory 24 Gb (peaks around 12% daily)

 

Storage is on our fastest SAS drives.

 

Database size is ~ 5 Gb.

 

VM host is a Proliant DL380 Gen9 12 CPU's with CPU usage usually around 17% and 128 GB RAM with peak usage around 40%

 

 

SQL maximum memory usage is set to 19661 MB

 

 

We run frequent housekeeping on the Sims database yet still we get our Data Manager (who is using a brand new PC / SSD drive) complaining certain routines can have her sat waiting...

 

Is there anything else we could do?

[ranj]

If possible I would like to ensure our SIMS is running optimally with the hardware we have.

 

SIMS is virtualized (VMWare)

 

CPU = 4 vCPU (peaks around 20% daily)

Memory 24 Gb (peaks around 12% daily)

 

Storage is on our fastest SAS drives.

 

Database size is ~ 5 Gb.

 

VM host is a Proliant DL380 Gen9 12 CPU's with CPU usage usually around 17% and 128 GB RAM with peak usage around 40%

 

 

SQL maximum memory usage is set to 19661 MB

 

 

We run frequent housekeeping on the Sims database yet still we get our Data Manager (who is using a brand new PC / SSD drive) complaining certain routines can have her sat waiting...

 

Is there anything else we could do?

Is the vm on 2012? Does it use the vmxnet3 adapter?

[kennysarmy]

Is the vm on 2012? Does it use the vmxnet3 adapter?

 

2012 R2 & vmxnet3 Ethernet Adapter

 

Regards