INF: 存储过程重新编译的疑难解答

文章翻译 文章翻译
文章编号: 243586 - 查看本文应用于的产品
本文的发布号曾为 CHS243586
展开全部 | 关闭全部

本文内容

概要

本文介绍应用程序在使用 Microsoft SQL Server 时可能遇到的一种特定类型的性能问题: ????存储过程的运行时重新编译。 如果您正在解决某个性能问题,但还不能确定这是否是问题的真正原因,请在继续本文之前参考 Microsoft Knowledge Base 中的下列文章:

224587 INF: 解决 SQL Server 中的应用程序性能问题
本文假设通过参阅以上文章缩小了引起问题的范围,并且捕获了对特定事件和其中详细数据列的 SQL Server 事件探查器跟踪。

更多信息

在 SQL Server 7.0 中创建存储过程时,过程的文本存储在 syscomments 系统表中。 在用户执行过程时,如果过程还不在高速缓存中,SQL Server 将从 syscomments(在早期版的 SQL Server 中为 sysprocedures)中加载过程,并编译查询计划。 已编译的计划保存在高速缓存中,并在以后调用存储过程时再次使用,直到发生某个操作使计划无效并强制进行重新编译为止。 下列操作可能会引起存储过程计划的重新编译。
  • 在 CREATE PROCEDURE 或 EXECUTE 语句中使用 WITH RECOMPILE 子句。
  • 对任何过程所引用对象的架构进行更改,包括添加或取消约束、默认值或规则。
  • 对过程所引用的表运行 sp_recompile
  • 恢复包含过程或过程引用的任意对象的数据库(如果执行的是跨数据库操作)。
  • 太多的服务器活动引起计划在高速缓存中超时。
重新编译存储过程的所有这些原因在早期版本中确实存在,并导致在开始执行过程前重新编译过程。 在 SQL Server 7.0 中,引入了一个新功能,该功能使得可以在执行期间重新编译存储过程。 这种新功能可保证优化器对过程内的每个特定语句总是有最可行的计划。 下列事件可能会引起存储过程的运行时重新编译:
  • 存储过程引用的表中的大部分数据发生了更改。
  • 过程交错执行数据定义语言 (DDL) 和数据操作语言 (DML) 操作。
  • 过程在临时表上执行特定的操作。
上述所有情况的原因将在本文下面给予进一步的详细讨论。

在某些情况下,重新编译存储过程的开销远远大于所带来好处,对于大型过程尤其如此。 切记:启动重新编译时,整个批处理或过程均进行重新编译。 这意味着性能的下降直接与过程或批处理的大小成比例。 有关该主题的更多信息,请参阅 SQL Server 7.0 Books Online 中的“Transact-SQL Tips”主题。


本文下面的内容将集中在如何识别引起运行时存储过程重新编译的原因,并讨论可以用来防止重新编译的方法。

最优准则



当作为非所有者执行某个过程时,最好完全限定存储过程名称。 这样做会更明确且更易于当前用户重新使用现有执行计划。 例如,如果一个非数据库所有者 (dbo) 用户执行了数据库所有者 (dbo) 所拥有的 pubs 数据库中的存储过程(在本例中为 myProc),则应当使用下面的语句:
exec pubs.dbo.myProc
而不要使用下面的语句:
exec myProc
这种技术从编码和维护的角度,消除了不同所有者使用不同过程版本的混乱状况,同时也允许 SQL Server 更直接地访问特定过程的执行计划。

识别和解决问题



如果还没有这么做,要得到详细的信息,请参考 Microsoft Knowledge Base 中关于捕获“事件探查器”数据帮助分析系统性能的文章:
224587 INF: 解决 SQL Server 中的应用程序性能问题

查看“事件探查器”数据



SQL Server 事件探查器包括 SP:Recompile 事件,可以利用这个事件监控重新编译发生的次数。 只要在执行期间发生存储过程重新编译,就会发生 SP:Recompile 事件。
  • 根据 Event Class 对“事件探查器”跟踪进行分组:

    1. 文件菜单上,单击属性
    2. Data Columns 选项卡上,使用向上按钮移动 Groups 标题下的 Event ClassTextEvent Class 在先)。 使用向下按钮删除标题下的所有其它列。
    3. 单击确定
    检查 SP:Recompile 事件个数。

    可以展开 SP:Recompile 组以查看每次发生重新编译时的详细信息。 事件的 Text 列指出重新编译的存储过程名称。 如果多个过程正在引发重新编译事件,则按发生的次数进行存储?? 如果有大量的 SP:Recompile 事件,而且此时 CPU 占用率很高,则集中解决重新编译次数最多的过程。 请注意特定存储过程的一个实例的 SP:Recompile 事件的系统进程 ID (SPID) 和“启动时间”(Start Time),并按照下面的步骤执行。 如果没有看到任何 SP:Recompile 事件,但是仍然遇到性能问题,请参见 Microsoft Knowledge Base 中的下列文章:
    224587 INF: 解决 SQL Server 中的应用程序性能问题
  • 找出引发重新编译事件的语句

    1. 文件菜单上,单击属性
    2. Data Columns 选项卡上,使用向下按钮删除 Groups 标题下的所有其它列。
    3. Events 选项卡上,删除除 SP:StartingSP:StmtStartingSP:RecompileSP:Completed 之外的所有其它事件。 如果不捕获 SP:StmtStarting 事件,可以替换为 SP:StmtCompleted 事件,但不能同时包含这两个事件,因为这样会将需要查询的信息量加倍。
    4. 如果已经识别出要检查的存储过程重新编译的特定实例,则通过使用 Filters 选项卡,可以将查看的数据限定为事件发生时的特定 SPID 和时间范围。
    5. 单击确定

    SP:Recompile 事件将在发生存储过程语句引起重新编译的 SP:StmtStarted 事件之后立即发生。 重新编译事件完成后,将会看到 SP:StmtStarted 事件重新出现,表明正在使用新生成的计划执行语句。

    请考虑下列示例:
    use pubs
    go
    drop procedure RecompProc 
    go
    create procedure RecompProc as
    create table #t (a int)
    select * from #t
    go
    exec RecompProc
    如果在 Query Analyzer 中执行该代码并在“事件探查器”跟踪中查看上述事件,将看到下列序列:

    收起该表格展开该表格
    SP:Starting RecompProc
    SP:StmtStarting create table #t (a int)
    SP:StmtStarting select * from #t
    SP:Recompile RecompProc
    SP:StmtStarting select * from #t
    SP:Completed RecompProc


    您可以立即指出引起重新编译的语句是:
    select * from #t
    因为它出现在 SP:Recompile 事件的前后。

    如果您仅捕获 SP:StmtCompleted 事件,而没有捕获 SP:StmtStarting 事件,SP:Recompile 事件将直接出现在引发该事件的语句之前,如下所示:

    收起该表格展开该表格
    SP:Starting RecompProc
    SP:Recompile RecompProc
    SP:StmtCompleted select * from #t
    SP:Completed RecompProc


    可以看到 SP:Recompile 事件在 select * from #t 语句的 SP:StmtCompleted 事件之前发生,从而引起了重新编译。 这是有道理的,因为直到为重新编译生成了新查询计划之后,才能完成该语句。 本文的其余示例均使用 SP:StmtStarting 事件。 如果您仅捕获 SP:StmtCompleted 事件,切记查看 SP:Recompile 之后的语句,如上所述。

    注意:如果多次执行这个特定存储过程,SQL Server 将重用该过程的现有计划。 您只在第一次执行过程时或在每次执行脚本时删除并重新创建过程时,才会看到重新编译事件。 在本文“由于交错执行数据定义语句 (DDL) 和数据操作语句 (DML) 操作引起重新编译”部分讨论这一特定情况下引起的重新编译的原因;这只是一个简单的示例以演示如何找到引起重新编译的语句。

由于行修改引起的重新编译



自从生成原始查询计划以来,如果由存储过程所引用的表中的大多数数据已发生更改,SQL Server 将重新编译存储过程以确保它有一个基于最新统计数据的计划。 例如,请考虑下列存储过程??
drop procedure RowModifications 
go
create procedure RowModifications as
-- assume SomeTable exists with the same definition as #t, 
-- and has over 1000 rows
create table #t (a int, b char(10))
select * from #t
insert #t select * from SomeTable
select count(*) from #t  where a = 37
go
exec RowModifications
exec RowModifications
对于 RowModifications 过程的第二次执行,在“事件探查器”中将看到下列事件:

收起该表格展开该表格
SP:Starting RowModifications
SP:StmtStarting create table #t (a int, b char(10))
SP:StmtStarting select * from #t
SP:StmtStarting insert #t select * from SomeTable
SP:StmtStarting select count(*) from #t where a = 37
SP:Recompile RowModifications
Auto-UpdateStats a
SP:StmtStarting select count(*) from #t where a = 37
SP:Completed RowModifications

备注:第一个执行过程也将显示 select * from #t 语句的 SP:Recompile 事件。 在这种特定情况下,重新编译的原因将在本文的“由于交错执行数据定义语句 (DDL) 和数据操作语句 (DML) 操作引起重新编译”部分进行讨论。 对于该示例,请注意如上所示的 SP:Recompile,因为每次执行过程时都发生该事件。

在本例中,由于表创建以来行数已发生更改,select count(*) from #t where a = 37 语句引起了过程的重新编译。 Auto-UpdateStats 事件的出现证实了重新编译是由于行修改引起的。 Text 列指出修改其统计数字的列。

创建 #t 表时,行数为零。 原始 select * from #t 语句的计划是用该行计数产生的,对于 select count (*) 查询的计划也一样。 但是在执行 select count(*) 前,1,000 行新行插入到 #t 表中。 由于大多数数据已更改,优化器将重新编译过程以确保为语句选择了效率最高的计划。 这个重新编译在每次执行存储过程期间都发生,因为通常认为插入 1,000 行对于保证产生重新编译足够了。

SQL Server 用来确定计划是否应重新编译的算法与用于自动更新统计的算法相同,如在下列 Microsoft Knowledge Base 文章所描述的:
195565 INF: How SQL Server 7.0 Autostats Work
在上述示例中,存储过程足够小,所以重新编译不会对性能有明显的影响。 但是,当有大型存储过程时,如果它执行类似的活动导致多次进行重新编译,可能会注意到性能下降。

使用以下方法消除由于行修改引起的重新编译:
  • 使用 sp_executesql 执行语句。
    这是首选的方法。 使用 sp_executesql 存储过程执行的语句并没有作为存储过程计划的一部分进行编译。 因此,执行该语句时,SQL Server 将可以随意选择使用语句高速缓存中的现有计划,或者在运行时创建新的计划。 不管哪一种情况下,正在调用的存储过程的计划不会受到影响,因而无须进行重新编译。

    EXECUTE 语句有同样的效果;但是并不推荐使用它。 使用 EXECUTE 语句的效率没有使用 sp_executesql 的高,因为它不允许进行参数化查询。

    上述所给出的 RowModifications 过程可以使用 sp_executesql 进行编写,如下所示:

    drop procedure RowModifications2
    go
    create procedure RowModifications2 as
    set nocount on
    -- assume SomeTable exists with the same definition as #t, 
    -- and has over 1000 rows
    create table #t (a int, b char(10))
    select * from #t
    insert #t select * from SomeTable
    exec sp_executesql N'select count(*) from #t where a = @a', 
                       N'@a int', @a =  37
    go
    exec RowModifications2
    exec RowModifications2

    如果是第二次执行 RowModifications2 过程,则在“事件探查器”中看到下列事件:

    收起该表格展开该表格
    SP:Starting RowModifications2
    SP:StmtStarting create table #t (a int, b char(10))
    SP:StmtStarting select * from #t
    SP:StmtStarting insert #t select * from SomeTable
    SP:StmtStarting exec sp_executesql N'select count(*) from #t where a = @a', N'@a int', @a = 37
    SP:Starting
    SP:StmtStarting select count(*) from #t where a = @a
    Auto-UpdateStats a
    SP:StmtStarting select count(*) from #t where a = @a
    SP:Completed
    SP:Completed RowModifications2


    注意 RowModifications2 过程没有 SP:Recompile 事件。 sp_executesql 调用环境具有从 SP:StartingSP:Completed 的完整事件,同时 a 列有 Auto-UpdateStats 事件。 但是,由于该调用超出存储过程的环境,在这种情况下无须对 RowModifications2 过程进行重新编译。

    有关使用 sp_executesql 存储过程的详细信息,请参见 SQL Server 7.0 Books Online 中的“sp_executesql (T-SQL)”和“Using sp_executesql”。
  • 使用子过程执行引起重新编译的语句。
    在这种情况下,该语句可能还会引起重新编译,但不是重新编译大型调用存储过程,而只是重新编译小型子过程。
  • 使用 KEEP PLAN 选项。
    临时表对于重新编译有特殊规则,在某些情况下,这些规则比默认的重新编译算法要严格得多。 可以使用 KEEP PLAN 选项将临时表的阈值放宽到默认算法。 有关其它信息,请参阅本文的“使用 KEEP PLAN 选项避免重新编译”部分。
备注:RowModifications 过程是由于行修改需要重新编译过程的一个非常简化的示例。 请查看以下有关该示例的警告信息:

  • 尽管示例使用的是临时表,这种状况也适用于引用永久表的存储过程。 如果引用表中的大量数据在生成查询计划以来已被更改,则重新编译存储过程。 在本文的“使用 KEEP PLAN 选项避免重新编译”部分,讨论如何在重新编译中考虑临时表的差别。
  • 当在临时表 #t 上第一次执行 SELECT 操作时,上述两个过程的第一次执行也会引起重新编译。进行重新编译的原因将在本文的“由于交错执行数据定义语句 (DDL) 和数据操作语句 (DML) 操作引起重新编译”部分进行讨论。
  • 在本示例中使用 select count(*) from #t 语句,而不是使用简单的 select * from #t 语句。 为避免过多的重新编译,SQL Server 不考虑因行修改重新编译“非常小的计划”(如 select * from a table)。

由于交错执行数据定义语句 (DDL) 和数据操作语句 (DML) 操作引起重新编译



如果 DDL 操作是在一个过程或批处理内执行的,则过程或批处理在遇到随之发生的影响 DDL 中涉及的表的第一个 DML 操作时将进行重新编译。

请考虑下列示例存储过程:
drop procedure Interleave 
go
create procedure Interleave as
-- DDL
create table #t1 (a int)
-- DML
select * from #t1
-- DDL
create index idx_#t1 on #t1(a)
-- DML
select * from #t1
-- DDL
create table #t2 (a int)
-- DML
select * from #t2
go
exec Interleave
如果在 Query Analyzer 中执行该代码并在“事件探查器”跟踪中查看上述事件,将看到下列序列:

收起该表格展开该表格
SP:Starting Interleave
SP:StmtStarting create table #t1 (a int)
SP:StmtStarting select * from #t1
SP:Recompile Interleave
SP:StmtStarting select * from #t1
SP:StmtStarting create index idx_#t1 on #t1(a)
SP:StmtStarting select * from #t1
SP:Recompile Interleave
SP:StmtStarting select * from #t1
SP:StmtStarting create table #t2 (a int)
SP:StmtStarting select * from #t2
SP:Recompile Interleave
SP:StmtStarting select * from #t2
SP:Completed Interleave


在这种情况下,存储过程在执行期间重新编译三次。 要了解发生这种情况的原因,请考虑优化器为存储过程开发计划的方式:
  1. 在过程的初始编译过程中,并不存在临时表 #t1 和 #t2。 因此,没有创建引用这些表的查询的计划。 这些计划必须是在执行期间生成的。
  2. 在过程第一次执行时,第一步是创建表 #t1。 第二步是从表 #t1 中进行选择,该选择还没有计划。 因此,此时重新编译过程来为 SELECT 语句生成计划。 为表 #t1 中的当前选择生成计划,也为创建索引后表 #t1 中的选择生成计划。 没有为表 #t2 中的选择生成计划,因为表 #t2 还不存在。
  3. 下一步是对表 #t1 创建索引。 随后,在表 #t1 上执行另一个选择,现在它有来自第一次重新编译的一个计划。 但是,因为表 #t1 的架构自从生成计划后已更改,因此必须再次重新编译过程来为表 #t1 中的选择生成新计划。 由于表 #t2 还不存在,还不可以为表 #t2 中的选择生成计划。
  4. 下一步,创建表 #t2,并执行表 #t2 中的选择。 由于没有用于该语句的计划,最终一次重新编译过程。
在每次执行存储过程时都发生这些重新编译。 要减少重新编译的次数,请将过程修改为先执行所有 DDL 操作,然后接着执行 DML 操作,如下所示:
drop procedure NoInterleave 
go
create procedure NoInterleave as
-- All DDL first
create table #t1 (a int)
create index idx_#t1 on #t1(a)
create table #t2 (a int)
-- Then DML 
select * from #t1
select * from #t1
select * from #t2
go
exec NoInterleave 
exec NoInterleave
NoInterleave 过程的第一次执行将在“事件探查器”中显示下列事件:

收起该表格展开该表格
SP:Starting NoInterleave
SP:StmtStarting create table #t1 (a int)
SP:StmtStarting create index idx_#t1 on #t1(a)
SP:StmtStarting create table #t2 (a int)
SP:StmtStarting select * from #t1
SP:Recompile NoInterleave
SP:StmtStarting select * from #t1
SP:StmtStarting select * from #t1
SP:StmtStarting select * from #t2
SP:Completed NoInterleave


在这种情况下,所有 DDL 语句是先完成的。 优化器如下编译该过程:
  1. 在过程的初始编译过程中,并不存在临时表 #t1 和 #t2。 因此,没有创建引用这些表的查询的计划。 这些计划必须是在执行期间生成的。
  2. 过程执行的第一个步骤是 DDL 操作,创建表 #t1 和 #t2,及表 #t1 上的索引。
  3. 下一步是从表 #t1 中进行第一次选择。 由于该 SELECT 语句没有可用计划,将重新编译过程。 由于所有对象均存在,此时将为过程中的所有 SELECT 语句生成计划。
  4. 使用生成的计划执行过程的剩余部分。 由于没有对引用对象进行更改,因此无须进一步重新编译过程。
备注: 第二次执行和后续执行将利用现有查询计划和高速缓存,而且根本不会引起重新编译。 频繁使用临时表的过程应加以修改,以确保所有 DDL 语句均在该过程的开始部分。

由于某些临时表操作引起的重新编译



在存储过程中使用临时表可能会导致在每次执行过程时重新编译存储过程。

为避免这种情况,请更改存储过程以符合下列要求:
  • 所有包含临时表名称的语句都引用在同一存储过程创建的临时表,而不是在正在调用或已调用的存储过程中创建的临时表,或在 EXECUTE 语句所执行的字符串或 sp_executesql 存储过程中创建的临时表。
  • 包含临时表名称的所有语句句法上出现在存储过程或触发器中临时表的执行 SELECT 语句。
  • 没有其 SELECT 语句引用临时表的 DECLARE CURSOR 语句。
  • 包含任意临时表名称的所有语句出现在引用临时表的任何 DROP TABLE 语句之前。

    在存储过程中创建的临时表不需要 DROP TABLE 语句。 在过程执行完毕时,这些表自动消失。
  • 创建临时表(如 CREATE TABLE 或 SELECT... INTO)的语句不出现在流控制语句如 IF... ELSE 或 WHILE 中。

使用 KEEP PLAN 选项避免重新编译



在存储过程中使用临时表使得查询优化程序变得有些复杂。 表的行计数和统计信息在存储过程执行的整个期间变化非常大。 为确保优化器在所有涉及临时表的情况下使用最优计划,已开发了一种特殊算法,该算法对于重新编译更具有主动性。 该算法的原理是:如果对存储过程创建的临时表已更改六次以上,将在下一个语句引用临时表时重新编译过程。

请考虑下列示例:
drop procedure useKeepPlan 
go
create procedure useKeepPlan as
create table #t (a int, b char(3))
select * from #t
-- Make greater than 6 changes to #t
insert #t values (1, 'abc')
insert #t values (2, 'abc')
insert #t values (3, 'abc')
insert #t values (4, 'abc')
insert #t values (5, 'abc')
insert #t values (6, 'abc')
insert #t values (7, 'abc')
-- Now reference #t
select count(*) from #t 
--option (KEEP PLAN)
go
exec useKeepPlan
exec useKeepPlan
在这种情况下,第二次执行时在“事件查看器”中将看到下列事件:

收起该表格展开该表格
SP:Starting useKeepPlan
SP:StmtStarting create table #t (a int)
SP:StmtStarting - The seven insert statements -
SP:StmtStarting select count(*) from #t1
SP:Recompile useKeepPlan
SP:StmtStarting select count(*) from #t1
SP:Completed useKeepPlan

对临时表 #t 进行第七次更改之后,当进行选择时将重新编译过程。

当对临时表数据分布进行更改会极大地影响引用它的语句的最优查询计划时,主动进行重新编译是非常有帮助的。 不过,在频繁修改临时表的大型过程的情况下,但并不到值得注意的程度,重新编译将导致整体性能更低。 在这种情况下引入 SELECT 语句的 KEEP PLAN 选项。

KEEP PLAN 消除了由于过程中有六次以上的临时表更改而引发的存储过程重新编译,并还原回标准算法,有关由于行修改而导致重新编译的内容在本文上述“由于行修改导致重新编译”部分给予讨论。 总而言之,KEEP PLAN 不会阻止重新编译,它只是阻止由于在过程中对引用表的更改超过六次而引发的那些重新编译。 在上述示例中,如果在存储过程的“option (KEEP PLAN)”行删除注释,将不会生成 SP:Recompile

如果从上述代码的“option (KEEP PLAN)”行删除注释并执行它,将在“事件探查器”中将看到下列事件:

收起该表格展开该表格
SP:Starting useKeepPlan
SP:StmtStarting create table #t (a int)
SP:StmtStarting - The seven insert statements -
SP:StmtStarting select count(*) from #t1 option (KEEP PLAN)
SP:Completed useKeepPlan


注意没有 SP:Recompile 事件。

参考资源



有关 SQL Server 7.0 中查询性能问题的其它信息,请查阅 SQL Server 7.0: Query Performance troubleshooter,地址是 http://support.microsoft.com/?scid=ph;en-us;2862 .

有关使用 SQL Server 事件探查器的信息,请参阅 SQL Server 7.0 Books Online。

属性

文章编号: 243586 - 最后修改: 2006年7月6日 - 修订: 2.0
这篇文章中的信息适用于:
  • Microsoft SQL Server 7.0 标准版
关键字:?
kbinfo kbsqlserv700 KB243586
Microsoft和/或其各供应商对于为任何目的而在本服务器上发布的文件及有关图形所含信息的适用性,不作任何声明。 所有该等文件及有关图形均"依样"提供,而不带任何性质的保证。Microsoft和/或其各供应商特此声明,对所有与该等信息有关的保证和条件不负任何责任,该等保证和条件包括关于适销性、符合特定用途、所有权和非侵权的所有默示保证和条件。在任何情况下,在由于使用或运行本服务器上的信息所引起的或与该等使用或运行有关的诉讼中,Microsoft和/或其各供应商就因丧失使用、数据或利润所导致的任何特别的、间接的、衍生性的损害或任何因使用而丧失所导致的之损害、数据或利润不负任何责任。

提供反馈

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com