Some discussions about stored procedure recompile caused by SQL Server temporary tables _ MySQL

To ensure that SQL server returns the correct value or has performance concerns, it intends to re-compile the execution plan without reusing the execution plan cached in the memory, it is called recompile ). Which of the following conditions can cause re-compilation of the stored procedure? In order to ensure that the correct value or performance concerns are returned, Luo SQLSERVER intends to re-compile the execution plan instead of the execution plan cached in the memory, it is called recompile ). Which of the following conditions can cause re-compilation of the stored procedure? The following lists some conditions that cause recompile:

-Alter table and alter view ).

-Modify any indexes used in the execution plan.

-UPDATE the STATISTICS used by the execution plan. these updates may be explicitly generated from statements (such as update statistics) or automatically generated.

-Delete the index used by the execution plan.

-Call sp_recompile explicitly.

-A large number of key changes (modifications made by other users to the tables referenced by the query using the INSERT or DELETE statement ).

-For tables with triggers, the number of inserted or deleted tables increases significantly.

-Use the with recompile option to execute the stored procedure.

-Some dbcc freeproccache; separation, additional databases, and data upgrades will also clear the cache execution plan in the memory.

Now, let's take a look at the problem that temporary table data changes cause re-compilation of stored procedures, in fact, the data changes in the temporary table lead to the re-compilation of the stored procedure. In fact, the data changes in the temporary table lead to the automatic update of the statistical information of the temporary table, resulting in re-compilation. So what are the conditions or thresholds that trigger updates of the temporary table statistics? It is also simple, that is, the following formula (n indicates the number of data records in the temporary table before the change, specifically the number of records in the temporary table when the previous statistics are collected)

Temporary table

If n <6, RT = 6.

If 6 <= n <= 500, RT = 500.

If n> 500, RT = 500 + 0.20 * n.

A netizen said that the threshold for changing temporary table data in the stored procedure is incorrect: his original statement is as follows:

If n <6, Recompilation threshold = 6.

If 6 <= n <= 500, Recompilation threshold = 500.

The above two intervals are correct. However, after the value is greater than 500, it is not a re-compilation after the change is greater than 20%. After reading his question, I am not very certain. after all, I have not actually verified it. Practice is the only criterion for testing and sorting. let's start the experiment. First, prepare the Test environment (Microsoft SQL Server 2008 (RTM)-10.0.1600.22 (X64). The script is as follows:

USE MyDBA;GOIF EXISTS(SELECT 1 FROM sys.sysobjects WHERE id=object_id(N'[dbo].[TEST]') AND OBJECTPROPERTY(id, N'IsTable')=1 )BEGIN DROP TABLE dbo.TEST;CREATE TABLE TEST(  ID INT IDENTITY(1, 1) ,  NAME VARCHAR(40) ,  CONSTRAINT PK_TEST PRIMARY KEY(ID))ENDGOINSERT INTO TEST VALUES(NEWID())GO 10000CREATE PROCEDURE Usp_Recompile_TEST(@Index INT)ASBEGIN CREATE TABLE #T(ID   INT , NAME VARCHAR(40)); INSERT INTO #T SELECT ID, NAME FROM TEST WHERE ID <=@Index; SELECT  m.* FROM #T m INNER JOIN TEST n ON m.ID = n.IDENDGO

After preparing the test environment, open SQL Server Profiler, select the "SP: Recompile" and "SP: Complete" events, and then cancel some selection columns, select only required columns, such as EventClass and TextData. As shown below

After enabling Profile tracing, we open a session window, check "including the actual execution plan", and then execute the following SQL statement in the window.

EXEC dbo. Usp_Recompile_TEST 1;

As shown below, in the actual execution plan, we can see that the "estimated number of rows" and "actual number of rows" are consistent.

EXEC dbo. Usp_Recompile_TEST 2;

EXEC dbo. Usp_Recompile_TEST 6;

Execute the preceding two statements and we will find that there is a deviation between "estimated number of rows" and "actual number of rows", because the database does not have the latest statistics for the temporary table # T, the data when the previous statistical information is collected (one row of data)

EXEC dbo. Usp_Recompile_TEST 7; at this time, the collection and update of the statistical information of the temporary table has been triggered (see the description below ).

EXEC dbo. Usp_Recompile_TEST 130;

EXEC dbo. Usp_Recompile_TEST 500;

EXEC dbo. Usp_Recompile_TEST 506;

EXEC dbo. Usp_Recompile_TEST 507;

So execute the preceding SQL statement 130. we are sure that the temporary table # T will not be used to update the statistical information. will 501 trigger the update of the statistical information of the # T table? If it is not triggered, what is the exact value? The answer is 507, as shown below:

Some may say that the results of my experiments are different (a lot). do you really understand the formula below? N indicates the number of records before the temporary table changes (specifically, the number of records when statistics are collected), and the RT indicates the number of records after the change.

Temporary table

If n <6, RT = 6.

If 6 <= n <= 500, RT = 500.

If n> 500, RT = 500 + 0.20 * n.

Since the first execution was EXEC dbo. usp_Recompile_TEST 1. if the number of records in the database is 1, then 1 + 6 = 7; that is, EXEC dbo. when Usp_Recompile_TEST 7 is used, the update of the statistical information of the temporary table # T is triggered. why is it 507 (7 + 500 = 507, the number of records in the temporary table # T is 7, so 7 + 500 = 507. Is it a bit confusing? you should execute this SQL statement as per me, then, use Profile to track and you will see the following results. if you still don't understand it, take a good look:

Dbcc freeproccache;

EXEC dbo. Usp_Recompile_TEST 2;

EXEC dbo. Usp_Recompile_TEST 6;

EXEC dbo. Usp_Recompile_TEST 7;

EXEC dbo. Usp_Recompile_TEST 8;

If you haven't understood it yet, my expression skills have reached the limit. please try again! Next we will focus on verifying and testing.

Dbcc freeproccache;

EXEC dbo. Usp_Recompile_TEST 501;

In this case, the number of records in the temporary table # T is 501. when the number of records in the temporary table # T changes, the update of statistics will be triggered? Because it is insert, the formula should be 501 + (500 + 0.2*501) = 1101.2, so it should be 1101, even 1100 will not change. The following SQL Server Profile can verify our speculation

EXEC dbo. Usp_Recompile_TEST 1100;

EXEC dbo. Usp_Recompile_TEST 1101;

If we continue to use this stored procedure, why does the parameter value trigger Statistics update? 1101 + (500 + 0.2*1101) = 1821.2, that is to say, it must be 1821 to trigger statistics updates. the following SQL Server Profile also verifies our speculation.

EXEC dbo. Usp_Recompile_TEST 1300;

EXEC dbo. Usp_Recompile_TEST 1320;

EXEC dbo. Usp_Recompile_TEST 1321;

EXEC dbo. Usp_Recompile_TEST 1820;

EXEC dbo. Usp_Recompile_TEST 1821;

Therefore, based on the above experimental results, the values of the temporary SQL SERVER tables that lead to recompile in the stored procedure are indeed correct and there is no problem. Of course, if there are any omissions or errors, please note.