Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In making a case to disallow parameterized SQL in my company's development environment the lead developer related a story about how the last time they used parameterized SQL the server had major performance issues. He said this was because the plan caching ate up almost all of the available memory on the server and switching to stored procedures cleared up the performance issues.

My question: is there a major difference in the memory footprint of a compiled/cached stored procedure and the cached plan for a parameterized SQL?

I have a guess that they also simplified the number of calls by going to procs and that probably had as much impact or more than just going to procs by itself, but I don't know.

share|improve this question

1 Answer 1

No, there really isn't, for the same query taking the same parameters (though this hasn't always been the case - SQL Server wasn't always able to optimize at the statement level).

Now, if the ad hoc SQL you're sending to SQL Server varies by quite a bit (say a lot of similar but not identical queries), then you could have a lot of plan cache bloat due to single-use plans. You can reduce this somewhat by using the optimize for ad hoc workloads server setting. And depending on the parameterization techniques you're using, you may want to play with dynamic SQL vs. "kitchen sink" style queries (WHERE (c1 = @p1 OR @p1 IS NULL) AND (c2 = @p2 OR @p2 IS NULL) ... etc ...), and also parameterization forced vs. simple.

If you're looking for opinions with justification, I prefer stored procedures for a variety of other reasons - including the fact that you can drastically reduce the number of calls (and the amount of data) you send over the wire, you can also simplify the hardest parts of transaction management and error handling in a single layer, and the database people can optimize the SQL without having source code access and without having to re-compile and re-deploy the application code (be it the front end or middle tier - trust me, we're not good at that, just like developers are stereotypically not very good at writing good SQL queries :-)).

share|improve this answer
    
The number one reason that I prefer stored procedures is their security control and options that are far superior to any other approach that I know of with SQL Server. –  RBarryYoung Sep 9 '13 at 17:34
2  
i smell the development of a canonical answer on this one –  swasheck Sep 9 '13 at 17:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.