Hi all,
I've asked Pegasus if they have any specific recommendations for configuring MS SQL Server to optimise SE but they've not given me anything other than the standard guides.
The customer in question has a very large payroll (min 5000 employees, max 8000) and are questioning if there is any way to speed up the payroll calculation. Particularly the point where it is 'committing data' which occurs after the calc has run and the calculation report produced. This part is writing the data into SQL server. It runs at an acceptable speed for the volume of data (I feel) but they are looking to make any improvements where possible so have just asked the question.
They've had their IT complete some performance monitoring whilst the process is running but this hasn't shown any major resource issues or bottlenecks on the server so they've come back to me. Based on this they are planning to increase the RAM. This is the info we received from them on this:-
I’ve had a look at the Data Set Collector information.
With SQL there is normally a bottle neck somewhere that causes performance issues, most commonly insufficient memory causing high Disk IO.
Looking at the data collected it is evident that the SQL data is 99.996% in memory (Green Line – Buffer Cache) which was confirmed by zero disk read/write (Dark Blue Line). There was a momentary dip of Buffer cache hit ratio down to 95% and a corresponding disk read/write elevation but only for 20 seconds at the end of the task.
The light blue line is the SQL Server Lock Request/sec, the pink line is the SQL CPU % demand.
As mentioned all cores were being used (Max degree of parallelism MAXDOP is set to 4 on SQL). It is interesting that only 25% CPU usage was occurring during the first running task, in comparison with the task running at 10:51:30 where we started to see higher CPU usage of 30 - 40% which appears to match the SQL Server Lock Requests.
The dotted green line is the SQL Compilations/Sec, the red dotted line SQL RE-Compilations/sec
In summary, whilst the server memory is a little tight, there is a sufficient amount to hold all of SQL DB in memory for now. Therefore Disk IO is of no issue at all.
I don’t think adding more CPU cores would improve performance as they are not being fully utilised by the database. Perhaps this is caused by a limitation of how the Pegasus SQL database has been constructed?
It would be interesting to double the CPU cores to 8 and increasing SQL MAXDOP to match and run the same test as a comparison however I don’t expect much difference.
I'm just wondering if anyone is able to interpret the above (!) or has come across any particular configuration changes or setups which optimises intensive processes in Opera SQL SE?
Thanks in advance.
Alex
