I've shared many times over the years the recommendation to split the iMIS database into multiple data files. This was based on some notes I read about Microsoft's Terraserver. Without solid guidance, I have been advising clients to split their databases into enough data files so each file was between 512MB and 2GB. (Originally, my thought was that a 2GB file can be transfered on a FAT partition if needed.) This was largely a guess with some useful side-benefits.
While looking for something else (naturally), I came across these articles about tuning for performance.
These articles give a much clearer answer: The number of data files within a single filegroup should equal to the number of CPU cores.
Several other items caught my attention:
* Always use Page Checksum to audit data integrity. Sounds like good advice, and that would be my instinct, but it's good to see it in writing.
* Use instant file initialization for performance optimization. Apparently, the default service account used by SQL (NETWORKSERVICE) does not have a necessary permission.
It includes deep analysis of many common questions related to building a server, such as the advantages and disadvantages of different RAIDoptions, including SAN and NAS options.
Likewise, this article (http://technet.microsoft.com/en-us/library/cc966545.aspx) suggests that having one tempdb data file per core can reduce certain kinds of tempdb contention. However, this article (http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx) points out this is not a hard-and-fast rule. As Paul explains, this will only help if tempdb is your bottleneck. In general, iMIS doesn't make much explicit use of tempdb. Dues billing is the one exception that comes to mind. Most other tempdb usage is implicit - automatic overflows of large sorting operations and such. As such, I don't have any strong hunch that adding files to tempdb would make a lot of difference, but I'd be interested to hear if anyone else has tried it out.