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)
The plan for the trace flag enabled using querytraceon: (broken into 2 parts due to size)
trace flag disabled: https://paste.city/99