Query Tuning, Why Bother?

  • 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • 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

    One of the best days of my IT career was the day I told my boss if the problem was so simple he should go fix it himself.

  • 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.

  • James.georgitis wrote:

    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.

     

    • This reply was modified 2 weeks, 4 days ago by  skeleton567.

    Rick

    One of the best days of my IT career was the day I told my boss if the problem was so simple he should go fix it himself.

  • 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.

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • 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

  • "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.

  • Other than my "fun" response above, the reason why I study it all is because of two things.

    1. I work with huge numbers of rows and I don't ever want to be the cause of blocking, high resource usage, long query run times, etc.
    2. I help other if help is needed.  My latest "kill" here is converting a query to use only 40MB of I/O instead of the 51GB it previously used.  It also save big-time on log files writes.  A couple of days before that, I fixed a query that would take ~31 minutes for the normal morning run and up to 7 hours for the Monday morning runs.  I got the former down to 44 Milliseconds and the latter down to 214 Milliseconds.

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • 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!

  • 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.

  • 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.

     

  • LittleMissSQL wrote:

    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

    One of the best days of my IT career was the day I told my boss if the problem was so simple he should go fix it himself.

  • 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • Virshu wrote:

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply