Why would ISNULL improve the performance of a query?

Question

We're currently trying to speedup some queries and we've run into something which I (not a DBA just a .NET developer) cannot explain or comprehent. We're running this query on SQL Server 2005.

We have the following query (made small and simple for the sake of argument);

SELECT    
    *
FROM    
    RandomTable 
WHERE   
    MoneyColumn <> 0
GROUP BY
    SomeColumn

This query run in around three seconds, then we randomly tried to following to speed it up (shot in the dark really)

SELECT    
    *
FROM    
    RandomTable 
WHERE   
    isnull(MoneyColumn,0) <> 0
GROUP BY
    SomeColumn

This reduces the query speed to around one second..

This column has no NULL values (yet due to the database design beeing HORRIBLE) it is however NULLABLE...

Is the fact that it's NULLABLE making SQL Server do something to account for this making it slow where the ISNULL isn't beeing mentioned? I simply have no idea why the ISNULL would make it perform faster (and by such a big margin). I'd think SQL would actually have more to do when there is a ISNULL statement in the query.

Can anyone shed some light on this?

EDIT Execution plans added

With ISNULL

<?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.5000.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="9019.76" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="1.48105" StatementText="SELECT    debiteur_id, MIN(Faktuurdatum) AS OldestOpenInvoiceDate, ISNULL(SUM(Totaal_Open),0) AS TotalOpenAmount&#xD;&#xA;FROM    dbo.tbl_Faktuur &#xD;&#xA;WHERE   (Afgehandeld_NeeJa = 0 OR Afgehandeld_NeeJa IS NULL)&#xD;&#xA;AND        (ISNULL(Totaal_Open,0) &lt;&gt; 0) &#xD;&#xA;--AND        (Totaal_Open &lt;&gt; 0) &#xD;&#xA;GROUP BY debiteur_id" StatementType="SELECT">
          <StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />
          <QueryPlan DegreeOfParallelism="1" MemoryGrant="1520" CachedPlanSize="54" CompileTime="11" CompileCPU="11" CompileMemory="704">
            <RelOp AvgRowSize="23" EstimateCPU="0.000901976" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="9019.76" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1.48105">
              <OutputList>
                <ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Debiteur_ID" />
                <ColumnReference Column="Expr1003" />
                <ColumnReference Column="Expr1005" />
              </OutputList>
              <ComputeScalar>
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Column="Expr1005" />
                    <ScalarOperator ScalarString="isnull([Expr1004],($0.0000))">
                      <Intrinsic FunctionName="isnull">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Column="Expr1004" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="($0.0000)" />
                        </ScalarOperator>
                      </Intrinsic>
                    </ScalarOperator>
                  </DefinedValue>
                </DefinedValues>
                <RelOp AvgRowSize="23" EstimateCPU="0.291662" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="9019.76" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1.48014">
                  <OutputList>
                    <ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Debiteur_ID" />
                    <ColumnReference Column="Expr1003" />
                    <ColumnReference Column="Expr1004" />
                  </OutputList>
                  <ComputeScalar>
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Column="Expr1004" />
                        <ScalarOperator ScalarString="CASE WHEN [Expr1013]=(0) THEN NULL ELSE [Expr1014] END">
                          <IF>
                            <Condition>
                              <ScalarOperator>
                                <Compare CompareOp="EQ">
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Column="Expr1013" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Const ConstValue="(0)" />
                                  </ScalarOperator>
                                </Compare>
                              </ScalarOperator>
                            </Condition>
                            <Then>
                              <ScalarOperator>
                                <Const ConstValue="NULL" />
                              </ScalarOperator>
                            </Then>
                            <Else>
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Column="Expr1014" />
                                </Identifier>
                              </ScalarOperator>
                            </Else>
                          </IF>
                        </ScalarOperator>
                      </DefinedValue>
                    </DefinedValues>
                    <RelOp AvgRowSize="23" EstimateCPU="0.291662" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="9019.76" LogicalOp="Aggregate" NodeId="2" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="1.48014">
                      <OutputList>
                        <ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Debiteur_ID" />
                        <ColumnReference Column="Expr1003" />
                        <ColumnReference Column="Expr1013" />
                        <ColumnReference Column="Expr1014" />
                      </OutputList>
                      <MemoryFractions Input="1" Output="1" />
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="156794" ActualEndOfScans="1" ActualExecutions="1" />
                      </RunTimeInformation>
                      <Hash>
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Column="Expr1003" />
                            <ScalarOperator ScalarString="MIN([directpay].[dbo].[tbl_Faktuur].[Faktuurdatum])">
                              <Aggregate AggType="MIN" Distinct="false">
                                <ScalarOperator>
                                  <Identifier>
                                    <ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Faktuurdatum" />
                                  </Identifier>
                                </ScalarOperator>
                              </Aggregate>
                            </ScalarOperator>
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Column="Expr1013" />
                            <ScalarOperator ScalarString="COUNT_BIG([directpay].[dbo].[tbl_Faktuur].[Totaal_Open])">
                              <Aggregate AggType="COUNT_BIG" Distinct="false">
                                <ScalarOperator>
                                  <Identifier>
                                    <ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Totaal_Open" />
                                  </Identifier>
                                </ScalarOperator>
                              </Aggregate>
                            </ScalarOperator>
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Column="Expr1014" />
                            <ScalarOperator ScalarString="SUM([directpay].[dbo].[tbl_Faktuur].[Totaal_Open])">
                              <Aggregate AggType="SUM" Distinct="false">
                                <ScalarOperator>
                                  <Identifier>
                                    <ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Totaal_Open" />
                                  </Identifier>
                                </ScalarOperator>
                              </Aggregate>
                            </ScalarOperator>
                          </DefinedValue>
                        </DefinedValues>
                        <HashKeysBuild>
                          <ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Debiteur_ID" />
                        </HashKeysBuild>
                        <RelOp AvgRowSize="23" EstimateCPU="0.255" EstimateIO="0.634196" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="27420" LogicalOp="Index Seek" NodeId="4" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.889196">
                          <OutputList>
                            <ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Debiteur_ID" />
                            <ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Faktuurdatum" />
                            <ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Totaal_Open" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="0" ActualRows="298726" ActualEndOfScans="1" ActualExecutions="1" />
                          </RunTimeInformation>
                          <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Debiteur_ID" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Faktuurdatum" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Totaal_Open" />
                              </DefinedValue>
                            </DefinedValues>
                            <Object Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Index="[_dta_index_tbl_Faktuur_5_583009158__K13_K9_K19_K2_5]" />
                            <SeekPredicates>
                              <SeekPredicate>
                                <Prefix ScanType="EQ">
                                  <RangeColumns>
                                    <ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Afgehandeld_NeeJa" />
                                  </RangeColumns>
                                  <RangeExpressions>
                                    <ScalarOperator ScalarString="(0)">
                                      <Const ConstValue="(0)" />
                                    </ScalarOperator>
                                  </RangeExpressions>
                                </Prefix>
                              </SeekPredicate>
                            </SeekPredicates>
                            <Predicate>
                              <ScalarOperator ScalarString="isnull([directpay].[dbo].[tbl_Faktuur].[Totaal_Open],($0.0000))&lt;($0.0000) OR isnull([directpay].[dbo].[tbl_Faktuur].[Totaal_Open],($0.0000))&gt;($0.0000)">
                                <Logical Operation="OR">
                                  <ScalarOperator>
                                    <Compare CompareOp="LT">
                                      <ScalarOperator>
                                        <Intrinsic FunctionName="isnull">
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Totaal_Open" />
                                            </Identifier>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Const ConstValue="($0.0000)" />
                                          </ScalarOperator>
                                        </Intrinsic>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Const ConstValue="($0.0000)" />
                                      </ScalarOperator>
                                    </Compare>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Compare CompareOp="GT">
                                      <ScalarOperator>
                                        <Intrinsic FunctionName="isnull">
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Totaal_Open" />
                                            </Identifier>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Const ConstValue="($0.0000)" />
                                          </ScalarOperator>
                                        </Intrinsic>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Const ConstValue="($0.0000)" />
                                      </ScalarOperator>
                                    </Compare>
                                  </ScalarOperator>
                                </Logical>
                              </ScalarOperator>
                            </Predicate>
                          </IndexScan>
                        </RelOp>
                      </Hash>
                    </RelOp>
                  </ComputeScalar>
                </RelOp>
              </ComputeScalar>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

Without ISNULL

<?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.5000.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="1322.43" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.274954" StatementText="SELECT    debiteur_id, MIN(Faktuurdatum) AS OldestOpenInvoiceDate, ISNULL(SUM(Totaal_Open),0) AS TotalOpenAmount&#xD;&#xA;FROM    dbo.tbl_Faktuur &#xD;&#xA;WHERE   (Afgehandeld_NeeJa = 0 OR Afgehandeld_NeeJa IS NULL)&#xD;&#xA;--AND        (ISNULL(Totaal_Open,0) &lt;&gt; 0) &#xD;&#xA;AND        (Totaal_Open &lt;&gt; 0) &#xD;&#xA;GROUP BY debiteur_id" StatementType="SELECT">
          <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="47" CompileTime="9" CompileCPU="9" CompileMemory="528">
            <RelOp AvgRowSize="23" EstimateCPU="0.000132243" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1322.43" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.274954">
              <OutputList>
                <ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Debiteur_ID" />
                <ColumnReference Column="Expr1003" />
                <ColumnReference Column="Expr1005" />
              </OutputList>
              <ComputeScalar>
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Column="Expr1005" />
                    <ScalarOperator ScalarString="isnull([Expr1004],($0.0000))">
                      <Intrinsic FunctionName="isnull">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Column="Expr1004" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="($0.0000)" />
                        </ScalarOperator>
                      </Intrinsic>
                    </ScalarOperator>
                  </DefinedValue>
                </DefinedValues>
                <RelOp AvgRowSize="23" EstimateCPU="0.167304" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1322.43" LogicalOp="Aggregate" NodeId="1" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="0.274822">
                  <OutputList>
                    <ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Debiteur_ID" />
                    <ColumnReference Column="Expr1003" />
                    <ColumnReference Column="Expr1004" />
                  </OutputList>
                  <MemoryFractions Input="0" Output="0" />
                  <Hash>
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Column="Expr1003" />
                        <ScalarOperator ScalarString="MIN([directpay].[dbo].[tbl_Faktuur].[Faktuurdatum])">
                          <Aggregate AggType="MIN" Distinct="false">
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Faktuurdatum" />
                              </Identifier>
                            </ScalarOperator>
                          </Aggregate>
                        </ScalarOperator>
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Column="Expr1004" />
                        <ScalarOperator ScalarString="SUM([directpay].[dbo].[tbl_Faktuur].[Totaal_Open])">
                          <Aggregate AggType="SUM" Distinct="false">
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Totaal_Open" />
                              </Identifier>
                            </ScalarOperator>
                          </Aggregate>
                        </ScalarOperator>
                      </DefinedValue>
                    </DefinedValues>
                    <HashKeysBuild>
                      <ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Debiteur_ID" />
                    </HashKeysBuild>
                    <RelOp AvgRowSize="23" EstimateCPU="0.030319" EstimateIO="0.0771991" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="27420" LogicalOp="Index Seek" NodeId="2" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.107518">
                      <OutputList>
                        <ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Debiteur_ID" />
                        <ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Faktuurdatum" />
                        <ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Totaal_Open" />
                      </OutputList>
                      <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Debiteur_ID" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Faktuurdatum" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Totaal_Open" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Index="[_dta_index_tbl_Faktuur_5_583009158__K13_K9_K19_K2_5]" />
                        <SeekPredicates>
                          <SeekPredicate>
                            <Prefix ScanType="EQ">
                              <RangeColumns>
                                <ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Afgehandeld_NeeJa" />
                              </RangeColumns>
                              <RangeExpressions>
                                <ScalarOperator ScalarString="(0)">
                                  <Const ConstValue="(0)" />
                                </ScalarOperator>
                              </RangeExpressions>
                            </Prefix>
                            <EndRange ScanType="LT">
                              <RangeColumns>
                                <ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Totaal_Open" />
                              </RangeColumns>
                              <RangeExpressions>
                                <ScalarOperator ScalarString="($0.0000)">
                                  <Const ConstValue="($0.0000)" />
                                </ScalarOperator>
                              </RangeExpressions>
                            </EndRange>
                          </SeekPredicate>
                          <SeekPredicate>
                            <Prefix ScanType="EQ">
                              <RangeColumns>
                                <ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Afgehandeld_NeeJa" />
                              </RangeColumns>
                              <RangeExpressions>
                                <ScalarOperator ScalarString="(0)">
                                  <Const ConstValue="(0)" />
                                </ScalarOperator>
                              </RangeExpressions>
                            </Prefix>
                            <StartRange ScanType="GT">
                              <RangeColumns>
                                <ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Totaal_Open" />
                              </RangeColumns>
                              <RangeExpressions>
                                <ScalarOperator ScalarString="($0.0000)">
                                  <Const ConstValue="($0.0000)" />
                                </ScalarOperator>
                              </RangeExpressions>
                            </StartRange>
                          </SeekPredicate>
                        </SeekPredicates>
                      </IndexScan>
                    </RelOp>
                  </Hash>
                </RelOp>
              </ComputeScalar>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

Answer 1

The ISNULL() version makes that part of the predicate unsargable.

As well as meaning that it can't use your index as effectively (it can still seek into the leading column from the other predicate in the query) this also makes the construct more opaque to the optimiser when it comes to estimating the number of rows that will match.

Both plans have an estimated 27,420 rows going into the hash match operator but they differ in how many estimated rows come out. The faster plan estimates 9,019 and the slower one 1,322.

Both of these estimates are in fact very wrong as in reality there are 156,794 distinct values of your group by column. As a result of the bad estimates neither query is allocated enough memory to build the hash table and both need to spill to disc. The slower one is particularly memory starved and so this happens multiple times.

You could try updating your existing statistics or looking at creating some multi column statistics in order to try and get a more accurate estimate of the number of distinct group by items that will be returned and thus either a different plan or more memory allocated.