科技行者

行者学院 转型私董会 科技行者专题报道 网红大战科技行者

知识库

知识库 安全导航

至顶网软件频道SQL Server 2005 批编译、重编译和计划缓存(1)

SQL Server 2005 批编译、重编译和计划缓存(1)

  • 扫一扫
    分享文章到微信

  • 扫一扫
    关注官方公众号
    至顶头条

本文阐述了批处理在 SQL Server 2005 中的缓存和重用方式,并就最大限度重用已缓存的计划提供了相应的最佳实务。另外,文中还说明了一些重新编译批处理的方案,并针对减少或消除不必要的重新编译,给出了最佳实务。

作者:microsoft 来源:microsoft 2007年8月21日

关键字: SQL 缓存 SQL Server SQL Server 2005

  • 评论
  • 分享微博
  • 分享邮件

一、本白皮书的目的

此白皮书的目的有几个。阐述了批处理在 SQL Server 2005 中的缓存和重用方式,并就最大限度重用已缓存的计划提供了相应的最佳实务。另外,文中还说明了一些重新编译批处理的方案,并针对减少或消除不必要的重新编译,给出了最佳实务。本白皮书阐述了 SQL Server 2005 的“语句级重新编译”功能。另外,还介绍了许多工具及实用程序,它们可在查询编译、查询重新编译、计划缓存和计划重用过程中充当很有用的观测工具。我们在整篇文章中对比了 SQL Server 2000 和 SQL Server 2005 的不同表现,以便读者能够更好地了解文中的内容。本文档所列举的语句均适用于 SQL Server 2000 SQL Server 2005。同时,明确指出了这两个 SQL Server 版本在行为上的差异。

本文面向三类读者:

用户:使用、维护并为 SQL Server 开发应用程序的人员。初识 SQL Server 2005 的用户及正从 SQL Server 2000 进行迁移的人员将在这里找到有用的信息。

开发人员:SQL Server 开发人员将在这里找到有用的背景信息。

测试人员和项目经理:本文档将对“SQL Server 2005 中的编译和重新编译”功能提供说明。

二、重新编译:定义

在查询、批处理、存储过程、触发器、被准备语句或动态 SQL 语句(以下称为“批处理”)在 SQL Server 上开始执行以前,批处理将被编译成计划。然后,将执行该计划以便发挥其效力或生成结果。

一个批处理可包含一个或多个 SELECT、INSERT、UPDATE 和 DELETE 语句、存储过程调用(T-SQL“粘连”或控制结构(比如:SET、IF、WHILE、DECLARE)可能使其产生交错)、DDL 语句(比如:CREATE、DROP)以及与权限相关的语句(比如:GRANT、DENY 和 REVOKE)。批处理还可包含 CLR 构造的定义和用法(比如:用户定义的类型、函数、过程和聚合)。

已编译的计划被保存到 SQL Server 的一部分内存中,这部分内存称为计划缓存。将搜索计划缓存以获得重用计划的机会。如果对某个批处理重用计划,就可避免编译工作。请注意,在有关 SQL Server 的文献中,过去所用的“过程缓存”一词在本文中被称为“计划缓存”。“计划缓存”用词更准确,因为计划缓存不仅仅保存存储过程的查询计划。

在涉及 SQL Server 的用语中,上段所提到的编译过程有时会被误认为是“重新编译”,但该过程仅涉及“编译”。

重新编译的定义:假设某个批处理被编译成一个或多个查询计划的集合。在 SQL Server 开始执行任何单独的查询计划之前,服务器将检查该查询计划的有效性(正确性)和最优性。如果某个检查失败了,将重新编译相应查询计划所对应的语句或整个批处理,并可能生成一个不同的查询计划。这种编译称为“重新编译”。

请特别注意,不必预先缓存该批处理的查询计划。实际上,某些批处理类型从不被缓存,但仍能引发重新编译。举个例,有个批处理包含一个大于 8 KB 的文本。假设该批处理创建了一个临时表,并在表中插入了 20 行。所插入的第七行将导致重新编译,但由于其包含的文本较大,将不缓存该批处理。

在 SQL Server 中执行的多数重新编译都是有根据的。有些是为了确保语句的正确性;另一些是为了在 SQL Server 数据库中的数据发生变化时,获得最佳的查询执行计划。然而,重新编译有时会大大延缓批处理执行的速度。这时,就有必要减少进行重新编译的次数。

三、比较 SQL Server 2000 和 SQL Server 2005 中的重新编译

在 SQL Server 2000 中重新编译批处理时,将重新编译批处理中的所有语句,而不仅仅是触发重新编译的语句。SQL Server 2005 在该行为上做了改进,只编译导致重新编译的语句,而不是整个批处理。与 SQL Server 2000 相比,这一“语句级重新编译”功能将改善 SQL Server 2005 的重新编译行为。尤其,在批处理重新编译过程中,SQL Server 2005 所用的 CPU 时间和内存更少,而得到的编译块也更少。

语句级重新编译有一个优点显而易见:不必再只是为了减少较长的存储过程的重新编译罚点,而将其分成多个较短的存储过程。

四、计划缓存

在处理重新编译问题之前,本文将用较大的篇幅来讨论查询计划缓存的相关及重要的主题。缓存计划以便重用。如果未缓存查询计划,重用机会将为零。这种计划将在每次执行时被编译,从而导致性能低下。只有在极少数情况下,才不需要进行缓存。本文将在后面指出这些情况。

SQL Server 可缓存许多批处理类型的查询计划。以下列举了这些类型。对于每种类型,我们都说明了重用计划的必要条件。请注意,这些条件不一定充分。稍后,读者将在本文中看到完整的相关内容。

1.

特殊查询。特殊查询是一种包含 SELECT、INSERT、UPDATE 或 DELETE 语句的批处理。SQL Server 要求两个特殊查询的文本完全匹配。文本必须在大小写和空格上都匹配。例如,下列这两个查询不共享相同的查询计划。(出现在本白皮书中的所有 T-SQL 代码段都在 SQL Server 2005 的 AdventureWorks 数据库中。)

SELECT ProductID 
FROM Sales.SalesOrderDetail 
GROUP BY ProductID 
HAVING AVG(OrderQty) > 5 
ORDER BY ProductID 
SELECT ProductID 
  
FROM Sales.SalesOrderDetail 
GROUP BY ProductID 
HAVING AVG(OrderQty) > 5 
ORDER BY ProductId

2.

自动参数化查询。对于某些查询,SQL Server 2005 将常量文本值替换为变量,并编译查询计划。如果后续的查询仅在常量的值上有所不同,那么其将与自动参数化查询相匹配。通常,SQL Server 2005 会对那些查询计划不取决于常量文本的特定值的查询进行自动参数化处理。

附录 A 包含一个语句类型列表,SQL Server 2005 将不对其上列出的语句类型进行自动参数化处理。

作为 SQL Server 2005 中自动参数化的一个示例,下列这两个查询可重用查询计划:

SELECT ProductID, SalesOrderID, LineNumber 
 FROM Sales.SalesOrderDetail  
WHERE ProductID > 1000 
ORDER BY ProductID 
  
SELECT ProductID, SalesOrderID, LineNumber 
 FROM Sales.SalesOrderDetail  
WHERE ProductID > 2000 
ORDER BY ProductID

上方查询的自动参数化形式为:

SELECT ProductID, SalesOrderID, LineNumber 
 FROM Sales.SalesOrderDetail  
WHERE ProductID > @p1 
ORDER BY ProductID

当出现在查询中的常量文本的值会影响查询计划时,将不对该查询进行自动参数化处理。这类查询的查询计划将缓存,但同时会插入常量,而不是占位符(比如:@p1)。

SQL Server 的“showplan”功能可用于确定是否已对查询进行了自动参数化处理。例如,可在“set showplan_xml on”模式下提交查询。如果结果 showplan 包含诸如 @p1 和 @p2 等占位符,那么将对查询进行自动参数化处理;否则将不进行自动参数化处理。XML 格式的 SQL Server showplan 还包含在编译时(‘showplan_xml’和‘statistics xml’模式)和执行时(仅‘statistics xml’模式)的参数值的相关信息。

3.

sp_executesql 过程。这是促进查询计划重用的方法之一。当使用 sp_executesql 时,用户或应用程序将明确地确定参数。例如:

EXEC sp_executesql N'SELECT p.ProductID, p.Name, p.ProductNumber  
FROM Production.Product p  
INNER JOIN Production.ProductDescription pd  
ON p.ProductID = pd.ProductDescriptionID  
WHERE p.ProductID = @a', N'@a int', 170 
  
EXEC sp_executesql N'SELECT p.ProductID, p.Name, p.ProductNumber 
FROM Production.Product p INNER JOIN Production.ProductDescription pd 
ON p.ProductID = pd.ProductDescriptionID  
WHERE p.ProductID = @a', N'@a int', 1201

可通过逐一列举来指定多个参数。实际的参数值遵照相应的参数定义。根据查询文本(sp_executesql 后的第一个参数)的匹配情况以及查询文本(上例中的 N'@a int')后面所带的所有参数来预测计划重用机会。不考虑参数值(170 和 1201)的文本是否匹配。因此,在上述例子中,两个 sp_executesql 语句会发生计划重用。

4.

预备的查询.该方法——类似于 sp_executesql 方法——还促进了查询计划重用。在“准备”时发送批处理文本。SQL Server 2005 通过返回一个句柄(可用于在执行时调用批处理)进行响应。在执行时,一个句柄和一些参数值会被发送到服务器。ODBC 和 OLE DB 通过 SQLPrepare/SQLExecuteICommandPrepare 显露该功能。例如,使用 ODBC 的代码段可能如下所示:

SQLPrepare(hstmt, "SELECT SalesOrderID, SUM(LineTotal) AS SubTotal 
FROM Sales.SalesOrderDetail sod 
WHERE SalesOrderID < ? 
GROUP BY SalesOrderID 
ORDER BY SalesOrderID", SQL_NTS)
SQLExecute(hstmt)

5.

存储过程(含触发器)。存储过程设计用于促进计划重用。计划重用基于存储过程或触发器的名称。(但是,无法直接调用触发器。)SQL Server 在内部将存储过程的名称转化为 ID,而随后的计划重用将根据该 ID 的值来进行。触发器的计划缓存和重新编译行为与存储过程略有不同。我们将在本文档的适当位置指出这些不同之处。

当首次编译一个存储过程时,执行调用所提供的参数的值被用于优化该存储过程中的语句。这个过程被称为“参数嗅探”。如果这些值都是典型的,那么针对该存储过程的所有调用将从一个高效的查询计划中获益。本文随后将讨论可用于防止缓存带有非典型的存储过程参数值的查询计划。

6.

批处理.如果批处理文本完全匹配,那么将对相应的批处理进行查询计划重用。文本必须在大小写和空格上都匹配。

7.

通过 EXEC ( ...) 执行查询。SQL Server 2005 可缓存通过 EXEC 提交的字符串以便执行。这些字符串称为“动态 SQL”。例如:

EXEC ( 'SELECT *' + ' FROM Production.Product pr  
INNER JOIN Production.ProductPhoto ph' + '  
ON pr.ProductID = ph.ProductPhotoID' +  
' WHERE pr.MakeFlag = ' + @mkflag )

计划重用基于在执行语句时将变量(比如:上例中的 @mkflag )替换为其实际值后得到的连锁字符串。

    • 评论
    • 分享微博
    • 分享邮件
    邮件订阅

    如果您非常迫切的想了解IT领域最新产品与技术信息,那么订阅至顶网技术邮件将是您的最佳途径之一。

    重磅专题
    往期文章
    最新文章