multiple database files on SQL

Ostry · 2007-09-30

Hello,

I have a question. I have 30 GB database on SQL2005 on 6 hdds RAID 10

Should I partition ndf file for two?

kine · 2007-10-01

You can add another file (in general named xxx.mdf, but the extension is optional). If you add another file, SQL will use it and some data will be in the primary file (.ndf) and some in othe files. You can create as many files as you want.

DenSter · 2007-10-01

You don't have to at 30GB though, SQL Server is perfectly capable to handle a 30GB file.

kriki · 2007-10-03

I have read that for performance it MIGHT be better to have multiple files on the same (physical) disks. This because SQL can use multiple threads (1 per file) to access them and this might give an a extra for performance.
But if you already are at 100% of disk activity, this will not help.

I have been thinking about this statement for some time and I think this extra boost for performance is minimal (if existing). My experience is that when performance is low, the disks are already at 100% activity.
An extra problem is that when you multiple files on 1 (physical) disk, you might get some fragmentation because those files increase in size, so you would need to defrag them otherwise you LOSE performance.

Conclusion : to make a long story short : I think it is better to keep 1 big file then multiple smaller ones. The extra performance for multiple files is too small to be worth the extra administrating of them.

DenSter · 2007-10-03

But at 30GB that is not going to make a big difference, unless there is a hardware issue.

stryk · 2007-10-03

Hi!

Well, it is true that SQL Server handles multiple files with sparate Threads. This generally increases performance as the CPU are used more efficient. But on the other hand - as you said - multiple files would also increase the I/O on the disks.

In my opinion it makes no sense to have multiple files of the same Filergoup - like "PRIMARY" or "Data Filegroup 1", execpt if you could store each file on a dedicated (physical) disk/array to benefit from dedicated I/O.
As far as I know, SQL Server would not "splitt" data of one table into separate files of the same group. Hence, when writing table data in NAV, this would be handled in one single thread anyway.

But it makes sense to have e.g. separate file-groups for Indexes and/or SIFT tables, even though if those are located on the same physical disk - just to benefit from the "multi-threading":
One write transaction in NAV - e.g. T32 - triggers write transactions on indexes and SIFt tables, so this could be handled with multiple parallel threads!

Kind regards,
Jörg

DenSter · 2007-10-03

One word of caution about parallel queries is that this has been known to cause deadlocks itself during SIFT updates, where two threads conflict with eachother. We recommend not using parallelism during daily NAV processing. NAV sends multipler queries to SQL Server for the same transaction. Having those be processed by multiple threads can cause problems in itself.

multiple database files on SQL

Comments

Categories