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.
So all that additional CPUs will get you is more concurrency in terms of users, and perhaps some better speed for non-PS SQLs. Bear in mind that a very small percentage of delivered apllication SQLs are complex enough to even reach the default cost threshold for parallelism of 5 (I run my systems with a threshold of 50 to 100).
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.