Adding an extra file to TEMPDB

  • We have a sever with 2 physical processors and 8 logical ones. We are seeing quite a lot of blocking as the tempdb seems to be getting used quite heavily. From running sp_whoisactive i can see in the 'wait info' column that there are 'PAGELATCH_UP:Tempdb:1(GAM)' as the main reason for the waits whenever there is any blocking going on.

    As we only have one Tempdb.mdf file on the D drive (raid 5), I am thinking of adding a secondary Tempdb.ndf file on the E drive (raid 5).

    My main question is regarding the sizing of this new file. When i view the database properties and view the files i can see the intial size was 8mb (which is the defualt as far as i understand) and it is set to grow by 10% unrestricted. When i browse to the actual Tempdb file it is approx 1.5GB.

    Do i set the new secondary .ndf file as initial size 8MB or 1.5GB as i understand the files have to be sized the same.

    Thanks

    Steven

    ps I am also aware of the 1/4 - 1/2 number of tempdb files per logical processor which in my case is 8 logical processors - so 2 files should be ok for starters ! (maybe i'll go to 4 if it doesnt fix the contention issues):-)

  • You can add the new file on the D drive, you have allocation contention, not IO contention (unless there are additional problems you haven't mentioned.

    If TempDB is growing to 1.5 GB from a start of 8MB it should be set to start at at least 1.5GB. Add the second file at say 800 MB and set the first file to an initial size of 800 MB as well. Maybe 900 or 1000 each depending how much spare room you want. They need to be the same size and there's no good reason to force TempDb to grow from 8MB to 1.5GB every time SQL starts, unless you want to add more overhead to the apps that is. Once you've added the second file, restart SQL to reset the existing file to the new size and to clear TempDB.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Yes, add a new tempdb data file, at exactly the same size as the current data file. Use KB to get the exact size. [That way you will avoid any potential issue on how SQL splits work between the two files.]

    EXEC tempdb..sp_helpfile

    --That will give you the KB.

    You should always use a script to make file changes, so:

    ALTER DATABASE tempdb

    ADD FILE ( NAME = tempdev2, SIZE = <data_file_size_from_helpfile_above>KB, FILENAME = '...', FILEGROWTH = 50MB )

    ALTER DATABASE tempdb

    MODIFY FILE ( NAME = tempdev, FILEGROWTH = 50MB )

    Then, set up a start up proc to shrink both tempdb files ... I'd say to 1G each, so you have some cushion. You really don't ever want the tempdb data files dynamically growing if you can avoid it.

    After the next SQL start up, if you prefer, you can delete (or effectively disable by RETURNing immediately) the start up script.

    If you need help with the start up script, let me know.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher (2/27/2013)


    Then, set up a start up proc to shrink both tempdb files ... I'd say to 1G each, so you have some cushion. You really don't ever want the tempdb data files dynamically growing if you can avoid it.

    Why would you set a startup proc to shrink TempDB when restarting SQL sets TempDB back to it's defined size? If the files are wanted at 1GB each, set the size of both to 1GB and when SQL starts up the files will be 1 GB each without any need for shrinks (which is documented to be able to cause corruption in TempDB)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • ScottPletcher (2/27/2013)


    Then, set up a start up proc to shrink both tempdb files

    NEVER manually shrink tempdb files. It can corrupt the tempdb files. Avoid shrinking files of any database if you can, especially doing it on a regular basis. Putting it in a startup procedure is one of the worst ideas I've heard yet, tempdb or not tempdb.

    The key thing that you want to have is all tempdb data files to have the same amount of free space in them. The algorithm SQL uses ends up with being basic round-robin if all data files have the same amount of free space. So simply adding a new file of the same physical size is not sufficient as the new file will have more free space, and all tempdb activity will go to that file only. You need to set them to the same size and then restart SQL so they all start empty (same free space).


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • GilaMonster (2/27/2013)


    ScottPletcher (2/27/2013)


    Then, set up a start up proc to shrink both tempdb files ... I'd say to 1G each, so you have some cushion. You really don't ever want the tempdb data files dynamically growing if you can avoid it.

    Why would you set a startup proc to shrink TempDB when restarting SQL sets TempDB back to it's defined size? If the files are wanted at 1GB each, set the size of both to 1GB and when SQL starts up the files will be 1 GB each without any need for shrinks (which is documented to be able to cause corruption in TempDB)

    If a tempdb data file is 1.5G when SQL goes down, won't it be 1.5G when SQL comes back up? Isn't it automatically reset to its previous size? [And from a performance perspective, that's what you want it to do!]

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Robert Davis (2/27/2013)


    ScottPletcher (2/27/2013)


    Then, set up a start up proc to shrink both tempdb files

    NEVER manually shrink tempdb files. It can corrupt the tempdb files. Avoid shrinking files of any database if you can, especially doing it on a regular basis. Putting it in a startup procedure is one of the worst ideas I've heard yet, tempdb or not tempdb.

    The key thing that you want to have is all tempdb data files to have the same amount of free space in them. The algorithm SQL uses ends up with being basic round-robin if all data files have the same amount of free space. So simply adding a new file of the same physical size is not sufficient as the new file will have more free space, and all tempdb activity will go to that file only. You need to set them to the same size and then restart SQL so they all start empty (same free space).

    H

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Robert Davis (2/27/2013)


    ScottPletcher (2/27/2013)


    Then, set up a start up proc to shrink both tempdb files

    NEVER manually shrink tempdb files. It can corrupt the tempdb files. Avoid shrinking files of any database if you can, especially doing it on a regular basis. Putting it in a startup procedure is one of the worst ideas I've heard yet, tempdb or not tempdb.

    The key thing that you want to have is all tempdb data files to have the same amount of free space in them. The algorithm SQL uses ends up with being basic round-robin if all data files have the same amount of free space. So simply adding a new file of the same physical size is not sufficient as the new file will have more free space, and all tempdb activity will go to that file only. You need to set them to the same size and then restart SQL so they all start empty (same free space).

    How else do you reduce the size of a tempdb data file safely except at initial start up?

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Maybe it's just an issue I had.

    The last time I altered the SIZE of tempdb data files in SQL, when SQL restarted tempdb files were still larger than that.

    If the ALTER DATABASE ... SIZE = nnnKB beforehand will work on restart, then of course that's a much better method.

    I'm dealing with a range of servers from early SQL 2005 to late SQL 2008 so I might be mixing an earlier problem in that no longer applies.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher (2/27/2013)


    If a tempdb data file is 1.5G when SQL goes down, won't it be 1.5G when SQL comes back up? Isn't it automatically reset to its previous size? [And from a performance perspective, that's what you want it to do!]

    No, it returns to the defined size, not the physical size. The defined size for tempdb files are maintained in master.sys.master_files whereas the current size is maintained in tempdb.sys.database_files. These two tables are the same for all databases except tempdb. Only tempdb is tracked differently like this.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Robert Davis (2/27/2013)


    ScottPletcher (2/27/2013)


    If a tempdb data file is 1.5G when SQL goes down, won't it be 1.5G when SQL comes back up? Isn't it automatically reset to its previous size? [And from a performance perspective, that's what you want it to do!]

    No, it returns to the defined size, not the physical size. The defined size for tempdb files are maintained in master.sys.master_files whereas the current size is maintained in tempdb.sys.database_files. These two tables are the same for all databases except tempdb. Only tempdb is tracked differently like this.

    OK. I always explicitly specify the size of tempdb data files, and they don't grow, so mine come back up the same size.

    I have had to used start up procs in the past, but hopefuly that's obsolete now, if you can issue an ALTER on the size and have the file automatically become that smaller size when SQL restarts.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Ok thanks for the info.

    So just to be clear about this one.

    1. I can put the secondary tempdb file on the same disc as the primary file.

    2. Make the second file an inital size of say 1Gb

    3. Reduce the current size of the primary tempdb to 1 Gb (or whatever size i have set in point 2 above)

    4. Reboot the server for the change in the primary file to take effect.

    Regarding point number 2 above. My current size is 1.5gb, so would it not be better to make the second file 1.5gb or is the reason for your suggestion of 800mb because there would be 2 files of 800mb each therefore equating to approx 1.5gb in total ?

    Regarding point number 3 above. How to i reduce the size from 1.5gb to 800mb or 1gb ? Would this be by using the ALTER database command with the shrinkfile statement ?

    Regarding point number 4 above. Is a reboot necessary - could i just restart the sql server agent ?

    Thanks

    Steven

  • Yes, that was the reason for his suggestion of two files at 800MB so that together they would be about 1.5 GB. Personally, I always advocate pre-sizing your tempdb files to consume ~90% of the available drive space.

    You can read my recommendations in full detail in the white paper I wrote on tempdb:


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • ScottPletcher (2/27/2013)


    GilaMonster (2/27/2013)


    ScottPletcher (2/27/2013)


    Then, set up a start up proc to shrink both tempdb files ... I'd say to 1G each, so you have some cushion. You really don't ever want the tempdb data files dynamically growing if you can avoid it.

    Why would you set a startup proc to shrink TempDB when restarting SQL sets TempDB back to it's defined size? If the files are wanted at 1GB each, set the size of both to 1GB and when SQL starts up the files will be 1 GB each without any need for shrinks (which is documented to be able to cause corruption in TempDB)

    If a tempdb data file is 1.5G when SQL goes down, won't it be 1.5G when SQL comes back up? Isn't it automatically reset to its previous size?

    No, it's set to it's default size. So if the file's initial size is 800MB and it's grown to 1.5 GB, on restart it'll be 800MB again. That's the only safe way to shrink TempDB files (other than starting SQL in single user mode and running a shrinkfile). Any form of shrink where there's a chance for other users (even at the point of startup) can cause corruption that requires a restart to fix.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • stehoban (2/27/2013)


    Regarding point number 3 above. How to i reduce the size from 1.5gb to 800mb or 1gb ? Would this be by using the ALTER database command with the shrinkfile statement ?

    Not shrink. Alter database and set the initial size of the original TempDB file. That size takes effect on a restart of SQL, resulting in 2 files of the defined size.

    Regarding point number 4 above. Is a reboot necessary - could i just restart the sql server agent ?

    No reboot necessary. Not SQL agent though, restart SQL Server itself

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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