SQL Server Datafiles

[mohitsahu85]

Hello Experts,

We have ms sql 2008 R2 database with 4 data files each of 900 GB. The database is growing and it has left with only 300 GB free space.

So I would like to take an opinion with you all if

1). It is a good idea to add one more data file of 900 GB or

2). Perform the restructuring of the database and distribute this 4 datafile data to may be 8 new datafile with more space?

If we choose any of the above option, what about the performance and the indexing of the database?

If you have any other option then please suggest. Thanks in advance

Regards,

Mohit

[Former Member · Accepted solution]

On the contrary, I will suggest you do a R3load based load/unload of the database and spread it across many more data files. Few things I am mentioning which you may want to look into before deciding the way forward.

- sql server proportional fill : This was a concept introduced by SQL Server 7.0. please go through note 1238993  - Data File Management for SQL Server. It explains this concept and its ramifications on the performance of a SQL Server database. It also has some logic which will give you guidance on how many data-files you should create while installing/configuring a system.

in summary and simply put, SQL Server will write wherever it finds space, if you add a new datafile, it will start writing all new data to this datafile. the fill rate of the database will be disproportionate and you will start seeing bad performance on some datafiles. If that happens, even throwing really high performing storage will not help.

- unless it is a small system, I never recommend adding a datafile. I recommend let the existing datafiles grow and moving them to a place where there is enough space. You will have to put in a little thought one time and plan it carefully. It will reduce your pain and maintenance overheads for years to come. Keep the following settings at all times. These are a few important ones.

1. Keep the sizes of all disks the same, if possible use mountpoints instead of drive letters.
2. make sure that data files are evenly distributed across all disks.
3. make size of all data files same. Introduce flag 1117 which does that for you. This way all you need to do is make sure you have enough free space in all drives, 20% is a good threshold but it is up to you.
4. try using a storage which can be expanded on the fly, if that is not possible use storage which uses concepts like thin proviosining so that you can create really large drive to start with without having to actually assign that much of storage to them.
5. Number of datafiles correspond to available CPU on the DB server but having more will not hurt. If you expect the DB size to grow, start with lot of datafiles, we use a default of 16 even for very small ABAP systems, for java it can be low unless you intend to put custom tables in there.

- I am not sure what system you have but if you use the latest system copy tools, it compresses the database by default, if it is an older release, there are some notes you can apply. in our system SQL Server page compression save approximately 70% space. Also if you have enough CPU, compression actually improves performance considerably. Basically you save all the I/O time and replace it with CPU cycles for managing compressed data. Disk I/O are always more expensive than CPU cycles.

Do suggest which path you decided to go.

Yogesh

[mohitsahu85 · Accepted solution]

Hello Yogesh,

Thank you so much for the detailed information. I have checked the the SAP note 12338993. And I think i should go for the export and import method. The only thing which is stopping me is the downtime. As the I stated earlier the database is of around 3 TB and taking the export of the same and then importing will take lot of time. I will surely test it first on the the QAS system or some test environment of same hardware. I need to check as well if rebuilding of indexing would be required or not after importing the database.

I am thinking of redistributing this database to 8 data files. Can you suggest something about export and import approach or may be if we have some online documentation for same. Thank you for that.

Regards,

Mohit

[Former Member · Accepted solution]

I am glad you understood and taking the right course. For a faster export/import, we had the same dilemma. here are some recommendations.

- Of-course try it in non-prod first but I will recommend using a server which has same performance as production DB server.

- more CPU cores in the DB server, faster the export import will be.

- Also it is a very I/O intensive activity, I will suggest using FC drives, also create multiple drives and get your storage team to dedicate as much storage performance as possible, especially in production. By adjusting the CPU and Storage I/O, you will get your optimal speed. Understand that one export parallel process per CPU core so you can actually export a lot of tables in parallel if you have lot of CPU cores in the DB server but then the I/O starts becoming the bottleneck if your storage is not very good.

- If you can spare two servers, you can do a parallel export import, it will reduce your downtime to half.

- If you have really large tables, it will increase your total export/import time. So you will have to split the tables. Use migration monitor for that.

When we did our ECC system export import, it was over 3TB in size, after it was reduced to 700. Our performance improved to 3-4ms/op from something which was over 50 on the larger datafiles. Total time for export/import of a 3.1TB system was less than 11 hours. + another 2-3 for other system copy steps. Though it took atleast 4-5 full cycle tries to reach the magic number.

I will recommend at-least 16+ datafiles. If you DB server has more dedicated CPU cores to your instance, then add more. Plan the number of datafiles in multiple of the luns. so if you are distributing the datafiles to 5 drives, have in the multiples of 5 and so on. This to make sure number of datafiles in each drive/lun is the same.

~Yogesh