or
sql-server
Tom V
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
Top Answer
Paul White
`FAST_FORWARD` cursors [do not support parallelism][1] (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][2] 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.


  [1]: http://blogs.msdn.com/b/psssql/archive/2013/08/29/10444849.aspx
  [2]: http://blogs.msdn.com/b/sqlqueryprocessing/archive/2009/08/12/understanding-sql-server-fast-forward-server-cursors.aspx
sp_cursoropen and parallelism

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.