SQL Server: sp_recompile side effects

In sqlserver, there is a system stored procedure: sp_recompile, which can force re-compile the execution plan for the SQL statements related to access to recompile [stored procedure, trigger, table, view!

 

IfObjectIs the name of the stored procedure or trigger. The stored procedure or trigger will be re-compiled at the next runtime. IfObjectIs the name of a table or view. All stored procedures that reference the table or view will be re-compiled at the next runtime.

 

Queries used by stored procedures and triggers are optimized only during compilation. After the database is indexed or other changes that may affect database statistics, compiled stored procedures and triggers may lose efficiency. You can re-compile the stored procedure and trigger acting on the table to optimize the query. Microsoft SQL Server automatically recompiles stored procedures and triggers at ease.

 

However, this statement does not always have a positive effect and sometimes makes the execution plan worse. The reason is being further explored ..........???????

 

<Appendix>

Cache and reuse of execution plans

Microsoft SQL Server 2000 has a memory pool for storing execution plans and data buffers. The percentage allocated to the execution plan or data buffer in the pool dynamically fluctuates with the system status. The part of the memory pool used to store execution plans is called process cache.

The SQL Server 2000 Execution Plan contains the following two main components:

• Query plan

  The main body of the Execution Plan is a reentrant read-only data structure, which can be used by any number of users. This is called a query plan. The user environment is not stored in the query plan. The query plan will never have one or more replicas in the memory: One replica is used for all serial executions, and one replica is used for all parallel executions. Parallel copies overwrite all parallel executions, regardless of the parallel execution degree.

• Execution Environment

  Each user performing a query has a data structure that contains the specific data (such as the parameter value) for execution. The data structure is called the execution environment. The execution environment data structure can be reused. If a user executes a query and a structure is not used, the structure will be reinitialized in the new user's environment.

When executing any SQL statement in SQL Server 2000, the relational engine first checks whether there is an existing execution plan for the same SQL statement in the process cache. SQL Server 2000 re-uses any existing plan found to save the overhead of re-compiling SQL statements. If no execution plan exists, SQL Server 2000 generates a new execution plan for the query.

SQL Server 2000 has an efficientAlgorithmTo find the existing execution plans for any specific SQL statement. In most systems, this scan consumes less resources than it saves by reusing an existing plan instead of compiling each SQL statement.

This algorithm matches the new SQL statement with the existing unused execution plan in the cache, and requires that all object references are completely legal. For example, the first statement in the two select statements does not match the existing plan, and the second statement does:

 
Select * from employees
 

 
Select * From northwind. DBO. Employees
 

Aging of execution plan

After the execution plan is generated, it will reside in the process cache. Only when space is required, SQL Server 2000 will aging the old unused plan from the cache. Each query plan and execution environment have related cost factors, which indicate the cost required for compiling the structure. The data structure also has an age field. Each time an object is referenced by a connection, its age field increases progressively according to the compilation cost factor. For example, if the cost factor of a query plan is 8 and it is referenced twice, its age will change to 16. The inert writer Process regularly scans the list of objects cached in the process. The inertia writer reduces the age field of each object by 1 for each scan. In this example, the age of the query plan is reduced to 0 after 16-process cache scans, unless other users reference the plan. If the following three conditions are met, the inert writer process releases the object:

• The Memory Manager requires memory and all available memory is in use.
• The age field of the object is 0.
• The object is not currently referenced by the connection.

Because the age field increases every time an object is referenced, the age field of frequently referenced objects will not be reduced to 0, nor will it become aging from the cache. Objects that are not often referenced will soon meet the release conditions, but will not be released unless other objects have memory requirements.

Re-compile the execution plan

Due to the new status of the database, some changes in the database may cause the execution plan to be inefficient or no longer valid. SQL server detects these changes that make the execution plan invalid and marks the plan as invalid. After that, you must re-compile the new plan for the next connection that executes the query. Plan invalidation may be caused:

• make any structural changes (alter table and alter view) to the table or view referenced by the query ).
• the statement (such as update statistics) is used to generate or automatically generate new distribution statistics.
• remove the index used by the execution plan.
• explicitly call sp_recompile .
• a large number of changes to keys (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 rows in the inserted or deleted tables increases significantly.