Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

migrated from stackoverflow.com Jan 3 at 3:42

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.