(2006-09-01 added a paragraph on parallel query plans)
In SQL Server, “Statistics Profile” is a mode in which a query is run where you can see the number of invocations of each physical plan operator in the tree. Instead of running a query and just printing the output rows, this mode also collects and returns per-operator row counts. Statistics Profile is used by the SQL Query Optimizer Team to identify issues with a plan which can cause the plan to perform poorly. For example, it can help identify a poor index choice or poor join order in a plan. Oftentimes, it can help identify the needed solution, such as updating statistics (as in the histograms and other statistical information used during plan generation) or perhaps adding a plan hint. This document describes how to read the statistics profile information from a query plan so that you can also debug plan issues.
A simple example query demonstrates how to retrieve the statistics profile output from a query:
use nwind
set statistics profile on
select * from customers c inner join orders o on c.customerid = o.customerid;