Sergiy (5/3/2016)
Jeff Moden (5/3/2016)
Except for the occasional hard-disk problem, performance problems usually exist (there are always exceptions to every rule) in the code.Strongly disagree! 🙂
Unless your meaning of "code" includes DDL code too.
On a badly designed data storage SQL code improvements may be as effective as hardware tweaking.
Speeding up queries only leads to increased number of deadlocks.
Most effective code fixing performance issues usually starts with "CREATE TABLE".
There's no doubt that correct design of tables and indexes is the right place to start. Unfortunately, that's normally not the case. Normally, you have to put up with one bad design or another. The databases that I currently have to work with are just that... really bad design.
Even with that, though, I've been able to greatly reduce overall CPU time, run durations, and I/O (logical reads and writes to log files) by huge amounts. And, that action has also drastically reduced the number of deadlocks because there's a whole lot less contention than there was when I first started at the company.
Most of the improvements have included more than a 60x reduction in CPU, Duration, and I/O with about half of those exceeding 250x reductions and some reductions as high as 10000X.
Yeah... the code was that bad. And, except for the occasional addition of an index and one very heavily hit audit table that I fought to have changed, it was all DML and no DDL.
--Jeff Moden
Change is inevitable... Change for the better is not.