The following example shows how performance of a query using a nonclustered index scan can be improved by configuring for a hash-based scan. The performance statistics and tuning goals are:
Data pages accessed |
1500 |
Access method |
Nonclustered index, 2K I/O |
Serial response time |
30 seconds |
Target performance |
6 seconds |
The steps for configuring for parallel operation are:
Set max scan parallel degree configuration parameters to 5 to use 5 worker processes in the hash-based scan.
Set number of worker processes and max parallel degree to at least 5.
In parallel execution, each worker process scans 300 pages in 6 seconds.
