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.
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. HASH_THRASHING_PERCENT 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 this value permits more paging to disk before a rollback and decreasing this value permits less paging before a rollback.
For more information on controlling excessive paging and using HASH_THRASHING_PERCENT, see System Administration Guide: Volume 1 > Troubleshooting Hints > Solutions for Specific Conditions > Processing Issues > Unexpectedly Long Loads or Queries.