Identify what causes tempdb to increase

  • I have sql server 2005 and recently my tempdb is hitting the limits of the logical disk drive and cannot expand.

    It's usually about 1- 4 GB in size, but has recently hit > 11 GB (which is the max size on the logical disk).

    That only started happening in the last 10 days or so. I've been the dba on it for 1 year now.

    Is running the profiler the only way to identify what process is causing tempdb to expand to such a large degree?

    Steve

  • When it comes down to it, the reason the tempdb fills up is because the query is returning way too much data, and you need to find out why and fix it. Often, it is because the query allows a user to specify one or more criteria that was not specific enough, and way too much data was returned.

    In fact, it is a good idea to pre-size the tempdb database anyway, so that when SQL Server is restarted, it will automatically be set at a specific size. This way, when the tempdb really does need to be used, time doesn't have to be wasted as the tempdb has to autogrow.

    Predicting the ideal size for the tempdb is not easy. Generally, I take a guess, and then watch it, seeing how good my guess is. And if I am wrong, then I will make the necessary adjustments.

    Many people think that tempdb is only used for #temp tables. When in fact, you can easily fill up tempdb without ever creating a single temp table. Some other scenarios that can cause tempdb to fill up:

    * any sorting that requires more memory than has been allocated to SQL Server will be forced to do its work in tempdb;

    * if the sorting requires more space than you have allocated to tempdb, one of the above errors will occur;

    * DBCC CheckDB('any database') will perform its work in tempdb -- on larger databases, this can consume quite a bit of space;

    * DBCC DBREINDEX or similar DBCC commands with 'Sort in tempdb' option set will also potentially fill up tempdb;

    * large resultsets involving unions, order by / group by, cartesian joins, outer joins, cursors, temp tables, table variables, and hashing can often require help from tempdb;

    * any transactions left uncommitted and not rolled back can leave objects orphaned in tempdb;

    * use of an ODBC DSN with the option 'create temporary stored procedures' set can leave objects there for the life of the connection.

    The following will tell you how tempdb's space is allocated:

    USE tempdb

    GO

    EXEC sp_spaceused

    The following should give you some clues as to which table(s) consume most of the space in the data file(s) -- this will help you narrow down any transactions that are either taking a long time or repeatedly being left in limbo:

    USE tempdb

    GO

    SELECT name

    FROM tempdb..sysobjects

    SELECT OBJECT_NAME(id), rowcnt

    FROM tempdb..sysindexes

    WHERE OBJECT_NAME(id) LIKE '#%'

    ORDER BY rowcnt DESC

    The higher rowcount values will likely indicate the biggest temporary tables that are consuming space. And while it won't tell you everything, since tempdb is used for internal I/O and other processes such as sorting, it may help you narrow down the stored procedure(s) that are causing the growth (you can query INFORMATION_SCHEMA.ROUTINES for ROUTINE_DEFINITION LIKE '%#table_name%' from above).

    In addition to this, you can use Profiler to watch for events like database file auto grow and log file auto grow. If this is happening often, then you know that the space you've allocated to tempdb is not sufficient.

    You can also watch performance monitor's counter for PhysicalDisk: CurrentDiskQueueLength on the drive where tempdb exists. If this number is consistently greater than 2, then there is likely a bottleneck in disk I/O.

    Here are some suggestions for maintaining a healthy tempdb:

    * Make sure that tempdb is set to autogrow -- do *NOT* set a maximum size for tempdb. If the current drive is too full to allow autogrow events, then buy a bigger drive, or add files to tempdb on another device (using ALTER DATABASE) and allow those files to autogrow. You will need at least one data file and at least one log file in order to avoid this problem from halting your system in the future.

    * For optimal performance, make sure that its initial size is adequate to handle a typical workload (autogrow events can cause performance to suffer as it allocates new extents). For an approach to setting a non-default size for tempdb.

    * If possible, put tempdb on its own physical disk, array or disk subsystem (see KB #224071 for more information).

    * To prevent tempdb log file growth, make sure tempdb is in simple recovery mode.

    * Try to make sure you have covering indexes for all large table that are used in queries that can't use a clustered index / index seek.

    * Batch larger heavily-logged operations (especially deletes) that *might* overflow into tempdb into reasonable 'chunks' of rows, especially when joins are involved.

    * Pore over your code for potential uncommitted transactions and other elements from the list at the top of the page.

    * In general, try to make your code as efficient as possible... avoid cursors, nested loops, and #temp tables if possible.

    Check out the WebCast in KB #834846 for some ideas from Microsoft about administering and maintaining TempDB. http://support.microsoft.com/default.aspx/kb/834846

  • So, the answer is "No".  You don't know how to determine the code that is causing TempDB to grow.

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

  • sgambale wrote:

    I have sql server 2005 and recently my tempdb is hitting the limits of the logical disk drive and cannot expand. It's usually about 1- 4 GB in size, but has recently hit > 11 GB (which is the max size on the logical disk). That only started happening in the last 10 days or so. I've been the dba on it for 1 year now. Is running the profiler the only way to identify what process is causing tempdb to expand to such a large degree? Steve

    No... see the following links for a "starter" on finding the actual code that causes tempdb to grow.  Of course, you'll have to shrink it back down so it has room to grow.

    https://www.brentozar.com/archive/2015/12/tracking-tempdb-growth-using-extended-events/

    https://nebraskasql.blogspot.com/2016/06/finding-file-growths-with-extended.html

    https://sqlzealots.com/2015/04/01/auto-file-growth-track-the-growth-events-using-extended-events-in-sql-server/

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

  • SQL2005 doesn't have XE!

    Check your default trace file or run a trace to figure it out !

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Johan Bijnens wrote:

    SQL2005 doesn't have XE!

    Check your default trace file or run a trace to figure it out !

    Ah... thanks, Johan.  Shows how much I use EE.

    The problem with using the default trace is that it may not actually last long enough to be of any benefit.

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

  • indeed, Jeff.

    Default trace only last so long ...

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 7 posts - 1 through 7 (of 7 total)

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