For large batch jobs, RECOMPILEs aren't so bad. Using "optimize for Ad hoc Workloads" actually masks the terrible problem of multiple renditions of the same code being just far enough off to require a separate and sometimes very expensive recompile of code coming from poorly formed embedded SQL, SQL generated from an ORM, and dynamic SQL. It's a real bitch when front end code (regardless of method) "only" takes 100ms to run but takes 2-22 seconds to execute and it's execute thousands of times per hour. Most folks never find that type of problem and just chalk it up to the "server being slow" when it's actually the fault of the code and the recompiles that are taking place.
To wit, "with recompile" should be used very, very sparingly and with great caution and understanding. It is NOT a panacea to avoid parameter sniffing and should only be used with the greatest of care.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)