September 28, 2021 at 9:30 pm
Blitz Cache reveals that the 50 most expensive queries run against the production servers are raw statements, not wrapped in stored procedures, and earlier, Blitz revealed plan cache instability, with most plans being compiled in the last 24 hours despite the server having last been restarted a fortnight back. We have memory pressure which we just relieved (hopefully) by quadrupling from 32 to 128Gb.
Although so many single-use strings with no attempt (that I can see) at parameterization would explain why the cache is so bloated at 32gb, I am having some trouble believing that the app vendor (in cloud) would be so reckless as to allow this level of avoidance of stored procedures, especially given it is they who sized the server. It seems the only sprocs being run are on Reporting (where they are least needed), which is on the same server. Background, I've seen plenty of client side servers with huge flaws, I've come to expect these at clients, but not from a vendor-side Server.
My questions are, am I right in thinking this avoidance of sprocs is likely to be adding to how would I find out if they are indeed running nothing in stored procedure, and am I right to assume in the first place that the lack of sproc usage was aggravating the lack of memory?
September 29, 2021 at 4:01 pm
Well, I'm not so shocked about the lack of stored procedures as you seem to be.
The use of stored procedures for any kind of web/object oriented programming has long been out of fashion. The use of ORM tooling has taken over. It's an abstraction layer between OO languages and SQL. This has the benefit (for the developer) that there's no need to know SQL. Everything can be contained and maintained within the same project and there's no apparent need to consult a DB specialist!
So instead of having someone on the team who deals with the database layer (creating performant SQL in a sp) they now can have the web developer do that. Awesome!
Unfortunately the ORM will often construct a bloated sub-optimal SQL query - specially if just using the ORM default setttings.
This has been the topic of many online discussions
https://stackoverflow.com/questions/2228437/what-is-so-great-about-orm
https://www.talentopia.com/back-end/orm-vs-plain-sql-which-should-you-choose-and-when
https://stackoverflow.com/questions/494816/using-an-orm-or-plain-sql
https://ma.ttias.be/bad-orm-is-infinitely-worse-than-bad-sql/
Why I Support Code Automation Tools Like ORMs - Grant Fritchey (scarydba.com)
... and many, many, oh so many others. It's really a topic that divides people!
September 30, 2021 at 11:03 am
"Unfortunately the ORM will often construct a bloated sub-optimal SQL query - specially if just using the ORM default setttings."
This is what I suspect to be a contributing factor to the woes on this server. Thanks for the hint, I'll investigate 🙂
September 30, 2021 at 3:12 pm
Just curious - but do you have the option 'Optimize for Adhoc Workloads' enabled on that server? I would also review the parameterization setting on the database - the default is simple and this may be a candidate to change it to forced.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply