thank you Lars for your valuable comment!
here is the reason that this parallelism was changed on MSSQL that is why I'm looking to tweak the same on HANA.
Testing on a simple single processor server resulted in improved performance over an 8 core processor. Further investigation showed that the Max Degree of Parallelism setting on the SQL server was not working very well for the type of queries that it needed to handle (numerous and small).
The setting controls how a query will be split into multiple parts across multiple processors, to enable parallel processing, and then recombined before the result being delivered back. If you can image a small simple request being split across multiple processors, it now has the additional overhead of being split up for processing and then being re-joined to form the result. Further to this, if one of the processors is working on another more complex query, the small query now has to wait for the larger query to complete (even though the other parts of the query have been completed). The result of this is numerous pauses when using the SAP UI with B1 UP running.
Checking processor load will normally show not much activity, however, SQL waits will show lengthy queues. SQL locks are quite likely as well.
Max Degree of Parallelism is by default set to 0, which means that it can use as many processors as it wishes to split sql queries across (thereby actually increasing the chances of system slowdowns).
Setting this value to 1 or 2 or 3 etc can reduce the overhead associated with each query and reduce the chances of it having to wait in a processor queue whilst another is serviced. The downside of this is that large complex queries will take longer to run as they are not split across all of the processors. This is why the single processor server was out performing the multi core processor server.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.