Home / server / Questions / 436733
In Process
Danilo Brambilla
Asked: 2012-10-11 01:20:35 +0800 CST

Optimize SAP SQL Server database using DTA

  • 772

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

sql-server

3 Answers

  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.

    • 1

  2. 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).

    • 1

  3. 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
    • 1