Hints not working inside blocks

[SiPurdy]

Strange one.

Trying to activate/deactivate workflows through a script (trying to align ownership)

When I have a query like the following:

update workflow set
    statecode=1,
    statuscode=-1
from workflow
inner join solutioncomponent on workflow.workflowid = solutioncomponent.objectid
inner join solution on solutioncomponent.solutionid = solution.solutionid
where
    workflow.category in (0,3,5) and -- 0=Workflow, 3=Action, 5=Flow
    workflow.type = 1 and -- Definition
    workflow.statecode != 1 and
    solution.uniquename = '<UNIQUENAME>'
OPTION (MAXDOP 1, USE HINT('BATCH_SIZE_1' , 'USE_LEGACY_UPDATE_MESSAGES'))

The hints all take affect, queries are run in series 1 at a time using SetState requests :).

If I have a similar query but inside a condition the hints aren't followed, for example

if (@workflowOwnerId is not null) begin
    update workflow set
        statecode=0,
        statuscode=-1
    from workflow
    inner join solutioncomponent on workflow.workflowid = solutioncomponent.objectid
    inner join solution on solutioncomponent.solutionid = solution.solutionid
    where
        workflow.category in (0,3) and -- 0=Workflow, 3=Action
        workflow.type = 1 and -- Definition
        workflow.statecode != 0 and
        solution.uniquename = '<UNIQUENAME>' and
        workflow.ownerid != @workflowOwnerId
    OPTION (MAXDOP 1, USE HINT('BATCH_SIZE_1' , 'USE_LEGACY_UPDATE_MESSAGES'))   
end

What's happening is that the MAXDOP and the USE_LEGACY_UPDATE_MESSAGES hints aren't being followed (it does seem to follow the batch size) and I'm getting errors around concurrent WorkflowSetState actions.

Am I hinting right?

[MarkMpn]

Thanks - yes, it looks like the IF condition is overwriting the hints applied to the inner statements. I’ll take a look at this for the next release.

[SiPurdy]

Thanks I think I owe you beer, coffee or both 😀. Any estimate on v.next release date? It does seem a massive release with a few bits I'm interested in.

[MarkMpn]

I'm hoping to release it sometime in April - just doing some more testing on the conversions using the latest FetchXML features and I want to get the equivalent FetchXML -> SQL conversions in place as well.