During a recent discussion with another PS Admin running on SQL Server it became apparent that his efforts to improve application SQL performance through adding more CPUs (with the associated license costs) were based on a fundamentally wrong assumption:
PeopleTools/Application SQL will go parallel if needed.
This is simply not true. In fact, most SELECT SQL can never go parallel under PeopleTools on SQL server due to the fact that they are run through cursors and PeopleTools requests a cursor type of FAST_FORWARD. FAST_FORWARD cursors result in an execution plan with a NonParallelReason value of NoParallelFastForwardCursor.
Pretty self-explanatory I think. Of course, if the type requested had been FORWARD_ONLY then a parallel plan would be possible.
Unfortunately, there are times where parallel could help – mostly in power user queries, but they are all forced single threaded by the choice of cursor type.
Someone more cynical than myself might think this was deliberate 🙂
Top tip: Remember to add OPTION(MAXDOP 1) to PeopleTools SQL you paste into SSMS if you want to see an execution plan even close to the one PeopleTools gets. But you also need to be sure all of your connection options in SSMS (ARITHABORT etc) exactly match the ones used by your application/process scheduler servers to guarantee the same plan.