Hi Friends, I often work on Performance Issues, and I found that developers use RECOMPILE option without even understanding it thoroughly. Instead of resolving the performance issue by query tuning, they end up putting more CPU pressure on server by using WITH RECOMPILE procedure level option suspecting Parameter Sniffing.
This was a similar scenario when analyzing the high CPU issue on server, I found that there were many procedures created by Developers using WITH RECOMPILE procedure level option. We can use the below code to find such procedures:-
SELECT QUOTENAME(R.ROUTINE_SCHEMA)+'.'+QUOTENAME(R.ROUTINE_NAME) AS ObjectName
FROM INFORMATION_SCHEMA.ROUTINES AS R
WHERE R.ROUTINE_DEFINITION LIKE '%WITH RECOMPILE%'
Apart from this, I would suggest our Developer friends to visit blog post ‘Improving query performance with OPTION (RECOMPILE), Constant Folding and avoiding Parameter Sniffing issues’ on MSDN by Robin Lester where he has covered scenarios of Parameter Sniffing, and various options appropriate as per need. Also, one can read the blog post ‘The Use and Abuse of RECOMPILE in SQL Server’ by Kendra Little.
Friends, please feel free to correct me by comments. If you like the article, do Like & Share. Happy Coding 🙂