C********r
发帖数: 145 | 1 Yesterday, the system constantly hang up due to database time out. The
solution was to recompile the timed out store procedure.
And then it was discovered there was a job in previous version to recompile
this store procedure regularly which got left out in the new version.
I wonder when do we need to recompile a store procedure? Can the DaNiu here
help to explain when we should expect to recompile a store procedure
regularly(when table structure/index not changing)? |
B*****g
发帖数: 34098 | 2 最好注明一下是哪个版本，不同版本可能有区别
recompile
here
【在 C********r 的大作中提到】
: Yesterday, the system constantly hang up due to database time out. The
: solution was to recompile the timed out store procedure.
: And then it was discovered there was a job in previous version to recompile
: this store procedure regularly which got left out in the new version.
: I wonder when do we need to recompile a store procedure? Can the DaNiu here
: help to explain when we should expect to recompile a store procedure
: regularly(when table structure/index not changing)?
|
C********r
发帖数: 145 | 3 We are on SQL server 2008 .
Thanks. |
s**********o
发帖数: 14359 | 4 通常SP是不需要经常RECOMPILE的，除非你的TABLE数据变化快，
一天添加删除几万几百万，怀疑还是你的INDEX没做好，一般的数据
一周REINDEX一次就可以了，RUN SP的EXECUTION PLAN就能看出来，
哪里少了INDEX，哪个INDEX上有问题 |
p***c
发帖数: 5202 | 5 最近刚发现一招，sql server SPROC有时候又parameter sniffing的问题，会突然变慢
，解决方法是不要直接用 SPROC的parameter，declare local variable，然后把
parameter 赋值给local variable，SPROC内部用local variable
就这样修好一个SPROC，我靠，简直匪夷所思 |
