Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
<?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="11.0.6567.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="56980.2" StatementId="1" StatementOptmLevel="TRIVIAL" StatementSubTreeCost="4.04474" StatementText="SELECT [varchar1]&#xD;&#xA; ,[varchar4]&#xD;&#xA; ,[Date1]&#xD;&#xA; ,[varchar5]&#xD;&#xA; ,[varchar6]&#xD;&#xA; ,[varchar7]&#xD;&#xA; ,[varchar8]&#xD;&#xA; ,[varchar9]&#xD;&#xA; ,[xml1]&#xD;&#xA;FROM [Database1].[dbo].[Table1] WITH (NOLOCK)&#xD;&#xA;WHERE [varchar1] = '0'&#xD;&#xA;AND ([Date1] BETWEEN '2014-1-1' AND '2017-1-1')&#xD;&#xA;AND [varchar8] = 'someText'&#xD;&#xA;AND [varchar9] LIKE '%a%'&#xD;&#xA;ORDER BY [varchar1] ASC, [Date1] DESC&#xD;&#xA;OFFSET 0 ROWS&#xD;&#xA;FETCH NEXT 65000 ROWS ONLY" StatementType="SELECT" QueryHash="0x9F1F807652F8E406" QueryPlanHash="0x725862C2C89E2E5F" RetrievedFromCache="true">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_varchar9="true" />
<QueryPlan DegreeOfParallelism="1" CachedPlanSize="24" CompileTime="6" CompileCPU="6" CompileMemory="312">
<MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="26214" EstimatedPagesCached="6553" EstimatedAvailableDegreeOfParallelism="2" />
<RelOp AvgRowSize="4787" EstimateCPU="0.00569802" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="56980.2" LogicalOp="Top" NodeId="0" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="4.04474">
<OutputList>
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="varchar1" />
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="varchar4" />
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="Date1" />
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="varchar5" />
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="varchar6" />
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="varchar7" />
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="varchar8" />
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="varchar9" />
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="xml1" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="61123" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="false" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(65000)">
<Const ConstValue="(65000)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="4787" EstimateCPU="0.100472" EstimateIO="3.92216" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="56980.2" LogicalOp="Index Seek" NodeId="1" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="4.02263" TableCardinality="94061">
<OutputList>
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="Id" />
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="varchar1" />
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="varchar4" />
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="Date1" />
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="varchar5" />
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="varchar6" />
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="varchar7" />
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="varchar8" />
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="varchar9" />
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="xml1" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="61123" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="Id" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="varchar1" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="varchar4" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="Date1" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="varchar5" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="varchar6" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="varchar7" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="varchar8" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="varchar9" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="xml1" />
</DefinedValue>
</DefinedValues>
<Object Database="[Database1]" Schema="[dbo]" Table="[Table1]" Index="[i_table_search]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="varchar1" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="'0'">
<Const ConstValue="'0'" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
<StartRange ScanType="LE">
<RangeColumns>
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="Date1" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="'2017-01-01 00:00:00.000'">
<Const ConstValue="'2017-01-01 00:00:00.000'" />
</ScalarOperator>
</RangeExpressions>
</StartRange>
<EndRange ScanType="GE">
<RangeColumns>
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="Date1" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="'2014-01-01 00:00:00.000'">
<Const ConstValue="'2014-01-01 00:00:00.000'" />
</ScalarOperator>
</RangeExpressions>
</EndRange>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[Database1].[dbo].[Table1].[varchar8]='someText' AND [Database1].[dbo].[Table1].[varchar9] like '%a%'">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<varchar9>
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="varchar8" />
</varchar9>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'someText'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Intrinsic FunctionName="like">
<ScalarOperator>
<varchar9>
<ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="varchar9" />
</varchar9>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'%a%'" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</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