Skip to Content
0

BW: Poor Performance after migrating to MSSQL 2014?

Oct 10, 2016 at 05:12 PM

253

avatar image

Hello all,

We recently migrated our BW system from MSSQL 2008 to MSSQL 2014. Since this time, we've noticed very poor performance, specifically when writing to the DB.

One of the things we've noticed is the procedure cache is much lower post-migration and our hit ratio isn't as high as it used to be. For example, pre-migration our procedure cache was consistently above 2GB - many times up around 4GB. Post-migration, it's reached 2GB just a couple of times and when it does, it seems to be emptied in favor of lock cache.

Also post-migration, our lock cache seems to be running much higher (about 6GB) vs. 3-4GB pre-migration.

I understand that SQL 2014 handles memory differently than 2008, but has anyone else had similar performance / cache issues after moving to SQL 2014? If so, do you have any suggestions?

Thank you.

~TJ

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Abhi K Oct 11, 2016 at 08:28 PM
0

Hello TJ,

Did you apply the required recommendations post migration?

Please have a look on below SAP notes, which you may find helpful.

1054852 - Recommendations for migrations using Microsoft SQL Server

1986775 - Configuration Parameters for SQL Server 2014

1744217 - MSSQL: Improving the database performance

Thanks

Abhi

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Abhi,

I don't think the note 1054852 is very helpful in this case. I would point out to the following SAP Notes:

888210 - NW 7.**: System copy (supplementary note)

1593998 - SMIGR_CREATE_DDL for MSSQL

888210 contains some detailed information tasks after the import which are very important.

Best regards, Luis

0
Luis Darui
Oct 10, 2016 at 05:29 PM
0

Hi ~TJ,

Did you just migrated to 2014 or did you also converted all your cubes to Columnstore? Are you also using Flat cube?

For BW in general you should go for Columnstore to achieve best performance, and use Flat cube. Martin published many blogs about BW, Columnstore and SQL Server 2014:

https://blogs.msdn.microsoft.com/saponsqlserver/2015/03/23/sql-server-2014-columnstore-released-for-sap-bw/

But I think it has little to do with the poor performance you're reporting. You have mentioned "writing to DB". This is usually IO related problem, did you check the IO performance in DBACockpit?

Check the real time performance for DBACockpit and also the Historic performance data, to find out whether this started right after the upgrade or before: https://wiki.scn.sap.com/wiki/x/8Cm7Gg

Check also the Whitepaper about SAP on SQL Server 2012, 2014 (and 2016):

https://blogs.msdn.microsoft.com/saponsqlserver/2016/03/07/whitepaper-sap-on-sql-server-2012-and-sql-server-2014/

Best Regards,

Luis Darui

Share
10 |10000 characters needed characters left characters exceeded
TJ Wilkinson Oct 12, 2016 at 05:11 PM
0

Good evening all and thank you for the responses. I will review the information you provided - some of these notes I'm familiar with, but a couple I'm not.

We did not do anything with respect to converting to column store. We just changed the DB / OS versions as part of an intermediate step to upgrading BW. We have not completed the upgrade yet, but we sit in a supported OS/DB/App combo per PAM.

From an IO perspective, here's what we see in DBACOCKPIT (since DB start). I can't state what it looked like prior to the change. We never had issues so we didn't really look. (second number from left is read / third from left is write).

BIPDATA1 F: Data 5.794 6.362 3.226 813.010 61.226 11.905 2.632 68.289 23.262
BIPDATA2 G: Data 6.709 7.083 4.952 807.235 60.230 11.776 2.501 68.547 24.080
BIPDATA3 F: Data 5.831 6.299 3.677 804.838 61.005 11.693 2.543 68.831 23.989
BIPDATA4 G: Data 6.707 7.205 4.417 809.656 61.159 11.763 2.562 68.830 23.869
BIPDATA5 F: Data 6.892 7.680 3.288 808.664 61.160 11.782 2.576 68.638 23.746
BIPDATA6 G: Data 8.099 8.847 4.595 809.815 60.421 11.685 2.496 69.303 24.207
BIPDATA7 F: Data 6.249 6.940 3.212 810.135 62.367 11.861 2.700 68.303 23.102
BIPDATA8 G: Data 6.915 7.451 4.414 807.484 60.769 11.768 2.520 68.614 24.116
BIPDATA_log L: Log 1.619 5.117 1.232 892.945 734.352 2.276 20.545 392.274 35.744

We've seen these numbers higher in the last couple weeks, but every time we look at the DB server, specifically the sec/read and sec/write within Perf Mon, it looks very fast - basically 1ms.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi T-J,

Thanks for your reply.

But the data copy paste based on numbers is not properly legible.

Did you see SQL Server log, if it is complaining about anything?

Thanks

Abhi

0
Derek Fox Nov 02, 2016 at 06:16 AM
0

Hi TJ

In June 2016, we upgraded our SAP BW system (SAP NetWeaver BW 7.4 SP13) from Microsoft SQL Server 2012 to Microsoft SQL Server 2014 (planning for next year to upgrade to Microsoft SQL Server 2016 after our SAP NetWeaver BW 7.5 SP05 upgrade).

I have a comparison below of the before and after view (in Microsoft SQL Server 2014, we also switched our Infocubes to the columnstore format as well):

The columnstore value fluctuates (automatic load/unload) depending on the demand for data from the users.

So far overall performance is about the same, within certain areas of BW we can see improved performance, for example with the loading of data into the Infocubes, the compression of the Infocubes, etc...

From an IO perspective, it's difficult to say, since we are aware that our BW system is maxing the IO on our existing SAN (5 years old) - we have a solution for that, getting a new SAN with more IO performance, the timing of the SAN replacement fits with our replacement policy/strategy anyway.

Regards

Derek


Share
10 |10000 characters needed characters left characters exceeded