This example shows how a table might be partitioned to meet performance goals. Queries that scan whole tables and return a limited number of rows are good candidates for parallel performance. An example is this query containing group by:
select type, avg(price)
from titles
group by type
Here are the performance statistics and tuning goals:
Table size |
48,000 pages |
Access method |
Table scan, 16K I/O |
Serial response time |
60 seconds |
Target performance |
6 seconds |
The steps for configuring for parallel operation are:
Create 10 partitions for the table, and evenly distribute the data across the partitions.
Set the number of worker processes and max parallel degree configuration parameters to at least 10.
Check that the table uses a cache configured for 16K I/O.
In serial execution, 48,000 pages can be scanned in 60 seconds using 16K I/O. In parallel execution, each process scans 1 partition, approximately 4,800 pages, in about 6 seconds, again using 16K I/O.
