Why does SQL Server Profiler trace not capture SELECT operator in execution plans?

Question

When I run SELECT queries in SSMS and choose Include Actual Execution Plan, it always includes the SELECT operator. Nice because I can see the full properties like memory grants, cached plan size, subtree cost, etc.

When I run a trace with SQL Server Profiler and grab execution plans via the Performance > Showplan XML Statistics Profile event, I get all the operators except for the SELECT. Even when I extract to a file and view the plan in SSMS, it's the same--no SELECT operator. Is this expected behavior for that particular event? Should I be using some other event to capture the SELECT operator and its details?

I'm running Profiler from SSMS version 2014, doing traces against a SQL Server 2008 R2 instance.

Also wondering how to capture stats to a trace that I'd get from running SET STATISTICS IO in SSMS. Or should that already be in the XML Showplan somewhere?

Answer 1

Do not run profiler while grabbing execution plans (very expensive operation !), instead run a server side trace (modify as per your need) or sp_whoisactive and grab the tsql you are interested and then run the tsql separately using Plan explorer to get actual execution plan.

The best way to get all the relevant data is to run the query from sql sentry's plan explorer (its free - but worth spending a little money on Pro version).

The free version is sufficient as it gets you the Table IO (same like stats IO ON) along with Plan diagram, statements tree, plan tree and top operations, etc