If you are moving applications from a single-CPU environment to an SMP environment, this section offers 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. It is especially important to adhere to the principles of good database design to avoid contention. Following 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 will be updated often.
For information about the effects of index maintenance on performance, see “Index management” on page 60 in the Performance and Tuning: Monitoring and Analyzing for Performance book.
Managing disks
The additional processing power of SMP may increase demands on the disks. Therefore, it is best to spread data across multiple devices for heavily used databases.
See “Disk I/O management” on page 102 for information about sp_sysmon reports on disk utilization.
Adjusting the fillfactor for create index commands
You may need to adjust the fillfactor in 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
Temporary tables
Temporary tables (tables in tempdb) 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 can be some contention on the system tables in tempdb.
See “Temporary tables and locking” for information on ways to reduce contention.
