I am using SQL Server 2014, but the version of my database is SQL Server 2012. I am therefore trying to use the 2312 trace flag to force the query optimizer to use the v120 cardinality estimator.
I've noticed through my own tests on a specific query:
- When this trace flag is enabled, the query takes about 20 minutes, but in SSMS, the results are returned all at once after 20 minutes.
- If the trace flag is not enabled, the query takes about 30 minutes, but the first results are instantly returned and continue to be added.
I have no problem with the trace flags in SSMS, the trace flag seems to be set correctly, whether or not I set a global trace flag with the help of
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 request has the
querytraceon option, then
IDBCommand.ExecuteReader () seems to hang until the end of the query, which seems to be what I expect from my results in SMSS. However, if I globally activate the traceflag, but I delete the
querytraceon option of my query and then
IDBCommand.ExecuteReader () ends right away.
For me, this seems like my query does not get the trace flag 2312, even if it's enabled globally for all sessions.
What could cause the difference here?