 | |
Sanity Check Query and/or Execution Plan (execution plan looks incorrect)
microsoft.public.sqlserver.programming microsoft.public.sqlserver.programming
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Sanity Check Query and/or Execution Plan (execution plan looks incorrect) -
07-27-2012
, 04:58 PM
#2
| |||
| |||
|
|
I have been experimenting with columnstore indexes on large Entity Name Value (EAV: Entity-Attribute-Value) tables. The execution plans don?t seem to be correct for even basic queries. |
depth, but all your three columnstore operators uses Batch mode which is
good.
Quote:
|
Can someone please review the attached execution plans for a quick sanity check? Is it wrong to assume that Plan 1 should take a similar time to complete as Plan 2 + Plan 3? |
clustered index that includes attributeid and skuid, that index can be used
efficiently.
And, even if there is not... you have a columnstore index, and since values
are stored by column, all atribute ids are in one place per segment and same
for the skuid. If there are larger columns in the columnstore index, these
pages can be skipped.
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
#3
| |||
| |||
|
|
Dave (daveg.01 gmail.com) writes: I have been experimenting with columnstore indexes on large Entity Name Value (EAV: Entity-Attribute-Value) tables. The execution plans don?t seem to be correct for even basic queries. What more precisely do you think is wrong? I have not analysed the query in depth, but all your three columnstore operators uses Batch mode which is good. Can someone please review the attached execution plans for a quick sanity check? Is it wrong to assume that Plan 1 should take a similar time to complete as Plan 2 + Plan 3? Depends on what indexes there are on ptNameValue, but if there is a non- clustered index that includes attributeid and skuid, that index can be used efficiently. And, even if there is not... you have a columnstore index, and since values are stored by column, all atribute ids are in one place per segment and same for the skuid. If there are larger columns in the columnstore index, these pages can be skipped. -- Erland Sommarskog, SQL Server MVP, esquel sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx |
The table has two indexes. It is clustered on date_, SkuID
And it has a columnstore index on all columns.
The execution plan looks like what I would expect from the schema and indexes, however the performance is terrible. The execution plan says most of the cost is in the table insert. The table insert only takes 26 seconds (Plan 3) which is what makes me suspect the plan may not reflect the correct cost.
#4
| |||
| |||
|
|
This is a partitioned table. It is partitioned on Date_ with each hour as its own partition. Date_ is a smalldatetime. The table has two indexes. It is clustered on date_, SkuID And it has a columnstore index on all columns. The execution plan looks like what I would expect from the schema and indexes, however the performance is terrible. The execution plan says most of the cost is in the table insert. The table insert only takes 26 seconds (Plan 3) which is what makes me suspect the plan may not reflect the correct cost. |
pay fairly little attention to them.
If the performance is below your expectations, there may be other issues
in play, for instance the hardware. What sort of machine are you running
this on?
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
#5
| |||
| |||
|
|
Dave (daveg.01 (AT) gmail (DOT) com) writes: This is a partitioned table. It is partitioned on Date_ with each hour as its own partition. Date_ is a smalldatetime. The table has two indexes. It is clustered on date_, SkuID And it has a columnstore index on all columns. The execution plan looks like what I would expect from the schema and indexes, however the performance is terrible. The execution plan says most of the cost is in the table insert. The table insert only takes 26 seconds (Plan 3) which is what makes me suspect the plan may not reflect the correct cost. The percentages you see are estimates; not actual values. I usually pay fairly little attention to them. If the performance is below your expectations, there may be other issues in play, for instance the hardware. What sort of machine are you running this on? -- Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx |
The machine is a virtual machine. We are using a premium hosted service. It has 4 logical CPU's, 8 GB Ram, and a ton of IO.
The part that I am really getting hung up on is the fact that Plan 1 takes significantly longer than [Plan 2 + Plan 3]. The plans are all fairly simple and to me, it seems like the overall "time" it takes to execute should be similar.
#6
| |||
| |||
|
|
I understand that the cost calculation is relative to a test machine sitting in a lab somewhere, and the cost includes IO, CPU, and RAM, though i assumed it would be fairly accurate. |
misestimate in this plan appears to be with the lowest CS scan operator.
The Insert operator has fairly good relation between estimates and actual
values. But there are not actuals for all estimates, and as I said, I
generally don't pay too much attention to the percentages. I'm more
interested in the thickness of the arrows.
Quote:
|
The machine is a virtual machine. We are using a premium hosted service. It has 4 logical CPU's, 8 GB Ram, and a ton of IO. |
Unforuntely, the IO performance of such setup is not always fantastic.
Quote:
|
The part that I am really getting hung up on is the fact that Plan 1 takes significantly longer than [Plan 2 + Plan 3]. The plans are all fairly simple and to me, it seems like the overall "time" it takes to execute should be similar. |
the second query, which could affect both the plan - and the amount of
memory. If you run out of those 8GB, things will happen.
I have not seen the plan for query 2, so I can't speak about that one. As
for what happens on the server, the default trace may include warnings about
hash spill. Performance counters can be useful too.
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
#7
| |||
| |||
|
|
SQL Server 2012 Developer 64bit I have been experimenting with columnstore indexes on large Entity Name Value (EAV: Entity-Attribute-Value) tables. The execution plans don’t seem to be correct for even basic queries. Can someone please review the attached execution plans for a quick sanitycheck? Is it wrong to assume that Plan 1 should take a similar time to complete as Plan 2 + Plan 3? --#a_product and #s2 are filter tables with only one column. Plan 1 = 2 minutes 18 seconds select p1.skuid,value50 ,p1.attributeid,DataBatchId into #test1 from raw.ptNameValue_1 p1 with(nolock) inner join #a_product a on a.attributeid =p1.attributeid inner join #s2 s on s.skuid =p1.skuid --nonclustered index on databatchid,attributeid,value1000 Plan 2 = 11 seconds select count(*) from raw.ptNameValue_1 p1 with(nolock) inner join #a_product a on a.attributeid =p1.attributeid inner join #s2 s on s.skuid =p1.skuid Plan 3 = 26 seconds select * into #test2 from #test1 Plan 1 is below (just save the xml with a .sqlplan extension) ?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.2100.60" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" BatchSequence Batch Statements StmtSimple StatementCompId="2" StatementEstRows="8009990" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="4278.61" StatementText="select p1.skuid,value1000 ,p1.attributeid,DataBatchIdinto #test15
 --select count(*)--11+26
 from raw.ptNameValue_1 p1 with(nolock) 
 inner join #a_product a on a.attributeid =p1.attributeid
 inner join #s2 s on s.skuid =p1.skuid
 --nonclustered index on databatchid,attributeid,value1000" StatementType="SELECT INTO" QueryHash="0x1A8A11473AA325FC" QueryPlanHash="0xE05F85ACC988F051" 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="4" MemoryGrant="820976" CachedPlanSize="128" CompileTime="39" CompileCPU="39" CompileMemory="1336" ThreadStat Branches="3" UsedThreads="12" ThreadReservation NodeId="0" ReservedThreads="12" / /ThreadStat MemoryGrantInfo SerialRequiredMemory="2048" SerialDesiredMemory="813840" RequiredMemory="9168" DesiredMemory="820976" RequestedMemory="820976" GrantWaitTime="0" GrantedMemory="820976" MaxUsedMemory="15512" / OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="157286" EstimatedPagesCached="39321" EstimatedAvailableDegreeOfParallelism="2" / RelOp AvgRowSize="9" EstimateCPU="8.00999" EstimateIO="3455.27" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="8009990" LogicalOp="Insert" NodeId="0" Parallel="false" PhysicalOp="Table Insert" EstimatedTotalSubtreeCost="4278.61" OutputList / RunTimeInformation RunTimeCountersPerThread Thread="0" ActualRows="8296211" ActualEndOfScans="1" ActualExecutions="1" / /RunTimeInformation Update DMLRequestSort="false" Object Table="[#test15]" / SetPredicate ScalarOperator ScalarString="[#test15].[skuid] = [matching].[raw].[ptNameValue_1].[SkuID] as [p1].[SkuID],[#test15].[value1000] = [matching].[raw].[ptNameValue_1].[Value1000] as [p1].[Value1000],[#test15].[attributeid] = [matching].[raw].[ptNameValue_1].[AttributeID] as [p1].[AttributeID],[#test15].[DataBatchId] = [matching].[raw].[ptNameValue_1].[DataBatchID] as [p1].[DataBatchID]" ScalarExpressionList ScalarOperator MultipleAssign Assign ColumnReference Table="[#test15]" Column="skuid" / ScalarOperator Identifier ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="SkuID" / /Identifier /ScalarOperator /Assign Assign ColumnReference Table="[#test15]" Column="value1000" / ScalarOperator Identifier ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="Value1000" / /Identifier /ScalarOperator /Assign Assign ColumnReference Table="[#test15]" Column="attributeid" / ScalarOperator Identifier ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="AttributeID" / /Identifier /ScalarOperator /Assign Assign ColumnReference Table="[#test15]" Column="DataBatchId" / ScalarOperator Identifier ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="DataBatchID" / /Identifier /ScalarOperator /Assign /MultipleAssign /ScalarOperator /ScalarExpressionList /ScalarOperator /SetPredicate RelOp AvgRowSize="523" EstimateCPU="154.823" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="8009990" LogicalOp="Gather Streams" NodeId="1" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="815.327" OutputList ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="SkuID" / ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="AttributeID" / ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="DataBatchID" / ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="Value1000" / /OutputList RunTimeInformation RunTimeCountersPerThread Thread="0" ActualRows="8296211" ActualEndOfScans="1" ActualExecutions="1" / /RunTimeInformation Parallelism RelOp AvgRowSize="523" EstimateCPU="2.44674" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch" EstimateRows="8009990" LogicalOp="Inner Join" NodeId="2" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="660.504" OutputList ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="SkuID" / ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="AttributeID" / ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="DataBatchID" / ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="Value1000" / /OutputList MemoryFractions Input="1" Output="1" / RunTimeInformation RunTimeCountersPerThread Thread="4" ActualRows="1726136" Batches="44666" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" / RunTimeCountersPerThread Thread="3" ActualRows="2106536" Batches="52139" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" / RunTimeCountersPerThread Thread="1" ActualRows="2180973" Batches="52919" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" / RunTimeCountersPerThread Thread="2" ActualRows="2282566" Batches="56262" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" / RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" / /RunTimeInformation Hash DefinedValues / HashKeysBuild ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#s2]" Alias="[s]" Column="skuid" / /HashKeysBuild HashKeysProbe ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="SkuID" / /HashKeysProbe ProbeResidual ScalarOperator ScalarString="[tempdb].[dbo].[#s2].[skuid] as [s].[skuid]=[matching].[raw].[ptNameValue_1].[SkuID] as [p1].[SkuID]" Compare CompareOp="EQ" ScalarOperator Identifier ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#s2]" Alias="[s]" Column="skuid" / /Identifier /ScalarOperator ScalarOperator Identifier ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="SkuID" / /Identifier /ScalarOperator /Compare /ScalarOperator /ProbeResidual RelOp AvgRowSize="11" EstimateCPU="0.627563"EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="500000" LogicalOp="Repartition Streams"NodeId="3" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.962092" OutputList ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#s2]" Alias="[s]" Column="skuid" / /OutputList RunTimeInformation RunTimeCountersPerThread Thread="4" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" / RunTimeCountersPerThread Thread="3" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" / RunTimeCountersPerThread Thread="1" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" / RunTimeCountersPerThread Thread="2" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" / RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" / /RunTimeInformation Parallelism PartitioningType="Hash" PartitionColumns ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#s2]" Alias="[s]" Column="skuid" / /PartitionColumns RelOp AvgRowSize="11" EstimateCPU="0.244638" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch" EstimateRows="500000" LogicalOp="Batch Hash Table Build" NodeId="4" Parallel="true" PhysicalOp="Batch Hash Table Build" EstimatedTotalSubtreeCost="0.33453" OutputList ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#s2]" Alias="[s]" Column="skuid" / /OutputList MemoryFractions Input="0.99727" Output="0.99727" / RunTimeInformation RunTimeCountersPerThread Thread="2" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" / RunTimeCountersPerThread Thread="3" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" / RunTimeCountersPerThread Thread="4" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" / RunTimeCountersPerThread Thread="1" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" / /RunTimeInformation BatchHashTableBuild BitmapCreator="true" DefinedValues DefinedValue ColumnReference Column="Opt_Bitmap1013" / /DefinedValue /DefinedValues RelOp AvgRowSize="11" EstimateCPU="0..0275079" EstimateIO="0.0623843" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch" EstimateRows="500000" LogicalOp="Index Scan" NodeId="5" Parallel="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.0898921" TableCardinality="500000" OutputList ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#s2]" Alias="[s]" Column="skuid" / /OutputList RunTimeInformation RunTimeCountersPerThread Thread="2" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" / RunTimeCountersPerThread Thread="3" ActualRows="500000" Batches="556" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" / RunTimeCountersPerThread Thread="4" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" / RunTimeCountersPerThread Thread="1" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" / RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" / /RunTimeInformation IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="ColumnStore" DefinedValues DefinedValue ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#s2]" Alias="[s]" Column="skuid" / /DefinedValue /DefinedValues Object Database="[tempdb]" Schema="[dbo]" Table="[#s2]" Index="[ixcs]" Alias="[s]" IndexKind="NonClustered" / /IndexScan /RelOp /BatchHashTableBuild /RelOp /Parallelism /RelOp RelOp AvgRowSize="523" EstimateCPU="0" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="8009990" LogicalOp="Repartition Streams" NodeId="6" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="657.095" OutputList ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="SkuID" / ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="AttributeID" / ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="DataBatchID" / ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="Value1000" / /OutputList RunTimeInformation RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" / /RunTimeInformation Parallelism PartitioningType="Hash" PartitionColumns ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="SkuID" / /PartitionColumns RelOp AvgRowSize="523" EstimateCPU="2.44674" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch" EstimateRows="8009990" LogicalOp="Inner Join" NodeId="7" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="657.095" OutputList ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="SkuID" / ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="AttributeID" / ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="DataBatchID" / ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="Value1000" / /OutputList MemoryFractions Input="0.00272976" Output="0.00272976" / RunTimeInformation RunTimeCountersPerThread Thread="4" ActualRows="1908608" Batches="44666" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" / RunTimeCountersPerThread Thread="3" ActualRows="2317333" Batches="52139" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" / RunTimeCountersPerThread Thread="1" ActualRows="2404125" Batches="52919" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" / RunTimeCountersPerThread Thread="2" ActualRows="2518528" Batches="56262" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" / RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" / /RunTimeInformation Hash DefinedValues / HashKeysBuild ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#a_product]" Alias="[a]" Column="attributeid" / /HashKeysBuild HashKeysProbe ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="AttributeID" / /HashKeysProbe ProbeResidual ScalarOperator ScalarString="[matching].[raw].[ptNameValue_1].[AttributeID] as [p1].[AttributeID]=[tempdb].[dbo].[#a_product].[attributeid] as [a].[attributeid]" Compare CompareOp="EQ" ScalarOperator Identifier ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="AttributeID" / /Identifier /ScalarOperator ScalarOperator Identifier ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#a_product]" Alias="[a]" Column="attributeid" / /Identifier /ScalarOperator /Compare /ScalarOperator /ProbeResidual RelOp AvgRowSize="11" EstimateCPU="0..0285707" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="59" LogicalOp="Repartition Streams" NodeId="8" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.032623" OutputList ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#a_product]" Alias="[a]" Column="attributeid" / /OutputList RunTimeInformation RunTimeCountersPerThread Thread="4" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" / RunTimeCountersPerThread Thread="3" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" / RunTimeCountersPerThread Thread="1" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" / RunTimeCountersPerThread Thread="2" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" / RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" / /RunTimeInformation Parallelism PartitioningType="Hash" PartitionColumns ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#a_product]" Alias="[a]" Column="attributeid" / /PartitionColumns RelOp AvgRowSize="11" EstimateCPU="0.000916262" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch" EstimateRows="59" LogicalOp="Batch Hash Table Build" NodeId="9" Parallel="true" PhysicalOp="Batch Hash Table Build" EstimatedTotalSubtreeCost="0.00405236" OutputList ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#a_product]" Alias="[a]" Column="attributeid" / /OutputList MemoryFractions Input="0.00272976" Output="0.00272976" / RunTimeInformation RunTimeCountersPerThread Thread="1" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" / RunTimeCountersPerThread Thread="2" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" / RunTimeCountersPerThread Thread="4" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" / RunTimeCountersPerThread Thread="3" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" / /RunTimeInformation BatchHashTableBuild BitmapCreator="true" DefinedValues DefinedValue ColumnReference Column="Opt_Bitmap1014" / /DefinedValue /DefinedValues RelOp AvgRowSize="11" EstimateCPU="1.1095E-05" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch" EstimateRows="59" LogicalOp="Index Scan" NodeId="10" Parallel="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.0031361" TableCardinality="59" OutputList ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#a_product]" Alias="[a]" Column="attributeid" / /OutputList RunTimeInformation RunTimeCountersPerThread Thread="1" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" / RunTimeCountersPerThread Thread="2" ActualRows="59" Batches="1" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" / RunTimeCountersPerThread Thread="4" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" / RunTimeCountersPerThread Thread="3" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" / RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" / /RunTimeInformation IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="ColumnStore" DefinedValues DefinedValue ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#a_product]" Alias="[a]" Column="attributeid" / /DefinedValue /DefinedValues Object Database="[tempdb]"Schema="[dbo]" Table="[#a_product]" Index="[ixcs_a]" Alias="[a]" IndexKind="NonClustered" / /IndexScan /RelOp /BatchHashTableBuild /RelOp /Parallelism /RelOp RelOp AvgRowSize="523" EstimateCPU="0" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="8009990" LogicalOp="Repartition Streams" NodeId="11" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="654.615" OutputList ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="SkuID" / ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="AttributeID" / ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="DataBatchID" / ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="Value1000" / /OutputList RunTimeInformation RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" / /RunTimeInformation Parallelism PartitioningType="Hash" PartitionColumns ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="AttributeID" / /PartitionColumns RelOp AvgRowSize="523" EstimateCPU="79.4428" EstimateIO="532" EstimateRebinds="0" EstimateRewinds="0"EstimatedExecutionMode="Batch" EstimateRows="1443430000" LogicalOp="Index Scan" NodeId="13" Parallel="true" Partitioned="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="611.443" TableCardinality="1443430000" OutputList ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="SkuID" / ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="AttributeID" / ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="DataBatchID" / ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="Value1000" / /OutputList RunTimeInformation RunTimeCountersPerThread Thread="4" ActualRows="1908608" Batches="44666" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" / RunTimeCountersPerThread Thread="3" ActualRows="2317333" Batches="52139" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" / RunTimeCountersPerThread Thread="2" ActualRows="2518528" Batches="56262" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" / RunTimeCountersPerThread Thread="1" ActualRows="2404125" Batches="52919" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" / RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" / /RunTimeInformation RunTimePartitionSummary PartitionsAccessed PartitionCount="0" / /RunTimePartitionSummary IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="ColumnStore" DefinedValues DefinedValue ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="SkuID" / /DefinedValue DefinedValue ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="AttributeID" / /DefinedValue DefinedValue ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="DataBatchID" / /DefinedValue DefinedValue ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="Value1000" / /DefinedValue /DefinedValues Object Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Index="[ixcs]" Alias="[p1]" IndexKind="NonClustered" / Predicate ScalarOperator ScalarString="PROBE([Opt_Bitmap1013],[matching].[raw].[ptNameValue_1].[SkuID] as [p1].[SkuID]) AND PROBE([Opt_Bitmap1014],[matching].[raw].[ptNameValue_1].[AttributeID] as [p1].[AttributeID])" Logical Operation="AND" ScalarOperator Intrinsic FunctionName="PROBE" ScalarOperator Identifier ColumnReference Column="Opt_Bitmap1013" / /Identifier /ScalarOperator ScalarOperator Identifier ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="SkuID" / /Identifier /ScalarOperator /Intrinsic /ScalarOperator ScalarOperator Intrinsic FunctionName="PROBE" ScalarOperator Identifier ColumnReference Column="Opt_Bitmap1014" / /Identifier /ScalarOperator ScalarOperator Identifier ColumnReference Database="[matching]" Schema="[raw]" Table="[ptNameValue_1]" Alias="[p1]" Column="AttributeID" / /Identifier /ScalarOperator /Intrinsic /ScalarOperator /Logical /ScalarOperator /Predicate /IndexScan /RelOp /Parallelism /RelOp /Hash /RelOp /Parallelism /RelOp /Hash /RelOp /Parallelism /RelOp /Update /RelOp /QueryPlan /StmtSimple /Statements /Batch /BatchSequence /ShowPlanXML |
--raw.ptNameValue_1 is clustered on (date_, SkuID)
create index ix_cover_product on raw.ptNameValue_1 (DataBatchId,AttributeId) include (value1000)
where attributeid in (2,4,8,6319,6479)
on [ps_nvp_1](date_)
Do you have any suggestions on how I might reduce the reads to something that approaches the number of reads from staged data?
Logical Reads:
DISTINCT(CHECKSUM(SkuID,Value1000)) = 20,961,991
DISTINCT(CHECKSUM(SkuID)) = 1,031,450
DISTINCT(CHECKSUM(SkuID,Value1000)) staging table(relevant rows) = 154,987
select count(distinct checksum(p1.skuid) )
from raw.ptNameValue_1 p1 with(nolock)
inner join #a_product a on a.attributeid =p1.attributeid
inner join #s2 s on s.skuid =p1.skuid
Table '#s2______________________________________________ __________________________________________________ _________________000000000CA6'. Scan count 4, logical reads 505, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#a_product_______________________________________ __________________________________________________ _________________000000000C9C'. Scan count 4, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ptNameValue_1'. Scan count 4, logical reads 1031450, physical reads 1178, read-ahead reads 1386868, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 13549 ms, elapsed time = 11621 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
select count(distinct checksum(p1.skuid,value1000) )
from raw.ptNameValue_1 p1 with(nolock)
inner join #a_product a on a.attributeid =p1.attributeid
inner join #s2 s on s.skuid =p1.skuid
Table '#s2______________________________________________ __________________________________________________ _________________000000000CA6'. Scan count 4, logical reads 505, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#a_product_______________________________________ __________________________________________________ _________________000000000C9C'. Scan count 4, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ptNameValue_1'. Scan count 4, logical reads 20961991, physical reads 7368, read-ahead reads 13741103, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 79733 ms, elapsed time = 265290 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
select count(distinct checksum(skuid,value1000))
from #stage1
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#stage1__________________________________________ __________________________________________________ _________________000000000CB5'. Scan count 5, logical reads 154987, physical reads 0, read-ahead reads 154987, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 28516 ms, elapsed time = 14042 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
#8
| |||
| |||
|
|
The Logic Reads is the big difference between the two (20x more)! I tried creating a covering index to reduce the IO, but it is not being used. Logical Reads: DISTINCT(CHECKSUM(SkuID,Value1000)) = 20,961,991 DISTINCT(CHECKSUM(SkuID)) = 1,031,450 |
index. I don't know nothing about this data, but maybe overall there are
fewer distinct values of SkuId than over Value1000.
If you try this without the columnstore index, I guess you see quite
different numbers, not the least for the second query, don't you?
I'm sorry that I don't really have any ideas to offer. I have not dug deep
into the ColumnStore internals, but there are some catalog views which you
can dig into.
Finally, it would be appreciated if you could trim your quotes to only
cover the parts you comment on, or just the intro if you can't make a pick.
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.