哪些因素会导致 SQL Server 上的存储过程重新编译? [英] What factors can cause Stored Procedure Recompilation on SQL Server?
问题描述
我应该注意哪些因素会导致过多的存储过程重新编译?
What factors should I be aware of that can cause excessive stored procedure recompilation?
将导致存储过程重新编译的代码示例会很有用.目的是尽可能避免重新编译，这应该可以提高性能.
Examples of the code that will cause a stored procedure to recompile would be useful. The purpose would be to avoid a recompile if possible which should improve performance.
导致不同输出(按数据类型和/或列数)的动态 SQL 和变量路径似乎可能存在问题.假设是否正确?还有其他例子吗.
Dynamic SQL and variable paths resulting in different outputs (either by data type and/or number of columns) seem like they could present a problem. Are the assumptions correct? Are there other examples.
我确实找到了另一个例子.在流控制语句中创建临时表将导致重新编译.
I did find another example. Creating a temporary table in a flow control statement will cause a recompile.
推荐答案
有几种方法可以确保重新编译存储过程:
There are a few ways to ensure recompilation of a stored procedure:
- 使用
WITH RECOMPILE， - 使存储过程动态化(想想
exec()) - 使用
sp_recompile标记要重新编译的 proc. - 更改缓存查询计划所依赖的架构
- 调用
DBCC FREEPROCCACHE - 在查询级别，可以使用 RECOMPILE 查询提示 (SQL 2008) 重新编译 proc 中的单个语句.
- using
WITH RECOMPILE, - making the stored procedure dynamic (think
exec()) - marking the proc for recompile with
sp_recompile. - changing the schema that a cached query plan relies upon
- calling
DBCC FREEPROCCACHE - At the query level an individual statement within a proc can be recompiled with the RECOMPILE query hint (SQL 2008).
重新编译的因素
除了上面列出的硬性因素，还有什么原因会导致存储过程重新编译?嗯，很多东西.其中一些与上面的列表交织在一起，但我想在 b/c 中重新呈现它们，这可能并不明显.
Besides the hard-factors listed above, what causes stored procedure recompilation? Well, lots of things. Some of these are interwoven with the list above, but I want to re-present them b/c it might not be obvious.
- 插入或删除大量数据(索引和表中的数据密度通常控制查询计划)
- 重建索引(对基础对象的更改)
- 创建/删除临时表(同样，底层 DML 更改).
- 查询计划过期(认为最近没有使用，sql 想要清理内存使用)
这绝不是一份详尽的清单.无论您使用 SQL Server 多久，查询优化器都会不断发展并带来惊喜.但这里有一些可能有用的资源:
This is by no means an exhaustive list. The query optimizer evolves and suprises no matter how long you've been using SQL Server. But here are some resources that may be of use:
- Troubleshooting stored procedure recompilation (an oldie, but a goodie)
- Recompling Stored Procedures
- Optimizing SQL Server Stored Procedures to Avoid Recompiles
- Execution Plan Caching and Reuse (a must read)
等等 - 还有更多！
话虽如此，您问题中的假设是重新编译总是对性能不利.事实上，经常重新编译是好的.
With that said, the presumption in your question is that recompiles are always bad for performance. In fact, often recompliation is good.
那么你希望它什么时候重新编译?让我们看一个按姓氏搜索的 proc 示例.存储过程执行参数嗅探"，这是一种祝福(如果它有效)对你)和诅咒(如果它对你不利).首先通过某人在 Zebr% 上搜索 zerbrowski.姓氏索引意识到这是非常具体的，并且将返回，比如说，从一百万行中返回 3 行——因此构建了一个执行计划.为低行结果编译 proc，下一个搜索是 S%.好吧，S 是您最常用的名称，它匹配 100 万行中的 93,543 行.
So when would you want it to recompile? Let's look at one example of a proc that searches by last name. Stored procedures do 'parameter sniffing' which is a blessing (if it works for you) and a curse (if it works against you). First pass someone searches on Zebr% for zerbrowski. The last name index realizes this is very specific and will return, lets say, 3 rows from a million -- so one execution plan is built. With the proc compiled for a low row result, the next search is for S%. Well, S is your most common name and matches 93,543 rows out of 1 million.
这篇关于哪些因素会导致 SQL Server 上的存储过程重新编译?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
