Discussion:
Sanity Check Query and/or Execution Plan (execution plan looks incorrect)
(too old to reply)
Dave
2012-07-27 21:58:57 UTC
Permalink
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 sanity check? 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,DataBatchId into #test15&#xD;&#xA; --select count(*)--11+26&#xD;&#xA; from raw.ptNameValue_1 p1 with(nolock) &#xD;&#xA; inner join #a_product a on a.attributeid =p1.attributeid&#xD;&#xA; inner join #s2 s on s.skuid =p1.skuid &#xD;&#xA; --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>
Erland Sommarskog
2012-07-27 22:32:10 UTC
Permalink
Post by Dave
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.
Post by Dave
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, ***@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
Dave
2012-07-30 17:25:27 UTC
Permalink
Post by Erland Sommarskog
Post by Dave
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.
Post by Dave
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
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
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.
Erland Sommarskog
2012-07-30 19:28:48 UTC
Permalink
Post by Dave
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, ***@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
Dave
2012-07-31 14:21:00 UTC
Permalink
Post by Erland Sommarskog
Post by Dave
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?
--
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
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.

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.
Erland Sommarskog
2012-07-31 19:54:17 UTC
Permalink
Post by Dave
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.
Yes, but they are estimates, and estimates are often off. The biggest
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.
Post by Dave
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.
A big SAN I guess? Which is shared with a ton of other VMs, supposedly.
Unforuntely, the IO performance of such setup is not always fantastic.
Post by Dave
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.
As I said queries 2 and 3 are quite different. You read fewer columns in
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, ***@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
Dave
2012-08-01 18:36:23 UTC
Permalink
Post by Dave
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 sanity check? 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,DataBatchId into #test15&#xD;&#xA; --select count(*)--11+26&#xD;&#xA; from raw.ptNameValue_1 p1 with(nolock) &#xD;&#xA; inner join #a_product a on a.attributeid =p1.attributeid&#xD;&#xA; inner join #s2 s on s.skuid =p1.skuid &#xD;&#xA; --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>
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.

--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.
Erland Sommarskog
2012-08-01 18:47:29 UTC
Permalink
Post by Dave
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.
DISTINCT(CHECKSUM(SkuID,Value1000)) = 20,961,991
DISTINCT(CHECKSUM(SkuID)) = 1,031,450
So it seems that SkuID compresses better than Value1000 in the columnstore
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, ***@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
Loading...