CPU Pegged by App SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

CPU Pegged by App SQL

Author

Topic

dbthj
Posting Yak Master

143 Posts

Posted - 2011-08-09 : 11:58:19

This application was written by a very clever fellow who likes to put all logic in a single SQL stmt by joining many large tables. The server is a VMware 64 bit Windows 2008 Standard with 4 cpus and 32GB memory (SQL max 14GB (App stuff runs on same server)).

At certain times the cpu is pegged at 100% with 90% or more used by SQL Server (2008 Standard). There really aren't many processes running at these times. Blocking is not an issue. The biggest resource user seems to be the SQL call below. This call is in a stored procedure which may account for 2 or 3 processes simultaneously. I am not good with SQL Server execution plans and making sense of the estimated costs. I will try pasting the plan XML below. If that fails I'll try alternate method of posting.

My question is whether it makes sense that 2 or 3 of these queries running simultaneously should peg 4 cpus.

The Query:
INSERT @@temp( tempID, ToSortItem, Mfactor, Dfactor )
SELECT
DISTINCT OrderLines.Id, ItemGroups.Description ,Uomconversions.factor, Uomconversions.dividedfactor
FROM OrderLines
inner join Uomconversions on Orderlines.itemId=uomconversions.itemid
and orderlines.uomid=uomconversions.uomid
inner join OrderHeaders ON OrderHeaders.Id=OrderLines.OrderHeaderId
inner join Programs ON OrderHeaders.ProgramId = Programs.ID
inner join CrossReferences Customer ON (Customer.ItemId = OrderLines.ItemId)
AND (Customer.CompanyId = Programs.CustomerId)
AND (Customer.TypeId =1)
inner join CrossReferences Vendor ON (Vendor.ItemId = OrderLines.ItemId)
AND (Vendor.CompanyId = Programs.VendorId)
AND (Vendor.TypeId =1)
inner join Items on Items.Id = OrderLines.ItemId
inner join CrossReferences on CrossReferences.ItemId = OrderLines.ItemId
inner join ItemGroups on ItemGroups.Id = Items.ItemGroupId
WHERE 1=1
AND (CrossReferences.CompanyId=7899)
AND (CrossReferences.TypeId=1)
AND (OrderLines.OrderHeaderId=1214540)
AND (OrderHeaders.Id=1214540)
AND (exists (SELECT 1 FROM OrderLineProcessSteps
INNER JOIN ProcessSteps ON OrderLineProcessSteps.ProcessStepId=ProcessSteps.Id
WHERE (ProcessSteps.SysStepId=1)
AND (OrderLineProcessSteps.OrderLineId = OrderLines.Id)
))
ORDER BY ItemGroups.Description

TABLE SIZES:
tablename row_count
========= ==========
Programs 32,441
CrossReferences 20,923,706
UomConversions 27,233,369
ItemGroups 5
Items 10,171,470
OrderHeaders 1,214,606
OrderLines 11,644,918

Explain Plan XML:

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.0.2531.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="TimeOut" StatementSubTreeCost="2.46048" StatementText="#xD;#xA;SELECT #xD;#xA; DISTINCT OrderLines.Id, ItemGroups.Description ,Uomconversions.factor, Uomconversions.dividedfactor#xD;#xA;FROM OrderLines#xD;#xA;inner join Uomconversions on Orderlines.itemId=uomconversions.itemid #xD;#xA; and orderlines.uomid=uomconversions.uomid #xD;#xA;inner join OrderHeaders ON OrderHeaders.Id=OrderLines.OrderHeaderId#xD;#xA;inner join Programs ON OrderHeaders.ProgramId = Programs.ID#xD;#xA;inner join CrossReferences Customer ON (Customer.ItemId = OrderLines.ItemId) #xD;#xA; AND (Customer.CompanyId = Programs.CustomerId) #xD;#xA; AND (Customer.TypeId =1)#xD;#xA;inner join CrossReferences Vendor ON (Vendor.ItemId = OrderLines.ItemId) #xD;#xA; AND (Vendor.CompanyId = Programs.VendorId) #xD;#xA; AND (Vendor.TypeId =1) #xD;#xA;inner join Items on Items.Id = OrderLines.ItemId #xD;#xA;inner join CrossReferences on CrossReferences.ItemId = OrderLines.ItemId #xD;#xA;inner join ItemGroups on ItemGroups.Id = Items.ItemGroupId #xD;#xA;WHERE 1=1 #xD;#xA;AND (CrossReferences.CompanyId=7899) #xD;#xA;AND (CrossReferences.TypeId=1) #xD;#xA;AND (OrderLines.OrderHeaderId=1214540) #xD;#xA;AND (OrderHeaders.Id=1214540) #xD;#xA;AND (exists (SELECT 1 FROM OrderLineProcessSteps #xD;#xA; INNER JOIN ProcessSteps ON OrderLineProcessSteps.ProcessStepId=ProcessSteps.Id #xD;#xA; WHERE (ProcessSteps.SysStepId=1) #xD;#xA; AND (OrderLineProcessSteps.OrderLineId = OrderLines.Id)#xD;#xA; )) #xD;#xA; ORDER BY ItemGroups.Description#xD;#xA;" StatementType="SELECT" QueryHash="0x8686B484F5528F20" QueryPlanHash="0xB3D56A39A3967042">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan CachedPlanSize="120" CompileTime="159" CompileCPU="153" CompileMemory="4744">
<RelOp AvgRowSize="41" EstimateCPU="0.000100041" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Distinct Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="2.46048">
<OutputList>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="Id" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[UomConversions]" Column="Factor" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[UomConversions]" Column="DividedFactor" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[ItemGroups]" Column="Description" />
</OutputList>
<MemoryFractions Input="0.25" Output="1" />
<Sort Distinct="true">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[ItemGroups]" Column="Description" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="Id" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[UomConversions]" Column="Factor" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[UomConversions]" Column="DividedFactor" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="41" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="2.44912">
<OutputList>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="Id" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[UomConversions]" Column="Factor" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[UomConversions]" Column="DividedFactor" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[ItemGroups]" Column="Description" />
</OutputList>
<NestedLoops Optimized="true">
<OuterReferences>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[UomConversions]" Column="Id" />
</OuterReferences>
<RelOp AvgRowSize="29" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="3" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="2.44583">
<OutputList>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="Id" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[UomConversions]" Column="Id" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[ItemGroups]" Column="Description" />
</OutputList>
<NestedLoops Optimized="true">
<OuterReferences>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="UomId" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Column="ItemId" />
</OuterReferences>
<RelOp AvgRowSize="33" EstimateCPU="3.94961E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="5" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="2.44254">
<OutputList>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="Id" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="UomId" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Column="ItemId" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[ItemGroups]" Column="Description" />
</OutputList>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[Programs]" Column="CustomerId" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Column="ItemId" />
</OuterReferences>
<RelOp AvgRowSize="37" EstimateCPU="3.94963E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="9.44883" LogicalOp="Inner Join" NodeId="6" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="2.41461">
<OutputList>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="Id" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="UomId" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[Programs]" Column="CustomerId" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Column="ItemId" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[ItemGroups]" Column="Description" />
</OutputList>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[Items]" Column="ItemGroupId" />
</OuterReferences>
<RelOp AvgRowSize="27" EstimateCPU="3.94963E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="9.44888" LogicalOp="Inner Join" NodeId="7" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="2.40995">
<OutputList>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="Id" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="UomId" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[Programs]" Column="CustomerId" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[Items]" Column="ItemGroupId" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Column="ItemId" />
</OutputList>
<NestedLoops Optimized="true">
<OuterReferences>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Column="ItemId" />
</OuterReferences>
<RelOp AvgRowSize="23" EstimateCPU="3.94963E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="9.44888" LogicalOp="Inner Join" NodeId="9" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="2.38201">
<OutputList>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="Id" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="UomId" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[Programs]" Column="CustomerId" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Column="ItemId" />
</OutputList>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Alias="[Vendor]" Column="ItemId" />
</OuterReferences>
<RelOp AvgRowSize="23" EstimateCPU="3.9707E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="9.44888" LogicalOp="Left Semi Join" NodeId="10" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="2.35408">
<OutputList>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="Id" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="UomId" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[Programs]" Column="CustomerId" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Alias="[Vendor]" Column="ItemId" />
</OutputList>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="Id" />
</OuterReferences>
<RelOp AvgRowSize="23" EstimateCPU="1.28806" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="9.49928" LogicalOp="Inner Join" NodeId="11" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="2.32137">
<OutputList>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="Id" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="UomId" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[Programs]" Column="CustomerId" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Alias="[Vendor]" Column="ItemId" />
</OutputList>
<NestedLoops Optimized="false">
<Predicate>
<ScalarOperator ScalarString="[PROD01].[dbo].[OrderHeaders].[ProgramId]=[PROD01].[dbo].[Programs].[ID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderHeaders]" Column="ProgramId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[Programs]" Column="ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
<RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="12" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1214380">
<OutputList>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderHeaders]" Column="ProgramId" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderHeaders]" Column="ProgramId" />
</DefinedValue>
</DefinedValues>
<Object Database="[PROD01]" Schema="[dbo]" Table="[OrderHeaders]" Index="[PK_OrderHeaders]" IndexKind="Clustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderHeaders]" Column="Id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="(1214540)">
<Const ConstValue="(1214540)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="27" EstimateCPU="0.504675" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="308147" LogicalOp="Inner Join" NodeId="13" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="0.93759">
<OutputList>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="Id" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="UomId" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[Programs]" Column="ID" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[Programs]" Column="CustomerId" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Alias="[Vendor]" Column="ItemId" />
</OutputList>
<MemoryFractions Input="0" Output="0" />
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Alias="[Vendor]" Column="CompanyId" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[Programs]" Column="VendorId" />
</HashKeysProbe>
<RelOp AvgRowSize="23" EstimateCPU="7.94199E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="19" LogicalOp="Inner Join" NodeId="14" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0495">
<OutputList>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="Id" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="UomId" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Alias="[Vendor]" Column="ItemId" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Alias="[Vendor]" Column="CompanyId" />
</OutputList>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="ItemId" />
</OuterReferences>
<RelOp AvgRowSize="19" EstimateCPU="3.9707E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="9.49928" LogicalOp="Inner Join" NodeId="15" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0213491">
<OutputList>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="Id" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="ItemId" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="UomId" />
</OutputList>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="Id" />
</OuterReferences>
<RelOp AvgRowSize="15" EstimateCPU="0.000167449" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="9.49928" LogicalOp="Index Seek" NodeId="16" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00329245" TableCardinality="11642900">
<OutputList>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="Id" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="ItemId" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="Id" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="ItemId" />
</DefinedValue>
</DefinedValues>
<Object Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Index="[idx_OrderHeaderIdItem]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="OrderHeaderId" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="(1214540)">
<Const ConstValue="(1214540)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="8.49928" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="18" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0180169" TableCardinality="11642900">
<OutputList>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="UomId" />
</OutputList>
<IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="UomId" />
</DefinedValue>
</DefinedValues>
<Object Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Index="[PK_OrderLines]" TableReferenceId="-1" IndexKind="Clustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="Id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[PROD01].[dbo].[OrderLines].[Id]">
<Identifier>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="Id" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="15" EstimateCPU="0.0001592" EstimateIO="0.003125" EstimateRebinds="8.49928" EstimateRewinds="0" EstimateRows="2.00015" LogicalOp="Index Seek" NodeId="22" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0280715" TableCardinality="20923500">
<OutputList>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Alias="[Vendor]" Column="ItemId" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Alias="[Vendor]" Column="CompanyId" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Alias="[Vendor]" Column="ItemId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Alias="[Vendor]" Column="CompanyId" />
</DefinedValue>
</DefinedValues>
<Object Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Index="[ItemId_TypeId_CompanyId_PartNumber]" Alias="[Vendor]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Alias="[Vendor]" Column="ItemId" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Alias="[Vendor]" Column="TypeId" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[PROD01].[dbo].[OrderLines].[ItemId]">
<Identifier>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="ItemId" />
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="(1)">
<Const ConstValue="(1)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="19" EstimateCPU="0.0358421" EstimateIO="0.347569" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="32441" LogicalOp="Index Scan" NodeId="23" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.383412" TableCardinality="32441">
<OutputList>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[Programs]" Column="ID" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[Programs]" Column="CustomerId" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[Programs]" Column="VendorId" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[Programs]" Column="ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[Programs]" Column="CustomerId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[Programs]" Column="VendorId" />
</DefinedValue>
</DefinedValues>
<Object Database="[PROD01]" Schema="[dbo]" Table="[Programs]" Index="[Program_companyid]" IndexKind="NonClustered" />
</IndexScan>
</RelOp>
</Hash>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="9" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="8.49928" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="26" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0326655">
<OutputList />
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLineProcessSteps]" Column="ProcessStepId" />
</OuterReferences>
<RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="8.49928" EstimateRewinds="0" EstimateRows="1" LogicalOp="Index Seek" NodeId="27" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0279944" TableCardinality="556580">
<OutputList>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLineProcessSteps]" Column="ProcessStepId" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLineProcessSteps]" Column="ProcessStepId" />
</DefinedValue>
</DefinedValues>
<Object Database="[PROD01]" Schema="[dbo]" Table="[OrderLineProcessSteps]" Index="[m_OrderLineProcessSteps_m_OrderLineId_m_ProcessStepId_m_Id]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLineProcessSteps]" Column="OrderLineId" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[PROD01].[dbo].[OrderLines].[Id]">
<Identifier>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="Id" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="8.49928" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="28" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.00462684" TableCardinality="6">
<OutputList />
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues />
<Object Database="[PROD01]" Schema="[dbo]" Table="[ProcessSteps]" Index="[PK_ProcessSteps]" IndexKind="Clustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[ProcessSteps]" Column="Id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[PROD01].[dbo].[OrderLineProcessSteps].[ProcessStepId]">
<Identifier>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLineProcessSteps]" Column="ProcessStepId" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[PROD01].[dbo].[ProcessSteps].[SysStepId]=(1)">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[ProcessSteps]" Column="SysStepId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="8.44888" EstimateRewinds="0" EstimateRows="1" LogicalOp="Index Seek" NodeId="29" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0278956" TableCardinality="20923500">
<OutputList>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Column="ItemId" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Column="ItemId" />
</DefinedValue>
</DefinedValues>
<Object Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Index="[ItemId_TypeId_CompanyId_PartNumber]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Column="ItemId" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Column="TypeId" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Column="CompanyId" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[PROD01].[dbo].[CrossReferences].[ItemId] as [Vendor].[ItemId]">
<Identifier>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Alias="[Vendor]" Column="ItemId" />
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="(1)">
<Const ConstValue="(1)" />
</ScalarOperator>
<ScalarOperator ScalarString="(7899)">
<Const ConstValue="(7899)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="8.44888" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="30" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0278965" TableCardinality="10171400">
<OutputList>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[Items]" Column="ItemGroupId" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[Items]" Column="ItemGroupId" />
</DefinedValue>
</DefinedValues>
<Object Database="[PROD01]" Schema="[dbo]" Table="[Items]" Index="[PK_Items]" IndexKind="Clustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[Items]" Column="ID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[PROD01].[dbo].[CrossReferences].[ItemId]">
<Identifier>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Column="ItemId" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="21" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0.0702995" EstimateRewinds="8.37858" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="31" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.00461887" TableCardinality="5">
<OutputList>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[ItemGroups]" Column="Description" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[ItemGroups]" Column="Description" />
</DefinedValue>
</DefinedValues>
<Object Database="[PROD01]" Schema="[dbo]" Table="[ItemGroups]" Index="[PK_ItemGroups]" IndexKind="Clustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[ItemGroups]" Column="Id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[PROD01].[dbo].[Items].[ItemGroupId]">
<Identifier>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[Items]" Column="ItemGroupId" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="9" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="8.44883" EstimateRewinds="0" EstimateRows="1" LogicalOp="Index Seek" NodeId="32" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0278954" TableCardinality="20923500">
<OutputList />
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues />
<Object Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Index="[ItemId_TypeId_CompanyId_PartNumber]" Alias="[Customer]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Alias="[Customer]" Column="ItemId" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Alias="[Customer]" Column="TypeId" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Alias="[Customer]" Column="CompanyId" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[PROD01].[dbo].[CrossReferences].[ItemId]">
<Identifier>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Column="ItemId" />
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="(1)">
<Const ConstValue="(1)" />
</ScalarOperator>
<ScalarOperator ScalarString="[PROD01].[dbo].[Programs].[CustomerId]">
<Identifier>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[Programs]" Column="CustomerId" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Index Seek" NodeId="33" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="27233100">
<OutputList>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[UomConversions]" Column="Id" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[UomConversions]" Column="Id" />
</DefinedValue>
</DefinedValues>
<Object Database="[PROD01]" Schema="[dbo]" Table="[UomConversions]" Index="[item_uom_id]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[UomConversions]" Column="ItemId" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[UomConversions]" Column="UomId" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[PROD01].[dbo].[CrossReferences].[ItemId]">
<Identifier>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[CrossReferences]" Column="ItemId" />
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="[PROD01].[dbo].[OrderLines].[UomId]">
<Identifier>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[OrderLines]" Column="UomId" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="23" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="35" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="27233100">
<OutputList>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[UomConversions]" Column="Factor" />
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[UomConversions]" Column="DividedFactor" />
</OutputList>
<IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[UomConversions]" Column="Factor" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[UomConversions]" Column="DividedFactor" />
</DefinedValue>
</DefinedValues>
<Object Database="[PROD01]" Schema="[dbo]" Table="[UomConversions]" Index="[PK_UomConversion]" TableReferenceId="-1" IndexKind="Clustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[UomConversions]" Column="Id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[PROD01].[dbo].[UomConversions].[Id]">
<Identifier>
<ColumnReference Database="[PROD01]" Schema="[dbo]" Table="[UomConversions]" Column="Id" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</Sort>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-09 : 12:24:21

What are the wait types for these queries? What is "max degree of parallelism" set to at the server level? When I get a minute, I'll see if I can load up the plan into SSMS. In the meantime, can you post what indexes you have on the relevant tables?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dbthj
Posting Yak Master

143 Posts

Posted - 2011-08-09 : 17:10:38

Max Degree of Parallelism is 0.

Wait types I can't say yet. Today, it isn't running much. If I get something, I'll let you know

Indexes are
(I tried to format this for readability but all the spaces seem to get compressed in the Preview. Sorry)
I suggest pasting this text into a spreadsheet and click Format/Autoformat.
TABLE_NAME INDEX_NAME COLUMN_NAME IX_Col_Order
CrossReferences ItemId_TypeId_CompanyId ItemId 1
CrossReferences ItemId_TypeId_CompanyId TypeId 2
CrossReferences ItemId_TypeId_CompanyId CompanyId 3

CrossReferences ItemId_TypeId_CompanyId_PartNumber ItemId 1
CrossReferences ItemId_TypeId_CompanyId_PartNumber TypeId 2
CrossReferences ItemId_TypeId_CompanyId_PartNumber CompanyId 3
CrossReferences ItemId_TypeId_CompanyId_PartNumber PartNumber 4

CrossReferences ix_1 ItemId 1
CrossReferences ix_1 PartNumber 2

CrossReferences PartNo_type_id PartNumber 1
CrossReferences PartNo_type_id CompanyId 2
CrossReferences PartNo_type_id ItemId 3
CrossReferences PartNo_type_id TypeId 4

CrossReferences PK_CrossReference ID 1

ItemGroups PK_ItemGroups Id 1

Items Id_ProgramId_ManufacturerId_Description_RefNo1_RefNo2 ID 1
Items Id_ProgramId_ManufacturerId_Description_RefNo1_RefNo2 ProgramId 2
Items Id_ProgramId_ManufacturerId_Description_RefNo1_RefNo2 ManufacturerId 3
Items Id_ProgramId_ManufacturerId_Description_RefNo1_RefNo2 Description 4
Items Id_ProgramId_ManufacturerId_Description_RefNo1_RefNo2 RefNo1 5
Items Id_ProgramId_ManufacturerId_Description_RefNo1_RefNo2 RefNo2 6

Items m_Items_ProgramId_fActive_fDeleted_ID ProgramId 1
Items m_Items_ProgramId_fActive_fDeleted_ID fActive 2
Items m_Items_ProgramId_fActive_fDeleted_ID fDeleted 3
Items m_Items_ProgramId_fActive_fDeleted_ID ID 4

Items PK_Items ID 1

OrderHeaders idx_m_iProdKitItemId OnHold 1
OrderHeaders idx_m_iProdKitItemId StatusId 2
OrderHeaders idx_m_iProdKitItemId ProdKitQty 3
OrderHeaders idx_m_iProdKitItemId m_iProdKitItemId 4

OrderHeaders idx_prog_status ProgramId 1
OrderHeaders idx_prog_status ProcessId 2
OrderHeaders idx_prog_status OnHold 3
OrderHeaders idx_prog_status StatusId 4
OrderHeaders idx_prog_status Id 5

OrderHeaders idx_StatusProcess StatusId 1
OrderHeaders idx_StatusProcess ProcessId 2
OrderHeaders idx_StatusProcess OnHold 3
OrderHeaders idx_StatusProcess Id 4

OrderHeaders PK_OrderHeaders Id 1

OrderLines idx_active ItemId 1
OrderLines idx_active m_iOrderTypeId 2
OrderLines idx_active m_bIsActive 3
OrderLines idx_active Id 4

OrderLines idx_itemid ItemId 1
OrderLines idx_itemid Id 2

OrderLines idx_OrderHeader OrderHeaderId 1
OrderLines idx_OrderHeader Id 2

OrderLines idx_OrderHeaderIdItem OrderHeaderId 1
OrderLines idx_OrderHeaderIdItem ItemId 2
OrderLines idx_OrderHeaderIdItem ProcessId 3
OrderLines idx_OrderHeaderIdItem Id 4

OrderLines PK_OrderLines Id 1

Programs PK_program ID 1

Programs Program_companyid Name 1
Programs Program_companyid CustomerId 2
Programs Program_companyid VendorId 3
Programs Program_companyid ID 4

UomConversions item_uom_id ItemId 1
UomConversions item_uom_id UomId 2
UomConversions item_uom_id Id 3

UomConversions m_UomConversions_itemid_uomid ItemId 1
UomConversions m_UomConversions_itemid_uomid UomId 2

UomConversions PK_UomConversion Id 1

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-09 : 18:11:40

See if these help:

1. Run UPDATE STATISTICS on all the tables references in the query.
2. Try this version:

INSERT @@temp(tempID, ToSortItem, Mfactor, Dfactor)
SELECT DISTINCT OrderLines.Id, ItemGroups.Description, Uomconversions.factor, Uomconversions.dividedfactor
FROM OrderLines 
INNER JOIN Uomconversions ON Orderlines.itemId=uomconversions.itemid AND orderlines.uomid=uomconversions.uomid 
INNER JOIN OrderHeaders ON OrderHeaders.Id=OrderLines.OrderHeaderId AND OrderHeaders.Id=1214540
INNER JOIN Programs ON OrderHeaders.ProgramId=Programs.ID
INNER JOIN CrossReferences Customer ON Customer.ItemId=OrderLines.ItemId AND Customer.CompanyId=Programs.CustomerId AND Customer.TypeId=1
INNER JOIN CrossReferences Vendor ON Vendor.ItemId=OrderLines.ItemId AND Vendor.CompanyId=Programs.VendorId AND Vendor.TypeId=1
INNER JOIN Items ON Items.Id=OrderLines.ItemId 
INNER JOIN CrossReferences ON CrossReferences.ItemId=OrderLines.ItemId AND CrossReferences.TypeId=1 AND CrossReferences.CompanyId=7899
INNER JOIN ItemGroups ON ItemGroups.Id=Items.ItemGroupId 
WHERE EXISTS (SELECT 1 FROM OrderLineProcessSteps 
INNER JOIN ProcessSteps ON OrderLineProcessSteps.ProcessStepId=ProcessSteps.Id 
WHERE ProcessSteps.SysStepId=1 AND OrderLineProcessSteps.OrderLineId=OrderLines.Id) 
ORDER BY ItemGroups.Description

It's essentially the same except I moved some WHERE conditions to the JOIN clause.

Based on what Plan Explorer shows:

The most time is spent in the joins between Programs, CrossReferences, OrderLines and OrderHeaders (see thick black lines and yellow & red highlighted items).

Plan Explorer is a free tool if you want to try it yourself: http://www.sqlsentry.com/plan-explorer/sql-server-query-view.asp

If the plan doesn't change and performance doesn't improve after updating statistics, you might want to add indexes to Programs on just CompanyID and VendorID, and possibly include TypeID on each.
My guess is the Name column being first on the existing index is forcing it to scan instead of seeking, which the new indexes should fix.
That will hopefully cascade into the Hash Match and Nested Loop operators and improve their cost.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources