I have always observed that if I run a query (either select or update) for multiple values in the IN clause, if those rows fall in two different partitions, query takes a lol longer and CPU usage goes up by 20-30% compared to query where rows fall under one partition. If rows fall under 3 or 4 partitions, both latency and CPU goes up further. This suggests that each partition has a dedicated physical storage location on the disc. And likely that server will use one thread per partition. But I could not find any document that explains this. Can someone please answer? Thanks
-
1Please include the actual query plan for performance questions.– vonPryzJul 3 at 4:46
-
FWIW, Partitioning is not meant to improve performance for DQL and DML type of queries, and the behavior you're witnessing is normal. Rather it's a tool for improving data management.– J.D.Jul 3 at 12:04
Add a comment
|
1 Answer
This suggests that each partition has a dedicated physical storage location on the disc. And likely that server will use one thread per partition. But I could not find any document that explains this.
Partitioning is documented in several places. You could start with Partitioned tables and indexes.
At a high level, all tables are partitioned but some only have one partition. You can think of a partitioned table as a collection of table-like (rowset) structures. Each partition is distinguished by a partition ID.
How threads are distributed among partitions in a parallel query is documented in Parallel query execution strategy for partitioned objects. In a serial execution plan accessing multiple partitions, partitions are accessed and processed one at a time by the single thread of execution.
There is a cost to switching between partitions at runtime so the query processor often takes steps to minimize the number of partition switches, often by introducing a sort on partition ID.
