Erik Darling takes us through the pros and cons of slapping that RECOMPILE label on a query:
It’s been a while since SQL Server has had a real RECOMPILE problem. And if you put it up against the performance problems that you can hit with parameter sniffing, I’d have a hard time telling someone strapped for time and knowledge that it’s the worst idea for them.
Obviously, you can run into problems if you (“you” includes Entity Framework, AKA the Database Demolisher) author the kind of queries that take a very long time to compile. But as I list them out, I’m kinda shrugging.
My rule of thumb is that WITH RECOMPILE isn’t the first answer, but I won’t mess around too long before going to it.