Post reply

Tempdb Growth

  • December 5, 2019 at 12:56 am

    #3704600

    Looking for the causes of tempdb growth. I do see the open tran on and off and it disappears. Where else i can look for and the cause could be. Thanks in advance?

    use tempdb

    go

    dbcc opentran()

    --if there is open tran SPID will be given for that

    DBCC INPUTBUFFER(SPID)

  • December 5, 2019 at 6:07 am

    #3704642

    select distinct *

    from sys.fn_trace_geteventinfo(1) ei

    inner join sys.trace_events e

    on e.trace_event_id = ei.eventid

    where name like '%grow%'

    We are the pilgrims, master.
    We shall go always, a little further.

  • December 5, 2019 at 9:34 am

    #3704702

    The default trace will show you when tempdb grew, but not why.  Analysing tempdb growth is difficult, because there are so many moving parts.  Are you using Snapshot Isolation?  Does your code make excessive or inefficient use of temp tables?  Do you rebuild your indexes with the SORT_IN_TEMPDB option?  Are your statistics up to date?  If not, your queries could be getting insufficient memory grants, leading to spilling to tempdb.  Spills to temptb are recorded (in aggregate form) in sys.dm_exec_query_stats.

    John

  • December 5, 2019 at 10:01 am

    #3704734

    when are you finding tempdb grows? is there a pattern? at weekends, every day?

    i'm suspecting maybe sort in tempdb

    https://docs.microsoft.com/en-us/sql/relational-databases/indexes/sort-in-tempdb-option-for-indexes?view=sql-server-ver15

    but there are other things that can cause this - joins or order by clauses that spool to tempdb

    MVDBA

  • December 5, 2019 at 1:46 pm

    #3704956

    Thanks. It is a vendor product so i am not sure if i can look for any inefficient query's? Just expansion of drive would be the option?

  • December 5, 2019 at 2:03 pm

    #3704967

    You can still look for them and bring them to the vendor's attention.  But it may not be anything to do with the quality of their code - it may be one of the other things I mentioned.  This is something you're going to need to investigate for yourself.

    John

  • December 5, 2019 at 2:51 pm

    #3704994

    Explosive growth of TempDB is normally caused by just one thing... some code that has created a many-to-many join, accidentally or not.  To emphasize the problem with this, let's call it what it really is... an accidental cross join.

    I do agree that SORT IN TEMPDB can be a problem when it comes to reindexing but it usually won't be larger than the largest Clustered Index.  That's something to check on.   I'll also state that if you're rebuilding huge indexes in-place, that's also going to cause explosive growth of possibly unwanted free space in the MDF file.  There's a way to fix even that although it does require some head-room because the copy of the index that REBUILD will make has to at least temporarily live somewhere.  It just doesn't need to permanently live in the MDF file.

    I'll try to get back to this thread tonight (unfortunately, no guarantees this week because we're prepping for our "DiRT" this weekend).

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

  • December 5, 2019 at 7:32 pm

    #3705128

    Thanks!  Sorry, I want to take it back what i said previously, it's a vendor product but it does have some user dbs and vendor db's. Under the heavy load mostly data load some deadlocks and blocking are noticed. Would that cause tempdb growth as well? I am seeing PLE value is low so may be need more memory added to it? Rebuilding index is done using ola hallengren scripts. After looking into the trace file found when was the tempdb file growth happened. Looking at the application name it could be third party monitoring tool for the cause of growth. It seems the growth is happened very minimal and not happening anymore. However, the tempdb drive is very small which would need to be expanded, but wanted to check the cause of growth.

  • December 6, 2019 at 12:12 am

    #3705197

    Admingod wrote:

    Thanks!  Sorry, I want to take it back what i said previously, it's a vendor product but it does have some user dbs and vendor db's. Under the heavy load mostly data load some deadlocks and blocking are noticed. Would that cause tempdb growth as well? I am seeing PLE value is low so may be need more memory added to it? Rebuilding index is done using ola hallengren scripts. After looking into the trace file found when was the tempdb file growth happened. Looking at the application name it could be third party monitoring tool for the cause of growth. It seems the growth is happened very minimal and not happening anymore. However, the tempdb drive is very small which would need to be expanded, but wanted to check the cause of growth.

    As odd as it may seem, index maintenance may be a part of the problem if the loads aren't being done in the same order as the indexes and the indexes have a very high Fill Factor.  The deadlocks and blocking may be caused by page splits of indexes (including the clustered indexes) of the tables you're loading to.

    You're using terms like "low" and "very small".  You need to quantify all of this a bit.  For example, how much memory do you actually have, how big is the drive that TempDB is on, how big did it grow to, etc, etc.

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

  • December 6, 2019 at 1:16 am

    #3705202

    Thanks Jeff. The Low PLE value is less than 300 secs. The total memory on the server is 8GB and 4 GB is assigned to SQL Server. After looking into further found that the collection service query from the monitoring tool has taken approx 8GB tempdb space out of 10 GB tempdb disk space. However, i am concerned that the monitoring i use is to identify the problems on DB side, but this itself become a problem for the cause of tempdb growth. Not sure if that is normal for monitoring system to take that much tempdb space? I have never seen this before.  As i said couple of vendor db's and user dbs has approx total db size of 40 GB. I agree adding the disk space is a simple solution but wanted to find the root cause and learn from it so it won't happen again. But is that possible to avoid a situation like this?

  • December 6, 2019 at 8:58 am

    #3705261

    ok - so 8GB and 4Gb assigned, no wonder your PLE is 300 seconds. get someone to upgrade the memory.

    I'd just dump the monitoring solution, it's negatively affecting production... look at redgate's SQL monitor

    MVDBA

  • December 16, 2019 at 6:05 pm

    #3708241

    Thanks. Tempdb growth is a situation that can't be avoided as always right? It can happen with multiple reasons as everyone else stated here. To avoid the situation of tempdb growth is there is no hard rule unless we can follow some recommendations and practices. However, some prevention factors can be taken into the account if we know the cause of growth. So I came as below list. Can you think of anything else?

    The cause of tempdb growth can be following reasons right? Index maintenance can cause the tempdb growth and log file growth, so the impact can be on tempdb and ldf file right?

    Some code that has created a to-many joins.

    Better option is to make several tempdb data files and place them all on the same tempdb drive. The number of files depends on the number of CPUs on the system. It will improve the performance, not sure how would it help with tempdb growth?

    Preventive measures:

    In place monitoring system to capture the tempdb growth and trigger a alert and fix it before user find it and then work on the root cause and fix it.

    Increase workload and adding number of user databases and not adding any space for tempdb can cause disk space issue when there is a tempdb growth occurred. So monitor the growth history and ensure to add the disk space every 6 months or 1 yr accordingly to avoid this.

    Please advise if another recommendation you guys can think off?

  • December 16, 2019 at 6:33 pm

    #3708256

    Admingod wrote:

    Better option is to make several tempdb data files and place them all on the same tempdb drive. The number of files depends on the number of CPUs on the system. It will improve the performance, not sure how would it help with tempdb growth?

    It is widely recommended to have multiple tempdb data files--according to the number of CPUs (to mean cores, not sockets) on the machine. Some people advocate 1/2 the number of files to the number of CPUs; for example if there are 4 CPUs, then you would want 2 tempdb data files. I personally like to have the same number of data files for tempdb as there are CPUs. Grow them out to a specified size, like 10 GB (or whatever makes sense for your system--I would say the minimum drive size for tempdb would be 50 GB) per file and turn off auto-growth. Then the files are pre-sized, and with auto-growth off, you don't have to worry about your drive filling up. This also assumes that you put the tempdb log file on a different drive. If you want it on the same drive, then I would recommend adding another 10%-20% of space for the log file (making the drive 55-60 GB), just to be safe.

    • This reply was modified 4 years, 9 months ago by  SQL_Hacker.

    Alan H
    MCSE - Data Management and Analytics
    Senior SQL Server DBA
    Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • December 17, 2019 at 4:02 am

    #3708329

    Thanks! So it's always good practice to go multiple tempdb data files based on CPUs. So my understanding is since SQL 2016 installation there is a option to configure multiple tempdbs ,so prior to SQL 2016 version would you recommend to manually create multiple tempdbs based on the available CPUs with minimum tempdb size of 50 - 60 GB disk space with even small applications?

  • December 17, 2019 at 2:18 pm

    #3708488

    Admingod wrote:

    Thanks! So it's always good practice to go multiple tempdb data files based on CPUs. So my understanding is since SQL 2016 installation there is a option to configure multiple tempdbs ,so prior to SQL 2016 version would you recommend to manually create multiple tempdbs based on the available CPUs with minimum tempdb size of 50 - 60 GB disk space with even small applications?

    Yes, I would highly recommend that regardless of the "size" of the application. If you're already having problems with tempdb, making more files available will help for sure. And yes, the additional data files (.ndf) are easily added manually. In fact, there's a great article here: https://www.sqlskills.com/blogs/paul/correctly-adding-data-files-tempdb/ that shows exactly how to do it.

    Alan H
    MCSE - Data Management and Analytics
    Senior SQL Server DBA
    Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

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