locked
How Much Space is Required In Temp DB? RRS feed

  • Question

  • Hi All,

    I want update one huge table using batches and how much space is  required in my temp db database in sqlsever.

    example:

    update table A set year='2102' where year is null

    year column data type is varchar(5) and I have 40 millions records in that table

    How to calculate the required space for above update statement.

    Thanks

    CMK

    Tuesday, January 21, 2014 8:39 PM

Answers

  • >How to calculate the required space for above update statement.

    You said you are going to do in batches. So it is not going to require significant space in tempdb.

    UPDATE in batches:

    http://www.sqlusa.com/bestpractices2005/hugeupdate/


    Kalman Toth Database & OLAP Architect SELECT Query Video Tutorial 4 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    • Proposed as answer by Elvis Long Wednesday, January 22, 2014 9:50 AM
    • Marked as answer by Elvis Long Friday, January 31, 2014 3:41 AM
    Wednesday, January 22, 2014 1:04 AM
  • As you might have suspected, it depends.

    There may be more factors involved, but at the very least the following are relevant:

    • Is the columns indexed? If it is not part of any index, then the update will not affect tempdb
    • The size of the column you are updating, and the size of the clustered index. The larger they are, the more space you need
    • The version of SQL Server you are running. When running SQL Server 2005 or later, there is no significant difference. SQL Server 2000 and earlier will use more space

    If table A has a clustered index on a unique int column, and column year is indexed, then on SQL Server 2005 and later, updating a batch of 500,000 rows needs 23 MB in tempdb, a batch of 1,000,000 rows 44 MB, etc.

    Note that you would typically use smaller batches, although that depends on your system resources.


    Gert-Jan

    • Marked as answer by Elvis Long Friday, January 31, 2014 3:41 AM
    Wednesday, January 22, 2014 7:48 PM

All replies