About T-SQL recompilation, with RECOMPILE and option (RECOMPILE) is the difference between stored procedure-level recompilation and SQL statement-level recompilation only?

The source of this article: http://www.cnblogs.com/wy123/p/6262800.html

There are two ways to implement forced recompilation when considering recompiling T-SQL (or stored procedures), if you ignore other factors that cause recompilation, such as rebuilding indexes, updating statistics, and so on.
One is based on the stored procedure level recompilation with RECOMPILE, and the other is option (RECOMPILE)-based statement-level recompilation.
Previously understood relatively shallow, just think is the former is to compile all the statements in the entire stored procedure, the latter is to recompile a stored procedure in a statement, nor is it just a little bit different.
In fact, in some specific cases, the difference between the two is not just the difference between stored procedure-level recompilation and statement-level recompilation,
From the execution plan generated by the compilation, the intrinsic mechanism differences between the two methods of forced compilation are still relatively large.
Here comes another question: the Parameter embedding optimization (how to translate?) There is no Chinese reference to the Parameter embedding optimization, reluctantly translated as "parametric implant optimization")

This article uses a simple example to illustrate the difference between the two. Here, first of all, thank uest Students for the reference and guidance suggestions.

Differences in use with RECOMPILE and OPTION (RECOMPILE)

For recompilation of stored procedure levels, the typical usage is as follows, specifying "with RECOMPILE" after stored procedure parameters

CREATE PROCEDURETestrecompile_withrecompile (@p_parameter int) withRECOMPILE asBEGIN    SETNOCOUNT on; SELECT *  fromTestrecompileWHEREId= @p_parameter OR @p_parameter  is NULLENDGO

For statement-level recompilation, the typical usage is to specify option at the end of a SQL statement (RECOMPILE)

CREATE PROCEDURETestrecompile_optionrecompile (@p_parameter VARCHAR( -)) asBEGIN    SETNOCOUNT on; SELECT *  fromTestrecompileWHEREId= @p_parameter OR @p_parameter  is NULL OPTION(RECOMPILE)ENDGO

As a rule, build a test environment first
Create a testrecompile table, which is the table used in the stored procedure above, insert 100W rows of data, create an index named IDX_ID on the ID field

CREATE TABLETestrecompile (Idint, Valuevarchar( -))GODECLARE @i int = 0 while @i<=1000000BEGIN    INSERT  intoTestrecompileVALUES(@i,NEWID())    SET @i = @i+1ENDCREATEINDEXidx_id ontestrecompile (Id)GO

Similarities and differences in the execution plan generated by recompile with RECOMPILE and OPTION (RECOMPILE)

If you say that with recompile stored procedure-level recompilation and option recompile, the SQL statement-level recompilation effect is the same,
It is known from the stored procedure above that there is only one SQL code in the stored procedure, and that the recompilation of the stored procedure level and the compilation of the SQL statement level are all compiled by the SQL
If this is the case, the execution plan should be the same when the same parameters are entered, so what is the difference?

First of all, testrecompile_withrecompile the execution plan for this stored procedure, you can see that it is an index scan

　　

Then take a look at Testrecompile_optionrecompile's execution plan and bring in the same parameters

　　

At this point, it can be seen that although the index is used, it is clear that the first statement is an index scan, and the second is an index lookup.
It can be proved that the execution plan generated under the same conditions is not the same as that specified with RECOMPILE forced recompilation at the stored procedure level and option (RECOMPILE) specified at the SQL statement level.

Why is the execution plan with RECOMPILE forced recompilation and OPTION (RECOMPILE) forced recompilation different?

With recompile forced recompilation is every time the stored procedure is run, it is recompiled according to the current parameter condition.
First of all, we do not struggle to find out why the first method does not use index lookup (seek of index).
In fact formally because of the use of id = @p_parameter OR @p_parameter is null This is caused by the writing, specifically I do explain later.
So why is it possible to use an index for an SQL statement that uses the same wording in the option (RECOMPILE) Forced recompile stored procedure?
Because there is a "Parameter embedding optimization" compilation problem when forced to recompile with option (RECOMPILE),
In fact, I have not heard of this term before, literal translation is "parameter implantation compilation" (Do not know just inappropriate)
OPTION (RECOMPILE) forces recompilation to some extent to enhance and optimize the effect of recompilation,
Reference here: Https://sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options, The analysis of the article is extremely good, the case is very exciting

This is explained in the original text:
The Parameter embedding optimization takes this process a step further:query parameters is replaced with literal Constan T values during query parsing.
The parser is capable of surprisingly complex simplifications, and subsequent query optimization may refine things even Fu Rther.
The meaning of the translation is:
The parameter embedding optimization mechanism in OPTION (RECOMPILE) is a step further in the way of forced recompilation with RECOMPILE, and the parameter values are replaced by literal constants during the parsing of the query.
The parser magically simplifies complex problems. As for how to simplify, it is strongly recommended to refer to the original text, the case of the demonstration is quite hanging.

As for how to simplify, here is probably the explanation, the original explanation is more detailed and interesting.
First, the SQL statement reads: SELECT * from testrecompile WHERE Id = @p_parameter OR @p_parameter is NULL
After "Implant parameters", the @p_parameter = 123456,sql statement used in the preceding paragraph becomes a select * from testrecompile WHERE Id = 12345 OR 12345 is NULL
Because or 12345 is null, it can even be considered as simplifying the SQL statement directly into the SELECT * from testrecompile where Id = 12345, which, in the current case, is definitely available for indexing.
As a result, OPTION (RECOMPILE) forced the recompilation of SQL after compiling and simplifying, it becomes the following SQL, the explanation here or feel a bit far-fetched, no original persuasive.

Then look back. With RECOMPILE forced recompilation, with RECOMPILE forced recompilation, no option (RECOMPILE) forced recompilation "parser magically simplifies complex problems"
Refer to this link: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
For such queries like where Id = @p_parameter OR @p_parameter is null

This is explained in the above article:
The problem with these types of queries are that there are no stable plan.
The optimal plan differs completely depending on what paramters is passed.
The optimiser can tell that and it plays safe. It creates plans that'll always work. That's (one of the reasons) why is in the first example it is an index scan, not an index seek.
The meaning of the translation is:
The problem with this type of query is that there is no fixed execution plan,
The optimization scheme is based on the specific incoming parameter values,
The optimizer can only guarantee security (plays safe), and the execution plan he creates ensures that it always works correctly.

I add here to explain the IT plays safe in my understanding:
If the @p_parameter parameter is not empty, go to index seek completely without problems.
If @p_parameter is null, then and (id= @p_parameter or @p_parameter is null) This condition is constant, what happens if I go to index seek?
If you continue to execute with index seek, semantically it becomes the lookup of a value with an ID of NULL, which is logically wrong.
Thus, for security reasons (the so-called plays safe), the optimizer can only select one of these indexed scanning scenarios (so-called always-work execution plans)

About option (RECOMPILE) in SQL statement-level recompilation magical magic, he will do the actual recompilation according to the specific parameters, we are doing a test:
This time setting @p_parameter = NULL to see if a reasonable execution plan has been recompiled, yes, this time it generates a full-table scan of the execution plan, and there is no problem.
The only thing that is flawed is that his execution plan does not work in parallel with the way the recompile is forced to recompile. This is also the difference between the build execution plan with RECOMPILE and option (RECOMPILE) forced recompilation
But it cannot be denied that option (RECOMPILE) parameter embedding optimization This optimization mechanism in the forced recompilation of the features

　　

At this point, with the execution plan of the recompile forced recompile, when the passed parameter value is null, a parallel execution plan is generated

　　

Now to explain why it is very strongly not recommended to write this sql:select * from testrecompile WHERE Id = @p_parameter OR @p_parameter is NULL

I also wrote about Http://www.cnblogs.com/wy123/p/5958047.html before, feeling that the reason for the index inhibition problem was not thoroughly explained.

A potentially multiple-condition query SQL is common in development, and the specific query criteria are dependent on user input,
For example, to provide users with three query conditions optional query criteria, the user can enter one, two or three, this is too common, no need to explain
So we're going to construct a solution that adapts to this kind of query.
In the face of this catch-all-queries query, one of the options is similar to this writing
SELECT * from Testrecompile
WHERE (parameter1 = @p_parameter1 OR @p_parameter1 is NULL)
and (Parameter2 = @p_parameter2 OR @p_parameter2 is NULL)
and (Parameter3 = @p_parameter3 OR @p_parameter3 is NULL)
The biggest problem is that there is an index on the query column, and the input parameter received on the query column is not empty, it is suppressed to the use of the index
As shown above, although the index on the ID column is used, index scan is a little bit stronger than the full table scan (table Scan), he and the real use of index seek in efficiency, is completely different,
So we strongly do not recommend using Id = @p_parameter OR @p_parameter is null in the development process,
Of course, when parameter sinffing is not considered, we prefer parameterized dynamic SQL, even if the non-parameterized dynamic SQL (exec executes a patchwork string), than id = @p_parameter or @p_parameter is Null this way good
If someone further asked: why the query criteria in the ID = @p_parameter OR @p_parameter is null this way to suppress the use of the index, it is really a difficult to explain the problem, it is not clear that the explanation is a very embarrassing thing.
This logic is suppressed to the optimal use of the index, as the above analysis, the optimizer does not really use the index SEEK, for the sake of security (so-called plays safe) to consider
I'm starting to get messy again, that is, with RECOMPILE and option (RECOMPILE), there is a sense of imaginative achievement here inexpressible in both ways.
This is the reason why the execution plan for index seek is compiled even though the specific parameter values are known in the process of compiling
I've always found a similar explanation of what I've been reading about the problem here at http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/, and I'm relieved.

Summary: In a simple case, this article explains the difference between the forced recompilation with RECOMPILE and option (RECOMPILE), and the Parameter embedding Optimization (first time heard) optimization mechanism.
Most of the time, I just want to understand and use some of the knowledge, such as casual use with RECOMPILE and option (RECOMPILE),
It is rude to assume that the two methods of forced recompilation differ only in the fact that one is a recompile of the stored procedure level, and one is the SQL statement-level recompilation. When I really took the case test, I found out that it was really different.

The next article writes an equally interesting article similar to this mechanism.

Reference: Https://sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Http://www.sommarskog.se/dyn-search-2005.html

At the same time, thanks again to uest students to provide reference and guidance suggestions.

About T-SQL recompilation, with RECOMPILE and option (RECOMPILE) is the difference between stored procedure-level recompilation and SQL statement-level recompilation only?