Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Adding an extra file to TEMPDB Expand / Collapse
Author
Message
Posted Wednesday, February 27, 2013 9:47 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:07 AM
Points: 238, Visits: 948
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)
Post #1424621
Posted Wednesday, February 27, 2013 10:10 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 42,771, Visits: 35,870
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 2008, MVP
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

Post #1424633
Posted Wednesday, February 27, 2013 11:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:49 PM
Points: 2,030, Visits: 3,030
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1424672
Posted Wednesday, February 27, 2013 1:53 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 42,771, Visits: 35,870
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 2008, MVP
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

Post #1424736
Posted Wednesday, February 27, 2013 2:03 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 15, 2014 5:01 PM
Points: 1,613, Visits: 1,539
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 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1424740
Posted Wednesday, February 27, 2013 2:53 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:49 PM
Points: 2,030, Visits: 3,030
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1424770
Posted Wednesday, February 27, 2013 2:54 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:49 PM
Points: 2,030, Visits: 3,030
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1424771
Posted Wednesday, February 27, 2013 2:54 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:49 PM
Points: 2,030, Visits: 3,030
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1424774
Posted Wednesday, February 27, 2013 2:58 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:49 PM
Points: 2,030, Visits: 3,030
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1424776
Posted Wednesday, February 27, 2013 2:58 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 15, 2014 5:01 PM
Points: 1,613, Visits: 1,539
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 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1424777
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse