Why queries are using certain indexes

Question

Why queries are using certain indexes

pamozer

SSCarpal Tunnel

Points: 4943
More actions
August 21, 2013 at 12:05 pm

#297918

I have the following query written two ways. The first query uses different indexes than the second and I am trying to understand why. Why wouldn't the optimizer figure out to use the same indexes for an or clause versus a union all.
QUERY #1
SELECT TOP 400
*
FROM AuditLog WITH ( NOLOCK )
WHERE CustomerID = 2379
AND PracticeID = 55
AND ( ( AuditTypeID = 4
AND EntityTypeID = 1022638
)
OR ( AuditTypeID = 6
AND SubEntityTypeID = 1022638
)
)
ORDER BY CreatedDate DESC
QUERY #2
SELECT TOP 400 *
FROM (
SELECT TOP 400
*
FROM AuditLog WITH ( NOLOCK )
WHERE CustomerID = 2379
AND PracticeID = 55
AND ( AuditTypeID = 4
AND EntityTypeID = 1022638
)
ORDER BY CreatedDate
UNION ALL
SELECT TOP 400
*
FROM AuditLog WITH ( NOLOCK )
WHERE CustomerID = 2379
AND PracticeID = 55
AND ( AuditTypeID = 6
AND SubEntityTypeID = 1022638
)
ORDER BY CreatedDate)sub
ORDER BY sub.CreatedDate
<?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.3373.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="2" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.00657216" StatementText="SELECT TOP 400 * FROM AuditLog WITH ( NOLOCK ) WHERE CustomerID = 2379 AND PracticeID = 55 AND ( ( AuditTypeID = 4 AND EntityTypeID = 1022638 ) OR ( AuditTypeID = 6 AND SubEntityTypeID = 1022638 ) ) ORDER BY CreatedDate DESC " StatementType="SELECT" QueryHash="0x9F3AF4664382C044" QueryPlanHash="0x2CA78D552FCE421D" 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="40" CompileTime="5" CompileCPU="5" CompileMemory="368">
<MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="341333" EstimatedPagesCached="512000" EstimatedAvailableDegreeOfParallelism="6" />
<RelOp AvgRowSize="4238" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Top" NodeId="0" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.00657216">
<OutputList>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="6" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="false" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(400)">
<Const ConstValue="(400)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="4238" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657038">
<OutputList>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="6" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" />
</OuterReferences>
<RelOp AvgRowSize="31" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Index Seek" NodeId="2" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="175160000">
<OutputList>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="62088" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="BACKWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" />
</DefinedValue>
</DefinedValues>
<Object Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Index="[IX_AuditLog_CustomerID_PracticeID_CreatedDate]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="(2379)">
<Const ConstValue="(2379)" />
</ScalarOperator>
<ScalarOperator ScalarString="(55)">
<Const ConstValue="(55)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="4214" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="175160000">
<OutputList>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="6" ActualEndOfScans="62082" ActualExecutions="62088" />
</RunTimeInformation>
<IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" />
</DefinedValue>
</DefinedValues>
<Object Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Index="[PK_AuditLog]" TableReferenceId="-1" IndexKind="Clustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[KareoAudit].[dbo].[AuditLog].[AuditLogID]">
<Identifier>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[KareoAudit].[dbo].[AuditLog].[AuditTypeID]=(4) AND [KareoAudit].[dbo].[AuditLog].[EntityTypeId]=(1022638) OR [KareoAudit].[dbo].[AuditLog].[AuditTypeID]=(6) AND [KareoAudit].[dbo].[AuditLog].[SubEntityTypeId]=(1022638)">
<Logical Operation="OR">
<ScalarOperator>
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(4)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1022638)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
<ScalarOperator>
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(6)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1022638)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</Top>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
<Batch>
<Statements>
<StmtSimple StatementCompId="3" StatementEstRows="2" StatementId="2" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.0245098" StatementText="SELECT TOP 400 * FROM ( SELECT TOP 400 * FROM AuditLog WITH ( NOLOCK ) WHERE CustomerID = 2379 AND PracticeID = 55 AND ( AuditTypeID = 4 AND EntityTypeID = 1022638 ) ORDER BY CreatedDate UNION ALL SELECT TOP 400 * FROM AuditLog WITH ( NOLOCK ) WHERE CustomerID = 2379 AND PracticeID = 55 AND ( AuditTypeID = 6 AND SubEntityTypeID = 1022638 ) ORDER BY CreatedDate)sub ORDER BY sub.CreatedDate " StatementType="SELECT" QueryHash="0x85D7E4BBA213B22A" QueryPlanHash="0x9FBEFBBE742E66A3" 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" MemoryGrant="1024" CachedPlanSize="64" CompileTime="7" CompileCPU="7" CompileMemory="544">
<MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="560" RequiredMemory="512" DesiredMemory="560" RequestedMemory="1024" GrantWaitTime="0" GrantedMemory="1024" MaxUsedMemory="16" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="341333" EstimatedPagesCached="512000" EstimatedAvailableDegreeOfParallelism="6" />
<RelOp AvgRowSize="4238" EstimateCPU="0.000107358" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2" LogicalOp="TopN Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.0245098">
<OutputList>
<ColumnReference Column="Union1006" />
<ColumnReference Column="Union1007" />
<ColumnReference Column="Union1008" />
<ColumnReference Column="Union1009" />
<ColumnReference Column="Union1010" />
<ColumnReference Column="Union1011" />
<ColumnReference Column="Union1012" />
<ColumnReference Column="Union1013" />
<ColumnReference Column="Union1014" />
<ColumnReference Column="Union1015" />
<ColumnReference Column="Union1016" />
<ColumnReference Column="Union1017" />
<ColumnReference Column="Union1018" />
<ColumnReference Column="Union1019" />
</OutputList>
<MemoryFractions Input="1" Output="1" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="6" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<TopSort Distinct="false" Rows="400">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Column="Union1019" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="4238" EstimateCPU="2E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2" LogicalOp="Concatenation" NodeId="1" Parallel="false" PhysicalOp="Concatenation" EstimatedTotalSubtreeCost="0.0131412">
<OutputList>
<ColumnReference Column="Union1006" />
<ColumnReference Column="Union1007" />
<ColumnReference Column="Union1008" />
<ColumnReference Column="Union1009" />
<ColumnReference Column="Union1010" />
<ColumnReference Column="Union1011" />
<ColumnReference Column="Union1012" />
<ColumnReference Column="Union1013" />
<ColumnReference Column="Union1014" />
<ColumnReference Column="Union1015" />
<ColumnReference Column="Union1016" />
<ColumnReference Column="Union1017" />
<ColumnReference Column="Union1018" />
<ColumnReference Column="Union1019" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="6" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Concat>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Union1006" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1007" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1008" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1009" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1010" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1011" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1012" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1013" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1014" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1015" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1016" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1017" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1018" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1019" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" />
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="4238" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Top" NodeId="2" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.00657048">
<OutputList>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="false" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(400)">
<Const ConstValue="(400)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="4238" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="3" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657038">
<OutputList>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" />
</OuterReferences>
<RelOp AvgRowSize="39" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Index Seek" NodeId="4" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="175160000">
<OutputList>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" />
</DefinedValue>
</DefinedValues>
<Object Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Index="[IX_AuditLog_EntityTypeID_CustomerID_PracticeID_AuditTypeID]" TableReferenceId="1" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="(1022638)">
<Const ConstValue="(1022638)" />
</ScalarOperator>
<ScalarOperator ScalarString="(2379)">
<Const ConstValue="(2379)" />
</ScalarOperator>
<ScalarOperator ScalarString="(55)">
<Const ConstValue="(55)" />
</ScalarOperator>
<ScalarOperator ScalarString="(4)">
<Const ConstValue="(4)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="4205" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="6" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="175160000">
<OutputList>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5" ActualEndOfScans="0" ActualExecutions="5" />
</RunTimeInformation>
<IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" />
</DefinedValue>
</DefinedValues>
<Object Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Index="[PK_AuditLog]" TableReferenceId="-1" IndexKind="Clustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[KareoAudit].[dbo].[AuditLog].[AuditLogID]">
<Identifier>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</Top>
</RelOp>
<RelOp AvgRowSize="4238" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Top" NodeId="25" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.00657048">
<OutputList>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="false" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(400)">
<Const ConstValue="(400)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="4238" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="26" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657038">
<OutputList>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" />
</OuterReferences>
<RelOp AvgRowSize="39" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Index Seek" NodeId="27" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="175160000">
<OutputList>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" />
</DefinedValue>
</DefinedValues>
<Object Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Index="[IX_AuditLog_SubEntityTypeID_CustomerID_PracticeID_AuditTypeID]" TableReferenceId="2" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="(1022638)">
<Const ConstValue="(1022638)" />
</ScalarOperator>
<ScalarOperator ScalarString="(2379)">
<Const ConstValue="(2379)" />
</ScalarOperator>
<ScalarOperator ScalarString="(55)">
<Const ConstValue="(55)" />
</ScalarOperator>
<ScalarOperator ScalarString="(6)">
<Const ConstValue="(6)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="4205" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="29" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="175160000">
<OutputList>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" />
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" />
</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="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" />
</DefinedValue>
</DefinedValues>
<Object Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Index="[PK_AuditLog]" TableReferenceId="-1" IndexKind="Clustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[KareoAudit].[dbo].[AuditLog].[AuditLogID]">
<Identifier>
<ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</Top>
</RelOp>
</Concat>
</RelOp>
</TopSort>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>

Viewing 6 posts - 1 through 5 (of 5 total)

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Even IF your 2 queries are logically equivalent (which I am not going to bother to figure out), the optimizer is just like any other piece of software. It can only do what it is programmed to do. Just like your shop, Microsoft SQL Server Development Team has a limited number of man-hours and other resources available to them and a METRIC BUTT TON of things on their TODO list. They have to prioritize, plan, schedule, test, etc in order to keep up with release cadences (and patches, etc). So the simple answer could well be that your desire for a better query plan for this particular scenario just hasn't made it high enough on their TODO list.

I encourage you to use the CONNECT website to see if someone else has submitted this feature request and up-vote it if so and add it if not.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

I wouldn't expect the 2 queries to have the same execution plan. In the first query the OR condition essentially eliminates the optimizers ability to do a seek on the index used in the 2nd query with UNION ALL, thus the optimizer decided it was faster (less expensive) to seek the [IX_AuditLog_CustomerID_PracticeID_CreatedDate] using the values provided for customerID and practiceID and then push the other criteria down as filters on the Key Lookup. It also looks like your statistics are out of date because it estimated 1 row returned from the seek and got 62088 rows returned. If the statistics were up to date I'd bet you'd get a different plan for query 1, I'd even think you might see a scan of the clustered PK since the Key Lookup is pretty expensive.

Query 2 with the UNION ALL can SEEK on [IX_AuditLog_EntityTypeID_CustomerID_PracticeID_AuditTypeID] for both of the union'd queries since there is no OR'ing just AND'ing on all 4 of the key columns for this index. This is the index I'd expect to see used for this query.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 3

You don't really need the inner TOP in the UNION ALL query.

But as Kevin says, the optimizer is not perfect, and it does not shine on queries with OR. I've been bitten a couple of times myself.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

pamozer SSCarpal Tunnel Points: 4943 More actions · Answer 4

If I don't use the top 400 I will get way too many records back. The table has millions of records

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 5

I said that you don't need the inner TOP. The outer TOP is sufficient. Hm, it may have an interesting effect on the query plan. Try it!

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]