Recompiles to a stored procedure can cause performance issues. Other than WITH RECOMPILE, sp_configure, or a server restart, what else can cause a recompile?
We often use SET option in stored procedure without realizing if it can cause performance problem. I was working on Critical Database Performance issue recently arguing on SET option causing recompile without realizing which one causes or which one not. Moreover I came across in situation when some experts mentioned that "set isolation level read uncommitted" and "set nocount on" are causing recompilation. Whereas truth is these options DO NOT cause recompilation. So I thought of putting following list available handy for reference whenever we write code or optimize code or find stored procedure recompiling due to "set option changed".
No comments:
Post a Comment