Skip to main content
There is no such thing as SQL 2014, the product is called SQL Server
Link
user1822
user1822

Unable to get actual execution plan for SQL2014SQL Server 2014 SP3

Source Link
BeginnerDBA
  • 2.2k
  • 3
  • 26
  • 53

Unable to get actual execution plan for SQL2014 SP3

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.