SQL Server Datafiles

[mohitsahu]

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

[mohitsahu · 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

[Former Member]

Hi,

additional datafiles of the same size would be benefitial - in case much growth is still expected.

1st add datafiles, then compress. Data will spread evenly accross all datafiles and access will improve. Study carefully SAP note #1744217 (esp point3.).

brgds,

Eszter

[mohitsahu]

Hello Eszter,

Thank you for your message.

I would like to be clear myself as this is production system.

If I add 900 GB one more datafile. then the scenario would be

4 old disk having 820 GB each data files

and 1 new disk will be having 0 GB data file.

After this you are stating to compress the database. for this if i take a look on the SAP note (esp on point 3) it states to compress the database tables and not to database all together.

If this is the case, then how the data will get distributed on the all 5 drives.

-----------------------------

Is it possible, if i add new datafile and then compress the database from the sql server with shrinking command?? Then will it distribute the data on all data files or not?

Regards,

Mohit

[Former Member]

No, compression will not distribute the data across all data files. In-fact when you add the new datafile and it will be as large as 900G, it will start writing all new data over to this new file. after some time all your newly modified/created data will be on this datafile and you will start seeing very high I/O on this datafile. Size of your database is pretty big, you will soon start seeing performance problems.

[srinivasan_vinayagam]

Hi Mohit,

Add new HDD drive and create add datafile or move datafile to new HDD.

Regards,

V Srinivasan

[mohitsahu]

Hello Srinivasan,

Thanks for the message.

You suggest to add new data file?

As i stated before the data files are of big sizes of 900 GB. Adding one more of 900 GB will keep on moving the new data only in this drive and the data will be heterogeneous and i dont think that would good for the database.

What you suggest on that?

Regards,

Mohit

[Sriram2009]

Hi Mohit

Before adding the data file you have to check weather you can reduce the clear the data from existing system?

Have you enable to database compression feature?

Could you share your snapshot of transaction ST04 & SAP system SP levels?

Regards

Sriram

[mohitsahu]

Hello Sriram,

There is not much free space in datafile to compress.

Still i am attaching the screenshot. Either i have to add data file or add increase drive size or increase both no. of drives and size on disk and drive.

Regards,

Mohit

[Sriram2009]

Hi Mohit

You can perform the Database compression as mention in the SAP notes you get free space around 20 to 50 %

1488135 - Database compression for SQL Server

BR

SS

[mohitsahu]

Hello SS,

But as you can see from the screenshot. There is only very less free space in the datafile.

Compressing will free only 20 GB from the data file which free on the data file. But it will not help much.

Regards,

Mohit

[Sriram2009]

Hi Mohit

On your development system you can perform the one of the table compression and check the table size before and after

BR

SS

[mohitsahu]

Hello SS,

You mean this compression is different from the compression that we perform from the database.

Like We do with command

DBCC SHRINKFILE (N' SIDDATA1', 14750)

I think this compresses the data both in rows and column.

If your approach, will compress data further then please confirm.

Regards,

Mohit

[Matt_Fraser]

Mohit,

That's right, SS is not suggesting SHRINKFILE, he is suggesting MSSCOMPRESS. It's very different. This doesn't shrink the file, it actually compresses the data, and it's quite effective. Have a look at the Note he linked earlier. SAP and Microsoft both recommend using database compression from SQL Server 2008 and up. This should create more free space in your existing data files.

You could also add another drive with another data file, but as you pointed out this will not help your I/O much, as all new data will just end up going into the new data file. It is possible to use the MSSCOMPRESS process to migrate some of the data to the new file during the compression process, and that's one way you could mitigate this. The best option, however, would generally be to export your database with SWPM, add the new drive and data file, and then re-import it across all the data files. This will spread the data evenly. However, for a large database like yours, this could involve quite a lot of downtime, so you would definitely want to test it on a copy of production first to get an idea of how long it will take.

Either way, you're almost certainly going to want compression.

Regards,

Matt

[mohitsahu]

Hello Matt,

Thanks for you for the suggestion. I will take a look on MSCOMPRESS and then will get back to you.

Regards,

Mohit

[said_shepl]

Hi Mohit,

    We are alreay use MSSCOMPRESS, which is reduce my database from 250G to about 80G, which will provide your system by a more available free space and this a recommended by SAP and Microsoft.

Note: you can run this program MSSCOMPRESS and compress table by table if transaction log increase, try to shrink it.

Or you can Export the database with SWPM, and import it again.

BR

Said Shepl