Performance problems

[Former Member]

Hello everyone.

I'm new to SAP.

I'm having performance problems on SAP ERP on SQL Server 2005.

How do I know if I'm having problems with indexes on the database?

How can I view the indexes of the database that are fragmented?

Should I use SAP to manage the indexes problems or the tools provided by SQL Server 2005?

My database has 1 TB, my server database has 16GB of memory of which 13GB is reserved for SQL Server.

Thanks!

[Former Member · Accepted solution]

The system performance is a very vast topic to discuss, first you need to findout is the system performance for only selected users or systemwide. Is this issue with locally developed programs?

go thorugh the http://wiki.sdn.sap.com/wiki/display/MaxDB/GeneralPerformanceProblemsinaSAPSystem

[Former Member]

Hi Silvio,

please read [this|http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/sap_sql2005_best%20practices.doc] document. The performance part starts at page 54. There are sample queries and a lot of background stuff that you need to know for analyzing your DB. When I check my databases I always use SSMS.

BTW: 13 GB RAM for a 1 TB database seems to be one of your problems A good point to start your analysis.

Regards,

Sven

[Former Member]

Hi Sven Otromke!

How can i see the amount of memory needed for a 1TB database? The are any guidelines for this?

Many tks!

[former_member204746]

Size of database does not really matter that much.

refer to SAP note 830576 to set your Oracle parameters.

[Former Member]

Eric, his database is MS SQL server not oracle.

[former_member204746]

Oops,

my bad.

sorry about that.

[Former Member]

Hi,

no, there are no guidelines that I know of. Simply the two values 1TB of data and 13 GB RAM (think of the buffer cache) don't really fit together on a highly concurrent multi user system like SAP.

Have a look at [this|http://msdn.microsoft.com/en-us/library/cc966540.aspx] website.

Regards,

Sven

[Former Member]

Hello everybody, in a SAN environment, which is the best RAID level for storing the Datafiles for SQL Server? and what is best RAID level for the tempdb database? What is the best RAID level to store the Files Page?

Thanks!

[Former Member]

Do not use the same thread if it's answered, create a new one.

You can get all the required information in installation guide under https://websmp203.sap-ag.de/instguides

[mathew_matt]

As far as DB is concerned, depending on the version of ECC, SAP has some very robust tools to see what's happening. DB02 / DBACOCKPIT can get you alot of the DB information, including potential missing indexes, DB response times, etc.

As far as some general recommendations to start...

SM21 - Check the logs to see if there is a larger issue causing your performance problems.

ST22 - If you're short dumping quite a bit, it can hamper performance considerably.

SM12 - Database locks - if you're consistently seeing alot of locks could have issues as well.

SM13 - Updates - check and make sure updates are clearing properly.

ST02 - Buffers - look closely at the buffers highlighted in this tcode, it will also give you some details on specific profile parameters.

ST03/ST03N - Check response times, and you might be able to find the bottleneck.

ST04 - This will give you some idea of how hard SQL is being hit. Generally, if SQL is choking on memory, you will see a lower cache hit ratio.

Also, be sure to check your work process logs on the OS (usr/sap/<SID>/DVEBMGS<NO>/work). dev_w0, etc. might show some errors, etc. that may be occuring causing your problem.

There's a lot of information in the screens above, so just try narrowing down big problems (ST22, SM21), then move to performance analysis (ST transactions) and see if you can determine from there.

Hope this helps!

Thanks,

MM

[Former Member]

Hi! tkans for the ansers.

How can a see if my database indexes are fragmented?

[Former Member]

>

> How can a see if my database indexes are fragmented?

well that is good question, and DBA cockpit will not help you on this. There is no where DBA cockpit will show you fragmented indexes by percentage. (others can step in and correct me)

to start with here is the [link|http://blog.sqlauthority.com/2008/03/27/sql-server-2005-find-index-fragmentation-details-slow-index-performance/] to find fragmentation. But i would not use this script is on Prod server, as it will run for hours and resource intensive.

I would execute this on an restored prod DB on an another server.

Thanks

Mushtaq