预编译sql查询语句_频繁的查询重新编译– SQL查询性能的杀手–简介

预编译sql查询语句

In this article, we will explain what compilations and recompilations are, and give recommendations for creating reusable queries to keep the pressure off your processor.

在本文中，我们将解释什么是编译和重新编译，并提供有关创建可重用查询的建议，以减轻处理器的负担。

什么是汇编？ (What is a compilation?)

A compilation is the process when a stored procedure’s query execution plan is optimized, based on the current database and database objects state. This query execution plan is then stored in cache and can be quickly accessed.

编译是基于当前数据库和数据库对象状态优化存储过程的查询执行计划时的过程。 该查询执行计划然后存储在缓存中，并且可以快速访问。

When a query is executed, it’s sent to the parser first. The parser checks the query syntax and stops the execution if the syntax is incorrect.

执行查询后，它将首先发送到解析器。 语法分析器检查查询语法，如果语法不正确，则停止执行。

 
SELECT *
  FROM person.address;
INERT INTO Person.Address1 (AddressID, AddressLine1, AddressLine2)
VALUES(1, N'1970 Napa St.', NULL)
    

If multiple syntax errors exist in the submitted query (in this example both in the first and the third line), the parser will stop when it reaches the first one.

如果提交的查询中存在多个语法错误（在此示例中，第一行和第三行均如此），则解析器在到达第一个查询时将停止。

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘*’.

Msg 102，第15级，状态1，第1行
'*'附近的语法不正确。

When the syntax is correct, the query goes further to the algebrizer. The algebrizer finds all objects used in the query, verifies their names, finds the data types used, checks whether aggregate functions are used, and based on the information collected creates a syntax-based optimization.

当语法正确时，查询将进一步进入代数器。 代数查询器查找查询中使用的所有对象，验证其名称，查找使用的数据类型，检查是否使用了聚合函数，并基于收集的信息创建基于语法的优化。

A query compilation consists of the processes executed in the parser and algebrizer. A complied plan is saved in cache.

查询编译包含在解析器和代数器中执行的过程。 符合计划的计划保存在缓存中。

什么是重新编译？ (What is recompilation?)

A recompilation is the same process as a compilation, just executed again. If the database structure or data change significantly, a recompilation is required to create a new query execution plan that will be optimal for the new database state and ensure better procedure performance.

重新编译与编译的过程相同，只是再次执行。 如果数据库结构或数据发生重大变化，则需要重新编译以创建新的查询执行计划，该计划对于新的数据库状态将是最佳的，并确保更好的过程性能。

A recompilation degrades SQL Server performance, as SQL Server is performing the same action multiple times, instead of using its resources for other important actions. If you could use the cached execution plan, instead of recompiling the stored procedure again, that would make query execution faster for the time needed for a recompilation.

重新编译会降低SQL Server的性能，因为SQL Server会多次执行同一操作，而不是将其资源用于其他重要操作。 如果可以使用缓存的执行计划，而不必再次重新编译存储过程，则可以使查询的执行速度更快，达到重新编译所需的时间。

Therefore, it’s recommended to have reusable query execution plans.

因此，建议制定可重复使用的查询执行计划。

Recompilation cannot be completely eliminated, but you should watch for a large number of recompilations which indicates that queries are reused, but their query execution plan is not. Also, a large number of compilations should be investigated, as that means that new queries are excessively compiled, and not reused. In some cases, it’s recommended to check the compilation/recompilation ratio.

重新编译不能完全消除，但是您应该注意大量的重新编译，这表明查询已被重用，但查询执行计划却没有。 另外，应调查大量的编译，因为这意味着新的查询会被过度编译，而不会被重用。 在某些情况下，建议检查编译/重新编译比率。

If the recompilations occur more than expected, find the stored procedures that are frequently recompiled. You can use SQL Profiler, or a SQL Server monitoring tool. Then determine why the stored procedure is frequently recompiled instead of reused from cache, and finally fix the problem.

如果重新编译发生的次数超出预期，请查找经常重新编译的存储过程。 您可以使用SQL事件探查器或SQL Server监视工具。 然后确定为什么要频繁地重新编译存储过程而不是从高速缓存中重新使用存储过程，最后解决该问题。

什么是参数化？ (What is parameterization?)

One of the mechanisms that SQL Server uses to provide compiled queries to be reused is using parameterization. For example, we will create a query execution plan for the following query that contains the specific value for the AddressID.

SQL Server用于提供要重用的已编译查询的机制之一是使用参数化。 例如，我们将为以下查询创建查询执行计划，该查询包含AddressID的特定值。

 
DELETE FROM Person.Address1
  WHERE AddressID = 100000
    

If no parameterization was used, it would mean that each time the same query is executed, but with different values for the AddressID, it would have to be recompiled. As shown in the query execution plan, this is not the case, as the @1 parameter is used instead of the exact value, so the query execution plan can be reused for every AddressID value.

如果未使用参数化，则意味着每次执行相同的查询，但AddressID的值不同时，都必须重新编译。 如查询执行计划中所示，情况并非如此，因为使用@ 1参数代替确切的值，因此可以对每个AddressID值重用查询执行计划。

查询何时重新编译？ (When is a query recompiled?)

As described above, a compiled query execution plan is stored in cache.

如上所述，已编译的查询执行计划存储在高速缓存中。

The queries are automatically recompiled when:

在以下情况下，查询将自动重新编译：

A query is executed using a RECOMPILE query hint.

使用RECOMPILE查询提示执行查询。

There are three query hints that define query execution plan use. Query hints are executed on the statement level, so they don’t affect the whole stored procedure or query.

有三个查询提示定义了查询执行计划的使用。 查询提示在语句级别执行，因此它们不会影响整个存储过程或查询。

RECOMPILE – specifies that after the query is executed, its query execution plan stored in cache is removed from cache. When the same query is executed again, there will be no existing plan in cache, so the query will have to be recompiled. This is an alternative to using the WITH RECOMPILE clause in stored procedures; it is useful if you want to recompile only some of the statements in the stored procedure, not all of them.

RECOMPILE –指定在执行查询后，从缓存中删除存储在缓存中的查询执行计划。 当再次执行同一查询时，缓存中将没有现有计划，因此必须重新编译该查询。 这是在存储过程中使用WITH RECOMPILE子句的替代方法。 如果只想重新编译存储过程中的某些语句，而不是全部，则很有用。

 
CREATE PROCEDURE <name>
Query1
Query2 OPTION (RECOMPILE)
Query3
        

For example:

例如：

 
SELECT *
FROM Sales.SalesOrderDetail OPTION (RECOMPILE)
        

KEEP PLAN – specifies that a query execution plan is not recompiled when it normally is. When the table index column changes due to an INSERT, DELETE, UPDATE, or MERGE statement, the query execution plan is recompiled. The number of changed rows that triggers a recompilation is different for temporary and permanent tables and depends on the number of rows in the table. The threshold value is higher for permanent tables. For example, if a temporary table has less than 6 rows, the plan will be recompiled if 6 new rows are added. For the permanent table with 6 rows, 500 new rows must be added to trigger a recompilation. The KEEP PLAN raises this threshold, so the recompilations occur less frequently.

KEEP PLAN –指定在正常情况下不重新编译查询执行计划。 当表索引列由于INSERT，DELETE，UPDATE或MERGE语句而更改时，将重新编译查询执行计划。 临时表和永久表的触发重新编译的已更改行数不同，并且取决于表中的行数。 永久表的阈值较高。 例如，如果一个临时表少于6行，则如果添加6个新行，则将重新编译该计划。 对于具有6行的永久表，必须添加500个新行以触发重新编译。 KEEP计划提高了此阈值，因此重新编译的频率降低了。

 
CREATE TABLE #t (Id INT, Address1 [nvarchar](60), Address2 [nvarchar](60), Town [nvarchar](30))
 
INSERT #t
SELECT [AddressID], [AddressLine1], [AddressLine2], [City]
FROM Person.Address
 
SELECT count(*)
FROM #t WHERE ID = 37 OPTION (KEEP PLAN)
        

KEEPFIXED PLAN – specifies that a query execution plan is never recompiled due to index column changes and changes in statistics. When this query hint is used, the plan is recompiled only if the schema of tables used in the query is changed or the sp_recompile stored procedure is executed on these tables. The example above applies here as well; the only difference is OPTION (KEEPFIXED PLAN) instead of OPTION (KEEP PLAN).

KEEPFIXED PLAN –指定永远不要由于索引列更改和统计信息更改而重新编译查询执行计划。 使用此查询提示时，仅当更改查询中使用的表的模式或对这些表执行sp_recompile存储过程时，才重新编译计划。 上面的示例也适用于此。 唯一的区别是OPTION（保持计划）而不是OPTION（保持计划）。

A WITH RECOMPILE option us used in a CREATE PROCEDURE statement or in an EXECUTE statement when the procedure is called

在调用过程时，在CREATE PROCEDURE语句或EXECUTE语句中使用WITH WITH COMCOMLE选项

 
CREATE PROCEDURE Person.AddressLines @AdrID INT = '32'
	WITH RECOMPILE
AS
SELECT *
FROM Person.Address
WHERE AddressID = @AdrID;
GO
        

The query execution plan is not stored in cache after the stored procedure is executed, so it will be recompiled each time it’s executed

执行存储过程后，查询执行计划未存储在缓存中，因此每次执行时都会重新编译查询计划

When executing a stored procedure with a WITH RECOMPILE option in the EXECUTE statement, a new query execution plan is created and used for this specific execution, but it’s not stored in cache. If there is already a plan in cache for this specific stored procedure, it’s intact.

当在EXECUTE语句中使用WITH RECOMPILE选项执行存储过程时，将创建一个新的查询执行计划并将其用于此特定执行，但不会存储在缓存中。 如果缓存中已经有针对该特定存储过程的计划，则该计划是完整的。

 
EXECUTE dbo.uspGetBillOfMaterials WITH RECOMPILE
    

The sp_recompile system stored procedure is used

使用了sp_recompile系统存储过程

The stored procedure removes an existing query execution plan for a specific stored procedure or query from cache, so they are recompiled the next time they are called.

存储过程从高速缓存中删除了特定存储过程或查询的现有查询执行计划，因此在下次调用它们时会对其进行重新编译。

 
EXEC sp_recompile N'dbo.uspGetBillOfMaterials'
        

When the stored procedure is executed, the following message is shown

当执行存储过程时，显示以下消息

Object ‘dbo.uspGetBillOfMaterials’ was successfully marked for recompilation.

对象“ dbo.uspGetBillOfMaterials”已成功标记为重新编译。

In this article, we explained what compilations, recompilations, and parameterization are. We showed how to recompile a query using T-SQL query hints, options, and stored procedures. In the next part of this article, we will show how to detect frequently recompiled queries.

在本文中，我们解释了什么是编译，重新编译和参数化。 我们展示了如何使用T-SQL查询提示，选项和存储过程重新编译查询。 在本文的下一部分中，我们将展示如何检测经常重新编译的查询。

翻译自: https://www.sqlshack.com/frequent-query-recompilations-sql-query-performance-killer-introduction/

预编译sql查询语句