I took a script from web to demonstrate column store index. It creates a table and inserts test data.
use adventureworks2012;
go
drop table sales2;
create table sales2 (
[id] int not null identity (1,1),
[date] date not null,
itemid smallint not null,
price money not null,
quantity numeric(18,4) not null)
;
go
create unique clustered index cdx_sales2_date_id on sales2 ([date], [id]);
go
set nocount on;
go
--insert sales2
declare @i int = 0;
declare @date datetime2;
begin transaction;
while @i < 1000000
begin
set @date = dateadd(day, @i /250000.00, '20110712');
insert into sales2 ([date], itemid, price, quantity)
values (@date, rand()*10000, rand()*100 + 100, rand()* 10.000+1);
set @i += 1;
if @i % 10000 = 0
begin
raiserror (N'Inserted %d', 0, 1, @i);
commit;
begin tran;
end
end
commit;
go
create columnstore index cs_sales2_price on sales2 ([date], price, quantity);
go
ok so far... then I checked allocated Pages:
select t.name, au.* from sys.system_internals_allocation_units au
join sys.system_internals_partitions p
join sys.tables t on p.object_id = t.object_id
on p.partition_id = au.container_id
where (
p.object_id = object_id('sales2'))
and total_pages >0
order by t.name, au.type
go
result:
name type total_pages
sales2 1 4353
sales2 2 1002
set statistics io on;
go
select [date], sum(price*quantity) from sales2 with (index(cs_sales2_price)) where date = '20110713' group by [date]
go
set statistics io off;
go
Result:
Table 'sales2'. Scan count 1, logical reads 1355, physical reads 0, read-ahead reads 4706,
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
WHAT?? 1355 logical reads? Why are there 353 reads more than allocated pages?
Thanks a lot!