SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability52
A lookup can only occur on a clustered index when additional nonclustered indexes are defined on the table. The lookup categorizes a seek on a nonclustered index. In this case, a clustered index is used to retrieve the data rows when the nonclustered index does not cover the query. The sum of all seeks on nonclustered indexes is greater than or equal to the lookups on the clustered index.
Updates show how often an index has been updated for data modifications. An update modification does not always trigger an index update. With SQL Server 2005, only indexes that are affected directly by the updated data are changed.
Following is an example of a query that finds indexes not used in the current database:
select object_name(object_id), i.name from sys.indexes i where i.index_id NOT IN (select s.index_id from sys.dm_db_index_usage_stats s where s.object_id=i.object_id and i.index_id=s.index_id ) order by object_name(object_id) asc
SAP performance tuning
SQL Server 2005 contains features for monitoring performance and, to a certain degree, resource consumption. Similarly, SAP systems provide a number of key features for performance tuning. These features take advantage of SQL Server–specific functionality, as well as some functionality that is SAP-specific.
Common performance problems
The principal problem classes that might require performance tuning include:
The entire system is slow. The entire SAP system is sluggish and slow, the system does not perform as anticipated, and it continues to perform slowly. SAP end users experience slow response times executing most business transactions.
Certain SAP business transactions are slow. One certain transaction or job experiences a slowdown and remains in that state. This causes SAP end-user complaints and a few batch jobs can be affected or slowdown.
Certain SAP functionality or jobs have intermittent slowdowns. Some SAP functionality or jobs run fast most of the time, but slowly at other times, such as a slowdown that lasts for a few hours or a day. In this case, jobs might run fast for days at a time until another slowdown occurs.
Microsoft Corporation © 2005