[sims] SIMS DB Performance

[ITGURU]

Hello

 

Does anyone else battle with SIMS performance issues ?

 

When running large reports or even adding in a staff training day to the academic year the whole SIMS system freezes whilst it carries out the task and knocks all users out of SIMS.

 

We are a secondary school of 1500 students, Database size is 6.8GB, running on a 2 x 6 Core Server with 24GB RAM (Physical). When the task is running disk usage is minimal and CPU performance doesn't go above 5% whilst running.

 

SIMS response is just to run the indexing patch but doesn't help.

 

Anyone else had these kind of issues, with a solution?

 

Thanks in advance.

[3s-gtech]

What version of SQL Server are you using?

[ITGURU]

What version of SQL Server are you using?

 

 

2012 R2 with all latest updates.

[matt40k]

SQL, not Windows. Have you purchased SQL Server Standard or you using the Express version. Also for a SQL database, disc is king. Specifically latency - look at disc queue length Edited June 2, 2015 by matt40k

[ITGURU]

Windows Server and SQL are both 2012 R2.

Disk is no issues - 10% disk time maximum whilst task is running.

 

 

SQL, not Windows. Have you purchased SQL Server Standard or you using the Express version. Also for a SQL database, disc is king.

[matt40k]

There isn't a SQL Server 2012 R2. There is a SQL Server 2012 and a SQL Server 2014, nothing in the middle.

 

Also Express, or Standard edition. If its Express you're limited to 1 core and 1GB ram. Standard you have to purchase additional licenses from Microsoft.

[ITGURU]

Typo sorry, it is SQL 2012.

We've purchased SQL so the full version.

 

 

There isn't a SQL Server 2012 R2. There is a SQL Server 2012 and a SQL Server 2014, nothing in the middle.

 

Also Express, or Standard edition. If its Express you're limited to 1 core and 1GB ram. Standard you have to purchase additional licenses from Microsoft.

[matt40k]

Worth just checking it isn't the system waiting for the disc to respond - often happens if you have slow rpm hdd, it is like your disc io is a motorway, you may have 4 lanes, but if the road is icy you can't get from point A to point B very fast regardless if you have 1 car on the road or 100, you may be looking at the number of cars, which might not be the problem, it might be the road conditions - not sure how to do it on win server 2012 r2, but on win server 2008 r2

 

Open PerformanceMonitor from Administrative Tools

Data Collector Sets> User Defined

R-click in the right hand window panel > New > DataCollector Set

Name: IO Performance- Select Create manually (Advanced)- Next

Create data logs> Performance counter

Click Add..

Select counters from computer:

PhysicalDisk -Select each individual drive that SIMS uses - ie S: - Select Current Disk Queue Length, Avg. Disc sec/Read, Avg. Disc sec/Write - click OK

Sample interval: 1- Units: Minutes

Root directory: %systemdrive%\PerfLogs\Admin\IO Performance

Save and close - Finish

R-click on the IOPerformance > Properties - Stop Condition - Overall duration (tick), 1 - Units: Weeks- click OK

Start the IOPerformance - single click on it, then click on the play button

Once it's finished - Open Performance Monitor from AdministrativeTools

Reports > User Defined > IO Performance > Double-click the report

Ideally Current Disc Queue Length will be less than 3 and ideallythe Avg. Disc sec/Read and Avg. Disc sec/Write will be 10 ms or less however upto 20 ms would be acceptable. These should be only used a target as they willbe almost impossible to achieve 100% of the time in a real-world environment.

For more information - visit: http://msdn.microsoft.com/en-us/library/cc966412.aspx#ECAA

[ITGURU]

Checked those already and in range. average disk queue is 1.214

Got the usual 2 patches from Capita so will try those again.

Edited June 2, 2015 by ITGURU

[matt40k]

Assuming it happens on the server and isn't just a client based hw problem. It's a Capita problem. Lets hope the patches sort it.

[vikpaw]

Re-indexing can help, especially for reports, but for large complex ones they can be v.slow, but should be only for the client that requested it.

Tasks like editing the academic year, are notorious for freezing up all client systems, i don't think it's a bug (bad coding maybe), but it's sort of by design and they do warn you i'm sure when you start the wizard.

[Damon]

See jinnantonnixx's post here if the patch helps temporarily:

http://www.edugeek.net/forums/mis-systems/128123-sims-gradually-getting-slower-slower-2.html

[PhilNeal]

We don't have many cases where SIMS is running slowly. If you have performance issues that are causing usage issues then please raise a case stating where the performance issues occur - delays in seconds etc.