Queries processing longer than normal when reviewing SAP stats in ST03
I'm struggling with some SAP performance issues. We are running SAP R/3 Enterprise version 4.7 on a Windows 2003 Server with MS SQL 2000 (SP 4). When I review SAP stats in ST03, response times seem adequate, buffer and cache hit ratios are over 90% and server memory and CPU don't exceed 20-25%. However, some queries take many minutes. I've run SQL profiler to try and capture some select statements, but we think it could be I/O to the SAN. Management wants to perform a DB "reorg" but I don't know if that is the answer. Do you have any suggestions?
I doubt that this is a SAN problem. If it was, you should have a global performance problem. From the info you gave, it looks like you do not. You should investigate the select statements more closely. Discover which tables and indexes they are using (use transaction ST04 > detailed analysis to do so). Maybe they are not using any index at all. If so, consider creating indexes matching often used selection criteria.
In addition, some indexes might be heavily fragmented; you can use DBCC SHOWCONTIG to verify the fragmentation of the indexes. Index fragmentation and what to do about it is explained in detail in the Microsoft TechNet article Index de-fragmentation best practices. It is available at http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.
This was first published in July 2007