Post reply

tempdb space issues

  • October 27, 2019 at 2:43 pm

    Go to Answer

    #3692197

    Hi All,

    One of the prod env, we are seeing high utilization of Tempdb and eventually hitting Tempdb drive full.

    We increased the drive size by 100 Gb twice but still we are seeing same drive full errors due to tempdb every weekend. We have captured the usage using sp_whoisactive and provided the details to dev team to optimize their queries but there is no action taken by that team.

    DBA team, has created an alert of Tempdb usage and if free % less than 20, we get the Tempdb data file usage per file and eventually we had to kill those txns.  Again, this is due to specific txns which are run over the weekends.

    Now, what I want to know is , as a DBA what should be done to avoid such issues apart from keep increasing the Tempdb drive size each and every time. Also, trying to understand how much should be the Tempdb drive size ?

     

    Sample Tempdb utilization in GB

    ============================

    tempdb

     

    Thanks,

    Sam

  • October 27, 2019 at 4:24 pm

    #3692212

    The problem is the code that is "overly" using TempDB; you can't make TempDB use less space. Those that wrote the query need to fix it; if it's using 100GB for a single query then something is seriously wrong.

    You're Dev team can't blame the DBA here, this is a clear example of trying to push the blame to someone else if they are pointing the finger at you. If the Devs won't fix the query, then they aren't doing their job; the code is clearly broken. Personally, I see 100GB for TempDB as huge.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • October 27, 2019 at 10:07 pm

    #3692255

    Thom A wrote:

    The problem is the code that is "overly" using TempDB; you can't make TempDB use less space. Those that wrote the query need to fix it; if it's using 100GB for a single query then something is seriously wrong.

    You're Dev team can't blame the DBA here, this is a clear example of trying to push the blame to someone else if they are pointing the finger at you. If the Devs won't fix the query, then they aren't doing their job; the code is clearly broken. Personally, I see 100GB for TempDB as huge.

    Totally agreed.  Thom's post above should be required reading.

    --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)

  • October 28, 2019 at 7:12 am

    #3692279

    Thom A wrote:

    The problem is the code that is "overly" using TempDB; you can't make TempDB use less space. Those that wrote the query need to fix it; if it's using 100GB for a single query then something is seriously wrong.

    You're Dev team can't blame the DBA here, this is a clear example of trying to push the blame to someone else if they are pointing the finger at you. If the Devs won't fix the query, then they aren't doing their job; the code is clearly broken. Personally, I see 100GB for TempDB as huge.

    Hi Thom, How to approach such people issues. This is been like a habbit for the dev team to send out email to dba team to tune as their management is stronger than ours.

  • October 28, 2019 at 9:06 am

    Answer

    #3692294

    vsamantha35 wrote:

    Hi Thom, How to approach such people issues. This is been like a habbit for the dev team to send out email to dba team to tune as their management is stronger than ours.

    You need to explain why a query that need over 100GB storage in tempdb is a bad idea; educate them on the problems but do so constructively.

    Different people receive (constructive) criticism differently, and you know those people far better than I (because I don't know them at all) but the hard fact is here is that the developers need to change their query. If they don't fix it, it won't work, and then their development work is faulty. If they won't fix it now, they will have to eventually when people complain the system isn't working as it should; and doing so then rather than in the development cycle is a far worse time to do it.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • October 28, 2019 at 9:32 am

    #3692300

    Many times the best way to ensure that Devs do what is required is to pick up some of the bad SQL's and rewrite them so that they behave well and then show the impact of the change to them - note that not all devs know how to do "good" sql - and sometimes those that know better even if not a developer themselves, need to show the others how to do things in a better/different way that has less impact on the server

  • October 28, 2019 at 11:29 am

    #3692316

    This is flat out a coding and design issue. The code could be simply bad. Or, it could be doing things that aren't using the appropriate data structures, indexes, etc.. Or, it could be that you need to adjust the structures to get the right constraints and indexes in place in support of the code.

    However, it's not just, make tempdb 5 terrabytes in size. What happens when the code needs 6, 10, 700? Pretty clear you're dealing with problematic code. Focus there.

    "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

  • October 28, 2019 at 11:47 am

    #3692322

    It could be that someone is creating large tables for temporary storage in tempdb. I don't see anything wrong with this if they have done it to improve the performance of some ETL or some other process. You should try to find out what queries are causing the large usage of tempdb then, if it is temporary tables, ask the developers if this if necessary and either make more space available or get them to create their temporary tables elsewhere.

  • October 28, 2019 at 1:04 pm

    #3692351

    Thom A wrote:

    vsamantha35 wrote:

    Hi Thom, How to approach such people issues. This is been like a habbit for the dev team to send out email to dba team to tune as their management is stronger than ours.

    You need to explain why a query that need over 100GB storage in tempdb is a bad idea; educate them on the problems but do so constructively.

    Different people receive (constructive) criticism differently, and you know those people far better than I (because I don't know them at all) but the hard fact is here is that the developers need to change their query. If they don't fix it, it won't work, and then their development work is faulty. If they won't fix it now, they will have to eventually when people complain the system isn't working as it should; and doing so then rather than in the development cycle is a far worse time to do it.

    To add to what Thom has stated, when you have extreme usage of TempDB, it's normally because of "accidental cross-joins" (accidental many-to-many joins and frequently covered up with (especially) DISTINCT or (less frequently) by GROUP BY and inequality aggregates) in the code or the criteria that cause a huge number of internal rows to be formed, which all appear in the form of work-tables and spools.  These are actually pretty easy to find in a "Actual Execution Plan"... just look for large arrows with large row counts.

    These aren't necessarily caused by bad programming... they are usually caused simply by not having the right criteria or from trying to do "all in one massive queries" because a lot of people think that "set based" means "all in one query" and it patently does NOT.

    The queries simply need to be identified and fixed.  The fixes are usually pretty easy if you know a bit about "Divide'n'Conquer" techniques that, quite ironically, actually do intentionally used TempDB but much more efficiently.

    --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)

  • October 28, 2019 at 1:39 pm

    #3692358

    erm…. I picked up on one word - "weekend"

    so is there any possibility that it's a rebuild index with "Sort in tempdb" option enabled... we all know which jobs we run at the weekend that will hit t-log and tempdb hard

    however Grant, Jeff and all the others have valid points... i'm just thinking weekend !!!

    MVDBA

  • October 28, 2019 at 1:45 pm

    #3692360

    MVDBA (Mike Vessey) wrote:

    erm…. I picked up on one word - "weekend"

    so is there any possibility that it's a rebuild index with "Sort in tempdb" option enabled... we all know which jobs we run at the weekend that will hit t-log and tempdb hard

    however Grant, Jeff and all the others have valid points... i'm just thinking weekend !!!

    Totally could be. It's tempdb, the midden heap of SQL Server. We all know it could be a million and one different things, or combinations of those million and one things. However, from the descriptions, especially the efforts to make it about the DBAs being wrong, sounds like code.

    "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

  • October 28, 2019 at 2:33 pm

    #3692386

    Grant Fritchey wrote:

    Totally could be. It's tempdb, the midden heap of SQL Server. We all know it could be a million and one different things, or combinations of those million and one things. However, from the descriptions, especially the efforts to make it about the DBAs being wrong, sounds like code.

    Its ALWAYS the dba's fault - we just get used to it 🙂 ..as for code … the number of times I've heard "can't you just throw some more hardware at it" … I die a little each time

    MVDBA

  • October 28, 2019 at 3:20 pm

    #3692418

    Thom A wrote:

    vsamantha35 wrote:

    Hi Thom, How to approach such people issues. This is been like a habbit for the dev team to send out email to dba team to tune as their management is stronger than ours.

    You need to explain why a query that need over 100GB storage in tempdb is a bad idea; educate them on the problems but do so constructively.

    Different people receive (constructive) criticism differently, and you know those people far better than I (because I don't know them at all) but the hard fact is here is that the developers need to change their query. If they don't fix it, it won't work, and then their development work is faulty. If they won't fix it now, they will have to eventually when people complain the system isn't working as it should; and doing so then rather than in the development cycle is a far worse time to do it.

    Thanks Thom. Feeling very inspired.

  • October 28, 2019 at 3:21 pm

    #3692419

    Thanks everyone for explaining your views.

  • October 28, 2019 at 3:28 pm

    #3692423

    one last thing... have you got a query plan for the process that "might" be causing you an issue...it could be as simple as a join or "order by" spooling out to tempdb…..

    MVDBA

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

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