what does statistics profile do and how to run the statistics profile?

Problem: what does statistics profile do and how to run the statistics profile?

Solution: There are few statistics statements which allows us to easily identify some important execution operations and cpu time.

This statistics profile option is a set option useful when you want to quickly view all operations information into a text format and with a quick set statement. When you want to focus on an ad-hoc query this will be helpful, this is only at run time unlike query execution statistics dynamic management view.

SET STATISTICS PROFILE - Displays the profile information for a statement. STATISTICS PROFILE works for ad hoc queries, views, and stored procedures.

Syntax: 

SET STATISTICS PROFILE { ON | OFF }

The above set statements help us to identify execution cost, physical operators, logical operators, execution count, statement text.

SET STATISTICS TIME - Displays the number of milliseconds required to parse, compile, and execute each statement.

Syntax:

SET STATISTICS TIME { ON | OFF }

The above set statements will give us results in the messages output about the execution time and compile time in milliseconds.

Example:

I would like to set this property of statistics profile on and run a query to see how this will show the output and what type of results look like.

SET STATISTICS PROFILE ON;

SET STATISTICS TIME ON;

select * from dbo.Address

GO

OUTPUT:

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

(515331 row(s) affected)

(2 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:

   CPU time = 297 ms,  elapsed time = 3327 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

As we can see this took us to write a single set statement to get this all information and produce the results to seek for trouble shooting.