Database file sizes

[sukhdev_kaloor]

Hi All,

Is there any specific guidelines on the size of datafiles in mssql?.

The best practices documents say you can maintain number of data files = the number of processors. When installing the SAP system it creates 3 data files by default. In production systems currently the size of these 3 files are very high.So is it a good option to restrict the growth of these files and add another 3 new datafiles and allow those files to grow.

regards,

dev

[Former Member]

In older DB systems, it was very important to limit the size of the data files. In some systems, like say Oracle on Windows, there was a hard requirement that datafiles not exceed 2GB. With SQLServer today and the availabilty of multi-core processors and SANs, really the biggest consideration is performance. It is not necessary to have more datafiles than CPU cores, in fact only having as many files as cores will make the administrator's job a little less cumbersome when it comes to growing them or system refreshes, etc.

This is my standard approach:

- Use at least 3 datafiles and 1 log file, but do not exceed number of CPU cores

- Use autogrow, but periodically manage the file sizes: do not let one get much bigger than the others

- Us a SAN, if possible, otherwise separate Log disk from Data disks, and spread out data read/write load

[sukhdev_kaloor]

Dear All,

My concern is this.

Now that the 3 default data files has grown big(200+GB) should i allow them to grow even bigger?..

From a maintenance point of view is it better to close these 3 files and create another 3 sets of files and allow them got grow.

If 3 files are full and 3 files are in autogrow mode will that impact the performance?

Regards,

dev

[Former Member]

Yes, you can do that, but you will get alerts on the files that are full. To avoid those, you can also add a few extra files and go through the process of leveling them so that they are all roughly the same size, and leave them all plenty of space to grow.

[Former Member]

Hi,

It is not mandatory to have the number of files equal to that of the number of processors but it is a good practice to have better performance. In case if u have more number of datafiles (say 😎 in a system having less number of CPU's(say 4), then u may experience a degradation in the performance during the peak hours.

Hope this clarifies.

Regards,

Varadharajan M

[sebastian_dusch]

Hi dev,

there's a whitepaper published on Juergen Thomas' Blog ([http://blogs.msdn.com/b/saponsqlserver/archive/2009/06/24/new-sap-on-sql-server-2008-whitepaper-released.aspx]) that states the following:

- Small sized systems, where 4 data files should be fine. These systems usually run on dedicated database servers that have around 4 cores.

- Medium sized systems, where at least 8 data files are required. These systems usually run on dedicated database servers that have between 8 and 16 CPU cores.

- Large sized systems where a minimum of 16 data files are required. These are usually systems that run on hardware that has between 16 and 32 CPU cores.

- Xtra Large sized systems. Upcoming hardware over the next years certainly will support up to 256 CPU cores. However, we donu2019t necessarily expect a lot of customers deploying this kind of hardware for one dedicated database server, servicing one database of an SAP application. For XL systems we recommend 32 to 64 data files.

For more information check out the whitepaper (its 404 currently which should be fixed soon)