• It's very difficult to tell precisely what's occurring when only looking at images. There is a lot of information contained within an execution plan, not simply the graphics. In the future, it would be helpful to upload the .sqlplan files so that it's possible to explore the information within the plan itself.

    Just looking at it, changing the hard coded parameter resulted in a different execution plan. That second execution plan either wasn't optimal for the indexes, etc., or, it's estimated cost wasn't as high as the other plan so parallelism wasn't invoked. Something along those lines was what's involved. Basically, parameter sniffing.

    Your query, as currently construsted, with the hard coded values, is going to get a different execution plan each time that hard coded value is changed. You might want to explore a different construct that takes advantage of paramters which will lead to procedure reuse.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning