Scenario 2: Establishing an appropriate amount of sort memory

Scenario 2 establishes an appropriate amount of sort memory.

Online transaction processing (OLTP) applications must not perform large sort operations. Large sort operations are very costly in terms of CPU, I/O, and elapsed time; as a result, sorts can slow down an OLTP application. The default SORTHEAP size is 1 MB (256 4KB pages), which is adequate for most situations.

You can use the information in the Application Top Summary workspace to help you track the number of sort overflows. In the Application Summary workspace, you can view information about the number of sort overflows and the sort overflow percentage. Additionally, you can use the Application Sort/Hash Join Activity workspace to find information about the total number of sorts, the average sort time, the number of sort overflows, and the percentage of sorts that cause an overflow condition.

Sort overflows indicate that large sorts are occurring. If the number of sort overflows represents greater than 3% of sorts, an application might experience serious, unexpected sort problems. You must identify the SQL statements that are causing the sorts and modify the SQL, indexes, or clustering to reduce the cost of the sorts.