Friday, July 13, 2007

How many files in a filegroup

Because SQL Server opens one IO thread for each file pr. filegroup - it gives the server a higher read/write performance to have more than one file pr. filegroup.

This does not mean though, that you should have as many files as possible! The optimal amount of files pr. filegroup is between 0.25 and 1 pr. CPU. Meaning that if your Server has 4 CPUs you should have between 1 and 4 files pr. filegroup.

Please note that when I write CPUs I acutally mean physical CPU Cores !

My experience is that you normally would benefit the most by aligning the number of files with the number of CPUs. Eg. a server with 1 CPU with 2 physical cores should have 2 files pr. filegroup in general.

This is always true for TEMPDB where you should have 1 file pr. CPU.

No comments: