ajg Posted March 2, 2016 Posted March 2, 2016 Had a look and we are using tcp for the document server and the test connection works instantly. Our HP MSA 2040 SAN is only a year old and the sims server is running on 15K SAS disks. My instinct tells me it is a SQL 2014 issue as we seemed to be fine on SQL 2012 but am reluctant to revert back to SQL 2012 as seems like a backward step.
JHLEHS Posted March 3, 2016 Posted March 3, 2016 You can run SQL 2014 IN 2012 mode if you do not want to revert back and then once the problems have been fixes in SQL 2014 you can revert back.
3s-gtech Posted March 3, 2016 Posted March 3, 2016 (edited) School Info 1,200 student school (SQL 2012, Solus 3, Discover) Sims server Virtual - VMWare 5.5, Server 2008 R2, 16GB RAM, 8 Cores on Opteron 6136 @ 2.4ghz, Sims Benchmarks 14 Seconds Client Login Time (from entering password to front page) 25 Seconds Reports >> Students >> addresses >> Year 7 >> 1 Reg Group 6 Seconds Search "Smith" student name in main screen search box 14 Seconds Solus 3 launch on server Sims Database Sims.mdf - 5.6 GB Sims.ldf - 1.1 GB Simsdiscover_Primary.mdf - 789 MB Simsdiscover_Primary.ldf - 3.1 GB Little slower than I'd hoped, but this server desperately needs a firmware update on the RAID controller. I should add that this server runs two SIMS databases plus two FMS databases as well. Edited March 3, 2016 by 3s-gtech
Sam_Brown Posted March 3, 2016 Author Posted March 3, 2016 The issue we have seems to be similar to the issue others are having. If someone else is running a large report it basically makes SIMS unusable for everyone else on the system. Has anyone had any issues with people running large reports and managed to solve it?
synaesthesia Posted March 3, 2016 Posted March 3, 2016 We have 4 x Samsung SM863 drives going into our SIMS server in RAID10 at Easter (with a spare on standby). Can't wait to see how it goes.
JHLEHS Posted March 3, 2016 Posted March 3, 2016 School Info 1,200 student school (SQL 2012, Solus 3, Discover) Sims server Virtual - VMWare 5.5, Server 2008 R2, 16GB RAM, 8 Cores on Opteron 6136 @ 2.4ghz, Sims Benchmarks 14 Seconds Client Login Time (from entering password to front page) 25 Seconds Reports >> Students >> addresses >> Year 7 >> 1 Reg Group 6 Seconds Search "Smith" student name in main screen search box 14 Seconds Solus 3 launch on server Sims Database Sims.mdf - 5.6 GB Sims.ldf - 1.1 GB Simsdiscover_Primary.mdf - 789 MB Simsdiscover_Primary.ldf - 3.1 GB Little slower than I'd hoped, but this server desperately needs a firmware update on the RAID controller. I should add that this server runs two SIMS databases plus two FMS databases as well. 3s-getch, looks like you need to shrink the Sims log file this would improve performance 1.1 GB is massive for a log file. 1
JHLEHS Posted March 3, 2016 Posted March 3, 2016 The issue we have seems to be similar to the issue others are having. If someone else is running a large report it basically makes SIMS unusable for everyone else on the system. Has anyone had any issues with people running large reports and managed to solve it? Do you have a command time out in your Sims.ini? This would make the report timeout hopefully before it brings the server down. Also take a look at the sims.ldf file and see if it can be shrunk?
jinnantonnixx Posted March 3, 2016 Posted March 3, 2016 (edited) 3s-getch, looks like you need to shrink the Sims log file this would improve performance 1.1 GB is massive for a log file. A large log file won't cause (read/query) performance problems but it could be an indication that all is not well on the backup front. Managing Transaction Logs - SQLServerCentral Log size A large log file will not cause performance problems for queries in the database. It is possible that a very large number of Virtual Log Files will result in slower than expected log backups and other operations that read the log, but that's not a problem of large log, that's a result of growing the log file in small intervals And the log file growing in small intervals is precisely what you get when you shrink the log file to its minimum - The reindex patch does this, BTW. Edited March 3, 2016 by jinnantonnixx
jinnantonnixx Posted March 3, 2016 Posted March 3, 2016 (edited) Here's a thought - maybe the reports make heavy use of the tempdb file(s). Every insert into the tempdb needs a log update, so this could be the bottleneck, especially if the tempdb log file is on the same disk as the database files. Could be an I/O problem. Perhaps if you move the tempdb to a different disk/LUN, even though the tempdb data and log file share the same disk, at least it doesn't share the same disk as the SIMS database. Just a thought, and I might be completely wrong. We have several tempdb files (using the one per-processor rule-of-thumb) on a separate LUN, and keep the SIMS database and log files on separate LUNs, and the server OS on yet another disk. This minimises I/O contention. The Brent Ozar site is a treasure trove of SQL goodies. https://www.brentozar.com/sql/tempdb-performance-and-configuration/ Edited March 3, 2016 by jinnantonnixx 1
3s-gtech Posted March 3, 2016 Posted March 3, 2016 (edited) 3s-getch, looks like you need to shrink the Sims log file this would improve performance 1.1 GB is massive for a log file. It is - might just trim it down a bit. Found a couple of the other DBs were in Full recovery mode so trimmed those to Simple. Backups are looking fine, and have been tested working recently. Edited March 3, 2016 by 3s-gtech
jimlag Posted March 16, 2016 Posted March 16, 2016 School Info 1,150 student school (SQL 2012, Solus 2, Discover) Sims server Virtual Hyper-V, 10GB Dynamic to 20GB RAM, 12 Cores on Xeon E5-2650 @ 2.0GHz, Sims Benchmarks 05 Seconds Client Login Time (SSO from when hitting Enter) 20 Seconds Reports >> Students >> addresses >> Year 7 >> 1 Reg Group 01 Second Search "Smith" student name in main screen search box N\A Seconds Solus 3 launch on server Sims Database Sims.mdf - 6.7 GB Sims.ldf - 0.3 GB Our Data Manager still complains that running reports is a slow process, She's got a 2nd PC to keep up with work while running reports. I found that an SSD client speeds up her reports a bit. The Assistant Head told me that they are going to be running whole school reports at the same day (is it even possible??) so he was asking if we could invest on the system to be a bit faster. Any ideas?
ITGURU Posted March 16, 2016 Posted March 16, 2016 School Info 1,150 student school (SQL 2012, Solus 2, Discover) Sims server Virtual Hyper-V, 10GB Dynamic to 20GB RAM, 12 Cores on Xeon E5-2650 @ 2.0GHz, Sims Benchmarks 05 Seconds Client Login Time (SSO from when hitting Enter) 20 Seconds Reports >> Students >> addresses >> Year 7 >> 1 Reg Group 01 Second Search "Smith" student name in main screen search box N\A Seconds Solus 3 launch on server Sims Database Sims.mdf - 6.7 GB Sims.ldf - 0.3 GB Our Data Manager still complains that running reports is a slow process, She's got a 2nd PC to keep up with work while running reports. I found that an SSD client speeds up her reports a bit. The Assistant Head told me that they are going to be running whole school reports at the same day (is it even possible??) so he was asking if we could invest on the system to be a bit faster. Any ideas? When our Data Manager runs hefty reports which slows the system or stops other users logging in, I have found there to be minimal load on the server resources (disk/CPU usage). Where SIMS falls foul of performance is because it has so many tables and entries which it has to link with/search through so all the data is crossed referenced with several tables so that one table cannot be read in clear text with all the data.
Barcrest Posted March 17, 2016 Posted March 17, 2016 (edited) School Info 750 student school (SQL 2012, Solus 3, Discover, FMS) Sims Benchmarks 08 Seconds Reports >> Students >> addresses >> Year 7 >> 1 Reg Group Instant Search "Smith" student name in main screen search box Sims Database Sims.mdf - 5.56 GB Sims.ldf - 813 MB I am wondering if those of you where it seems to take an age have some bloat or issues with your DB. Have you archived off old academic year attendance marks? Have you run Tools >> System Diagnostics? Have you run the Tools >> Housekeeping routines? Do you have lots of old Pre Admission Groups with students? I don't know if any of that will have any bearing on the issue but it is something I like to keep on top of. Edited March 17, 2016 by Barcrest
mukz Posted March 24, 2016 Posted March 24, 2016 Quick Question for all of those Guys who are using VMs: Are you using Dynamically expanding VHD's for your SQL? or Fixed? When i took over from the old chap here, i made all SQL server's Data & Log stores as Fixed and don't ask me why but it helped speed up everything!!
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now