July 23, 2022 at 12:00 am
Comments posted to this topic are about the item Query Tuning, Why Bother?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 23, 2022 at 9:55 am
Why bother? Well, maybe first of all, it's this thing inside me that makes me want to do it better and faster with less code. Maybe it's because we all have some degree of OCD about us that kicks in. Maybe it's a competitive thing that I can do it better that the last guy. I really think that maybe instead of so much concern about saving time and money, it's just that I want to do it for my own satisfaction and the knowledge that I CAN DO IT.
I remember a remark by one of my wife's employees some years ago: "Done right is good. Done is better". I always seemed to have some reervations about that.
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
July 23, 2022 at 2:23 pm
I keep learning because when stuff finally breaks at 3am because a new release 2 sprints ago competes with this release… fewer and Fewer of us know SQL well so I can get the systems back up and running because I can trail the data source. While I have to spend more time on other languages every other core language developer I meet are reliant on tools like sqlAlchemy to generate their code with no understanding on how their tool or SQL actually works.
I do it because I want my code to be DaftPunk “Harder, Better, Faster, Stonger”. And I’m lazy, do it right the first time so you don’t have to do it again later.
July 23, 2022 at 3:08 pm
I keep learning because when stuff finally breaks at 3am because a new release 2 sprints ago competes with this release… fewer and Fewer of us know SQL well so I can get the systems back up and running because I can trail the data source. While I have to spend more time on other languages every other core language developer I meet are reliant on tools like sqlAlchemy to generate their code with no understanding on how their tool or SQL actually works.
I do it because I want my code to be DaftPunk “Harder, Better, Faster, Stonger”. And I’m lazy, do it right the first time so you don’t have to do it again later.
My last position as a DBA actually worked out very well for me as I got to both work on DB design and to do pretty heavy SQL development. I worked with a developer group to provide stored procedure and data integration and replication development to support their work while also with our DBA group. We used no higher-level development tools other than native SQL so those skills got nicely developed. some SQL procedure processes reach a size of 30-40 pages, and batch processes could involve up to 25-30 individual procedures. The downside to this was that over the years my front-end coding skills got very obsolete. Our nation-wide network of dealer servers, beside interactive applications, did hourly and daily data collection and distribution in colaboration with mainframe systems. The original development of the processes betwewen about 50 SQL Server instances and an IBM mainframe had been very poorly designed and extremely batch-oriented with little-to-no data validation and error handling. This was a constant source of disappointment because there was no corporate desire for improvement. Changes for the sake of process improvement were nearly impossible to get implemented.
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
July 23, 2022 at 6:13 pm
I rarely do tuning now. I am sometimes called on to investigate an application that suddenly stopped performing at an acceptable level, a stored procedure that usually runs in 15min has been running all day - the extreme cases. The fixes are usually simple. Rebuild an index or tweak the maintenance plan, look for blocking sessions and excessive contention with other things running. Beyond that, you're right. No one wants the DBA spending hours fine tuning - just buy a bigger server, add RAM, etc. Most of our code is 3rd party that we aren't allowed to touch, so we're usually stuck with finding a way to limit the impact. I'm afraid it's becoming a lost art.
July 24, 2022 at 1:36 am
Why bother? It's funny... there must be a disturbance in the force because I've been asking myself the same questions a whole lot lately. For me, it all boils down to just one thing... it's more fun than playing "Wordle". 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
July 24, 2022 at 10:39 am
because I want find the truth . I want to dig out root cause instead of blindly throwing out resources . Simply put , I don’t want to be fooled especially When I know the issue is inside the query
July 24, 2022 at 2:07 pm
"This was a constant source of disappointment because there was no corporate desire for improvement." Rick, I especially like that sentence. I can so relate to what you're said here. When did not sinking become the pinnacle of corporate accomplishment?
Kindest Regards, Rod Connect with me on LinkedIn.
July 24, 2022 at 6:00 pm
Other than my "fun" response above, the reason why I study it all is because of two things.
People ask me what a DBA does. My answer frequently starts off with, "You'll know if I stop doing it". 😀
And, yeah... just like a lot of people, I owe a huge amount of any talent I have in that area to Grant Fritchey, his co-author Sajal Dam, and the technical reviewer Joe Sack who all got together to put out the book titled "SQL Server 2008 Query Performance Tuning Distilled, of which I have a signed copy by Grant Himself. I'm also a Grant Fritchey "lurker". If I have a question in the are of execution plans and other performance related knowledge, I look for his name at a part of my Internet searches. I also look for his 'tubes if I want something to do, which is a whole lot more entertaining and productive than playing "Wordle", computer games, or watching TV.
Grant, don't stop what you're doing... there are a whole lot of people out there that have used your good works as "lurkers"... more than you'll ever know.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 24, 2022 at 10:37 pm
so, hypothetically, I am the boss. I used to be technical; but that was long time ago. I am responsible to the users for the performance of the application (ERP, Medical Records; Inventory - whatever) or client-facing website. And it is slow! So, I ask DBA to troubleshoot it. What is the bottleneck. Is it a single (or small number) query? Or is it some query that - while reasonable - is invoked in a loop and has overhead that slows down the system? Or maybe it isn't on the database layer - the data get processed in the application layer? Wait - it maybe even in the browser? We are running SPA, after all - and if it takes 5 seconds to build a grid, it doesn't matter that the response comes in milliseconds.
Now, 3 days later DBA comes back and tells me that she found several very inefficient queries - and we should tune them, and they will run in half the time. Nervous, I am asking, how fast do they run now. 200-300ms, she replies; and if we change the structure a little - it can run 10 times faster. I summon my inner Borat and say "That's nice!". Is that the bottleneck? - No, not really, just very inefficient query.
But what's the bottleneck, I repeat? In response I get geeky mambo-jumbo about instance parameters; and index scanning vs. index seeking; and sharding; and TempDB parameters. Fortunately, client manager calls me to say that Azure AD is down - so I can send DBA to another class she was nagging me for months.
That is why I don't care about query tuning. Every IT area has their pet peeves (Developers; Networking). Show me how yours affects business - and I sign off on all conferences you want to go!
July 25, 2022 at 7:54 am
Why bother? Because 2 years after implementing the system, the data volume had increased to the point where the overnight batch ran well into the following morning (who'd have thought data volumes would increase over time?). We've made the big wins that take hours off the batch time, but we have to keep tuning because in a year's time there will be (at least) 50% more data.
PS: Thanks to many on this site who have written articles, answered questions, etc. because we'd have got nowhere near those gains without you.
July 25, 2022 at 8:05 am
In my experience people don't care about this, until they really care about it. Organisations are happy for pretty much anyone to write stored procs, but when their application is timing out because queries take over 30 seconds, then it's a big deal.
I had watched query tuning videos and read posts on it over time - the compound effect. So when I was asked to look at an application timing out, I knew where to start.
My career definitely improved because of the success I had with query tuning; more teams wanting to work with me, people listening to what I was saying, an interest in writing better code in the first place.
There is always a tipping point, and you always come up against "it worked fine yesterday", but being able to identify and resolve poorly performing queries is valuable. It's just the value isn't always seen until its needed.
July 25, 2022 at 12:53 pm
In my experience people don't care about this, until they really care about it. Organisations are happy for pretty much anyone to write stored procs, but when their application is timing out because queries take over 30 seconds, then it's a big deal....
My career definitely improved because of the success I had with query tuning; more teams wanting to work with me, people listening to what I was saying, an interest in writing better code in the first place.
Little Miss, I agree with that. I think I got the most satisfaction in my job just trying to create good SQL code. Many times nobody even knew about it, unless you were actually asked to work on some improvements.
But I knew I was an 'expert'. X indicates an unknown, and a spurt is a drip under pressure.
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
July 25, 2022 at 1:18 pm
Thanks for all the feedback everyone. Appreciated.
Me, I love tuning queries.
Yes Jeff, working on the new book. Chapter 20 right now, Graph data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 25, 2022 at 1:36 pm
so, hypothetically, I am the boss. I used to be technical; but that was long time ago. I am responsible to the users for the performance of the application (ERP, Medical Records; Inventory - whatever) or client-facing website. And it is slow! So, I ask DBA to troubleshoot it. What is the bottleneck. Is it a single (or small number) query? Or is it some query that - while reasonable - is invoked in a loop and has overhead that slows down the system? Or maybe it isn't on the database layer - the data get processed in the application layer? Wait - it maybe even in the browser? We are running SPA, after all - and if it takes 5 seconds to build a grid, it doesn't matter that the response comes in milliseconds.
Now, 3 days later DBA comes back and tells me that she found several very inefficient queries - and we should tune them, and they will run in half the time. Nervous, I am asking, how fast do they run now. 200-300ms, she replies; and if we change the structure a little - it can run 10 times faster. I summon my inner Borat and say "That's nice!". Is that the bottleneck? - No, not really, just very inefficient query.
But what's the bottleneck, I repeat? In response I get geeky mambo-jumbo about instance parameters; and index scanning vs. index seeking; and sharding; and TempDB parameters. Fortunately, client manager calls me to say that Azure AD is down - so I can send DBA to another class she was nagging me for months.
That is why I don't care about query tuning. Every IT area has their pet peeves (Developers; Networking). Show me how yours affects business - and I sign off on all conferences you want to go!
You've hit upon an issue that I have with such conferences, especially in this day and age of mass remote communications.
While I agree that it's nice to "get out and socialize and see people in person", why is it necessary for the "company to pay for it"? I've never asked for my company to pay for training on my primary job because, ostensibly, I said I could do my job on my resume a long time ago and, for new stuff, there's a shedload of information online and, before all this great communication, there were books, etc, etc. When you go to a conference, it's not like you'll be able to get Grant Fritchey's or Brent Ozar's personal and private attention for any significant period of time. Yes, in-person conferences and the like are a shedload of fun but they're relatively expensive to attend if you're from "out of town" with travel, lodging, food, and time. If you wouldn't pay to attend something like that on your own, why would you ask your company to pay for it if even you don't think it's worth enough to pay for it on your own?
Now... if the company makes a paradigm shift (move to the cloud, for example), I can see the company getting some training for it's people but it likely wouldn't be in the spray of offerings typical of in-person conferences. I'd hire a trainer to come in-house to provide the training.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 44 total)
You must be logged in to reply to this topic. Login to reply