none
TempDB is growing abnormally,How to check and find out what makes it grow

    Question

  • TempDB is growing abnormally...How to check and find out the reasons what makes it to grow

    Thanks
    Kishore N
    Friday, January 22, 2010 2:51 PM

Answers

  • TempDB is growing abnormally...How to check and find out the reasons what makes it to grow

    Thanks
    Kishore N

    One of the most common causes for this that I see is a complex query that makes heavy usage of worktables during processing or makes heavy use of Common Table Expressions (CTE's) that reference each other in a recursive fashion.  Often times these can be broken down into separate logical operations that use an intermediate temp table to avoid the large worktables generated during execution, but you have to know what query caused tempdb to blow up to do this.  Some other things not mentioned would be to look at the DMV's to see what's allocated the most space:


    select * 
    from sys.dm_db_session_space_usage spu
    join sys.dm_exec_sessions s on s.session_id = spu.session_id
    join sys.dm_exec_requests r on s.session_id = r.session_id
    cross apply sys.dm_exec_sql_text(sql_handle) t 
    order by internal_object_alloc_page_count desc

    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Monday, January 25, 2010 11:59 AM
  • Could you quantify abmormally?

    You can run a profiler trace to look for long running queries.  Chances are if something is pumping a ton of data through tempdb it is going to be taking longer than the other queries running.  This will work as long as this problem occurs constantly.  Otherwise you'll also need to identify when tempdb is growing so that you can correlate the time it's happening with your trace data.  To do that you'll need to monitor tempdb size and/or look at io to tempdb.

    As far as what makes it grow it could be many things.  Joins can take place in tempdb.  There are other times that the SQL engine may used tempdb based on the determined query plan.

    While I was doing a quick search to find items in the query plan that would cause the engine to use tempdb I found this great article from Microsoft that steps you through troubleshooting your problem:

    Friday, January 22, 2010 3:08 PM
  • Hello Kishore

    Tempdb is used all of the time by SQL Server.  Essentially all transactions (including Database Maintenance) and analytical processing are processed via tempdb.  Some of the more common reasons for a bloated tempdb include: -

    Index rebuilds/reorganisations on large tables.  There is an option in SQL 2005 maintenance plans to use/not use tempdb for sorting.

    Heavy transactional processing on databases with the backup option set to "full".  You could consider issuing a "Checkpoint" but research this first.

    Temporary Tables being created/dropped on a regular basis.  If this is the case you could create a "genuine" table and use "TRUNCATE" to purge  the data; but this will depend on who is creating the tables (if a part of regular user activity then do not consider this).


    Tony C
    Friday, January 22, 2010 5:12 PM
  • Hello kishore

    Check this link, it should be useful to you

    http://sqlserver2000.databases.aspfaq.com/why-is-tempdb-full-and-how-can-i-prevent-this-from-happening.html



    Regards RM Thirunavukkarasu MCP, MCITP http://thiruna.blog.com Please click the Mark as Answer button if a post solves your problem
    Monday, January 25, 2010 8:10 AM
  • you should read this paper: http://download.microsoft.com/download/D/9/4/D948F981-926E-40FA-A026-5BFCF076D9B9/TEMPDB_Capacity_Planning_Index.doc


    Maninder
    Monday, January 25, 2010 1:44 PM

All replies

  • Could you quantify abmormally?

    You can run a profiler trace to look for long running queries.  Chances are if something is pumping a ton of data through tempdb it is going to be taking longer than the other queries running.  This will work as long as this problem occurs constantly.  Otherwise you'll also need to identify when tempdb is growing so that you can correlate the time it's happening with your trace data.  To do that you'll need to monitor tempdb size and/or look at io to tempdb.

    As far as what makes it grow it could be many things.  Joins can take place in tempdb.  There are other times that the SQL engine may used tempdb based on the determined query plan.

    While I was doing a quick search to find items in the query plan that would cause the engine to use tempdb I found this great article from Microsoft that steps you through troubleshooting your problem:

    Friday, January 22, 2010 3:08 PM
  • Hello Kishore

    Tempdb is used all of the time by SQL Server.  Essentially all transactions (including Database Maintenance) and analytical processing are processed via tempdb.  Some of the more common reasons for a bloated tempdb include: -

    Index rebuilds/reorganisations on large tables.  There is an option in SQL 2005 maintenance plans to use/not use tempdb for sorting.

    Heavy transactional processing on databases with the backup option set to "full".  You could consider issuing a "Checkpoint" but research this first.

    Temporary Tables being created/dropped on a regular basis.  If this is the case you could create a "genuine" table and use "TRUNCATE" to purge  the data; but this will depend on who is creating the tables (if a part of regular user activity then do not consider this).


    Tony C
    Friday, January 22, 2010 5:12 PM
  • Hello kishore

    Check this link, it should be useful to you

    http://sqlserver2000.databases.aspfaq.com/why-is-tempdb-full-and-how-can-i-prevent-this-from-happening.html



    Regards RM Thirunavukkarasu MCP, MCITP http://thiruna.blog.com Please click the Mark as Answer button if a post solves your problem
    Monday, January 25, 2010 8:10 AM
  • TempDB is growing abnormally...How to check and find out the reasons what makes it to grow

    Thanks
    Kishore N

    One of the most common causes for this that I see is a complex query that makes heavy usage of worktables during processing or makes heavy use of Common Table Expressions (CTE's) that reference each other in a recursive fashion.  Often times these can be broken down into separate logical operations that use an intermediate temp table to avoid the large worktables generated during execution, but you have to know what query caused tempdb to blow up to do this.  Some other things not mentioned would be to look at the DMV's to see what's allocated the most space:


    select * 
    from sys.dm_db_session_space_usage spu
    join sys.dm_exec_sessions s on s.session_id = spu.session_id
    join sys.dm_exec_requests r on s.session_id = r.session_id
    cross apply sys.dm_exec_sql_text(sql_handle) t 
    order by internal_object_alloc_page_count desc

    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Monday, January 25, 2010 11:59 AM
  • you should read this paper: http://download.microsoft.com/download/D/9/4/D948F981-926E-40FA-A026-5BFCF076D9B9/TEMPDB_Capacity_Planning_Index.doc


    Maninder
    Monday, January 25, 2010 1:44 PM