May 2, 2012 at 3:23 pm
Please refer to the query below:
DECLARE
@applicationID int = 203,
@logType varchar(50) = 'all',
@startRange datetime = '20120501 8:55 AM' ,
@endRange datetime = '20120502 11:59 PM'
SET STATISTICS IO ON;
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT
app.name AS applicationName,
logType,
[description],
entryDate,
extendedLogs
FROM
tbl_Log L
INNER JOIN tbl_Application app ON L.applicationId = app.applicationID
WHERE
L.applicationID = @applicationID
ANDentryDate BETWEEN @startRange AND @endRange
AND(
@logType = 'all'
ORcharindex(@logType, logType) = 1
)
ORDER BY
entryDate DESC
Reference on Twitter @ShellyBradford #sqlhelp
_________________________________________________________________________________________________________________________
Show Plan XML Below:
<?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.1.1" Build="10.0.5766.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="5" StatementEstRows="1023.87" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="4.25143" StatementText="SELECT app.name AS applicationName, logType, [description], entryDate, extendedLogs FROM tbl_Log L (NOLOCK) INNER JOIN tbl_Application app ON L.applicationId = app.applicationID WHERE L.applicationID = @applicationID ANDentryDate BETWEEN @startRange AND @endRange AND( @logType = 'all' ORcharindex(@logType, logType) = 1 ) ORDER BY entryDate DESC " StatementType="SELECT" QueryHash="0x3B2B8454A26541F6" QueryPlanHash="0xD5E4D11C3BEF520E">
<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="33" CompileCPU="4" CompileMemory="376">
<RelOp AvgRowSize="6206" EstimateCPU="0.00427976" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1023.87" LogicalOp="Inner Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="4.25143">
<OutputList>
<ColumnReference Database="[LogMill]" Schema="[dbo]" Table="[tbl_Log]" Alias="[L]" Column="logType" />
<ColumnReference Database="[LogMill]" Schema="[dbo]" Table="[tbl_Log]" Alias="[L]" Column="entryDate" />
<ColumnReference Database="[LogMill]" Schema="[dbo]" Table="[tbl_Log]" Alias="[L]" Column="description" />
<ColumnReference Database="[LogMill]" Schema="[dbo]" Table="[tbl_Log]" Alias="[L]" Column="extendedLogs" />
<ColumnReference Database="[LogMill]" Schema="[dbo]" Table="[tbl_Application]" Alias="[app]" Column="name" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="250052" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<RelOp AvgRowSize="161" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="1" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="199">
<OutputList>
<ColumnReference Database="[LogMill]" Schema="[dbo]" Table="[tbl_Application]" Alias="[app]" Column="name" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[LogMill]" Schema="[dbo]" Table="[tbl_Application]" Alias="[app]" Column="name" />
</DefinedValue>
</DefinedValues>
<Object Database="[LogMill]" Schema="[dbo]" Table="[tbl_Application]" Index="[PK_tbl_Application]" Alias="[app]" IndexKind="Clustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[LogMill]" Schema="[dbo]" Table="[tbl_Application]" Alias="[app]" Column="applicationID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[@applicationID]">
<Identifier>
<ColumnReference Column="@applicationID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="6054" EstimateCPU="0.00427976" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1023.87" LogicalOp="Inner Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="4.24387">
<OutputList>
<ColumnReference Database="[LogMill]" Schema="[dbo]" Table="[tbl_Log]" Alias="[L]" Column="logType" />
<ColumnReference Database="[LogMill]" Schema="[dbo]" Table="[tbl_Log]" Alias="[L]" Column="entryDate" />
<ColumnReference Database="[LogMill]" Schema="[dbo]" Table="[tbl_Log]" Alias="[L]" Column="description" />
<ColumnReference Database="[LogMill]" Schema="[dbo]" Table="[tbl_Log]" Alias="[L]" Column="extendedLogs" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="250052" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false" WithOrderedPrefetch="true">
<OuterReferences>
<ColumnReference Database="[LogMill]" Schema="[dbo]" Table="[tbl_Log]" Alias="[L]" Column="identifier" />
<ColumnReference Column="Expr1008" />
</OuterReferences>
<RelOp AvgRowSize="38" EstimateCPU="0.126555" EstimateIO="0.611718" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1023.87" LogicalOp="Index Seek" NodeId="4" Parallel="false" Partitioned="true" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.738273" TableCardinality="1276750">
<OutputList>
<ColumnReference Database="[LogMill]" Schema="[dbo]" Table="[tbl_Log]" Alias="[L]" Column="identifier" />
<ColumnReference Database="[LogMill]" Schema="[dbo]" Table="[tbl_Log]" Alias="[L]" Column="applicationId" />
<ColumnReference Database="[LogMill]" Schema="[dbo]" Table="[tbl_Log]" Alias="[L]" Column="logType" />
<ColumnReference Database="[LogMill]" Schema="[dbo]" Table="[tbl_Log]" Alias="[L]" Column="entryDate" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="250052" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<RunTimePartitionSummary>
<PartitionsAccessed PartitionCount="1">
<PartitionRange Start="8" End="8" />
</PartitionsAccessed>
</RunTimePartitionSummary>
<IndexScan Ordered="true" ScanDirection="BACKWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[LogMill]" Schema="[dbo]" Table="[tbl_Log]" Alias="[L]" Column="identifier" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[LogMill]" Schema="[dbo]" Table="[tbl_Log]" Alias="[L]" Column="applicationId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[LogMill]" Schema="[dbo]" Table="[tbl_Log]" Alias="[L]" Column="logType" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[LogMill]" Schema="[dbo]" Table="[tbl_Log]" Alias="[L]" Column="entryDate" />
</DefinedValue>
</DefinedValues>
<Object Database="[LogMill]" Schema="[dbo]" Table="[tbl_Log]" Index="[IX_EntryDate]" Alias="[L]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<StartRange ScanType="GE">
<RangeColumns>
<ColumnReference Column="PtnId1004" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="RangePartitionNew(CONVERT_IMPLICIT(datetime2(3),[@startRange],0),(1),'2012-03-17 00:00:00.000','2012-03-24 00:00:00.000','2012-03-31 00:00:00.000','2012-04-07 00:00:00.000','2012-04-14 00:00:00.000','2012-04-21 00:00:00.000','2012-04-28 00:00:00.000','2012-05-05 00:00:00.000')">
<Intrinsic FunctionName="RangePartitionNew">
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1005">
<ScalarOperator>
<Convert DataType="datetime2" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@startRange" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2012-03-17 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2012-03-24 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2012-03-31 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2012-04-07 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2012-04-14 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2012-04-21 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2012-04-28 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2012-05-05 00:00:00.000'" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</RangeExpressions>
</StartRange>
<EndRange ScanType="LE">
<RangeColumns>
<ColumnReference Column="PtnId1004" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="RangePartitionNew(CONVERT_IMPLICIT(datetime2(3),[@endRange],0),(1),'2012-03-17 00:00:00.000','2012-03-24 00:00:00.000','2012-03-31 00:00:00.000','2012-04-07 00:00:00.000','2012-04-14 00:00:00.000','2012-04-21 00:00:00.000','2012-04-28 00:00:00.000','2012-05-05 00:00:00.000')">
<Intrinsic FunctionName="RangePartitionNew">
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1006">
<ScalarOperator>
<Convert DataType="datetime2" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@endRange" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2012-03-17 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2012-03-24 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2012-03-31 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2012-04-07 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2012-04-14 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2012-04-21 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2012-04-28 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2012-05-05 00:00:00.000'" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</RangeExpressions>
</EndRange>
</SeekKeys>
<SeekKeys>
<StartRange ScanType="GE">
<RangeColumns>
<ColumnReference Database="[LogMill]" Schema="[dbo]" Table="[tbl_Log]" Alias="[L]" Column="entryDate" />
<ColumnReference Database="[LogMill]" Schema="[dbo]" Table="[tbl_Log]" Alias="[L]" Column="applicationId" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="CONVERT_IMPLICIT(datetime2(3),[@startRange],0)">
<Identifier>
<ColumnReference Column="ConstExpr1005">
<ScalarOperator>
<Convert DataType="datetime2" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@startRange" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="[@applicationID]">
<Identifier>
<ColumnReference Column="@applicationID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</StartRange>
<EndRange ScanType="LE">
<RangeColumns>
<ColumnReference Database="[LogMill]" Schema="[dbo]" Table="[tbl_Log]" Alias="[L]" Column="entryDate" />
<ColumnReference Database="[LogMill]" Schema="[dbo]" Table="[tbl_Log]" Alias="[L]" Column="applicationId" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="CONVERT_IMPLICIT(datetime2(3),[@endRange],0)">
<Identifier>
<ColumnReference Column="ConstExpr1006">
<ScalarOperator>
<Convert DataType="datetime2" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@endRange" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="[@applicationID]">
<Identifier>
<ColumnReference Column="@applicationID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</EndRange>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[LogMill].[dbo].[tbl_Log].[applicationId] as [L].[applicationId]=[@applicationID] AND ([@logType]='all' OR charindex([@logType],[LogMill].[dbo].[tbl_Log].[logType] as [L].[logType])=(1))">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[LogMill]" Schema="[dbo]" Table="[tbl_Log]" Alias="[L]" Column="applicationId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="@applicationID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Logical Operation="OR">
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1007">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@logType" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'all'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Intrinsic FunctionName="charindex">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@logType" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[LogMill]" Schema="[dbo]" Table="[tbl_Log]" Alias="[L]" Column="logType" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="6037" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="1022.87" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="6" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="3.34274" TableCardinality="1276750">
<OutputList>
<ColumnReference Database="[LogMill]" Schema="[dbo]" Table="[tbl_Log]" Alias="[L]" Column="description" />
<ColumnReference Database="[LogMill]" Schema="[dbo]" Table="[tbl_Log]" Alias="[L]" Column="extendedLogs" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="250052" ActualEndOfScans="0" ActualExecutions="250052" />
</RunTimeInformation>
<IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[LogMill]" Schema="[dbo]" Table="[tbl_Log]" Alias="[L]" Column="description" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[LogMill]" Schema="[dbo]" Table="[tbl_Log]" Alias="[L]" Column="extendedLogs" />
</DefinedValue>
</DefinedValues>
<Object Database="[LogMill]" Schema="[dbo]" Table="[tbl_Log]" Index="[PK_tbl_Log_1]" Alias="[L]" TableReferenceId="-1" IndexKind="Clustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[LogMill]" Schema="[dbo]" Table="[tbl_Log]" Alias="[L]" Column="identifier" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[LogMill].[dbo].[tbl_Log].[identifier] as [L].[identifier]">
<Identifier>
<ColumnReference Database="[LogMill]" Schema="[dbo]" Table="[tbl_Log]" Alias="[L]" Column="identifier" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</NestedLoops>
</RelOp>
<ParameterList>
<ColumnReference Column="@logType" ParameterRuntimeValue="'all'" />
<ColumnReference Column="@endRange" ParameterRuntimeValue="'2012-05-02 23:59:00.000'" />
<ColumnReference Column="@startRange" ParameterRuntimeValue="'2012-05-01 08:55:00.000'" />
<ColumnReference Column="@applicationID" ParameterRuntimeValue="(203)" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
May 2, 2012 at 3:51 pm
Shelly,
The 1 row estimate, 250052 actual occurs on the Key Lookup. This is how SQL Server looks up columns in the base table needed for the query that are not part of the index IX_EntryDate on tbl_Log. The columns it needs in this case are description and extendedLogs. If these columns are frequently accessed via that index, and the columns are not too large, you might consider adding them to the IX_EntryDate index as INCLUDEd columns.
As I mentioned on Twitter, the 1 row estimate is per execution of the nested loops join. In this case, 250,052 rows are returned by the index seek on IX_EntryDate, so 250,052 lookups are performed. This is significantly more than the estimate of 1023.87 rows.
That estimate is likely incorrect because tbl_Log is partitioned and the conditions are complex. The server is having to perform a dynamic partition seek after converting @startRange and @endRange to datetime2(3). The second level of the seek is on entryDate and applicationID. There is then a residual predicate (applied to rows that match the seek predicate) of "applicationId=@applicationID AND ([@logType]='all' OR charindex([@logType],logType)=1)".
You could try adding OPTION (RECOMPILE) to this query. This will allow the optimizer to see the variable values each time and produce a specific plan for those values. This may give better estimates and a better query, but ultimately you may need to provide hints because the complex conditions make this a tough one for the optimizer to cost accurately.
P.S. There is an option, when writing a post, to attach a file. This is much easier to work with than raw XML show plan text 🙂
May 3, 2012 at 12:57 pm
Thank you for your reply. It was very helpful. 🙂
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply