使用 MSSQL 2005
今天，我被叫去查看一个存储过程，该过程在我们的程序中使用时开始执行缓慢。大约需要几秒钟的时间，它立即执行。我在其上运行了 SQL Server Profiler，它使用了 1000 多个 CPU 和超过 400,000 次读取。我将 Exec 行从 SQL Server Profiler 复制到 SQL Management Studio 以查看执行计划。存储过程立即执行并返回正确的结果。执行计划看起来正确，没有明显的错误。我尝试从我们的程序多次运行它，看看它是否没有被缓存，我只是看到在确定执行计划时第一次运行速度变慢，但每次运行的速度始终保持在 2-3 秒。
为了好玩，我运行了 DBCC FreeProcCache，只是为了看看这是否会使我从 SQL Management Studio 运行的速度变慢。从 Management Studio 运行存储过程，它仍然立即运行。然后我再次从程序中运行它，同时运行分析器，它也立即运行。分析器显示 CPU 已降至 0，读取次数已降至 40。现在它似乎一直保持快速运行。
为什么运行 DBCC FreeProcCache 会如此大幅度地加快存储过程的速度？
答案1
简短的回答：参数嗅探。
详细回答：首次运行存储过程时，查询优化器会查看传递给它的参数和有关正在查询的对象的元数据。如果元数据随时间发生显著变化（例如统计直方图发生变化），或者初始参数不能代表典型调用，则优化器缓存的计划可能不是最优的。通过执行 freeproccache，您可以摆脱“坏”计划并强制查询优化器再次运行该计划。
如果您能找出哪个存储过程是问题子项，则可以将“with recompile”添加到该过程的定义中，这样它就不会缓存该过程的计划。如果您能找到过程中导致问题的语句，则可以将“option (recompile)”添加到其中，这样在过程运行时只会重新编译该语句。