Tune Queries

• gupta1282

  Default port

  Points: 1465

  More actions

  May 19, 2010 at 9:09 am

  #224792

  Hi All

  How to tune following query

  <?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.0" Build="9.00.4035.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">

  <BatchSequence>

  <Batch>

  <Statements>

  <StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="6.87175" StatementText="insert into TblTotCallAct select count(*)as 'Counts',r.custnumber from tblrecord r,tblimport i with (nolock) where r.Followupdate between dateadd(day,-7,i.Follow_up_date) and i.Follow_up_date and r.custnumber=i.custnumber group by r.custnumber " StatementType="INSERT">

  <StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />

  <QueryPlan CachedPlanSize="32">

  <RelOp AvgRowSize="9" EstimateCPU="1E-06" EstimateIO="0.01" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Insert" NodeId="0" Parallel="false" PhysicalOp="Table Insert" EstimatedTotalSubtreeCost="6.87175">

  <OutputList />

  <Update>

  <Object Database="[GCP_XCS]" Schema="[GCP_XCS]" Table="[TblTotCallAct]" />

  <SetPredicate>

  <ScalarOperator ScalarString="[GCP_XCS].[GCP_XCS].[TblTotCallAct].[Counts] = [Expr1008],[GCP_XCS].[GCP_XCS].[TblTotCallAct].[custnumber] = [GCP_XCS].[dbo].[tblrecord].[CustNumber] as [r].[CustNumber]">

  <ScalarExpressionList />

  </ScalarOperator>

  </SetPredicate>

  <RelOp AvgRowSize="23" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Top" NodeId="1" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="6.86175">

  <OutputList>

  <ColumnReference Database="[GCP_XCS]" Schema="[dbo]" Table="[tblrecord]" Alias="[r]" Column="CustNumber" />

  <ColumnReference Column="Expr1008" />

  </OutputList>

  <Top RowCount="true" IsPercent="false" WithTies="false">

  <TopExpression>

  <ScalarOperator ScalarString="(0)">

  <Const ConstValue="(0)" />

  </ScalarOperator>

  </TopExpression>

  <RelOp AvgRowSize="23" EstimateCPU="1.1E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="2" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="6.86175">

  <OutputList>

  <ColumnReference Database="[GCP_XCS]" Schema="[dbo]" Table="[tblrecord]" Alias="[r]" Column="CustNumber" />

  <ColumnReference Column="Expr1008" />

  </OutputList>

  <ComputeScalar>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Expr1008" />

  <ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Expr1013],0)">

  <Convert DataType="int" Style="0" Implicit="true">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="Expr1013" />

  </Identifier>

  </ScalarOperator>

  </Convert>

  </ScalarOperator>

  </DefinedValue>

  </DefinedValues>

  <RelOp AvgRowSize="23" EstimateCPU="1.1E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Aggregate" NodeId="3" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="6.86175">

  <OutputList>

  <ColumnReference Database="[GCP_XCS]" Schema="[dbo]" Table="[tblrecord]" Alias="[r]" Column="CustNumber" />

  <ColumnReference Column="Expr1013" />

  </OutputList>

  <StreamAggregate>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Expr1013" />

  <ScalarOperator ScalarString="Count(*)">

  <Aggregate AggType="countstar" Distinct="false" />

  </ScalarOperator>

  </DefinedValue>

  </DefinedValues>

  <GroupBy>

  <ColumnReference Database="[GCP_XCS]" Schema="[dbo]" Table="[tblrecord]" Alias="[r]" Column="CustNumber" />

  </GroupBy>

  <RelOp AvgRowSize="19" EstimateCPU="0.000100019" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Sort" NodeId="4" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="6.86175">

  <OutputList>

  <ColumnReference Database="[GCP_XCS]" Schema="[dbo]" Table="[tblrecord]" Alias="[r]" Column="CustNumber" />

  </OutputList>

  <MemoryFractions Input="0.333333" Output="1" />

  <Sort Distinct="false">

  <OrderBy>

  <OrderByColumn Ascending="true">

  <ColumnReference Database="[GCP_XCS]" Schema="[dbo]" Table="[tblrecord]" Alias="[r]" Column="CustNumber" />

  </OrderByColumn>

  </OrderBy>

  <RelOp AvgRowSize="19" EstimateCPU="4.8E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Filter" NodeId="5" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="6.85039">

  <OutputList>

  <ColumnReference Database="[GCP_XCS]" Schema="[dbo]" Table="[tblrecord]" Alias="[r]" Column="CustNumber" />

  </OutputList>

  <Filter StartupExpression="false">

  <RelOp AvgRowSize="29" EstimateCPU="0.0285036" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Gather Streams" NodeId="6" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="6.85039">

  <OutputList>

  <ColumnReference Database="[GCP_XCS]" Schema="[dbo]" Table="[tblrecord]" Alias="[r]" Column="CustNumber" />

  <ColumnReference Database="[GCP_XCS]" Schema="[dbo]" Table="[tblimport]" Alias="" Column="CustNumber" />

  </OutputList>

  <Parallelism>

  <RelOp AvgRowSize="29" EstimateCPU="1.045E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="7" Parallel="true" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="6.82188">

  <OutputList>

  <ColumnReference Database="[GCP_XCS]" Schema="[dbo]" Table="[tblrecord]" Alias="[r]" Column="CustNumber" />

  <ColumnReference Database="[GCP_XCS]" Schema="[dbo]" Table="[tblimport]" Alias="" Column="CustNumber" />

  </OutputList>

  <NestedLoops Optimized="true">

  <OuterReferences>

  <ColumnReference Database="[GCP_XCS]" Schema="[dbo]" Table="[tblrecord]" Alias="[r]" Column="RecId" />

  </OuterReferences>

  <RelOp AvgRowSize="23" EstimateCPU="0.0338538" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="9" Parallel="true" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="6.8186">

  <OutputList>

  <ColumnReference Database="[GCP_XCS]" Schema="[dbo]" Table="[tblrecord]" Alias="[r]" Column="RecId" />

  <ColumnReference Database="[GCP_XCS]" Schema="[dbo]" Table="[tblimport]" Alias="" Column="CustNumber" />

  </OutputList>

  <NestedLoops Optimized="true" WithUnorderedPrefetch="true">

  <OuterReferences>

  <ColumnReference Database="[GCP_XCS]" Schema="[dbo]" Table="[tblimport]" Alias="" Column="Follow_Up_date" />

  <ColumnReference Column="Expr1009" />

  <ColumnReference Column="Expr1012" />

  </OuterReferences>

  <RelOp AvgRowSize="35" EstimateCPU="0.0008099" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="32396" LogicalOp="Compute Scalar" NodeId="12" Parallel="true" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1.15511">

  <OutputList>

  <ColumnReference Database="[GCP_XCS]" Schema="[dbo]" Table="[tblimport]" Alias="" Column="Follow_Up_date" />

  <ColumnReference Database="[GCP_XCS]" Schema="[dbo]" Table="[tblimport]" Alias="" Column="CustNumber" />

  <ColumnReference Column="Expr1009" />

  </OutputList>

  <ComputeScalar>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Expr1009" />

  <ScalarOperator ScalarString="dateadd(day,(-7),[GCP_XCS].[dbo].[tblimport].[Follow_Up_date] as .[Follow_Up_date])">

  <Intrinsic FunctionName="dateadd">

  <ScalarOperator>

  <Const ConstValue="(4)" />

  </ScalarOperator>

  <ScalarOperator>

  <Const ConstValue="(-7)" />

  </ScalarOperator>

  <ScalarOperator>

  <Identifier>

  <ColumnReference Database="[GCP_XCS]" Schema="[dbo]" Table="[tblimport]" Alias="" Column="Follow_Up_date" />

  </Identifier>

  </ScalarOperator>

  </Intrinsic>

  </ScalarOperator>

  </DefinedValue>

  </DefinedValues>

  <RelOp AvgRowSize="27" EstimateCPU="0.00894815" EstimateIO="1.14535" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="32396" LogicalOp="Clustered Index Scan" NodeId="13" Parallel="true" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="1.1543">

  <OutputList>

  <ColumnReference Database="[GCP_XCS]" Schema="[dbo]" Table="[tblimport]" Alias="" Column="Follow_Up_date" />

  <ColumnReference Database="[GCP_XCS]" Schema="[dbo]" Table="[tblimport]" Alias="" Column="CustNumber" />

  </OutputList>

  <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Database="[GCP_XCS]" Schema="[dbo]" Table="[tblimport]" Alias="" Column="Follow_Up_date" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[GCP_XCS]" Schema="[dbo]" Table="[tblimport]" Alias="" Column="CustNumber" />

  </DefinedValue>

  </DefinedValues>

  <Object Database="[GCP_XCS]" Schema="[dbo]" Table="[tblimport]" Index="[PK__tblimport__43A1090D]" Alias="" />

  </IndexScan>

  </RelOp>

  </ComputeScalar>

  </RelOp>

  <RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="32216" EstimateRewinds="178.989" EstimateRows="1" LogicalOp="Index Seek" NodeId="17" Parallel="true" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="5.62964">

  <OutputList>

  <ColumnReference Database="[GCP_XCS]" Schema="[dbo]" Table="[tblrecord]" Alias="[r]" Column="RecId" />

  </OutputList>

  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Database="[GCP_XCS]" Schema="[dbo]" Table="[tblrecord]" Alias="[r]" Column="RecId" />

  </DefinedValue>

  </DefinedValues>

  <Object Database="[GCP_XCS]" Schema="[dbo]" Table="[tblrecord]" Index="[Idx_Followupdate]" Alias="[r]" />

  <SeekPredicates>

  <SeekPredicate>

  <StartRange ScanType="GE">

  <RangeColumns>

  <ColumnReference Database="[GCP_XCS]" Schema="[dbo]" Table="[tblrecord]" Alias="[r]" Column="Followupdate" />

  </RangeColumns>

  <RangeExpressions>

  <ScalarOperator ScalarString="[Expr1009]">

  <Identifier>

  <ColumnReference Column="Expr1009" />

  </Identifier>

  </ScalarOperator>

  </RangeExpressions>

  </StartRange>

  <EndRange ScanType="LE">

  <RangeColumns>

  <ColumnReference Database="[GCP_XCS]" Schema="[dbo]" Table="[tblrecord]" Alias="[r]" Column="Followupdate" />

  </RangeColumns>

  <RangeExpressions>

  <ScalarOperator ScalarString="[GCP_XCS].[dbo].[tblimport].[Follow_Up_date] as .[Follow_Up_date]">

  <Identifier>

  <ColumnReference Database="[GCP_XCS]" Schema="[dbo]" Table="[tblimport]" Alias="" Column="Follow_Up_date" />

  </Identifier>

  </ScalarOperator>

  </RangeExpressions>

  </EndRange>

  </SeekPredicate>

  </SeekPredicates>

  </IndexScan>

  </RelOp>

  </NestedLoops>

  </RelOp>

  <RelOp AvgRowSize="48" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="19" Parallel="true" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831">

  <OutputList>

  <ColumnReference Database="[GCP_XCS]" Schema="[dbo]" Table="[tblrecord]" Alias="[r]" Column="CustNumber" />

  </OutputList>

  <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Database="[GCP_XCS]" Schema="[dbo]" Table="[tblrecord]" Alias="[r]" Column="CustNumber" />

  </DefinedValue>

  </DefinedValues>

  <Object Database="[GCP_XCS]" Schema="[dbo]" Table="[tblrecord]" Index="[PK_tblrecord]" Alias="[r]" TableReferenceId="-1" />

  <SeekPredicates>

  <SeekPredicate>

  <Prefix ScanType="EQ">

  <RangeColumns>

  <ColumnReference Database="[GCP_XCS]" Schema="[dbo]" Table="[tblrecord]" Alias="[r]" Column="RecId" />

  </RangeColumns>

  <RangeExpressions>

  <ScalarOperator ScalarString="[GCP_XCS].[dbo].[tblrecord].[RecId] as [r].[RecId]">

  <Identifier>

  <ColumnReference Database="[GCP_XCS]" Schema="[dbo]" Table="[tblrecord]" Alias="[r]" Column="RecId" />

  </Identifier>

  </ScalarOperator>

  </RangeExpressions>

  </Prefix>

  </SeekPredicate>

  </SeekPredicates>

  </IndexScan>

  </RelOp>

  </NestedLoops>

  </RelOp>

  </Parallelism>

  </RelOp>

  <Predicate>

  <ScalarOperator ScalarString="[GCP_XCS].[dbo].[tblrecord].[CustNumber] as [r].[CustNumber]=[GCP_XCS].[dbo].[tblimport].[CustNumber] as .[CustNumber]">

  <Compare CompareOp="EQ">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Database="[GCP_XCS]" Schema="[dbo]" Table="[tblrecord]" Alias="[r]" Column="CustNumber" />

  </Identifier>

  </ScalarOperator>

  <ScalarOperator>

  <Identifier>

  <ColumnReference Database="[GCP_XCS]" Schema="[dbo]" Table="[tblimport]" Alias="" Column="CustNumber" />

  </Identifier>

  </ScalarOperator>

  </Compare>

  </ScalarOperator>

  </Predicate>

  </Filter>

  </RelOp>

  </Sort>

  </RelOp>

  </StreamAggregate>

  </RelOp>

  </ComputeScalar>

  </RelOp>

  </Top>

  </RelOp>

  </Update>

  </RelOp>

  </QueryPlan>

  </StmtSimple>

  </Statements>

  </Batch>

  </BatchSequence>

  </ShowPlanXML>

  Please suggest

  Thanks

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  May 19, 2010 at 9:23 am

  #1169505

  Sure!

  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  -- Gianluca Sartori

• Grant Fritchey

  SSC Guru

  Points: 398882

  More actions

  May 19, 2010 at 9:35 am

  #1169522

  Please, edit the post, remove the XML. Then go back and save your execution plan as a .SQLPLAN file. You can then post the file as an attachment. From there it might be possible to help.

  Posting structure, the query, and sample code is highly recommended as well.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning