Opinionated Software

… we have opinions about everything!

PeopleTools and Parallelism on SQL Server —

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. And, of course for those SQLs not run via a cursor, of which the number is limited mostly to non-PT tools like SQR.

And 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).

Sadly, the place where parallel would help the most is in power user queries, but again they are all forced single threaded by the cursor type.

A cynic might think this was deliberate 🙂

But still, it would be nice not to be limited to single threaded by a poor choice of cursor type.

Top tip: Remember to add OPTION(MAXDOP 1) to PSQUERY SQL you paste into SSMS if you want to see an execution plan even close to the one PeopleTools gets.

Categorised as: PeopleTools | Performance | SQL | SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.