Has anyone had great performance gains by using 'with recompile' on procedures that take 50+ parameters?? Thanks
Yes on the next execution it will be recompiled for a fresh plan, but not ideal if that SP is being used extensively. You might think to schedule a task to recompile such SPs. http://www.sql-server-performance.com/rd_optimizing_sp_recompiles.asp is a good one for you in this regard. Satya SKJ Microsoft SQL Server MVP Contributing Editor & Forums Moderator http://www.SQL-Server-Performance.Com This posting is provided AS IS with no rights for the sake of knowledge sharing.
You're procedure is probably falling victim to "Parameter Sniffing". If you do a search in BOL or Google you'll get some good documentation that explains what it is and whty the recompile option can sometimes help.
