sp_cursoropen and parallelism

Question

I'm running into a performance problem with a query that I can't seem to get my head around.

I pulled the query out of a cursor definition.

This query takes seconds to execute

SELECT A.JOBTYPE
FROM PRODROUTEJOB A
WHERE ((A.DATAAREAID=N'IW')
AND ((A.CALCTIMEHOURS<>0)
AND (A.JOBTYPE<>3)))
AND EXISTS (SELECT 'X'
FROM PRODROUTE B
WHERE ((B.DATAAREAID=N'IW')
AND (((((B.PRODID=A.PRODID)
AND ((B.PROPERTYID=N'PR1526157') OR (B.PRODID=N'PR1526157')))
AND (B.OPRNUM=A.OPRNUM))
AND (B.OPRPRIORITY=A.OPRPRIORITY))
AND (B.OPRID=N'GRIJZEN')))
AND NOT EXISTS (SELECT 'X'
FROM ADUSHOPFLOORROUTE C
WHERE ((C.DATAAREAID=N'IW')
AND ((((((C.WRKCTRID=A.WRKCTRID)
AND (C.PRODID=B.PRODID))
AND (C.OPRID=B.OPRID))
AND (C.JOBTYPE=A.JOBTYPE))
AND (C.FROMDATE>{TS '1900-01-01 00:00:00.000'}))
AND ((C.TODATE={TS '1900-01-01 00:00:00.000'}))))))
GROUP BY A.JOBTYPE
ORDER BY A.JOBTYPE

The actual execution plan looks like this.

Noticing the server wide setting was set to MaxDOP 1 I tried playing around with maxdop settings.

Adding OPTION (MAXDOP 0) to the query, or changing the server settings results in much better performance and this query plan.

However, the application in question (Dynamics AX) doesn't execute queries like this, it uses cursors.

The actual code captured is this.

declare @p1 int
set @p1=189527589
declare @p3 int
set @p3=16
declare @p4 int
set @p4=1
declare @p5 int
set @p5=2
exec sp_cursoropen @p1 output,N'SELECT A.JOBTYPE FROM PRODROUTEJOB A WHERE ((A.DATAAREAID=N''IW'') AND ((A.CALCTIMEHOURS<>0) AND (A.JOBTYPE<>3))) AND EXISTS (SELECT ''X'' FROM PRODROUTE B WHERE ((B.DATAAREAID=N''IW'') AND (((((B.PRODID=A.PRODID) AND ((B.PROPERTYID=N''PR1526157'') OR (B.PRODID=N''PR1526157''))) AND (B.OPRNUM=A.OPRNUM)) AND (B.OPRPRIORITY=A.OPRPRIORITY)) AND (B.OPRID=N''GRIJZEN''))) AND NOT EXISTS (SELECT ''X'' FROM ADUSHOPFLOORROUTE C WHERE ((C.DATAAREAID=N''IW'') AND ((((((C.WRKCTRID=A.WRKCTRID) AND (C.PRODID=B.PRODID)) AND (C.OPRID=B.OPRID)) AND (C.JOBTYPE=A.JOBTYPE)) AND (C.FROMDATE>{TS ''1900-01-01 00:00:00.000''})) AND ((C.TODATE={TS ''1900-01-01 00:00:00.000''})))))) GROUP BY A.JOBTYPE ORDER BY A.JOBTYPE ',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5

resulting in this execution plan (and unfortunately the same multiple-second execution times).

I've tried several things such as dropping cached plans, adding options in the query inside the cursor definition, ... But none of them seem to get me a parallel plan.

I've also searched google for quite a bit looking for parallelism limitations of cursors but can't seem to find any limitations.

Am I missing something obvious here?

The actual SQL build is SQL Server 2008 (SP1) - 10.0.2573.0 (X64) which i realise is unsupported, but I cannot upgrade this instance as I see fit. I would need to transfer the database to another server and that would mean pulling a fairly large uncompressed backup over a slow WAN.

Trace flag 4199 doesn't make a difference, and neither does OPTION (RECOMPILE).

The cursor properties are:

API | Fast_Forward | Read Only | Global (0)

Answer 1

FAST_FORWARD cursors do not support parallelism (though the server generating the plan would need to be 2012 or above to get NonParallelPlanReason as part of the showplan XML).

When you specify FAST_FORWARD, the optimizer chooses between STATIC and DYNAMIC for you.

The provided execution plan shows the optimizer choosing a static-like plan. Because the query contains aggregation, I doubt a dynamic cursor plan is even possible here. Nevertheless, requesting a FAST_FORWARD cursor type is preventing a parallel plan.

You should change the cursor type explicitly to either STATIC or KEYSET, for example. Both these cursor types can use parallelism.

That said, because this is an API cursor, changing the type of cursor would likely require an application change. Naturally, you would need to benchmark performance to check that changing the cursor type really is the best option for you.