Procedures with RECOMPILE Option
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:-
|
1 2 3 4 5 6 7 8 9 |
USE <DatabaseName> GO SELECT QUOTENAME(R.ROUTINE_SCHEMA)+'.'+QUOTENAME(R.ROUTINE_NAME) AS ObjectName ,R.ROUTINE_TYPE ,R.ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES AS R WHERE R.ROUTINE_DEFINITION LIKE '%WITH RECOMPILE%' GO |
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 🙂
Related Posts
About The Author
Ajay Dwivedi
I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.