How many files should my database have?

A question I get asked quite a lot is "how many files / filegroups should my database have?".
As we all know there is only one right answer to all of life's questions and that is: "it depends!".

Lucky for us the guys from the SQLCAT team have taken the time to explain a little how to evaluate how many files you need for specific workloads.
In the first post they talk about OLAP environments and imho this is a must read!

Some I/O improvements in Windows Server 2008

Windows Server 2008 offers a lot of new features that are very visible like Internet Information Server 7.0, a whole new virtualization model, Server Manager and many many more. But being a SQL Server person I am interested in other things that have been improved. These not so visible features are mostly I/O related since databases are a synonym for I/O. Also note that many (if not all) of these changes also apply to Windows Vista.

I/O Completion Port improvement

Before Windows Server 2008 a thread that issued an async I/O also executed the I/O completion task causing a context switch which is expensive. The I/O completion is now deferred until the thread pulls the I/O off the completion port preventing this context switch.

I/O prioritization

This is completely new to the OS, not only do your processes have a priority but also the I/O's that are triggered by these processes have their priority. This priority is based on the thread priority but can be set on the I/O itself too.

I/O Performance improvements

Using the GetQueuedCompletionStatusEx API call enables Windows to retrieve multiple completion port entries in one call.

Prior to Windows Server 2008 the Memory Manager and I/O system limited every I/O request internally to 64KB, larger requests were divided into multiple 64KB parts. This limit has been removed so every request can now be issued as a whole, meaning less transitions to kernel-mode to send the I/O to your storage device.

NUMA improvements

When working with SQL Server 2005 and later you will most likely encounter more and more NUMA enabled machines. In Windows Server 2008 more memory allocations procedures have been updated to be NUMA aware and I/O interrupts direct their completion to the node that initiated the I/O. An addition to the NUMA APIs also allows applications to specify the preferred node.

SMB2

According to the people who designed SMB2 this should help performance of large file copies over the network with factor 30 to 40. This will not really help your SQL Server performance but it will certainly get your backups to other servers faster.

For more information about kernel changes in Windows Server 2008 check out this great webcast by Mark Russinovich.

I/O counters in Task Manager

One of my freaky habits is to check the I/O counters in the task manager. Whenever I'm doing I/O intensive stuff I like to quickly look at the MBs processed.

Whether these counters are updated or not is controlled by a registry setting (see below). You can set the value to 0 to disable it or 1 to enable it. Disabling it gives a little performance boost since Windows doesn't have to take care of the counters but on test systems I like to enable it. SQLIOSim also requires it to be enabled to get some of it's results.

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\I/O System\CountOperations

By the way, Windows Server 2003 Service Pack 2 (fixlist) is getting very close. Keep an eye out for it because it contains fixes to improve performance of SQL Server 2005 under heavy load. More details will follow.