Skip to content

Instantly share code, notes, and snippets.

/gist:fd56ead25d726d247518

Created June 18, 2015 09:57
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save anonymous/fd56ead25d726d247518 to your computer and use it in GitHub Desktop.
Save anonymous/fd56ead25d726d247518 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="12.0.2480.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="70" StatementSubTreeCost="0.0107825" StatementText="SELECT TOP (1) [t0].[ProcessID],&#xD;&#xA; [t0].[MaterialID],&#xD;&#xA; [t0].[Progress],&#xD;&#xA; [t0].[ProcessStatusDescription],&#xD;&#xA; [t0].[WorkflowProcessState],&#xD;&#xA; [t0].[WorkflowProcessSubState]&#xD;&#xA; , [t0].[ProcessState],&#xD;&#xA; [t0].[ProcessStateDateLastModified],&#xD;&#xA; [t0].[DateCreated],&#xD;&#xA; [t0].[DateLastChecked],&#xD;&#xA; [t0].[SleepUntil],&#xD;&#xA; [t0].[LongRunningProcessID]&#xD;&#xA;FROM [dbo].[WF_Process] AS [t0]&#xD;&#xA;WHERE ([t0].[ProcessStateDateLastModified] &lt;= '2015-06-18 09:48:31.597') &#xD;&#xA;AND (([t0].[SleepUntil] IS NULL) OR ([t0].[SleepUntil] &lt;= '2015-06-18 10:03:31.597')) &#xD;&#xA;AND ([t0].[ProcessState] = 1) &#xD;&#xA;--AND ((CONVERT(Int,CONVERT(NVarChar(1),CONVERT(NChar(1),SUBSTRING(CONVERT(NVarChar,[t0].[ProcessID]), (LEN(CONVERT(NVarChar,[t0].[ProcessID])) - 1) + 1, 1))))) IN (7, 8, 9))&#xD;&#xA;ORDER BY [t0].[ProcessStateDateLastModified]" StatementType="SELECT" QueryHash="0x23AC23FDB36E1BC4" QueryPlanHash="0x9A61D6309859461" RetrievedFromCache="true">
<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" CachedPlanSize="48" CompileTime="3" CompileCPU="3" CompileMemory="328">
<MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="183089" EstimatedPagesCached="366179" EstimatedAvailableDegreeOfParallelism="16" />
<RelOp AvgRowSize="606" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Top" NodeId="0" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.0107825">
<OutputList>
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="ProcessID" />
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="MaterialID" />
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="SubtitleVersionID" />
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="Progress" />
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="ProcessStatusDescription" />
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="WorkflowProcessState" />
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="WorkflowProcessSubState" />
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="ProcessState" />
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="ProcessStateDateLastModified" />
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="DateCreated" />
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="DateLastChecked" />
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="SleepUntil" />
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="LongRunningProcessID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="false" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(1)">
<Const ConstValue="(1)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="606" EstimateCPU="0.0832092" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0107824">
<OutputList>
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="ProcessID" />
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="MaterialID" />
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="Progress" />
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="ProcessStatusDescription" />
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="WorkflowProcessState" />
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="WorkflowProcessSubState" />
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="ProcessState" />
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="ProcessStateDateLastModified" />
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="DateCreated" />
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="DateLastChecked" />
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="SleepUntil" />
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="LongRunningProcessID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false" WithOrderedPrefetch="true">
<OuterReferences>
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="ProcessID" />
<ColumnReference Column="Expr1001" />
</OuterReferences>
<RelOp AvgRowSize="35" EstimateCPU="0.206787" EstimateIO="20.2283" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Index Seek" NodeId="3" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00420072" TableCardinality="187845">
<OutputList>
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="ProcessID" />
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="ProcessState" />
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="ProcessStateDateLastModified" />
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="SleepUntil" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="BACKWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="ProcessID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="ProcessState" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="ProcessStateDateLastModified" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="SleepUntil" />
</DefinedValue>
</DefinedValues>
<Object Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Index="[Process state date last mod - SleepUntil - ProcessState]" Alias="[t0]" IndexKind="NonClustered" Storage="RowStore" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<StartRange ScanType="LE">
<RangeColumns>
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="ProcessStateDateLastModified" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="'2015-06-18 09:48:31.597'">
<Const ConstValue="'2015-06-18 09:48:31.597'" />
</ScalarOperator>
</RangeExpressions>
</StartRange>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[MYDB].[dbo].[WF_Process].[ProcessState] as [t0].[ProcessState]=(1) AND ([MYDB].[dbo].[WF_Process].[SleepUntil] as [t0].[SleepUntil] IS NULL OR [MYDB].[dbo].[WF_Process].[SleepUntil] as [t0].[SleepUntil]&lt;='2015-06-18 10:03:31.597')">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="ProcessState" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Logical Operation="OR">
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="SleepUntil" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="LE">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="SleepUntil" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2015-06-18 10:03:31.597'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="577" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="1" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="5" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.00656556" TableCardinality="187845">
<OutputList>
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="MaterialID" />
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="Progress" />
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="ProcessStatusDescription" />
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="WorkflowProcessState" />
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="WorkflowProcessSubState" />
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="DateCreated" />
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="DateLastChecked" />
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="LongRunningProcessID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="MaterialID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="Progress" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="ProcessStatusDescription" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="WorkflowProcessState" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="WorkflowProcessSubState" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="DateCreated" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="DateLastChecked" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="LongRunningProcessID" />
</DefinedValue>
</DefinedValues>
<Object Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Index="[PK_Process]" Alias="[t0]" TableReferenceId="-1" IndexKind="Clustered" Storage="RowStore" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="ProcessID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[MYDB].[dbo].[WF_Process].[ProcessID] as [t0].[ProcessID]">
<Identifier>
<ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[WF_Process]" Alias="[t0]" Column="ProcessID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</Top>
</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