SET STATISTICS PROFILE : This set statements used to get the query output (result set) and query plan execution information with row count value. There are other set statements such as SHOWPLAN_TEXT, SHOWPLAN_XML and SHOWPLAN_ALL are also used to get the query execution plan but it will not return the result set as part of the output. This is very useful when we need both result set and execution plan information for performance tuning.
Usage
SET STATISTICS PROFILE ON
or
SET STATISTICS PROFILE OFF
Example
Executing a query when SET STATISTICS PROFILE is ON.