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 6 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply