Improving the performance of a nonclustered index scan

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


Access method

Nonclustered index, 2K I/O

Serial response time

30 seconds

Target performance

6 seconds

The steps for configuring for parallel operation are:

In parallel execution, each worker process scans 300 pages in 6 seconds.