Statistics - Pro SQL Server Internals - page 73

Comparing Cardinality Estimators: Outdated Statistics
Unfortunately, in systems with non-static data, data modifications always outdate the statistics. Let's look at how
this affects cardinality estimations in both models and insert 6,554 new rows in the table, which is 10 percent of total
number of rows. This number is below the statistics update threshold; therefore, SQL Server does not update the
statistics. Listing 3-16 shows the code for achieving this.
Listing 3-16. Comparing Cardinality Estimators: Adding new rows
;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 cross join N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 rows
,N4(C) as (select 0 from N3 as T1 cross join N3 as T2) -- 256 rows
,N5(C) as (select 0 from N4 as T1 cross join N4 as T2) -- 65,536 rows
,IDs(ID) as (select row_number() over (order by (select NULL)) from N5)
insert into dbo.CETest(ID,ADate)
select ID + 65536,dateadd(day,abs(checksum(newid())) % 365,'2013-06-01')
from IDs
where ID <= 6554;
Now let's repeat our tests by running the queries from Listings 3-13, 3-14, and 3-15. Figure 3-17 illustrates the
cardinality estimation for queries from Listing 3-13, where the value is present as a key in the histogram step. As you
can see, both models estimated 203.498 rows, which is 10 percent more than previously. SQL Server compares the
number of rows in the table with the original Rows value in the statistics and adjusted the value from the EQ_ROWS
column in the 14 th histogram step accordingly.
Figure 3-17. Outdated statistics: Cardinality estimations for the value, which is a key in the histogram step
Figure 3-18 shows the cardinality estimations for the queries from Listing 3-14 when the value is not a key in the
histogram step. You can see the difference here. The new model takes the 10 percent difference in the row count into
consideration, similar to the previous example. The legacy model, on the other hand, still uses the AVG_RANGE_ROWS
value from the histogram step, even though the number of rows in the table does not match the number of rows kept
in the statistics.