Steven O (3/9/2011)
I realize you wanted us to focus on settings we can see from the monitoring tool, but I am curious about instant file initialization, ad-hoc optimization, asynchronous update stats, etc.
Is there a way to customize what the tool shows you?
Regarding TempDB and the number of files, I was not able to find how many cores are on the servers.
What about separating TempDB MDF and LDF?
Also regarding separate filegroups for non-clustered indexes. In addition to the possible IO related benefits, I find that there are several administrative benefits, as well. For example, you then have more flexibility in where you locate your files, you can take filegroup backups, you can do filegroup dbcc checkdb commands, etc.
The forums database seems to be using full text filegroups. What about locating them on a separate file system from the regular data files?
The server has Dual Quad Core Xeon L5520 (2.66GHz) CPUs, for a total of eight cores.
MDFs and LDFs are on separate RAID 10 arrays. Tempdb is also on its own array. The arrays are part of a SAN where we have 20 dedicated disks.
Later in the series, I will go review the rest of the settings you asked about.
Brad M. McGehee
DBA