Created
June 5, 2016 12:53
-
-
Save NickCraver/9b76ced8cc5d13d048bbce0ef0083559 to your computer and use it in GitHub Desktop.
SQL 2016 RTM AG Regression Query Plan
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.5" Build="13.0.1601.5"> | |
| <BatchSequence> | |
| <Batch> | |
| <Statements> | |
| <StmtSimple StatementText="CREATE function sys.fn_hadr_is_same_replica(
	@lag_id uniqueidentifier,
 @lag_replica_id uniqueidentifier,
	@ag_replica_id uniqueidentifier)
returns bit
as
begin
 declare @ag_id uniqueidentifier
	 declare @local_replica_id uniqueidentifier
	 declare @ret bit
	 
 SELECT @ag_id = group_id 
 FROM sys.fn_hadr_distributed_ag_replica(@lag_id, @lag_replica_id)" StatementId="1" StatementCompId="3" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="1.157e-006" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x48E1F82013EEA15A" QueryPlanHash="0x3443401473380A77" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="130"> | |
| <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="false" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" /> | |
| <QueryPlan CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="144"> | |
| <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" /> | |
| <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="2457600" EstimatedPagesCached="4915200" EstimatedAvailableDegreeOfParallelism="16" /> | |
| <RelOp NodeId="0" PhysicalOp="Table-valued function" LogicalOp="Table-valued function" EstimateRows="1" EstimateIO="0" EstimateCPU="1.157e-006" AvgRowSize="23" EstimatedTotalSubtreeCost="1.157e-006" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"> | |
| <OutputList> | |
| <ColumnReference Table="[FN_HADR_DISTRIBUTED_AG_REPLICA]" Column="group_id" /> | |
| </OutputList> | |
| <TableValuedFunction> | |
| <DefinedValues> | |
| <DefinedValue> | |
| <ColumnReference Table="[FN_HADR_DISTRIBUTED_AG_REPLICA]" Column="group_id" /> | |
| </DefinedValue> | |
| </DefinedValues> | |
| <Object Table="[FN_HADR_DISTRIBUTED_AG_REPLICA]" /> | |
| <ParameterList> | |
| <ScalarOperator ScalarString="[@lag_id]"> | |
| <Identifier> | |
| <ColumnReference Column="@lag_id" /> | |
| </Identifier> | |
| </ScalarOperator> | |
| <ScalarOperator ScalarString="[@lag_replica_id]"> | |
| <Identifier> | |
| <ColumnReference Column="@lag_replica_id" /> | |
| </Identifier> | |
| </ScalarOperator> | |
| </ParameterList> | |
| </TableValuedFunction> | |
| </RelOp> | |
| <ParameterList> | |
| <ColumnReference Column="@lag_replica_id" ParameterCompiledValue="{guid'846D987A-A20E-47DF-94ED-56C014EAAF98'}" /> | |
| <ColumnReference Column="@lag_id" ParameterCompiledValue="{guid'6ECE41C8-8FFB-4FE4-8A4C-37B50E84BFCC'}" /> | |
| </ParameterList> | |
| </QueryPlan> | |
| </StmtSimple> | |
| <StmtSimple StatementText=" 

	 SELECT @local_replica_id = replica_id
	 FROM sys.dm_hadr_internal_availability_replica_states
	 WHERE group_id = @ag_id AND is_local = 1" StatementId="2" StatementCompId="4" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.000940157" StatementEstRows="4.72871" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x6C3D7AC7A2A2A315" QueryPlanHash="0xCEE84596246B2C53" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="130"> | |
| <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="false" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" /> | |
| <QueryPlan CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="312"> | |
| <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" /> | |
| <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="2457600" EstimatedPagesCached="4915200" EstimatedAvailableDegreeOfParallelism="16" /> | |
| <RelOp NodeId="0" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="4.72871" EstimateIO="0" EstimateCPU="0.00044" AvgRowSize="23" EstimatedTotalSubtreeCost="0.000940157" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"> | |
| <OutputList> | |
| <ColumnReference Table="[DM_HADR_INTERNAL_AVAILABILITY_REPLICA_STATES]" Column="replica_id" /> | |
| </OutputList> | |
| <Filter StartupExpression="0"> | |
| <RelOp NodeId="1" PhysicalOp="Table-valued function" LogicalOp="Table-valued function" EstimateRows="500" EstimateIO="0" EstimateCPU="0.000500157" AvgRowSize="40" EstimatedTotalSubtreeCost="0.000500157" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"> | |
| <OutputList> | |
| <ColumnReference Table="[DM_HADR_INTERNAL_AVAILABILITY_REPLICA_STATES]" Column="replica_id" /> | |
| <ColumnReference Table="[DM_HADR_INTERNAL_AVAILABILITY_REPLICA_STATES]" Column="group_id" /> | |
| <ColumnReference Table="[DM_HADR_INTERNAL_AVAILABILITY_REPLICA_STATES]" Column="is_local" /> | |
| </OutputList> | |
| <TableValuedFunction> | |
| <DefinedValues> | |
| <DefinedValue> | |
| <ColumnReference Table="[DM_HADR_INTERNAL_AVAILABILITY_REPLICA_STATES]" Column="replica_id" /> | |
| </DefinedValue> | |
| <DefinedValue> | |
| <ColumnReference Table="[DM_HADR_INTERNAL_AVAILABILITY_REPLICA_STATES]" Column="group_id" /> | |
| </DefinedValue> | |
| <DefinedValue> | |
| <ColumnReference Table="[DM_HADR_INTERNAL_AVAILABILITY_REPLICA_STATES]" Column="is_local" /> | |
| </DefinedValue> | |
| </DefinedValues> | |
| <Object Table="[DM_HADR_INTERNAL_AVAILABILITY_REPLICA_STATES]" /> | |
| </TableValuedFunction> | |
| </RelOp> | |
| <Predicate> | |
| <ScalarOperator ScalarString="DM_HADR_INTERNAL_AVAILABILITY_REPLICA_STATES.[group_id]=[@ag_id] AND DM_HADR_INTERNAL_AVAILABILITY_REPLICA_STATES.[is_local]=(1)"> | |
| <Logical Operation="AND"> | |
| <ScalarOperator> | |
| <Compare CompareOp="EQ"> | |
| <ScalarOperator> | |
| <Identifier> | |
| <ColumnReference Table="[DM_HADR_INTERNAL_AVAILABILITY_REPLICA_STATES]" Column="group_id" /> | |
| </Identifier> | |
| </ScalarOperator> | |
| <ScalarOperator> | |
| <Identifier> | |
| <ColumnReference Column="@ag_id" /> | |
| </Identifier> | |
| </ScalarOperator> | |
| </Compare> | |
| </ScalarOperator> | |
| <ScalarOperator> | |
| <Compare CompareOp="EQ"> | |
| <ScalarOperator> | |
| <Identifier> | |
| <ColumnReference Table="[DM_HADR_INTERNAL_AVAILABILITY_REPLICA_STATES]" Column="is_local" /> | |
| </Identifier> | |
| </ScalarOperator> | |
| <ScalarOperator> | |
| <Const ConstValue="(1)" /> | |
| </ScalarOperator> | |
| </Compare> | |
| </ScalarOperator> | |
| </Logical> | |
| </ScalarOperator> | |
| </Predicate> | |
| </Filter> | |
| </RelOp> | |
| </QueryPlan> | |
| </StmtSimple> | |
| <StmtSimple StatementText="

	 set @ret = case when (@local_replica_id = @ag_replica_id) then 1 else 0 end

 " StatementId="3" StatementCompId="5" StatementType="ASSIGN" RetrievedFromCache="true" /> | |
| <StmtSimple StatementText="return @ret" StatementId="4" StatementCompId="6" StatementType="RETURN" RetrievedFromCache="true" /> | |
| </Statements> | |
| </Batch> | |
| </BatchSequence> | |
| </ShowPlanXML> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment