IT Infrastructure optimisation for the UNIT-e environment

Any tips and useful information you have relating to UNIT-e
Post Reply
Posts: 1
Joined: Mon Oct 17, 2016 9:29 am
Forename: Jon
Surname: Cole
College Name: Morley College
Position: Head of MIS

IT Infrastructure optimisation for the UNIT-e environment

Post by coleja » Fri Nov 18, 2016 4:44 pm

There was a discussion on this at the London user group meeting and Robin Wilson from Guildford outlined the steps he went through with assistance from Capita. There was a request for Robin to write up the detail of this and he has kindly done so below. Additionally, Christophe has added some supplementary information in italics.

Many thanks to Robin and Christophe.



Some changes we made were:

Update Server Spec
  • Based on Capita’s recommended setup, the number of allocated CPU cores and RAM were both below the minimum spec on our server
  • We increased the spec and are now substantially over the spec

VM Configuration
  • We run the database on a VMWare server and Capita sent us a best practices guide to follow and we checked this but think everything was ok there
  • We have moved the database server to an Intel server as these work more efficiently with SQL Server compared with AMD chips of the same spec

Remove all scalar valued functions from our code and use only table valued functions in code
We have previously advised customers of the “To Text To Convert” tool in Report Generator which colleges should run to migrate most occurrences of the scalar function “To Text” into their equivalent SQLServer native functions “Convert”, “Datename” and “Datepart” as required depending on the format parameters used. This would benefit Columbus users in particular.
NB: Some VB tools still require the Totext function to be used to export data into MDB’s successfully. In particular this relates to Curriculum Balancing and the Scottish FES reports so these reports must be locked so that they do not get altered by running the To Text to Convert tool.
ToTextConvert.png (96.22 KiB) Viewed 143 times

Added clustered indexes to all main UNIT-e tables we query
Split the TempDB database into multiple files
  • We discovered that we could achieve better performance by splitting the TempDB database into multiple equally sized files to increase performance
  • I think Capita might have said they would update their documentation to suggest this but not sure if they did
We are adding reference to this generic best practice advice for SQL Server installations into the next version of the UNIT-e Infrastructure document (4.35.500)

Re-write RegGen created SQL avoiding data conversion issues and use parameters
  • After I submitted my evidence to Capita they have been working to make RepGen use parameterised queries instead although the SQL generated is still no better
  • We discovered that a lot of RepGen created code was creating slow queries due to the way it structured the table joins etc. such as un-needed grouping and returning 100% of the query with mixed up right, inner and left joins in the query and conditions being repeated multiple times. Also makes it very hard to debug the query and you must re-format it first to make it readable
  • Code was also full of instances of code being cast from a date to a string and then further sub-string commands which are very expensive
A recent software notice on TORS and extended classes (3 October) provided some of the detail to the background of this issue and to the first parts of our work in this area.

Monitor Performance
  • Use sp_whoisactive when system is slow to determine what process is causing the slowdown
  • We can use this and then kill crashed processes to stop them taking the system down
  • We also re-write expensive queries to make them more efficient. Use of temporary tables can help and reduction of extra groupings and sorts we do not need. Many queries were over-complex.
  • ... o-a-table/
Last edited by robinwilson16 on Sun Nov 20, 2016 1:57 pm, edited 2 times in total.
Reason: Added bold headers and added ToTextConvert screenshot

User avatar
Site Admin
Posts: 184
Joined: Sun Oct 16, 2016 6:46 pm
Forename: Robin
Surname: Wilson
College Name: FEA
Position: MIS Consultant
LinkedIn Profile:

Re: IT Infrastructure optimisation for the UNIT-e environment

Post by robinwilson16 » Sun Nov 20, 2016 1:37 pm

Hello Jon

Thanks for posting the information.

I actually forgot one point which was:

Repair and Truncate Audit Log
A previous bug in UNIT-e had generated spurious audit log records.
Also previously we had too many types of changes being audited.
This left to a huge 80GB audit log which we could not even run queries from as it led to I/O errors.
After examining the Windows Event log I identified errors relating to SQL Server and I/O and traced this back to corruption in the ulive database.
I ran queries to identify which table had problems and narrowed it down to being data in the audit table only (thankfully!)
I had to repair the table with:

Code: Select all

Some further info on this command: ... orruption/

This took most of the night that night and after running it I ran SQL queries to identify types of audit records ranked by the number of records and deleted ones we did not need - i.e. I don't need to know when in 2013 someone ran a SELECT query again the capd_enrolment table but I do want to know when someone changed an enrolment status.
After than we could then quickly query the audit table and use it in certain reports to identify when records were changed or to allow us to model the data at the same point last year which was impossible before as queries never completed.

Hopefully the points above will be helpful to anyone else if your UNIT-e systems are running slow and also please respond if you have found other changes/tips that have helped you so we can see if we can also follow these to improve performance further. :tools-hammerdrill:

This year's enrolment was a fairly smooth process but this was certainly not the case the previous year when everything kept crashing and hanging all the time. :techie-hiding:

The sp_whoisactive script (free) has been really helpful to give us visibility on why the system is slow as with that it is really easy to identify which query is causing table locks and blocking everything else so we can quickly stop it, inform the user affected and update the affected code.

Also I realised that all the examples I gave happened to be on BrentOzar but I have to say that I have no affiliation with them but that they do seem to offer an article for every issue we have come across so far and I have learnt a lot from reading them. :music-rockout:

Post Reply

Who is online

Users browsing this forum: No registered users and 2 guests