Execution plan analysis

Question

Execution plan analysis

scott.laird

Mr or Mrs. 500

Points: 521
More actions
August 25, 2015 at 7:41 am

#319987

I am doing some execution plan analysis. I have found that some plans do not contain: @StatementOptmEarlyAbortReason.
I am looking for values: GoodEnoughPlanFound, TimeOut, MemoryLimitExceeded.
Can anyone explain this / provide logic?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110639 More actions · Answer 1

scott.laird (8/25/2015)
I am doing some execution plan analysis. I have found that some plans do not contain: @StatementOptmEarlyAbortReason.
I am looking for values: GoodEnoughPlanFound, TimeOut, MemoryLimitExceeded.
Can anyone explain this / provide logic?

One possibility might be that the queries involved generated a "trivial plan", for which there can be no optimization. Perhaps in some cases, an ideal plan is found quickly because it's a query for which the ideal plan is truly easy to get to and either there aren't any alternatives, or the obvious alternatives have obviously high costs and thus the optimizer concludes within it's time budget that is has the best possible plan?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Grant Fritchey SSC Guru Points: 398711 More actions · Answer 2

Can you post one or two samples of the plans that don't have that property?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

scott.laird Mr or Mrs. 500 Points: 521 More actions · Answer 3

This in NOT my code...this is NOT my company's code...it is code from a third party vendor.

StatementOptmLevel="FULL"

<Batch>

<StmtSimple StatementText="(@P0 varchar(8000),@P1 datetime,@P2 varchar(8000),@P3 datetime) SELECT wcv.cnsmr_id as entityId FROM (SELECT DISTINCT c.cnsmr_id FROM cnsmr c INNER JOIN cnsmr_addrss cad on cad.cnsmr_id=c.cnsmr_id INNER JOIN cnsmr_tag ct ON c.cnsmr_id = ct.cnsmr_id AND ct.cnsmr_tag_sft_delete_flg = 'N' INNER JOIN tag ctt on ctt.tag_id = ct.tag_id WHERE cad.cnsmr_addrss_st_txt in ('NY','MA') and ctt.tag_shrt_nm = '10' ) wcv INNER JOIN cnsmr_Wrk_actn cwa ON wcv.cnsmr_id=cwa.cnsmr_id AND cwa.cnsmr_lck_flg='N' AND cwa.cnsmr_is_hld_flg='N' INNER JOIN cnsmr c on cwa.cnsmr_id = c.cnsmr_id INNER JOIN wrkgrp_scan_lst wc on wc.wrkgrp_id = c.wrkgrp_id AND wc.wrkgrp_scan_lst_id = @P0 WHERE (cwa.cnsmr_wrkd_lst_dt < @P1 OR cwa.cnsmr_wrkd_lst_dt IS NULL) AND c.cnsmr_id NOT IN ( SELECT dc.cnsmr_id FROM dfrrd_cnsmr dc WHERE dc.dfrrd_cnsmr_entty_assctn_cd = (SELECT entty_assctn_cd FROM Ref_entty_assctn_cd WHERE entty_assctn_val_txt = 'WORKCHAIN') AND dc.dfrrd_cnsmr_entty_id = @P2 AND dc.cnsmr_dfrrd_dttm > @P3) ORDER BY cwa.cnsmr_cntct_nxt_dt ASC " StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="65.9772" StatementEstRows="8408.83" StatementOptmLevel="FULL" QueryHash="0xD43726B9C2BFA5B5" QueryPlanHash="0x44D7AEAFCCFB2279">

</OutputList>

</OrderByColumn>

</OrderBy>

</OutputList>

</InnerSideJoinColumns>

</OuterSideJoinColumns>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Residual>

</OutputList>

</OuterReferences>

</OutputList>

</OrderByColumn>

</OrderBy>

</OutputList>

<Hash>

</HashKeysBuild>

</HashKeysProbe>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</ProbeResidual>

</OutputList>

</OuterReferences>

</OutputList>

</DefinedValue>

</DefinedValues>

</RangeColumns>

</Identifier>

</ScalarOperator>

</Convert>

</ScalarOperator>

</ColumnReference>

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</IndexScan>

</RelOp>

</OutputList>

</DefinedValue>

</DefinedValues>

</RangeColumns>

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</OutputList>

<Hash>

</HashKeysBuild>

</HashKeysProbe>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</ProbeResidual>

</OutputList>

<Hash>

</HashKeysBuild>

</HashKeysProbe>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</ProbeResidual>

</OutputList>

</OuterReferences>

</OutputList>

</DefinedValue>

</DefinedValues>

</RangeColumns>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</IndexScan>

</RelOp>

</OutputList>

</DefinedValue>

</DefinedValues>

</RangeColumns>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</OutputList>

</DefinedValue>

</DefinedValues>

</RangeColumns>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</RangeColumns>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</IndexScan>

</RelOp>

</Hash>

</RelOp>

</OutputList>

</DefinedValue>

</DefinedValues>

</IndexScan>

</RelOp>

</Hash>

</RelOp>

</Hash>

</RelOp>

</Sort>

</RelOp>

</OutputList>

</DefinedValue>

</DefinedValues>

</RangeColumns>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</OutputList>

</OuterReferences>

</OutputList>

</OuterReferences>

</OutputList>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</IndexScan>

</RelOp>

</OutputList>

</DefinedValue>

</DefinedValues>

</RangeColumns>

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Convert>

</ScalarOperator>

</ColumnReference>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</RangeColumns>

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</Merge>

</RelOp>

</Sort>

</RelOp>

</ParameterList>

</QueryPlan>

</StmtSimple>

</Statements>

</Batch>

</BatchSequence>

</ShowPlanXML>

Grant Fritchey SSC Guru Points: 398711 More actions · Answer 4

I don't know why that property is sometimes missing from a plan. I'll see if I can find out.

How are you capturing this query? Through SSMS? Querying the cache? Capturing it through trace or extended events. I'm just curious because that might be part of the equation.

It's clearly not a trivial query and it's marked as FULL optimization.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

scott.laird Mr or Mrs. 500 Points: 521 More actions · Answer 5

I am using SSMS.

IF object_id('tempdb..#X') IS NOT NULL

BEGIN

DROP TABLE #X;

END

--create temp table

CREATE TABLE #X (

sql_handle varbinary(64),

plan_handle varbinary(64),

statement_start_offset int,

statement_end_offset int,

query_hash binary(8),

query_plan_hash binary(8),

creation_time datetime,

last_execution_time datetime,

execution_count int,

AvgWorkerTime decimal(19,4),

AvgLogicalReads decimal(19,4),

SQL_Text varchar(max)

);

insert into #X (sql_handle,plan_handle,statement_start_offset,statement_end_offset,query_hash,query_plan_hash,creation_time,last_execution_time,execution_count,AvgWorkerTime,AvgLogicalReads)

select TOP 300 sql_handle,plan_handle,statement_start_offset,statement_end_offset,query_hash,query_plan_hash,

creation_time,last_execution_time,execution_count,

CAST(total_worker_time as decimal(19,4)) / execution_count as AvgWorkerTime,

CAST(total_logical_reads as decimal(19,4)) / execution_count as AvgLogicalReads

FROM sys.dm_exec_query_stats

ORDER BY AvgWorkerTime DESC;

update #X SET SQL_Text = st.text

FROM #X CROSS APPLY

sys.dm_exec_sql_text(#X.sql_handle) st;

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)

select getdate() as DateTimeStamp,'???' as AbortReason,#X.creation_time,#X.last_execution_time,#X.execution_count,#X.AvgWorkerTime,#X.AvgLogicalReads,

#X.SQL_Text,qp.query_plan

FROM

#X CROSS APPLY

sys.dm_exec_query_plan(#X.plan_handle) AS qp

WHERE NOT qp.query_plan.exist('//p:StmtSimple/@StatementOptmEarlyAbortReason[.="GoodEnoughPlanFound"]') = 1 and

NOT qp.query_plan.exist('//p:StmtSimple/@StatementOptmLevel[.="TRIVIAL"]') = 1;

DROP TABLE #X;

Grant Fritchey SSC Guru Points: 398711 More actions · Answer 6

I have an explanation from Paul White, so I put a lot of faith in it.

It was my understanding that when a plan was found that was adequate according to the optimizer, you would see the "Good Enough Plan Found" result. But, in fact, each of the different search levels within the optimizer has a set of triggers (and no, these aren't defined as such). When the triggers are met before the search is complete, you'll get "Good Enough Plan Found" as the reason for early termination because the process terminated early. But, if it completes the search and then decides that the plan is good enough, you won't see the "Good Enough Plan Found". This is because the search level completed and the plan found at the search level was determined to be adequate. You get timeouts when it runs through the iterations within the levels and runs out of iterations without finding a good plan.

So, in short, effectively, if it says "Good Enough Plan Found" the optimizer was satisfied that it found a good enough plan. If it says nothing at all for the optimization level of FULL, then the optimizer was also satisfied that it found a good enough plan, but the process was not terminated early. Then there's timeout and the out of memory error as the other reasons for early termination.

I hope that helps.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

scott.laird Mr or Mrs. 500 Points: 521 More actions · Answer 7

scott.laird

Mr or Mrs. 500

Points: 521

August 26, 2015 at 10:31 am

#1822548

Thank you for your time and effort.

Scott