Hernán Rojas (8/6/2012)
1. "Put the tempdb database on disks that differ from those that are used by user databases."But, the "tempdb database" is the datafiles and the log file, Is it enough to have a dedicated disk for the whole tempdb or is necesary to have two disk, in order to split the datafiles and the log files??
Ideally put the tempdb data files and log file on separate drives from each other and evrything else. This isnt always possible so the minimum you should achieve is to separate the tempdb files (data and log) to their own drive together. The main point here is to get them away from the user databases.
Hernán Rojas (8/6/2012)
2. "Create as many files as needed to maximize disk bandwidth. Using multiple files reduces tempdb storage contention and yields significantly better scalability. However, do not create too many files because this can reduce performance and increase management overhead. As a general guideline, create one data file for each CPU on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as necessary. Note that a dual-core CPU is considered to be two CPUs."Regarding this, all the additional database files created in order to improve the disk bandwidth for the tempdb should be placed on the primary filegroup, or should I create additional filesgroups?? For example, in a 16 CPUs server I decided to use 8 datafiles, should I have the primary filegroup with 8 datafiles or could be better to have two filegroups with 4 files each??
Thank you in advance for your recommendations.
Hernan.
The tempdb can only have 1 filegroup so no you can't put 8 files into 2 different filegroups. The recommendation now is files equal to 1\4 - 1\2 the number of cores.
i.e. 8 cores = 2 - 4 files
24 cores = 6 - 12 files
However, know that in later versions of SQL Server the pages that suffered from latch contention are now cached, as such in most systems you will almost certainly never encounter any contention.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉