1

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

  • A wild guess is that you don't have the same SET options in SSMS and your .NET app. Some SET options will cause the optimizer to create different plans for (i.e., same query isn't the same query because of different setting for some of these SET options). Again, a very wild guess but perhaps withe verifying... – Tibor Karaszi Nov 30 '18 at 18:03
  • 3
    Without more information Guesing is about the best anyone here is going to be able to do for you. If you'd posted your plan XML to where Erik had suggested we'd likely have an answer to yoru question by now. – Zane Nov 30 '18 at 18:17
  • Can you try both again in SSMS with also OPTION (RECOMPILE);? Since the text of your two queries is different, it could be that they got compiled at different times with very different stats, and now those very different plans are getting reused independently. But as has been suggested multiple times, we can make better guesses with the plans. Imagine calling your mechanic and playing an engine sound over the phone and expecting a diagnosis while refusing to bring the car in... – Aaron Bertrand Nov 30 '18 at 18:17
  • 1
    I'm confused. You said you were seeing different behavior when you enabled trace flag 2312 globally via DBCC TRACEON vs. when you enabled trace flag 2312 through OPTION (QUERYTRACEON). But then in your links you say the trace flag is enabled vs. disabled and, in fact, in the query plans, there is zero evidence that you enabled 2312 via QUERYTRACEON. You are missing a node like this: <TraceFlag Value="2312" Scope="Session"></TraceFlag> – Aaron Bertrand Nov 30 '18 at 19:11
  • 1
    No, they are not user-specific, they are session specific. I can connect twice in SSMS with the same credentials and in one session use ANSI_NULLS off, and the other turn them on. Also, the last plan you added is not complete. – Aaron Bertrand Nov 30 '18 at 19:15
1

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.