Plan cache memory: parameterized SQL vs stored procedures

Question

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.

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 :-)).