Difference in behaviour between `DBCC TRACEON` and `QUERYTRACEON` I am using SQL server 2014, but my database version is SQL server 2012. So I am trying to use trace flag 2312 to force the query optimizer to use cardinality estimator v120.

I have noticed through my own testing on a specific query:


With this trace flag active the query takes about 20 minutes, but in SSMS results come back all at once after 20 minutes
Without the trace flag active the query takes about 30 minutes, but the first few results come back instantly and continue to be added.


I have no issues with trace flags in SSMS, the trace flag seems to be properly set regardless of whether i set a global trace flag using DBCC traceon(2312, -1) or I add the option option (Querytraceon 2312).

The problem is when I try to run this query through my .NET application, if my query has the querytraceon option, then IDBCommand.ExecuteReader() seems to hang until the entire query completes, which seems like what I expect given my results in SMSS. However, if I globally enable the traceflag, but remove the querytraceon option from my query, then IDBCommand.ExecuteReader() finishes right away. 

To me this seems like my query is not picking up the trace flag 2312, even though it is globally enabled for all sessions.

What could cause the discrepancy here?

I have added the xml execution plan for my plan with the trace flag enabled globally:
(broken into 2 parts due to size)


https://paste.city/98
https://paste.city/103


The plan for the trace flag enabled using querytraceon:
(broken into 2 parts due to size)


https://paste.city/100
https://paste.city/102


trace flag disabled:
https://paste.city/99
The query you were sending from the .NET application was still likely to be using the "old" plan (which used the legacy cardinality estimator).  

When you enabled the trace flag globally, and then removed the QUERYTRACEON hint, the query text was different, resulting in a new plan being compiled for the query.  This newly compiled plan used the newer cardinality estimator, and thus met your expectations related to the plan shape, and having results return immediately.

Changing the query text in this case had the same effect as when you added OPTION (RECOMPILE) in SSMS while doing your testing.

Regarding your follow up question in the comments:


  although I am curious, why is it that we have to do this when a trace flag is enable/disabled?


The reason for this is that enabling trace flag 2312 globally does not clear the execution plan cache.  So for queries that you need to leverage the new CE, you'll need 


to manually evict those plans from the cache one by one (with DBCC FREEPROCCACHE (plan_handle)), 
temporarily add OPTION (RECOMPILE) to them, or 
just clear the entire plan cache (if you need most queries to use the new CE, and your environment can handle this action).