Let’s say we are runnig a complex query and our objective is to tune it for better performance. We know we can find which join is used by optimizer by examining the execution plan, but we all know execution plan provides a ton of other useful information too. let’s say we just want a quick look at the joins used by optimizer, the instead of going for execution plan use the command –

Set statistics profile ON
GO

Your SQL Script Here

Set statistics profile OFF
GO

Sample Query and output for me –

select *
from person.person as p inner join person.businessentityaddress as b on p.businessentityid = b.businessentityid
inner join person.address as a on b.addressid = a.addressid

Forcing optimizer to use hash join.

As you can see, it returns more than just join type like index usage and plan type (sequential or parallel). Certainly, execution plan provides much more details but if you just need an overview this help because it’s faster to read and hides details.

Thanks for reading.