If you are moving applications from a single-CPU environment to an SMP environment, this section discusses some issues to consider.
Increased throughput on multiprocessor Adaptive Servers makes it more likely that multiple processes may try to access a data page simultaneously. Adhere to the principles of good database design to avoid contention. These are some of the application design considerations that are especially important in an SMP environment.
Multiple indexes – the increased throughput of SMP may result in increased lock contention when allpages-locked tables with multiple indexes are updated. Allow no more than two or three indexes on any table that is updated often.
For information about the effects of index maintenance on performance, see Performance and Tuning Series: Monitoring Adaptive Server with sp_sysmon.
Managing disks – the additional processing power of SMP may increase demands on the disks. Spread data across multiple devices for heavily used databases.
See Performance and Tuning Series: Monitoring Adaptive Server with sp_sysmon.
Adjusting the fillfactor for create index commands – because of the added throughput with multiple processors, setting a lower fillfactor may temporarily reduce contention for the data and index pages.
Transaction length – transactions that include many statements or take a long time to run may result in increased lock contention. Keep transactions as short as possible, and avoid holding locks—especially exclusive or update locks—while waiting for user interaction. Ensure that the underlying storage provides both adequate bandwidth and sufficiently low latencies.
Temporary tables – do not cause contention, because they are associated with individual users and are not shared. However, if multiple user processes use tempdb for temporary objects, there may be some contention on the system tables in tempdb. Use multiple temporary databases or Adaptive Server version 15.0.2 and later to alleviate contention on tempdb’s system tables.
See Chapter 7, “tempdb Performance Issues,” in Performance and Tuning Series: Physical Database Tuning.