Sql-server – Unable to get actual execution plan for SQL Server 2014 SP3

execution-plansql serversql server 2014

I was reading this blog post https://techcommunity.microsoft.com/t5/sql-server/using-xevents-to-capture-an-actual-execution-plan/ba-p/392136

and based on same created extended event

CREATE EVENT SESSION [PerfStats_Node] ON SERVER

ADD EVENT sqlserver.query_thread_profile(

ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text))

ADD TARGET package0.event_file(SET filename=N'C:\Temp\PerfStats_Node.xel',max_file_size=(50),max_rollover_files=(2))

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

However when reviewing the target file i see the plan captured seems estimated even though some actual number shows up in target events but when i pass plan handle in sys.dm_exec_query_plan, the plan which comes up seems to have all estimates

Blog clearly says it will open actual plan, then why i am not able to see one?

I tried using SSMS 17.9 as well but still no luck.

Best Answer

There's a lot of information in that blog post, and it looks like you've mixed up a couple of the sections.

The query_thread_profile XE available on SQL Server 2014 SP2 and higher puts the actual runtime metrics in the event output. The cached plan is just a normal, cached (estimated) plan.

Pedro points out that you are responsible for the tedious task of mapping execution plan nodes (and their estimated rows) to runtime stats stored in the extended event output:

And now comparing with the XE output, I can see the graphical plan and the actual and estimated rows that were read by that Index Scan:
...
It can be an arduous task to do the above...

Later in the blog post, when discussing other XE available in newer versions (query_plan_profile and query_post_execution_plan_profile), Pedro mentions being able to get plans with a subset of the "Actual Plan" runtime metrics in them. However, these aren't stored back in the plan cache - they are stored in the XE events.

Related Question