Interpreting Execution Plans of Partitioned Objects

Article
12/03/2008

New: 5 December 2005

Query execution plans that involve partitioned database tables, indexes, and indexed views typically have Nested Loops operators that represent joins between the partitions with Constant Scan operators on the outer side, and scans or seeks of the partitioned object on the inner side.

In execution plans of partitioned objects, the Nested Loops join reads one or more table or index partitions from the inner side. The numbers contained in the Values attribute of the Constant Scan operators represent the partition numbers. Noting the partition numbers confirms that the query optimizer is performing partition elimination.

Partition elimination occurs at both compile time and run time. Eliminations that occur at compile time can be seen in executions plans that are generated by the SET options SHOWPLAN_ALL or SHOWPLAN_XML, or by clicking Display Estimated Execution Plan in SQL Server Management Studio. Eliminations that occur at run time can be seen in execution plans that are generated by the SET options STATISTICS PROFILE or STATISTICS XML, or by clicking Include Actual Execution Plan in Management Studio.

When parallel plans are generated for partitioned tables or indexes, a Parallelism operator appears between the Constant Scan and the Nested Loops join operators. In this case, multiple threads on the outer side of the join each read and work on a different partition. The number of threads could be different from the number of partitions. If the number of threads and partitions is the same, each thread works on exactly one partition. If there are more threads than partitions, the excess threads sit idle while the partitions are processed. If there are fewer threads than partitions, the threads start the work, and whichever thread finishes first works on the next partition. Only one thread processes a partition at any time. One partition is not shared between multiple threads, but multiple partitions can be processed in parallel.

Example

The following query accesses two partitions of the partitioned Production.TransactionHistoryArchive table in the AdventureWorks sample database. When Showplans are generated for this query, a Nested Loops join operator is produced which shows when the two table partitions are joined to satisfy the query. A Constant Scan operator appears immediately after the Nested Loops join operator. The two partitions that are joined to satisfy this query are identified in the Values attribute that is listed for the Constant Scan operator.

Note

To reproduce this example, you must first run the PartitionAW.sql script against the AdventureWorks sample database. For more information, see Readme_PartitioningScript.

USE AdventureWorks;
GO
-- The following SET statement option produces an XML Showplan.
-- To produce a graphical execution plan, click the Include Actual
-- Execution Plan icon in the Management Studio toolbar.
SET STATISTICS XML ON;
GO
SELECT ProductID, Quantity, TransactionDate
FROM Production.TransactionHistoryArchive
WHERE TransactionDate <= '12/02/2003'
ORDER BY ProductID;
GO

If a graphical execution plan is produced in Management Studio, move your mouse over the Constant Scan operator icon, or right-click the Constant Scan operator icon and choose Properties. In the Properties pane, the Values attribute lists ((1)), ((2)). These are numbers that correspond to the partitions involved in the query.

If an XML Showplan is produced, look for a ConstantScan element under the NestedLoops element. The partition numbers are listed for the Values subelement under ConstantScan as follows:

<Row>

</ScalarOperator>

</Row>

<Row>

</ScalarOperator>

</Row>

</Values>

</ConstantScan>

Interpreting Execution Plans of Partitioned Objects

Example

See Also

Reference

Concepts

Help and Information

Additional resources