Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
The Plan
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.539" Build="15.0.2000.5" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="2" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150" StatementSubTreeCost="0.0346973" StatementText="SELECT TOP 1 theDB.IDNum, FORMAT(theDate, 'M/d/yy') as theDate, Status, Rate FROM theDB INNER JOIN DomainTable on theDB.IDNum = DomainTable.IDNum where DomainIP = '127.0.0.1' and status IN ('Active', 'To ReActivate', 'To Deactivate', 'Deactivate ASAP', 'SUSPENDED', 'SUSPENDED X', 'SUSPENDED Y', 'SUSPENDED Z') order by theDB.IDNum DESC" StatementType="SELECT" QueryHash="0x5CC8267C047604B4" QueryPlanHash="0x6634D125444C9D74" RetrievedFromCache="true" SecurityPolicyApplied="false">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan DegreeOfParallelism="1" MemoryGrant="1024" CachedPlanSize="48" CompileTime="9" CompileCPU="9" CompileMemory="736">
<MemoryGrantInfo IDNumRequiredMemory="16" IDNumDesiredMemory="24" RequiredMemory="16" DesiredMemory="24" RequestedMemory="1024" GrantWaitTime="0" GrantedMemory="1024" MaxUsedMemory="0" MaxQueryMemory="21524568" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1676522" EstimatedPagesCached="838261" EstimatedAvailableDegreeOfParallelism="4" MaxCompileMemory="85160464" />
<OptimizerStatsUsage>
<StatisticsInfo Database="[theDB]" Schema="[dbo]" Table="[DomainTable]" Statistics="[DomainTable$WebDomainIP]" ModificationCount="5" SamplingPercent="100" LastUpdate="2020-01-20T08:30:16.29" />
<StatisticsInfo Database="[theDB]" Schema="[dbo]" Table="[DomainTable]" Statistics="[DomainTable$Purpose]" ModificationCount="5" SamplingPercent="100" LastUpdate="2020-01-20T08:30:16.22" />
<StatisticsInfo Database="[theDB]" Schema="[dbo]" Table="[DomainTable]" Statistics="[DomainTable$CustomerID]" ModificationCount="5" SamplingPercent="100" LastUpdate="2020-01-20T08:30:15.96" />
<StatisticsInfo Database="[theDB]" Schema="[dbo]" Table="[DomainTable]" Statistics="[DomainTable$IorR]" ModificationCount="5" SamplingPercent="100" LastUpdate="2020-01-20T08:30:16.17" />
<StatisticsInfo Database="[theDB]" Schema="[dbo]" Table="[DomainTable]" Statistics="[DomainTable$DomainTableIDNum]" ModificationCount="5" SamplingPercent="100" LastUpdate="2020-01-20T08:30:15.86" />
<StatisticsInfo Database="[theDB]" Schema="[dbo]" Table="[DomainTable]" Statistics="[DomainTable$Suffix]" ModificationCount="5" SamplingPercent="100" LastUpdate="2020-01-20T08:30:16.27" />
<StatisticsInfo Database="[theDB]" Schema="[dbo]" Table="[theDB]" Statistics="[theDB$theDBStatusTabletheDB]" ModificationCount="33" SamplingPercent="20.3575" LastUpdate="2020-01-20T08:30:10.46" />
<StatisticsInfo Database="[theDB]" Schema="[dbo]" Table="[theDB]" Statistics="[theDB$PrimaryKey]" ModificationCount="23" SamplingPercent="17.8084" LastUpdate="2020-01-20T08:30:10.28" />
<StatisticsInfo Database="[theDB]" Schema="[dbo]" Table="[DomainTable]" Statistics="[DomainTable$AdminContact]" ModificationCount="5" SamplingPercent="100" LastUpdate="2020-01-20T08:30:15.89" />
<StatisticsInfo Database="[theDB]" Schema="[dbo]" Table="[DomainTable]" Statistics="[DomainTable$StatusPTR]" ModificationCount="5" SamplingPercent="100" LastUpdate="2020-01-20T08:30:16.24" />
<StatisticsInfo Database="[theDB]" Schema="[dbo]" Table="[DomainTable]" Statistics="[DomainTable$DatePaid]" ModificationCount="5" SamplingPercent="100" LastUpdate="2020-01-20T08:30:15.98" />
<StatisticsInfo Database="[theDB]" Schema="[dbo]" Table="[DomainTable]" Statistics="[DomainTable$CustomerCounter]" ModificationCount="5" SamplingPercent="100" LastUpdate="2020-01-20T08:30:15.94" />
<StatisticsInfo Database="[theDB]" Schema="[dbo]" Table="[DomainTable]" Statistics="[DomainTable$DomainIP]" ModificationCount="6" SamplingPercent="100" LastUpdate="2020-01-20T08:30:16.07" />
<StatisticsInfo Database="[theDB]" Schema="[dbo]" Table="[DomainTable]" Statistics="[DomainTable$PaidToUs]" ModificationCount="5" SamplingPercent="100" LastUpdate="2020-01-20T08:30:16.2" />
<StatisticsInfo Database="[theDB]" Schema="[dbo]" Table="[DomainTable]" Statistics="[DomainTable$InvoiceNo]" ModificationCount="5" SamplingPercent="100" LastUpdate="2020-01-20T08:30:16.14" />
<StatisticsInfo Database="[theDB]" Schema="[dbo]" Table="[DomainTable]" Statistics="[DomainTable$DateReg]" ModificationCount="5" SamplingPercent="100" LastUpdate="2020-01-20T08:30:16" />
<StatisticsInfo Database="[theDB]" Schema="[dbo]" Table="[DomainTable]" Statistics="[DomainTable$DateReg1]" ModificationCount="5" SamplingPercent="100" LastUpdate="2020-01-20T08:30:16.03" />
<StatisticsInfo Database="[theDB]" Schema="[dbo]" Table="[DomainTable]" Statistics="[DomainTable$DomainTableDomain]" ModificationCount="6" SamplingPercent="100" LastUpdate="2020-01-20T08:30:16.12" />
<StatisticsInfo Database="[theDB]" Schema="[dbo]" Table="[DomainTable]" Statistics="[DomainTable$AmountPaid]" ModificationCount="5" SamplingPercent="100" LastUpdate="2020-01-20T08:30:15.91" />
</OptimizerStatsUsage>
<QueryTimeStats CpuTime="0" ElapsedTime="0" />
<RelOp AvgRowSize="4040" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0346973">
<OutputList>
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[theDB]" Column="IDNum" />
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[theDB]" Column="Status" />
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[theDB]" Column="Rate" />
<ColumnReference Column="Expr1004" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1004" />
<ScalarOperator ScalarString="format([theDB].[dbo].[theDB].[theDate],N'M/d/yy')">
<Intrinsic FunctionName="format">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[theDB]" Column="theDate" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N'M/d/yy'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="44" EstimateCPU="0.000100044" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="TopN Sort" NodeId="1" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.0346972">
<OutputList>
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[theDB]" Column="IDNum" />
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[theDB]" Column="theDate" />
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[theDB]" Column="Status" />
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[theDB]" Column="Rate" />
</OutputList>
<MemoryFractions Input="1" Output="1" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="0" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" InputMemoryGrant="1024" OutputMemoryGrant="1024" UsedMemoryGrant="0" />
</RunTimeInformation>
<TopSort Distinct="false" Rows="1">
<OrderBy>
<OrderByColumn Ascending="false">
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[theDB]" Column="IDNum" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="44" EstimateCPU="1.67227E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0233359">
<OutputList>
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[theDB]" Column="IDNum" />
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[theDB]" Column="theDate" />
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[theDB]" Column="Status" />
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[theDB]" Column="Rate" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[DomainTable]" Column="IDNum" />
</OuterReferences>
<RelOp AvgRowSize="11" EstimateCPU="1.67227E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="4.00064" LogicalOp="Inner Join" NodeId="3" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0133002">
<OutputList>
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[DomainTable]" Column="IDNum" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[DomainTable]" Column="DomainID" />
</OuterReferences>
<RelOp AvgRowSize="11" EstimateCPU="0.000161401" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="4.00064" EstimatedRowsRead="4.00064" LogicalOp="Index Seek" NodeId="4" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032864" TableCardinality="31055">
<OutputList>
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[DomainTable]" Column="DomainID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="3" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[DomainTable]" Column="DomainID" />
</DefinedValue>
</DefinedValues>
<Object Database="[theDB]" Schema="[dbo]" Table="[DomainTable]" Index="[DomainTable$DomainIP]" IndexKind="NonClustered" Storage="RowStore" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[DomainTable]" Column="DomainIP" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="N'127.0.0.1'">
<Const ConstValue="N'127.0.0.1'" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="3.00064" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="6" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0099971" TableCardinality="31055">
<OutputList>
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[DomainTable]" Column="IDNum" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[DomainTable]" Column="IDNum" />
</DefinedValue>
</DefinedValues>
<Object Database="[theDB]" Schema="[dbo]" Table="[DomainTable]" Index="[DomainTable$PrimaryKey]" TableReferenceId="-1" IndexKind="Clustered" Storage="RowStore" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[DomainTable]" Column="DomainID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[theDB].[dbo].[DomainTable].[DomainID]">
<Identifier>
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[DomainTable]" Column="DomainID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="44" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="3.00048" EstimateRewinds="0.000161031" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Clustered Index Seek" NodeId="7" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0100083" TableCardinality="45433">
<OutputList>
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[theDB]" Column="IDNum" />
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[theDB]" Column="theDate" />
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[theDB]" Column="Status" />
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[theDB]" Column="Rate" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[theDB]" Column="IDNum" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[theDB]" Column="theDate" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[theDB]" Column="Status" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[theDB]" Column="Rate" />
</DefinedValue>
</DefinedValues>
<Object Database="[theDB]" Schema="[dbo]" Table="[theDB]" Index="[theDB$PrimaryKey]" IndexKind="Clustered" Storage="RowStore" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[theDB]" Column="IDNum" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[theDB].[dbo].[DomainTable].[IDNum]">
<Identifier>
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[DomainTable]" Column="IDNum" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[theDB].[dbo].[theDB].[Status]=N'Deactivate ASAP' OR [theDB].[dbo].[theDB].[Status]=N'To Deactivate' OR [theDB].[dbo].[theDB].[Status]=N'Active' OR [theDB].[dbo].[theDB].[Status]=N'SUSPENDED' OR [theDB].[dbo].[theDB].[Status]=N'SUSPENDED ABUSE' OR [theDB].[dbo].[theDB].[Status]=N'SUSPENDED BILLING' OR [theDB].[dbo].[theDB].[Status]=N'SUSPENDED DDOS' OR [theDB].[dbo].[theDB].[Status]=N'To ReActivate'">
<Logical Operation="OR">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[theDB]" Column="Status" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N'Deactivate ASAP'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[theDB]" Column="Status" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N'To Deactivate'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[theDB]" Column="Status" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N'Active'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[theDB]" Column="Status" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N'SUSPENDED'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[theDB]" Column="Status" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N'SUSPENDED X'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[theDB]" Column="Status" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N'SUSPENDED Y'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[theDB]" Column="Status" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N'SUSPENDED Z'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[theDB]" Schema="[dbo]" Table="[theDB]" Column="Status" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N'To ReActivate'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</TopSort>
</RelOp>
</ComputeScalar>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.