Specifies the percent of hard disk I/Os allowed during the execution of a statement that includes a query involving hash algorithms, before the statement is rolled back and an error message is reported.
0 – 100
10
Can be set for an individual connection or the PUBLIC group. Takes effect immediately.
If a query that uses hash algorithms causes an excessive number of hard disk I/Os (paging buffers from memory to disk), query performance is negatively affected, and server performance might also be affected. The HASH_THRASHING_PERCENT option controls the percentage of hard disk I/Os allowed before the statement is rolled back and an error message is returned. The text of the error message is either “Hash insert thrashing detected” or “Hash find thrashing detected.”
The default value of HASH_THRASHING_PERCENT is 10%. Increasing it permits more paging to disk before a rollback and decreasing it permits less paging before a rollback.
For more information on controlling excessive paging and using the HASH_THRASHING_PERCENT option, see “Unexpectedly long loads or queries” in Chapter 14, “Troubleshooting Hints,” in the System Administration Guide: Volume 1.
Also see “HASH_PINNABLE_CACHE_PERCENT option”
