1

Is it safe to optimize a SQL Server 2005 SAP R/3 database using Database Tuning Advisor raccomandatations? We are experiencing very low performance on a dedicated SAP database because of intense read operations ad the db and DTA suggest to create about 25 indexes and 100 stats. I am not an expert of SAP and I am quite surprised to see that this database has about 56.000 tables and 6500 views (120 GB of data).

Thank you all for help

Danilo Brambilla
  • 1,031
  • 2
  • 15
  • 33
  • 1
    Trying to get a SAP stack exchange started, check it out http://area51.stackexchange.com/proposals/41621/sap-systems-applications-and-products – Jared Mar 14 '13 at 18:59

3 Answers3

1

The table count sounds close to what my experience has been with SAP SQL databases. I would not use the DTA without first clearing this with SAP support and before doing that try running an index rebuild/defrag on the database tables with heavy fragmentation, above 30%. Heavy fragmentation would affect read performance as well but it involves a less risky (IMHO) change to the logical structure of the application's database than adding indexes that may or may not help the issue.

Jason Cumberland
  • 1,579
  • 10
  • 13
  • Index rebuild has been already run with an clear speed improvement (about 50%). Now we are at the point to try improving by ourself by adding missing indexes and stats as some queries require minutes to run and SAP support is saying that all is fine with SAP and reindex was not necessary (but we proved this was not the case) – Danilo Brambilla Oct 10 '12 at 14:17
  • 2
    Well if you have already done the rebuilds then stats would be the next step before changing the underlying indexes. Once that's done then use the wait stats views to identify read waits as the issue and rule out any storage issues, then do a spot check with a few of the DTA recommended indexes and see if you get any performance improvement. If you are using the Enterprise Edition of SQL server you can turn on row or page level compression to minimize the amount of pages read from the disk but that will incur some CPU over head. I have not had a lot of luck with SAP support either. :( – Jason Cumberland Oct 10 '12 at 20:05
  • 2
    If your data gets updated, re-indexing should be part of your regular database maintenance, don't let anyone tell you different. The question is "how often to reindex", not "should I reindex?". With online reindexing, this doesn't have to be very painful, I suggest that you look at implementing Ola Hallengren's scripts, which are very popular with DBAs. They can be configured to skip tables that aren't significantly fragmented. http://ola.hallengren.com/ – Darin Strait Oct 10 '12 at 21:49
  • Agreed, I'm a fan. – Jason Cumberland Oct 11 '12 at 01:16
1

Off hand I can't see using the suggestions from the Database Tuning Advisor causing problems (with the usual caveat that you should fully understand what will happen before applying them -- you don't want to add a bunch of indexes and suddenly an insert takes an hour!).
In my limited experience the Database Tuning Advisor is pretty conservative in what it does and is not likely to cause problems.

Note however that SAP may have something to say about you making direct changes to their underlying database. This is something you need to talk to them (or a SAP specialist) prior to going forward, otherwise if something breaks in the future SAP's support team may point the finger at your changes and refuse to help you/charge you an exorbitant amount (even if the breakage is unrelated).

voretaq7
  • 79,879
  • 17
  • 130
  • 214
  • 3
    +1 for all of the above. If you decide to go forward on your own, be sure that you have a script that can remove your changes and put the database back to the way SAP expects it to be. If you don't, and the next upgrade process fails, you will be in trouble. I have been in the spot where an upgrade to the database fails because the client "improved" the database. If SAP declare that you are off of support because of something you have done, your boss may use your salary to pay SAP to fix things. – Darin Strait Oct 10 '12 at 21:48
1

I would:

  1. Go to transaction ST04. Look at the disk response time. Slow response time could indicate a disk issue or bottleneck
  2. Has the server got enough physical memory and paged memory?
  3. Look at the SQL stats to see which processes run often, with a long average runtime.
  4. Run the DTA on the SQL statements that are causing the most delays. Then implement the suggestions, only after checking on OSS for the tables mentioned. Note - are they SAP or custom tables?
  5. Look at transaction ST03 to further identify and resolve performance issues
Techboy
  • 1,550
  • 7
  • 31
  • 49