With stored procedures that are called hundreds or thousands of times per minute, recompilation is a killer. Some tricks to avoid.
SET Option that Causes Stored Procedure to Recompile
By Mohan Kumar -- Published on 09/14/2010
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