my version is:

Microsoft SQL Server 2008 (SP3) - 10.0.5538.0 (X64)   Apr  3 2015 14:50:02   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM) 

I have a query which is taking a long time. analyzing the QEP I see a large gap between the estimated number of rows and the actual number of rows. I simplified the query to just that section and the result is the same. estimated 1 row actual 6000+ rows. (the section is the basis for a join to other large tables which in stead of 1 row must join 6000+)

I have already updated statistics and recompiled the query but to no avail

how can I get sql server to create a better estimate and hence a better QEP?

the shortened query:

select *  FROM  dbo.items 
 WHERE items.net_code=1                    
       and dbo.items.item_num between 1 and 99000 and items.year_num=1971 

the QEP (in 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.1" Build="10.0.5538.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="TRIVIAL" StatementSubTreeCost="0.0032833" StatementText="SELECT * FROM [dbo].[items] WHERE [items].[net_code]=@1 AND ([dbo].[items].[item_num]&gt;=@2 AND [dbo].[items].[item_num]&lt;=@3) AND [items].[year_num]=@4" StatementType="SELECT" QueryHash="0x4C4EDD0E4C55883C" QueryPlanHash="0x3E389B754A284D59">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="1" CachedPlanSize="40" CompileTime="17" CompileCPU="17" CompileMemory="336">
            <RelOp AvgRowSize="8340" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0032833">
              <OutputList>
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="net_code" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="year_num" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_num" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="broadcast_code" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="sort_order" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_title" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_desc" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="duration" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="update_date" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="rowguid" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_program_code" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_archive_num" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="broadcast_archive_num" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="TC" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="lang_code" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="updated_by" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_broadcast_date" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="duration_time" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_TC" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="created_by" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="create_date" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_search" ComputedColumn="true" />
              </OutputList>
              <ComputeScalar>
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_search" ComputedColumn="true" />
                    <ScalarOperator ScalarString="[Expr1005]+isnull([recordings].[dbo].[items].[item_desc],CONVERT_IMPLICIT(nvarchar(max),'',0))">
                      <Arithmetic Operation="ADD">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Column="Expr1005" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Intrinsic FunctionName="isnull">
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_desc" />
                              </Identifier>
                            </ScalarOperator>
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Column="ConstExpr1003">
                                  <ScalarOperator>
                                    <Convert DataType="nvarchar(max)" Length="2147483647" Style="0" Implicit="true">
                                      <ScalarOperator>
                                        <Const ConstValue="''" />
                                      </ScalarOperator>
                                    </Convert>
                                  </ScalarOperator>
                                </ColumnReference>
                              </Identifier>
                            </ScalarOperator>
                          </Intrinsic>
                        </ScalarOperator>
                      </Arithmetic>
                    </ScalarOperator>
                  </DefinedValue>
                </DefinedValues>
                <RelOp AvgRowSize="4517" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0032832">
                  <OutputList>
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="net_code" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="year_num" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_num" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="broadcast_code" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="sort_order" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_title" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_desc" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="duration" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="update_date" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="rowguid" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_program_code" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_archive_num" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="broadcast_archive_num" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="TC" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="lang_code" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="updated_by" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_broadcast_date" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="duration_time" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_TC" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="created_by" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="create_date" />
                    <ColumnReference Column="Expr1005" />
                  </OutputList>
                  <ComputeScalar>
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Column="Expr1005" />
                        <ScalarOperator ScalarString="ltrim(isnull([recordings].[dbo].[items].[item_title],N'')+N' ')">
                          <Intrinsic FunctionName="ltrim">
                            <ScalarOperator>
                              <Arithmetic Operation="ADD">
                                <ScalarOperator>
                                  <Intrinsic FunctionName="isnull">
                                    <ScalarOperator>
                                      <Identifier>
                                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_title" />
                                      </Identifier>
                                    </ScalarOperator>
                                    <ScalarOperator>
                                      <Const ConstValue="N''" />
                                    </ScalarOperator>
                                  </Intrinsic>
                                </ScalarOperator>
                                <ScalarOperator>
                                  <Const ConstValue="N' '" />
                                </ScalarOperator>
                              </Arithmetic>
                            </ScalarOperator>
                          </Intrinsic>
                        </ScalarOperator>
                      </DefinedValue>
                    </DefinedValues>
                    <RelOp AvgRowSize="4314" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="2" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1274590">
                      <OutputList>
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="net_code" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="year_num" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_num" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="broadcast_code" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="sort_order" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_title" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_desc" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="duration" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="update_date" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="rowguid" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_program_code" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_archive_num" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="broadcast_archive_num" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="TC" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="lang_code" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="updated_by" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_broadcast_date" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="duration_time" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_TC" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="created_by" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="create_date" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="6258" ActualEndOfScans="1" ActualExecutions="1" />
                      </RunTimeInformation>
                      <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="net_code" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="year_num" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_num" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="broadcast_code" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="sort_order" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_title" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_desc" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="duration" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="update_date" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="rowguid" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_program_code" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_archive_num" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="broadcast_archive_num" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="TC" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="lang_code" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="updated_by" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_broadcast_date" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="duration_time" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_TC" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="created_by" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="create_date" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[recordings]" Schema="[dbo]" Table="[items]" Index="[PK_items]" IndexKind="Clustered" />
                        <SeekPredicates>
                          <SeekPredicateNew>
                            <SeekKeys>
                              <Prefix ScanType="EQ">
                                <RangeColumns>
                                  <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="net_code" />
                                  <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="year_num" />
                                </RangeColumns>
                                <RangeExpressions>
                                  <ScalarOperator ScalarString="[@1]">
                                    <Identifier>
                                      <ColumnReference Column="@1" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator ScalarString="[@4]">
                                    <Identifier>
                                      <ColumnReference Column="@4" />
                                    </Identifier>
                                  </ScalarOperator>
                                </RangeExpressions>
                              </Prefix>
                              <StartRange ScanType="GE">
                                <RangeColumns>
                                  <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_num" />
                                </RangeColumns>
                                <RangeExpressions>
                                  <ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[@2],0)">
                                    <Identifier>
                                      <ColumnReference Column="ConstExpr1004">
                                        <ScalarOperator>
                                          <Convert DataType="int" Style="0" Implicit="true">
                                            <ScalarOperator>
                                              <Identifier>
                                                <ColumnReference Column="@2" />
                                              </Identifier>
                                            </ScalarOperator>
                                          </Convert>
                                        </ScalarOperator>
                                      </ColumnReference>
                                    </Identifier>
                                  </ScalarOperator>
                                </RangeExpressions>
                              </StartRange>
                              <EndRange ScanType="LE">
                                <RangeColumns>
                                  <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_num" />
                                </RangeColumns>
                                <RangeExpressions>
                                  <ScalarOperator ScalarString="[@3]">
                                    <Identifier>
                                      <ColumnReference Column="@3" />
                                    </Identifier>
                                  </ScalarOperator>
                                </RangeExpressions>
                              </EndRange>
                            </SeekKeys>
                          </SeekPredicateNew>
                        </SeekPredicates>
                      </IndexScan>
                    </RelOp>
                  </ComputeScalar>
                </RelOp>
              </ComputeScalar>
            </RelOp>
            <ParameterList>
              <ColumnReference Column="@4" ParameterCompiledValue="(1971)" ParameterRuntimeValue="(1971)" />
              <ColumnReference Column="@3" ParameterCompiledValue="(99000)" ParameterRuntimeValue="(99000)" />
              <ColumnReference Column="@2" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />
              <ColumnReference Column="@1" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />
            </ParameterList>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

my version is:

Microsoft SQL Server 2008 (SP3) - 10.0.5538.0 (X64)   Apr  3 2015 14:50:02   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM) 

I have a query which is taking a long time. analyzing the QEP I see a large gap between the estimated number of rows and the actual number of rows. I simplified the query to just that section and the result is the same. estimated 1 row actual 6000+ rows. (the section is the basis for a join to other large tables which in stead of 1 row must join 6000+)

I have already updated statistics and recompiled the query but to no avail

how can I get sql server to create a better estimate and hence a better QEP?

the shortened query:

select *  FROM  dbo.items 
 WHERE items.net_code=1                    
       and dbo.items.item_num between 1 and 99000 and items.year_num=1971 

the QEP (in 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.1" Build="10.0.5538.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="TRIVIAL" StatementSubTreeCost="0.0032833" StatementText="SELECT * FROM [dbo].[items] WHERE [items].[net_code]=@1 AND ([dbo].[items].[item_num]&gt;=@2 AND [dbo].[items].[item_num]&lt;=@3) AND [items].[year_num]=@4" StatementType="SELECT" QueryHash="0x4C4EDD0E4C55883C" QueryPlanHash="0x3E389B754A284D59">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="1" CachedPlanSize="40" CompileTime="17" CompileCPU="17" CompileMemory="336">
            <RelOp AvgRowSize="8340" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0032833">
              <OutputList>
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="net_code" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="year_num" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_num" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="broadcast_code" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="sort_order" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_title" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_desc" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="duration" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="update_date" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="rowguid" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_program_code" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_archive_num" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="broadcast_archive_num" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="TC" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="lang_code" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="updated_by" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_broadcast_date" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="duration_time" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_TC" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="created_by" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="create_date" />
                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_search" ComputedColumn="true" />
              </OutputList>
              <ComputeScalar>
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_search" ComputedColumn="true" />
                    <ScalarOperator ScalarString="[Expr1005]+isnull([recordings].[dbo].[items].[item_desc],CONVERT_IMPLICIT(nvarchar(max),'',0))">
                      <Arithmetic Operation="ADD">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Column="Expr1005" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Intrinsic FunctionName="isnull">
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_desc" />
                              </Identifier>
                            </ScalarOperator>
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Column="ConstExpr1003">
                                  <ScalarOperator>
                                    <Convert DataType="nvarchar(max)" Length="2147483647" Style="0" Implicit="true">
                                      <ScalarOperator>
                                        <Const ConstValue="''" />
                                      </ScalarOperator>
                                    </Convert>
                                  </ScalarOperator>
                                </ColumnReference>
                              </Identifier>
                            </ScalarOperator>
                          </Intrinsic>
                        </ScalarOperator>
                      </Arithmetic>
                    </ScalarOperator>
                  </DefinedValue>
                </DefinedValues>
                <RelOp AvgRowSize="4517" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0032832">
                  <OutputList>
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="net_code" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="year_num" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_num" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="broadcast_code" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="sort_order" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_title" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_desc" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="duration" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="update_date" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="rowguid" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_program_code" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_archive_num" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="broadcast_archive_num" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="TC" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="lang_code" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="updated_by" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_broadcast_date" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="duration_time" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_TC" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="created_by" />
                    <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="create_date" />
                    <ColumnReference Column="Expr1005" />
                  </OutputList>
                  <ComputeScalar>
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Column="Expr1005" />
                        <ScalarOperator ScalarString="ltrim(isnull([recordings].[dbo].[items].[item_title],N'')+N' ')">
                          <Intrinsic FunctionName="ltrim">
                            <ScalarOperator>
                              <Arithmetic Operation="ADD">
                                <ScalarOperator>
                                  <Intrinsic FunctionName="isnull">
                                    <ScalarOperator>
                                      <Identifier>
                                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_title" />
                                      </Identifier>
                                    </ScalarOperator>
                                    <ScalarOperator>
                                      <Const ConstValue="N''" />
                                    </ScalarOperator>
                                  </Intrinsic>
                                </ScalarOperator>
                                <ScalarOperator>
                                  <Const ConstValue="N' '" />
                                </ScalarOperator>
                              </Arithmetic>
                            </ScalarOperator>
                          </Intrinsic>
                        </ScalarOperator>
                      </DefinedValue>
                    </DefinedValues>
                    <RelOp AvgRowSize="4314" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="2" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1274590">
                      <OutputList>
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="net_code" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="year_num" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_num" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="broadcast_code" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="sort_order" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_title" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_desc" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="duration" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="update_date" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="rowguid" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_program_code" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_archive_num" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="broadcast_archive_num" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="TC" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="lang_code" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="updated_by" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_broadcast_date" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="duration_time" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_TC" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="created_by" />
                        <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="create_date" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="6258" ActualEndOfScans="1" ActualExecutions="1" />
                      </RunTimeInformation>
                      <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="net_code" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="year_num" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_num" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="broadcast_code" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="sort_order" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_title" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_desc" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="duration" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="update_date" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="rowguid" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_program_code" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_archive_num" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="broadcast_archive_num" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="TC" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="lang_code" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="updated_by" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_broadcast_date" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="duration_time" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_TC" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="created_by" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="create_date" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[recordings]" Schema="[dbo]" Table="[items]" Index="[PK_items]" IndexKind="Clustered" />
                        <SeekPredicates>
                          <SeekPredicateNew>
                            <SeekKeys>
                              <Prefix ScanType="EQ">
                                <RangeColumns>
                                  <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="net_code" />
                                  <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="year_num" />
                                </RangeColumns>
                                <RangeExpressions>
                                  <ScalarOperator ScalarString="[@1]">
                                    <Identifier>
                                      <ColumnReference Column="@1" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator ScalarString="[@4]">
                                    <Identifier>
                                      <ColumnReference Column="@4" />
                                    </Identifier>
                                  </ScalarOperator>
                                </RangeExpressions>
                              </Prefix>
                              <StartRange ScanType="GE">
                                <RangeColumns>
                                  <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_num" />
                                </RangeColumns>
                                <RangeExpressions>
                                  <ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[@2],0)">
                                    <Identifier>
                                      <ColumnReference Column="ConstExpr1004">
                                        <ScalarOperator>
                                          <Convert DataType="int" Style="0" Implicit="true">
                                            <ScalarOperator>
                                              <Identifier>
                                                <ColumnReference Column="@2" />
                                              </Identifier>
                                            </ScalarOperator>
                                          </Convert>
                                        </ScalarOperator>
                                      </ColumnReference>
                                    </Identifier>
                                  </ScalarOperator>
                                </RangeExpressions>
                              </StartRange>
                              <EndRange ScanType="LE">
                                <RangeColumns>
                                  <ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_num" />
                                </RangeColumns>
                                <RangeExpressions>
                                  <ScalarOperator ScalarString="[@3]">
                                    <Identifier>
                                      <ColumnReference Column="@3" />
                                    </Identifier>
                                  </ScalarOperator>
                                </RangeExpressions>
                              </EndRange>
                            </SeekKeys>
                          </SeekPredicateNew>
                        </SeekPredicates>
                      </IndexScan>
                    </RelOp>
                  </ComputeScalar>
                </RelOp>
              </ComputeScalar>
            </RelOp>
            <ParameterList>
              <ColumnReference Column="@4" ParameterCompiledValue="(1971)" ParameterRuntimeValue="(1971)" />
              <ColumnReference Column="@3" ParameterCompiledValue="(99000)" ParameterRuntimeValue="(99000)" />
              <ColumnReference Column="@2" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />
              <ColumnReference Column="@1" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />
            </ParameterList>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowP

It seems that query has been auto-parameterised. Do you have forced parameterisation in use in this database?

Then, the plan is compiled for the values in your post, so the parameterisation is not likely to cause the poor estimate.

Rather, I would guess that it is a matter of correlation. You have predicates on three columns. SQL Server have histogram for each of them, and for each column it can make a decently accurate estimate how many rows the condition on the single column matches. But it has no information of correlation or lack thereof. As I recall, it assumes no correlation at all, but the correlation may be negative. For instaance, most rows from 1971 may prove to have net_code = 8.

the parameterization is simple. 

the correlation would make sense if the estimate was too high, but the estimate is 1 instead of 6000+.

looking at the data 99% of the data matches all the condition 1971. the other 2 conditions removes a few rows each.

the correlation would make sense if the estimate was too high, but the estimate is 1 instead of 6000+.

Sorry, got lost in the details.

Then again, it strikes both ways. That is, if there is a high correlation between the search conditions, the estimate will be too low. Although, the exact estimate of 1 seems spooky. If you try one column at a time, what estimates do you see?

for only net=1 estimate 589000 actual 633000 seems ok

for item between 0 and 99000  estimate      821000           actual 797000

for year=1971  estimate 1.0038   actual 6276

with all 3 conditions estimate 1 actual 6258

for year=1971  estimate 1.0038   actual 6276

So that is the creepy one.

This query should give information about the year column:

SELECT s.*, stats_date(o.object_id, s.stats_id)
FROM   sys.stats s
JOIN   sys.objects o ON s.object_id = o.object_id
WHERE  o.name = 'accountstats'
  AND  EXISTS (SELECT *
               FROM   sys.stats_columns sc
               JOIN   sys.columns c ON c.object_id = s.object_id
                                   AND c.column_id = sc.column_id
               WHERE  sc.object_id = s.object_id
                 AND  sc.stats_id = s.stats_id
                 AND  c.name = 'accountdate'
                 AND  sc.stats_column_id = 1)
FROM   sys.stats

Particularly, check stats_date and no_recompute. If they look normal, use DBCC SHOW_STATISTICS to check the histogram.

I am not sure what to make of the results:

your query:

object_id name stats_id auto_created user_created no_recompute has_filter filter_definition stats_date
1281306612 _WA_Sys_00000002_4C5F2FF4 8 1 0 0 0 NULL 2015-08-20 11:27:20.447

dbcc show_statistics

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
0 0 511399.7 0 1
1900 0 858.7517 0 1
1942 0 1.00388 0 1
1952 0 442.3872 0 1
1953 0 6089.33 0 1
1955 0 442.3872 0 1
1956 0 102.5213 0 1
1957 0 41.44932 0 1
1958 0 442.3872 0 1
1959 0 468.41 0 1
1960 0 1.00388 0 1
1961 0 102.5213 0 1
1962 0 754.6606 0 1
1964 0 1.00388 0 1
1968 0 1145.002 0 1
1969 0 2055.8 0 1
1970 0 2602.278 0 1
1971 0 1.00388 0 1
1972 0 338.2961 0 1
1973 0 8041.039 0 1
1974 0 5829.103 0 1
1975 0 6375.581 0 1
1976 0 8249.221 0 1
1977 0 7130.241 0 1
1978 0 6349.558 0 1
1979 0 6193.421 0 1
1980 0 8561.494 0 1
1981 0 10357.07 0 1
1982 0 12673.09 0 1
1983 0 15665.71 0 1
1984 0 11762.3 0 1
1985 0 12673.09 0 1
1986 0 12933.32 0 1
1987 0 12855.25 0 1
1988 0 17175.03 0 1
1989 0 18189.92 0 1
1990 0 18996.63 0 1
1991 0 17643.44 0 1
1992 0 15145.26 0 1
1993 0 17070.94 0 1
1994 0 44498.95 0 1
1995 0 31461.54 0 1
1996 0 36015.53 0 1
1997 0 38799.96 0 1
1998 0 17669.47 0 1
1999 0 27636.19 0 1
2000 0 15119.23 0 1
2001 0 15275.37 0 1
2002 0 30082.33 0 1
2003 0 15509.58 0 1
2004 0 29093.47 0 1
2005 0 13141.5 0 1
2006 0 15119.23 0 1
2007 0 14937.08 0 1
2008 0 14494.69 0 1
2009 0 1092.957 0 1
2010 0 4267.736 0 1
2011 0 14963.1 0 1
2012 0 30264.49 0 1
2013 0 49104.98 0 1
2014 0 21989.25 0 1
2015 0 16888.78 0 1
2020 0 1.00388 0 1

Statistics are recent, but the value for 1971 is obviously far away from the truth. I would guess that this is a sampling accident. What happens if you do

UPDATE STATISTICS tbl _WA_Sys_00000002_4C5F2FF48 WITH FULLSCAN

the fullscan finally helped and the query time reduced by 40%

once that was working, i found that I had duplicated a condition in the join and in the where. once I removed it from the join, the query flew. any idea why this should affect things so dramatically? 

Statistics are recent, but the value for 1971 is obviously far away from the truth. I would guess that this is a sampling accident. What happens if you do

UPDATE STATISTICS tbl _WA_Sys_00000002_4C5F2FF48 WITH FULLSCAN

Wow!  This is textbook.  The 6,000 rows for 1971 are only 0.4% of the 1,274,590 rows in the table, so a sampled statistics could easily miss most of them - and did.  And yet, that 0.4% could mean everything to an execution plan.  Interesting.

... or is it?  OP, if you restore the bad statistics (!) and run the query with the corrected join, how fast does that run?

Josh

the select took just over 30 seconds with the bad statistics. It dropped to 19 after the full scan. about a 40% gain in performance.

the select took just over 30 seconds with the bad statistics. It dropped to 19 after the full scan. about a 40% gain in performance.

Um, here's a question then, why does it even take 19 seconds?

Scanning 1.2m rows, even from disk, on modern systems, should go a lot faster than that.  Oh, but you did say that the *full* query does more joins and a lot more work.

That does put things back in perspective, even the sampled statistics scan error is in the "forgivable" range if after all the better plan is only (!) 30% better, and then it shows how scalability turns relatively small issues into bigger ones.

Josh

• Edited by JRStern 14 hours 28 minutes ago

once that was working, i found that I had duplicated a condition in the join and in the where. once I removed it from the join, the query flew. any idea why this should affect things so dramatically? 

I assume that you are talking about your original query, not the one you posted here? Obviously, it's kind of difficult to comment on query I have not seen. But permit me to note that even if it was apparent to you, that the condition was redundant, that may not have been the case for the optimizer, why it had to cater for the condition in the plan, and it plan it chose without that extra condition was not valid when this condition was present.