Shrink of BW datafiles - experience & recommendation

[former_member221093]

Hello,

We have completed compression of our BW production database . (SQL2008 R2  /  BI 7)

BEFORE  compression - The 3 database files were 351GB each and 286GB of it used in each file

AFTER compresssion -   The 3 database files were 351GB each and  70GB of it used in each file

From your experience / Knowledge will SHRINK of the datafiles to 100GB each cause performance / other problems because it's a BW system.

Thanks for your help,

Moshe Yerushalmy

[Former Member · Accepted solution]

Hi,

As far i remember shrink operation is a single thread operation, so it won't impact system performance etc.

We had the compression for 1+ TB of data files where we got 85% as a compression ratio.

Shrink operation takes long but it won't impact any normal operation.

Regards,

Nick Loy

[former_member221093 · Accepted solution]

Hello ,

Thanks for your reply.

1. Was your compression on BW system?

2. Did you SHRINK the datafiles after the compression?

3. If you performed shrink - Was performence affected AFTER the SHRINK  (Not during) ?

Thanks,

Moshe

[Former Member · Accepted solution]

Hi,

1. Was your compression on BW system?

Yes, it was on BW system

2. Did you SHRINK the datafiles after the compression?

Yes we did, otherwise system won't release freespace of your database

3. If you performed shrink - Was performence affected AFTER the SHRINK  (Not during) ?

Overall after this upgrade+compression project, we got 30-35% of performance improvement

Regards,

Nick Loy

[former_member221093 · Accepted solution]

Hello ,

Thank you very much.

I understand that you upgrade your BI system and performed compression+shrink in the same project.

Was this done  in one phase or 2 phases?  First Upgrade and then compression+shrink ?

Thanks

[Former Member · Accepted solution]

Hi,

You may consider the upgrade as phase 1 and compression+shrink as phase2.

Because upgrade is an offline activity and compression+shrink is an onliine single threaded activity where you really don't see any impact while execution.

All the best,

Nick Loy

[Former Member]

Hi, to answer you question related to performance after shrink, please have a look at this:

Why you should not shrink your data files

http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

Best Regards.

[former_member221093]

Dear Franco,

Thanks for your reply.

How do you explain the experience of Nick Loy (above):

Overall after this upgrade+compression project, we got 30-35% of performance improvement

[Former Member]

Hi,

Please go through the note Note 1488135 - Database compression for SQL Server 

To avoid any known issues after compression, please have a check on the above note.

Regarding performance related issues ,just keep your DB stats up to date.

If you have a performance problem in your database with a particular SQL statement (you can identify that in transaction DBACOCKPIT, under "Performance", "SQL Statements", and also in a ST05 trace on the slow transaction, report...), you should analyze the root cause for it in order to be able to permanently solve it. In this sense, you should check the execution plan being used, determine why is so bad and try to understand why the Query Optimizer selected it. Only when you are in that point, you have enough information to really decide how to improve the performance. Of course, it could be that the solution was to update the statistics, and so just updating them would solve the issue; but most probably it was not the solution (or at least you will never know why it was solved) and so, every time that the execution plan is recalculated (because you force it, or simply because the SQL Server engine was restarted, etc.) you will be on risk of having the same problem again, and again you will not be sure that simply updating the statistics (that sometimes is a very costly operation) will solve your problem.

So, whenver you have a problem with a particular SQL Statement, I recommend you to try to understand the reason for a bad execution plan and, in case you need help to analyze it, open a message to SAP on the BC-DB-MSS component clearly indicating what is the statement (the best is that you trace the issue and provide the date and time and the user with which you created the trace so that the SAP consultant is able to immediately start working on it).

Please check and provide your feedback.

Thanks and Regards,

Vimal

[former_member221093]

Hello Vimal,

I am afraid my question was not clear.

I am asking if AFTER  I will perform the shrink I will have performence problems due to the shrink.

I did NOT perform the shrink yet, I just completed the compression.

The question is : Does someone have experience with performing SHRINK to BW system?

I was told that the fact that its BW system might cause performence problems AFTER the SHRINK.

Thanks,

Moshe