Recompile Command Performance Effect

Question

We use .net core Ef .netcore version 8.0. We use an interseptor to manipulete each EF s sql commandS. This interceptor adds OPTION(RECOMPILE) to each sql query. Could you please tell me it is neccessary to use this command.

Answer 1

Adding OPTION(RECOMPILE) to each query seems to be over the top, and depending on the nature of the queries submitted have grave detrimental effects. I will try to explain this is more detail.

The normal procedure is that when you submit a query, SQL Server compiles the query and saves the query plan into the cache. Next time the very same query is submitted, the cached plan is reused. Thus, the cost of compilation is reduced.

However, this does not always work out well, because of the nature of the query. For instance, consider a query like:

  SELECT ... FROM Orders WHERE OrderDate > @date

There is a non-clustered index on OrderDate. Different users may pass very different values for @date. If @date is like today, using the non-clustered index is a good idea. Not so if @date is the beginning of yesteryear.

For this reason, you may want to use OPTION(RECOMPILE) to always get a plan that fits the submitted parameters. In the example, it is a very simple query, but imagine a very complex query that no matter what will run for at least one minute, but with bad luck would run for days. OPTION (RECOMPILE) is certainly a good idea, since the cost of compilation is negligible compared to the total execution time.

On the other hand, consider a statement on the line of:

INSERT tbl(...)
   VALUES(@a, @b, ...)

And there are many of these submitted in rapid succession. Here OPTION(RECOMPILE) will add a considerable overhead. (Another story is that this pattern is itself bad practice. The rows should be passed through a TVP, XML or similar.)

If the interceptor blindly adds OPTION(RECOMPILE) that strikes me as a very bad idea. Possibly, it could be a good idea, if it was a little more intelligent, and analysed the queries and only added OPTION(RECOMPILE) for queries of a certain pattern. I am not sure that I know what that pattern would be though.