February 17, 2011 at 8:16 am
We have a new dedicated sql server Intel Xeon Processor E7530 1.86 GHz with 2 CPU's & 6 cores per CPU.
Have done some research & it appears it is recommended to have .25 to 1 data files (per filegroup) for each CPU up to maximum of 8 and 1 data file per CPU for tempdb.
I understand that everything needs to be tested and there is no magic solution, but was hoping to setup close to recommendations.....so if I understand correctly, if we have 2 filegroups for a 350Gb database, it seems prudent to start with:
6 sql datafiles per filegroup
12 tempdb datafiles
I have also come across some articles which suggest 1 tempdb datafile per CPU is too much - would say 6 tempdb datafiles make more sense?
Thanks for your help
February 17, 2011 at 9:17 am
Hi There,
There is no easy way to say which is better. There are some things to keep in mind. The first question would be how are the Disks and Drives set up? Do you have dedicated physical drives for the MDF files and Log Files? Are you using SAN? The reason why I am asking is Log files are read sequentially but the data files are read in Random. If the USerDb, TempDB and its Log files are all in the same physical device, I do not think you will gain much performance on IO.
The recommendation from MS is to use the same number of files as that of CPUs configured for that instance. If you have too many files, it will have its high cost due to file switching.
BUT there are some blogs out there that states that not in all cases you will gain any performance on multiple files. You must have seen tempDB contention before on your previous server and that is why you are going to this route of having multiple files and multiple file group.
Just my 2 cents
-Roy
February 17, 2011 at 10:50 am
jabadwy (2/17/2011)
Have done some research & it appears it is recommended to have .25 to 1 data files (per filegroup) for each CPU up to maximum of 8 and 1 data file per CPU for tempdb.
The x files/cpu is for TempDB and should not (unless you're sitting at the top-level of activity for databases) be considered for user databases.
For user databases create files/filegroups based on recovery requirements, IO performance. Not x files/cpu because someone (probably a sqlcat article) said so. Remember SQLCat works with the biggest, most active SQL databases in the world.
Even tempDB should not be 1 file/core without consideration.
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply