如何通过profiler获知存储过程recompile的原因

最新推荐文章于 2025-06-04 10:33:18 发布

原创最新推荐文章于 2025-06-04 10:33:18 发布 · 626 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#profiler #存储 #statistics #schema #binding #permissions

本文详细解读了 SQL Server 中不同版本在编译过程中重编的原因，包括模式更改、统计信息变动、对象未在编译时找到等，为数据库维护提供了深入的理解。

通过查看profiler报告中的EventSubClass列，该列显示为整数类型，具体的意义如下表所示。

SQL Server 2000 SP2

   Integer code   Reason for recompilation
   -----------------------------------------------------------------------
   1              Schema, bindings, or permissions changed between compile or execute.

   2              Statistics changed.

   3              Object not found at compile time, deferred check to run time.

   4              Set option changed in batch.

   5              Temp table schema, binding, or permission changed.

   6              Remote rowset schema, binding, or permission changed.

SQL Server 2005

   Integer code   Reason for recompilation
   -----------------------------------------------------------------------
   1         Schema changed.

   2         Statistics changed.

   3         Recompile DNR.

   4         Set option changed.

   5         Temp table changed.

   6         Remote rowset changed.

   7         For browse perms changed.

   8         Query notification environment changed.

   9         MPI view changed.

   10         Cursor options changed.

   11         With recompile option.