September 19, 2011 at 6:49 pm
Hello, a question regarding multiple TempDB files.
SQl 2008
Recently we had an DB audit done by a 3rd party and they gave us a list of "Best Practice" recommendations.
Two points in the report regarding TempDB's that I am unsure on.
1. They said you should create one TempDB data file per CPU. We have two quad core CPU's so assuming that means we need 8 TempDB data files.
I can understand the logic behind this if our server had 8 separate physical disks and put one TempDB MDF / NDF on each disk but we don't.
So is their any benefit in doing what they suggested and just created 8 TempDB datafiles on the same physical disk.
i.e "G:\data\TempDBdata.mdf", "G:\data\TempDBdata2.ndf"........"G:\data\TempDB8.ndf"
The I/O contention would still be there as it is on one disk but the report mentions their will be optimisation due to multiple threads accessing the many Tempdb data files??
2. The report also mentioned doing the same for the TempDB log files. I have read that unless the max size of a log file is going to be reached and a "rollover" file is required then their is no point in doing this [multiple LDF files] because they may never be used.
Relative Report Snippet
"This also ties in with the Tempdb database optimization recommendations (p.19) to create one Tempdb datafile for each CPU on the server. This configuration would enable multiple threads of activity to access different Tempdb data files at the same time.
With multiple Tempdb files, SQL Server will use a round-robin method of allocating pages across each file. As new data page allocation is required, the next file in the sequence is used, with each file having its own global allocation map; this can reduce the locking contention that would occur on a heavily used single file Tempdb database."
Still can not see how creating multiple LDF files would help though or having multiple datafiles on the same disk.
September 19, 2011 at 8:38 pm
I am not sure how multiple LDFs can help either. SQL Server treats multiple log files as single log file only and uses them on a sequential basis, allocating VLFs on the file which is full and using the VLFs on the file in which it is ready for use.
Regards,
Raj
September 19, 2011 at 10:01 pm
We have assigned multiple data files for tempdb to both of the following configurations:
1. all on one disk (up to 12 files)
2. split evenly between two disks (up to 12 files)
In both cases we saw marked improvement in performance.
As for the second question concerning log files - NO don't do it. The only reason to have multiple log files is if you expect to have your log reach more than 2TB.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 19, 2011 at 10:33 pm
That is a very complicated subject, and is certainly not as simple as your vendor stated. You should read this article before taking any action.
A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core
September 19, 2011 at 11:20 pm
I fully agree. We have tested on some systems with different configurations, disk speeds etc...and found in some cases the multiple NDF's actually slowed down the processing of some tasks.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
September 20, 2011 at 2:45 am
uncle_boris (9/19/2011)
2. The report also mentioned doing the same for the TempDB log files. I have read that unless the max size of a log file is going to be reached and a "rollover" file is required then their is no point in doing this [multiple LDF files] because they may never be used.
They'll all be used, but they'll be used one after the other, not in parallel. No good reason whatsoever to create multiple log files for a database.
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
September 20, 2011 at 3:56 am
seems like a bit of dodgy advice from your 3rd party experts. The one file per CPU advice was given a while ago but has since been disproven to be a hard and fast rule..
September 20, 2011 at 2:36 pm
My 2 cents, based on how we do it in our shop.
We used to create one tempdb data file per cpu core, but later on I realized too many data files may backfire in terms of performance.
Currently, we apply the "one datafile per CPU" rule on servers with up to 8 processors. On servers with more than 8 CPU cores we don't normally raise the number of files to more than 8.
One log file should be enough, ie. this rule does not apply to log files.
You can place all files to a single dedicated IO device. The performance optimization you get is from multiple CPUs accessing the files simultaneously, so IO throughput is not as much of a concern (of course, the more disks you can spread them over the better).
We also ensure all data files are of the exact same size and are on no autogrowth. If they are stored in a dedicated disk, you can arrange it so that they are all of the same size and large enough so autogrowth is not needed. The equal sizing ensures the SQL optimization algorithm works properly for distributing the tempdb workload among the files.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 20, 2011 at 2:44 pm
I already have two tempdb data files set up on the main server. Each on a separate disk and each the same size.
I have my doubts as to how much the second one is actually being used.
Do you know of a way to see what tempdb file is being accessed? or when the last access was?
Does SQL use the initial one first and then the other when needed or could SQL use which ever Tempdb file it thinks is best at the time?
I have a feeling that in most cases throughout the day the load is not enough to need two tempdb's let alone 8, although I would keep two as minimum.
thanks
September 20, 2011 at 3:07 pm
uncle_boris (9/20/2011)
Do you know of a way to see what tempdb file is being accessed? or when the last access was?Does SQL use the initial one first and then the other when needed or could SQL use which ever Tempdb file it thinks is best at the time?
I have a feeling that in most cases throughout the day the load is not enough to need two tempdb's let alone 8, although I would keep two as minimum.
It's a round-robin algorithm, so it'll be using all of them.
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
September 20, 2011 at 3:08 pm
deleted
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 20, 2011 at 3:10 pm
uncle_boris (9/20/2011)
I already have two tempdb data files set up on the main server. Each on a separate disk and each the same size.I have my doubts as to how much the second one is actually being used.
Do you know of a way to see what tempdb file is being accessed? or when the last access was?
Does SQL use the initial one first and then the other when needed or could SQL use which ever Tempdb file it thinks is best at the time?
I have a feeling that in most cases throughout the day the load is not enough to need two tempdb's let alone 8, although I would keep two as minimum.
thanks
Monitor sys.dm_io_virtual_file_stats to check for activity on each of your files:
http://msdn.microsoft.com/en-us/library/ms190326.aspx
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 20, 2011 at 3:13 pm
and this round robin pattern is for both TempDB data files and also the log files (if their were multiple log files of course).
September 20, 2011 at 3:16 pm
uncle_boris (9/20/2011)
and this round robin pattern is for both TempDB data files and also the log files (if their were multiple log files of course).
I don't think it applies to the log files.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 20, 2011 at 3:17 pm
GilaMonster (9/20/2011)
uncle_boris (9/19/2011)
2. The report also mentioned doing the same for the TempDB log files. I have read that unless the max size of a log file is going to be reached and a "rollover" file is required then their is no point in doing this [multiple LDF files] because they may never be used.They'll all be used, but they'll be used one after the other, not in parallel. No good reason whatsoever to create multiple log files for a database.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply