September 9, 2013 at 2:43 pm
I discovered that we have a couple data files assigned to our tempdb. The primary data file (tempdev.mdf) is on the D: drive but there is a secondary data file (tempdev_2.ndf) that is on C:. The server locked up over the weekend and I suspect that the secondary log file grew to exceed the available hard drive space. My plan is to move the secondary file to the D: drive where there is more space. I see the best practices recommend that the data files be the same size and that we should have one data file per cpu core.
Currently the primary data file is set to a initial size of 3GB with autogrowth of 10%.
The secondary file is set to initial size of 3MB with autogrowth of 1MB.
I think the 3GB is a reasonable size for the tempdb based on the current load but I'm wondering if I should split it up into multiple data files with smaller initial sizes. What should I be checking to determine the number of files?
The system is running SS 2008 R2 with 8 cores and 32 GB ram.
September 9, 2013 at 4:14 pm
The thing about "one file per core" is not correct, but what is correct is more difficult to say. I vaguely recall something about 1 file for every four cores, at least if you have many of them. And of course, in the end in depends on your workload.
So if you see no major issues, stick with two 3GB files, both set to 10% autogrow.
Then again, if your theory is correct that the file on C outgrow the disk, both files are current far too small. Autogrow should not really happen.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 11, 2013 at 7:29 am
This is a good article outlining tempdb, it's not too lengthy and should help answer your questions:
http://www.idera.com/resourcecentral/whitepapers/demystify-tempdb-performance-and-management
It will definitely answer your questions regarding how to set the growth...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
September 11, 2013 at 7:43 am
Erland Sommarskog (9/9/2013)
The thing about "one file per core" is not correct, but what is correct is more difficult to say. I vaguely recall something about 1 file for every four cores, at least if you have many of them. And of course, in the end in depends on your workload.So if you see no major issues, stick with two 3GB files, both set to 10% autogrow.
Then again, if your theory is correct that the file on C outgrow the disk, both files are current far too small. Autogrow should not really happen.
Erland,
I was always under the imrpession that auto grow should be set to a fixed amount? Or isn't it as much of an issue being a % for the tempdb?
September 11, 2013 at 3:17 pm
SQLSteve (9/11/2013)
I was always under the imrpession that auto grow should be set to a fixed amount? Or isn't it as much of an issue being a % for the tempdb?
You may be right there, provided that you set a reasonable
big size for autogrow like 500 MB at least. Please not 1MB!
My main point was that size should not be fixed.
The reason you prefer fixed size for autogrow is that 10% for 1TB database will take a serious toll if it happens during peak hours.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 11, 2013 at 3:27 pm
Yes it should be set to a fixed amount...and other equally important items are outlined quite plainly in the article I pointed out from idera (above) - have you had a chance to go through it?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
September 13, 2013 at 9:53 am
Thanks for the info. I think I'm going to start conservative and keep the changes to a minimum. I've run some of the scripts and it doesn't look like we have any contention happening. I'm going to move the secondary file tempdev_2 to the D: drive so it's not on the OS drive and set the growth to a fixed amount. I also setup an alert to notify me if the data files grow so that I can adjust the initial sizes if necessary.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply