January 14, 2009 at 7:17 am
I believe I understand the reason why you can achieve tempdb performance benefits from creating one data file per CPU. What I am curious about now is how well the proportional fill algorithm keeps the files sizes similar. I created tempdb with 8 data files, one file per CPU. (4 CPU x 2 Core Each = 8). Each data file is 512MB with 100MB Growth. The log file is 1024MB with 100MB Growth.
After one week I checked sys.master_files and can see each data file at 512MB. sysfiles however reports different sizes as seen below.
name Size MB Growth
-------------------- ---------------------- -----------
tempdev1 2314.375 100
tempdev2 1244.1875 100
tempdev3 892.75 100
tempdev4 888.25 100
tempdev5 673.3125 100
tempdev6 690.8125 100
tempdev7 674.0625 100
tempdev8 988.6875 100
templog1 1024 100
The size in sysfiles appears to be the current size, not the initial size. BOL says SIZE listed in sys.master_files is the current size, but that is clearly not the case, at least for tempdb.
I expected to see similar data files sizes due to the proportional fill algorithm, but I'm not seeing that. Is what I see above normal behavior for the algorithm or is something wrong?
Thanks, Dave
January 15, 2009 at 7:06 am
The big thing here is that you should not be enabling autogrowth on the tempdb files. Not only would this be a performance hit on growth, but you won't get proportional fill.
Set all the files at the same size. (from http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx)
Having too many files increases the cost of file switching, requires more IAM pages, and increases the manageability overhead. The size of each file is configurable. More files can be added, moved, or removed. To better use the allocation mechanism (proportional fill), the size of the files should be equal. Generally, for SQL Server 2005 it is recommended that you create tempdb files striped across fast disks.
January 15, 2009 at 7:12 am
I am seeing one of the two effects you list.
I split my tempdb into 8 files at the suggestion of MS (for AX). The initial sizes show as expected in the sys.master_files view, and the current sizes show in the sys.sysfiles and sys.database_files views. Here's an explanation from Tibor, which I found on Egghead:
Differences in file sizes sys.database_files and sys.master_files - Tibor Karaszi
05-Mar-08 03:27:49
Tempdb is special. Sys.master_files holds the size etc to make the tempdb size at startup (remember
that tempdb is re-created each time you start SQL Server). Sys.database_files inside the database
holds the actual values.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi%5B/quote%5D
That makes sense (Tibor always makes sense, but we still must follow the reasoning ourselves). Tempdb is re-created from scratch at start-up, and it needs to know how you want it sized.
As for the differences in file sizes, I am not seeing such variance. My files' sizes are within 5% of each other. When I was calculating the sizes, I over-built a bit. Also, I set maxdop to 1 (another suggestion from MS) to permit only one core per query. This is not a suggestion to you, just wondering if that makes a difference in keeping multiple temp files evenly sized.
January 15, 2009 at 7:27 am
It literally is to allow proportional fill. Being on a SAN my environment really is not disk constrained from a partition standpoint (a lot of checking went into that one), so tempdb is used to really just manage some latch contention problems that were impacting some applications.Tempdb is also on a partition sized for it's purpose (again, solely because we don't have performance issues thanks to the SAN aspect).
Setting MAXDOP to 1 is ok, depending upon your workload. Bear in mind that doing this prevents any queries from executing in parallel (should the execution plan decide that this would be of benefit).
In general I set the MAXDOP to half of the number of sockets on the server, and from there tune up or down as needed (checking the CX_PACKET wait stats).
January 15, 2009 at 7:43 am
Thanks for the tip on CX_PACKET, Nicholas.
Please allow me to ask you about whether auto grow should be set on for these multiple tempdb files. I have mine all set to autogrow, and that's not causing me any problems so far. Also, the article you reference seems to indicate that we should set auto grow on (under the best practices section).
I'm wondering if the point of limiting auto grow for tempdb is just to make sure that the tempdb files don't get expanded too often. Most of these recommendations seem to match up with the ones in the article you referenced.
I hear you about the maxdop. In some cases it might be helpful to override that with a query hint, and during upgrades of the product I return it to 0 to allow the upgrade to proceed more quickly.
January 15, 2009 at 7:48 am
If you are using a single TempDB datafile then go with the autogrowth (also set the initial size at a size greater than you would expect it to grow to, prevents autogrowth hopefully).
If you are using multiple datafiles then do not enable autogrowth for them, otherwise you won't get proportional fill, and as you will be growing files you probably won't have much better performance than with a single file.
January 15, 2009 at 7:59 am
No offense intended, it just seems to me that the better path to go is to first try to set the file sizes large enough to deal with most of what's going to get thrown at the server, but to leave auto grow on in case the extra room is needed.
In other words, I completely disagree with your statement that auto grow should be set off for these multiple tempdb files. It conflicts with what MS considers best practices and my experience.
No doubt, this solution is working for you in your situation. In specific situations, such as yours, setting auto grow off has worked well for you. My disagreement is that this should be applied in general.
January 15, 2009 at 8:22 am
Oliver (1/15/2009)
No offense intended, it just seems to me that the better path to go is to first try to set the file sizes large enough to deal with most of what's going to get thrown at the server, but to leave auto grow on in case the extra room is needed.In other words, I completely disagree with your statement that auto grow should be set off for these multiple tempdb files. It conflicts with what MS considers best practices and my experience.
No doubt, this solution is working for you in your situation. In specific situations, such as yours, setting auto grow off has worked well for you. My disagreement is that this should be applied in general.
I agree with that... ever seen what happens when TempDB hits the stops? Try it... setup a small instance of the Developer Edition and try it.
--Jeff Moden
January 15, 2009 at 8:32 am
That's all good. My tempdb is vastly larger than it will ever need to be. Set yours how best works for you. 🙂
There's really no right or wrong when it comes to these things, it's not a one size fits all scenario. Man this stuff would be a great deal simpler if it was.
January 16, 2009 at 1:55 am
The tricky part for most of us is: How do you size your tempdb ?
Nicholas Cain (1/15/2009)
...My tempdb is vastly larger than it will ever need to be. ....
Do you have alert mechanisms in place to follow up if tempdb reaches more then 80% or so ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 16, 2009 at 2:06 am
Nicholas Cain (1/15/2009)
The big thing here is that you should not be enabling autogrowth on the tempdb files. Not only would this be a performance hit on growth, but you won't get proportional fill.Set all the files at the same size. (from http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx)
Having too many files increases the cost of file switching, requires more IAM pages, and increases the manageability overhead. The size of each file is configurable. More files can be added, moved, or removed. To better use the allocation mechanism (proportional fill), the size of the files should be equal. Generally, for SQL Server 2005 it is recommended that you create tempdb files striped across fast disks.
... but you won't get proportional fill....
I have to disaggree on that.
It states
To better use the allocation mechanism (proportional fill), the size of the files should be equal.
IMO this states the proportional fill will be able to work more optimal if file sizes are equal.
It does not state proportional fill will not be available.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 16, 2009 at 5:31 am
What's the name of the Microsoft article that talks about auto grow not using proportional fill? Better yet do you have the link?
Currently we have 8 data files spread across a SAN array (RAID 10). EMC recommended this week we spread the 8 data files over 4 LUNs so we would have (4) 1+1 mirrors, thereby increasing our IO paths from one to four. We may give that a try, but I would definitely like to read the article on how auto grow does not use proportional fill. I have yet to find anything stating that auto grow will not use proportional fill. Just curious.
Thanks, Dave
January 16, 2009 at 6:45 am
the article has been mentioned by Nicholas Cain in one of the previous replies.
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx)
Nicholas Cain (1/15/2009)
The big thing here is that you should not be enabling autogrowth on the tempdb files. Not only would this be a performance hit on growth, but you won't get proportional fill.Set all the files at the same size. (from http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx)
Having too many files increases the cost of file switching, requires more IAM pages, and increases the manageability overhead. The size of each file is configurable. More files can be added, moved, or removed. To better use the allocation mechanism (proportional fill), the size of the files should be equal. Generally, for SQL Server 2005 it is recommended that you create tempdb files striped across fast disks.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 16, 2009 at 7:26 am
I thought he was referring to a different article. The one he referenced in this chain doesn't state that auto grow will disable proportional fill. Regardless I'm still having issues with proportional fill not functioning like I thought it would. I'll try an experiment. The instance was recently restarted so all data files are again the same size. The next time they grow I will see if they grow proportionally. If they don't I will turn off auto grow and restart the instance and see if this resoves the issue of the files not growing proportionally. I don't believe it will, but I can give it a try.
Dave
January 16, 2009 at 7:33 am
DBADave (1/16/2009)
...If they don't I will turn off auto grow and restart the instance and see if this resoves the issue of the files not growing proportionally....
turn off auto grow VS files not growing proportionally
if you turn of auto grow, they will not grow, hence they cannot grow proportionally.....
I guess you mean you would compare usage stats and see if they are used in a balanced way.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply