Statistics - Pro SQL Server Internals - page 61

At this point, the table dbo.Books has 1,265,000 rows. Let's add 250,000 rows to the table with the prefix 999,
as shown in Listing 3-6.
Listing 3-6. Adding rows to dbo.Books
;with Postfix(Postfix)
as
(
select 100000001
union all
select Postfix + 1
from Postfix
where Postfix < 100250000
)
insert into dbo.Books(ISBN, Title)
select
'999-0' + CONVERT(char(9),Postfix)
,'Title for ISBN 999-0' + CONVERT(char(9),Postfix)
from Postfix
option (maxrecursion 0)
Now let's run the query that selects all of the rows with such a prefix. The query is shown in Listing 3-7.
Listing 3-7. Selecting rows with prefix 999
select * from dbo.Books where ISBN like '999%' -- 250,000 rows
If you examine the execution plan of the query shown in Figure 3-7 , you will see Nonclustered Index Seek and
Key Lookup operations, even though they are inefficient in cases where you need to select almost 20 percent of the
rows from the table.
Figure 3-7. Execution plan for the query selecting rows with the 999 prefix
You will also notice in Figure 3-7 a huge discrepancy between the estimated and actual number of rows for the
index seek operator. SQL Server estimated that there are only 31.4 rows with prefix 999 in the table, even though there
are 250,000 rows with such a prefix. As a result, a highly inefficient plan is generated.