SQL Server存储过程重新编译的因素

吴欣
280 阅读8分钟

在这篇文章中，我们将详细探讨导致SQL Server存储过程被重新编译的因素。

简介

SQL Server存储过程是准备好的T-SQL代码，可以被数据库用户反复执行，以执行数据操作命令和数据定义命令。我们可以列出以下4个特点，作为存储过程的好处，我们首先想到的是。

  • 性能
  • 代码重用
  • 可维护性
  • 安全性

存储过程的主要性能优势是它们能够重复使用已编译和已缓存的查询计划。在存储过程的第一次执行中，其执行计划被存储在查询计划缓存中，该查询计划被用于该过程的下一次执行。然而，一些因素会导致重新编译缓存的存储过程查询计划，这个过程被称为存储过程重新编译。存储过程的重新编译有一些优点和缺点。例如，在重建索引或更新统计数据后，存储过程查询计划可能被重新编译，这个新计划通常会更有效。另一方面，冗余的高度重新编译操作会增加CPU的开销，并可能对数据库引擎的性能产生负面影响。

前提条件

在这篇文章中，我们将使用Adventureworks的样本数据库，我们还将使用一个样本存储过程。通过下面的查询，我们可以创建这个样本的SQL Server存储过程。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

CREATEPROCEDUREGetSalesPersonSale

@LineTotal AS FLOAT

帐户

挑选

soh.[SalesPersonID].

,e.[职位名称]

,st.[姓名] AS [SalesTerritory]

,soh.[小计]。

,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear]

FROM [Sales].[SalesPerson] sp

INNER JOIN [Sales].[SalesOrderHeader] soh

ON sp.[BusinessEntityID] = soh.[SalesPersonID]。

INNER JOIN [Sales].[SalesTerritory] st

ON sp.[TerritoryID] = st.[TerritoryID].

INNER JOIN [HumanResources].[雇员] e

ON soh.[SalesPersonID] = e.[BusinessEntityID］

INNER JOIN [Person].[Person] p

ON p.[BusinessEntityID] = sp.[BusinessEntityID]

INNER JOIN Sales.SalesOrderDetail sd

ON sd.SalesOrderID = soh.销售订单ID

WHERE sd.LineTotal>@LineTotal

监控SQL Server存储过程的重新编译

为了监控SQL Server存储过程的重新编译事件，我们可以使用扩展事件中的sql_statement_recompile 事件，这个事件有助于捕获查询优化器执行语句级重新编译的时间。下面的查询创建了一个新的扩展事件，使用sql_statement_recompile事件，并立即启动它。

CREATE EVENT SESSION [MonitorStoredProcedureRecompile] ON SERVER

ADD EVENT sqlserver.sql_statement_recompile(SET collect_statement=(1)

ACTION(sqlserver.client_app_name,sqlserver.database_name,sqlserver.sql_text,sqlserver.username))

with (startup_state=on)

去

ALTER EVENT SESSION MonitorStoredProcedureRecompile

ON SERVER

状态 = 开始

我们可以使用Watch Live Data选项来用于查看扩展事件所收集的数据。我们在SQL Server Management Studio（SSMS）中展开扩展事件文件夹，然后右键单击所创建的扩展事件。

Using the Watch Live Data option of the Extended Events

当我们点击观察实时数据菜单时，将出现实时数据屏幕。为了只显示属于Adventureworks数据库的捕获事件，我们将过滤数据库名称。

Filtering Extended Events

索引是用来提高查询性能的，创建索引后可能会导致重新编译存储过程。现在让我们用一个例子来证明这一点。首先，我们将启用实际的执行计划，然后执行样本存储过程。在执行计划中，我们可以看到在执行计划的顶部有一个缺少索引的建议。

EXEC GetSalesPersonSale 10

Create missing index in an execution plan

我们将右键单击执行计划，并选择缺失索引详细信息选项。缺失索引的详细信息将显示在一个新的查询窗口中。我们将给索引起一个合适的名字，并删除多行注释符号。最后，我们执行索引创建查询并创建索引。

USE AdventureWorks2017

GO

CREATE NONCLUSTERED INDEX IX_Linetotal

ON [Sales].[SalesOrderDetail] ([LineTotal])

去

Create an index for performance

在创建完索引后，我们将执行同样的存储过程。在这个时候，由于新的索引的创建，存储过程将被查询优化器重新编译。这种情况可以在扩展事件中看到所有细节。特别是rempile_cause 显示了重新编译的原因。

Schema changed event for query recompilations

SQL Server存储过程的重新编译和统计

统计数据在直方图中存储了列数据的分布，同时统计数据也被查询优化器用来估计一个查询可以返回多少行。数据的修改会导致统计的准确性下降。在这一点上，如果启用自动更新统计数据选项，统计数据可以在查询的执行过程中自动更新。同时，统计数据也可以手动更新。统计数据的更新操作会引起重新编译。现在，我们将通过一个例子来理解这个概念。下面的查询将向SalesOrderDetail表插入许多行。

INSERT INTO Sales.销售订单细节

(SalesOrderID,CarrierTrackingNumber,OrderQty,ProductID,SpecialOfferID,UnitPrice,rowguid,ModifiedDate)

SELECT SalesOrderID,CarrierTrackingNumber,OrderQty,

ProductID,SpecialOfferID,UnitPrice,NEWID(),ModifiedDate

FROM Sales.销售订单细节

GO 3

数据修改后，我们将用相同的参数执行相同的存储过程。在执行查询的过程中，统计数据将被更新，因此存储过程将被重新编译。

EXECUTE GetSalesPersonSale 10

Statistics changed event for query recompilations

SQL Server存储过程的重新编译和查询库

SQL Server的查询存储功能捕获并存储已执行的查询执行计划和查询运行时的执行统计数据。在查询的退步查询报告中，根据不同的指标（持续时间、CPU、I/O等），显示了随着时间推移性能退步的前25个查询。

Regressed Queries report in the Query Store feature

回归查询报告告诉我们，我们的样本存储过程使用了不同的执行计划，这些计划的性能是不同的。在这一点上，我们可以强制样本存储过程使用任何特定的执行计划。这样，存储过程在每次执行时都会使用相同的执行计划。首先，我们在图中选择一个我们希望在每次执行存储过程时强制使用的查询计划。我们点击Force Plan 按钮，这样在每次执行存储过程时就会使用这个计划。

Regressed Queries graph in the Query Store feature

在信息框中，我们确认强制计划的操作。

Confirming plan forcing in Query Store

在强制执行查询计划后，检查符号将显示在它上面。

Forcing a query plan in the query store

现在，我们将用相同的参数执行我们的存储过程。

EXECUTE GetSalesPersonSale 10

当我们使用查询存储空间强制存储过程使用查询计划时，存储过程将在其第一次执行时被重新编译。recompile_cause属性表示查询存储计划强制政策的改变 解释。

Schema changed event for query recompilations Query Store plan forcing policy changed

SQL Server存储过程的重新编译和临时表

在存储过程中大量使用临时表来存储非永久性的数据。当一个存储过程涉及到一个临时表时，涉及到临时表的查询甚至不会被编译，而是等到第一次执行查询时才会被编译。这个属性被称为延迟编译。例如，下面的查询将创建一个存储过程，将一些行插入到一个临时表中。在这个存储过程的第一次执行中，它将被优化器重新编译，而在下一次执行中，不进行重新编译。

CREATE PROC TempTableCompileDemo

作为

创建 表 #TempSales

(SalesOrderID INT , CarrierTrackingNumber VARCHAR(100))

INSERT INTO #TempSales

SELECT TOP 100 SalesOrderID,CarrierTrackingNumber FROM Sales.帐目

DROP 表 #TempSales

读取

EXEC TempTableCompileDemo

Deferred compile event for query recompilations

正如我们所看到的，上面的图片清楚地告诉我们，存储过程中插入数据到临时表的部分正在被重新编译。在某些情况下，临时表的模式可以在存储过程中修改。然而，这种使用类型有一个缺点，因为每次执行存储过程都会被重新编译。现在让我们通过一个例子来研究这样的问题是如何产生的。在下面的示例过程中，我们将首先创建一个临时表，然后添加一个列。

CREATE PROC TempTableEveryCompileDemo

语法

创建 表 #TempSales

(SalesOrderID INT , CarrierTrackingNumber VARCHAR（100）)

INSERT INTO #TempSales

SELECTTOP 100 SalesOrderID,CarrierTrackingNumberFROM Sales.读取销售订单的详细信息

ALTER TABLE #TempSales ADD NewCol1 INT

UPDATE #TempSales SET NewCol1=0

去

EXECUTE TempTableEveryCompileDemo

GO 10

Temp tables and query store

扩展事件已经捕获了所有的查询重新编译事件和重新编译的原因。在临时表中添加新的列被视为模式变化，所以存储过程在每次执行时都会重新编译。

SQL Server存储过程的重新编译和SET选项

在SET选项的帮助下，我们可以确定SQL Server在会话级别的行为。改变SET选项将导致存储过程的重新编译。例如，当我们把SET CONCAT_NULL_YIELDS_NULL打开时，一个字符串和NULL值的结果会产生一个NULL结果。现在，我们在一个示例存储过程中启用这个选项并执行它。

CREATE PROC TempTableOptionCompileDemo

作为

SELECT 'Hello World' + NULL

设置 concat_null_yields_null关闭。

SELECT 'Hello World' + NULL

去

EXECUTE TempTableOptionCompileDemo

Set options change event for query recompilations

总结

在这篇文章中，我们学习了导致SQL Server存储过程重新编译的各种因素，同时也学习了如何通过使用扩展事件来监控这种重新编译。这些因素被总结为以下几点。

  • 添加一个索引

  • 更新统计数据

  • 在存储过程中使用SET选项

  • 强制存储过程使用不同的查询计划

  • 使用临时表

  • 作者

  • 最近的文章

Esat Erkec

Esat Erkec是一名SQL Server专业人士，8年多前开始了他的职业生涯，成为一名软件开发员。他是一名SQL Server微软认证解决方案专家。

他职业生涯的大部分时间都集中在SQL Server数据库管理和开发方面。他目前的兴趣是数据库管理和商业智能。你可以在LinkedIn上找到他。

查看Esat Erkec发表的所有帖子

Esat Erkec

avatar
文章
阅读
粉丝