Sean Redmond (8/30/2016)
ORMs and LINQ are theft.The developers steal DBAs' time so that they may have an easier ride. They meet their deadlines more easily while making the DBA's job harder.
As someone who takes an active interest in the performance of DB-objects, SPs are important.
SQL Server collects all manner of usage data and other useful data on the objects within the DB.
If a DB or function is performing poorly, I, as DBA, can rectify it. Not so with LINQ code.
I know how often an SP has been executed since the last server restart. I can find out which SPs have used a certain index in the last while. I can determine the average number of logical reads, physical reads and so on. I have something concrete with which to base my corrections.
For us DBAs maintenance and continual improvement are two of our tasks. The developers work within the parameters of their sprints and this work is usually new development, not fixing the crap that they have already coded.
If a manager comes and says that the application is running more slowly than usual, and all of the DB calls are made via LINQ, then I can only tell so much before having to send him to the developers to alleviate the problem. The DBAs become less useful (because they can't fix the problem as easily as they once could) and the developers become more stressed because they have to interrupt their sprint-work. It is a lose-lose situation.
And for all those developers who say that there should be no business logic in the DB, well, the DB itself is business logic. It is (or, at least, should have been) the structural personification of the business logic within the data resides.
Hey, the DBAs should appreciate us devs giving them something to do besides reading the papers 😉
I've always found it possible to profile ORM calls well enough for most purposes - though it sounds like you have a great command of the proc lifecycle tools. In that case it is a fairly quick piece of work for a dev to drop a call out to a procedure instead where needed (i.e. when there is a non-commensurate slowdown), perhaps initially simply using the generated SQL, which could then be reformatted and tuned to perfection.
Business logic - it's easiest to follow when you can flow through it in context. It's just harder when you have to jump context. But then we tend to do maintenance ourselves and certainly don't have DBAs to pick up the pieces.