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.

![enter image description here][1]


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.

![enter image description here][2]

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

![enter image description here][3]

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)


  [1]: http://i.stack.imgur.com/HfDJC.png
  [2]: http://i.stack.imgur.com/m6TLE.png
  [3]: http://i.stack.imgur.com/ofCcr.png