Statistics - Pro SQL Server Internals - page 64

create unique clustered index IDX_MemoryGrantDemo_ID
on dbo.MemoryGrantDemo(ID);
;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.MemoryGrantDemo(ID,Col,Placeholder)
select ID, ID % 100, convert(char(100),ID)
from IDs;
create nonclustered index IDX_MemoryGrantDemo_Col
on dbo.MemoryGrantDemo(Col);
As a next step, shown in Listing 3-9, we add 656 new rows to the table with Col=1000 . This is just 1 percent of the
total table data and, as a result, the statistics are not going to be outdated. As you already know, the histogram
would not have any information about the Col=1000 value.
Listing 3-9. Cardinality Estimation and Memory Grants: Adding 656 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 N2 as T2) -- 1,024 rows
,IDs(ID) as (select row_number() over (order by (select NULL)) from N5)
insert into dbo.MemoryGrantDemo(ID,Col,Placeholder)
select 100000 + ID, 1000, convert(char(100),ID)
from IDs
where ID <= 656;
Now let's try to run two queries that select data with the predicate on the Col column using the execution plan
with a Sort operator. The code for doing this is shown in Listing 3-10. I am using a variable as a way to suppress the
result set from being returned to the client.
Listing 3-10. Cardinality Estimation and Memory Grants: Selecting data
declare
@Dummy int
set statistics time on
select @Dummy = ID from dbo.MemoryGrantDemo where Col = 1 order by Placeholder
select @Dummy = ID from dbo.MemoryGrantDemo where Col = 1000 order by Placeholder
set statistics time off
Query optimizer will be able to estimate correctly the number of rows with Col=1 . However, this is not the case
for the Col=1000 predicate. Look at the execution plans shown in Figure 3-10 .