SHould I increase the data files on our production server

[Former Member]

We have a production SAP ECC server running SQL server 2005, the server has 24 cores. I have been reading a document publish by SAP indicating that we should have a 1:1 ratio between data files and cores.

Currently we have 24 cores but only 3 data files.

I am considering increasing the amount of data files to 10 to give us a better ratio.

Has anyone else done this and did you see any negative or positive results.

Here is the link of the document located https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/4ab89e84-0d01-0010-cda2-82ddc354...

It covers a lot more points about performance on SQL Server 2005 so worth reading if you haven't already.

[Former Member · Accepted solution]

Cheers,

I thought to help the different data sizes I would stop auto growth on the old files which would only cause the new files to grow.

Maybe 10 isn't enough for 24 core (now think 13).

Since you are using SQL server what type of backup software are you using.

[Former Member · Accepted solution]

Hi,

We keep files in auto growth as a last resort but the DBA guys are supposed to manage the growth.

For backup, we are using Netbackup 6 mp4 and our SAP servers are SAN media servers.

Regards,

Olivier

[Former Member · Accepted solution]

When distributing database files, observe the following general rules:

1. Distribute the I/O load to all disks in use. To achieve this, each disk used for database files should have free space in a database file. Automatic growth should be enabled for at least one data file per disk.

2. Enlarge database files manually before automatic enlargement occurs. Otherwise, automatic enlargement may occur during working hours and cause online users to wait. Automatic growth should only occur in exceptional cases.

3. As long as a partition is not full, allow data files on this partition to grow automatically. To limit the frequency of automatic growth, enlarge in steps of at least 100 MB.

4. As soon as a database file of an SQL Server database is full, it grows automatically, unless growth is not allowed or no space is available on the partition. For all data files in the R/3 system, enable Autogrowth using SQL Server tools.

5. Ensure that there is enough free space in the data file(s), using a proportional fill strategy across all the files. When data is written to the file, SQL Server writes an amount of data proportional to the free space in the file, rather than writing all of the data to the first file until this is full and then writing to the next file. As soon as the first data file is full, the proportional fill strategy is no longer applied. SQL Server automatically expands one file at a time in a round-robin fashion to accommodate more data (provided that the database is set to grow automatically).

[Former Member]

Hi,

I think the best practice documents were written when only single core processors exist.

On our Windows SQL Server systems, we have 4 quadri-core processors which means 16 cores.

But we have decided to create only 8 data files because we considered that 16 files would be a mess to manage. As the database performance is very good we don't have any reason to regret our decision.

So, in your case I would increase the number of files but I would not go up to 24 files.

Beware also that if you create now new files on your productive system, all the files data level will not be even and so the optimisation will not be very effective.

Regards,

Olivier