Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Adding an extra file to TEMPDB


Adding an extra file to TEMPDB

Author
Message
PearlJammer1
PearlJammer1
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 1427
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):-)
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47287 Visits: 44392
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


ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3946 Visits: 6686
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)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47287 Visits: 44392
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


Robert Davis
Robert Davis
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1702 Visits: 1623
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
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master, SQL Server MVP
Database Engineer at BlueMountain Capital Management
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3946 Visits: 6686
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)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3946 Visits: 6686
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)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3946 Visits: 6686
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)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3946 Visits: 6686
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)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Robert Davis
Robert Davis
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1702 Visits: 1623
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
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master, SQL Server MVP
Database Engineer at BlueMountain Capital Management
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search