February 27, 2013 at 10:00 am
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)
February 27, 2013 at 10:07 am
Please don't cross post. It just wastes peoples time and fragments replies.
No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx
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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply