sql server - Do Stored Procedures really boost performance in MS SQL / .NET?

Question

Jeff Atwood wrote about this here, and while I understand the theoretical performance boost a stored procedure could offer, it does seem like a tremendous pain.

What types of queries would you see the most performance increase using stored procedures, and what types of queries would you rather just build on the fly?

Any documentation one way or another would be greatly appreciated.

Answer 1

The stored proc/no stored procs argument has become a religious issue. For every person that emphasizes optimized execution plans for procs, another points out common dynamic queries are cached and optimized in most modern DBMSes. For anyone that points out the security a proc might offer, another explains that dynamic queries can be made just as secure. Some like the flexibility of changing a proc without recompiling your app, while others argue queries should be captured in app code so they live and grow in the same code base.

I say...

Do what you like. I doubt we can come up with the correct answer. If procs are a hassle, don't use them. If they seem like a good idea, go for it. I've worked with both models and I honestly don't have a preference. I'm productive with or without 'em.