2

After our SQL Server upgraded from 2012 to 2014, some stored procedure's performance has decreased.

We found that query hint can be used to solve this problem, however I wonder how can we add query hint in a SQL of following structure:

INSERT INTO TABLE1 ....
    SELECT ...
    FROM TABLE2
    --OPTION (QUERYTRACEON 9481)

If I simply add it at the end of the snippet (the commented line), is it affecting the SELECT query, the INSERT query, or both? How about if I would like to use query hint on SELECT or INSERT only?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
shole
  • 4,046
  • 2
  • 29
  • 69

2 Answers2

1

If you use this trace flag, what you are doing is that you instruct the query optimizer to use the 2012 cardinality estimator. You can do this on query level as stated in the documentation:

If a trace flag affects any query execution plan in an unwanted way, but improves some other query execution plan, you may want to enable a corresponding trace flag for only a particular query.

If you use it as you are doing it now, it will affect the whole INSERT INTO SELECT statement, but if you only want one of the queries to be executed using the old cardinality estimator, you need to change your statement into two different queries (for example insert data into a temp table and then select into your table) so that you only use the trace on one of those queries.

You can read more about this also on this StackOverflow post.

Community
  • 1
  • 1
Rigerta
  • 3,959
  • 15
  • 26
0

Trace flag 9481 forces the query optimizer to use version 70 (the SQL Server 2012 version) of the cardinality estimator when creating the query plan (msdn) It certainly affects all the operates in your query, both insert and select, but what could you expect to be different in the insert operator? The cardinality estimation affects join strategies, and insert does not perform ani scan/seek/join

sepupic
  • 8,409
  • 1
  • 9
  • 20