March 28, 2011 at 1:50 pm
Raghavender (3/28/2011)
In between, this is other question on TempDB only:If we add multiple datafiles to tempdb database we need to keep all datafiles of same size.
Can any body tell me what is the exact reason for this ?
Is this implies only for TempDB or other user databases also ?
It's because you want it to round-robin on the files, if memory serves. This technique is only really useful in a heavily contested TempDB when there's a lot (and I mean hundreds per second) of #tbl creation/destruction. There ends up being a delay because of all of them fighting for space in the same file.
You wouldn't do this in any other database for that reason, you'd only use it for filegroup control or if your RAIDs are really poor and you need to split across physical spindles... in which case I'd still do it by filegroup.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 28, 2011 at 1:55 pm
and one more question:
For eg: if we have 5 cpus in our server, suggested tempdb files will be 5, is it minimum or maximum ?
Thank You.
Regards,
Raghavender Chavva
March 28, 2011 at 2:50 pm
Raghavender (3/28/2011)
and one more question:For eg: if we have 5 cpus in our server, suggested tempdb files will be 5, is it minimum or maximum ?
Neither, it's a starting point. You'll have to watch for tempdb contention to figure out if you need more, but you also need to figure out WHY you have the contention before you do that.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 28, 2011 at 4:54 pm
The tempdb one-data-file-per-processor-core is discussed in detail on Paul Randall's blog here: http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%281230%29-tempdb-should-always-have-one-data-file-per-processor-core.aspx. He talks about how this Microsoft recommendation is probably not the best in most cases, but also how you can look at your specific waits to see how to basically adjust the number of files.
As regards the BOL documentation. Uh, ya. BOL is great at documenting some things, but is ambiguous or silent on other areas; hence, undocumented features. Here are a few ambiguous excerpts:
From technical article: http://msdn.microsoft.com/en-us/library/cc966545.aspx
"When the server restarts, the tempdb file size is reset to the configured value (the default is 8 MB)."
Bold and Italics are mine. We can infer that tempdb's size can be 'configured' and that the changes will stick, rather than the new tempdb taking the size from Model. Result: vAmbiguous.
"Each time SQL Server restarts, tempdb is copied from the model database. It inherits certain database configuration options, such as ALLOW_SNAPSHOT_ISOLATION, from the model database."
...so most misc database properties are taken from Model, but you have to dig deeper to figure out which properties don't come from Model. Result: vAmbiguous.
But, like Craig said, the easiest way to know is to observe this for yourself. Modify tempdb and add a new file to the filegroup and set its initial size to 1G, then recycle SQL Services. You'll notice that you will still continue to have this newly added file for the new tempdb, as well as it's initial size being 1G rather than closer to 2MB or 3MB like a new user database would be (unless you changed the file size settings in Model). XAmbiguous.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply