Why does Statement1 taking 30% more (in duration) than Statement2; clearly cpu & reads are smaller?
Statement 1: Duration 132ms, logical read 4.7k
Statement 2: Duration 100ms, logical read 6.1k
Query: Simple aggregate -> select count(*) from table
Yes both are different indexes in the sense that they're in different tables. Structure looks like the following:
declare @c as int = 1, @n int = 4262433; create table ##table1 (id int not null, data1 int, data2 int ); create table ##table2 (id int not null, data1 smallint, data2 int ); while @c <= @n begin insert into ##table1 select @c, round(RAND()*36129-30000,0,0), round(RAND()*268275,0,0); select @c = @c + 1 end insert into ##table2 select * from ##table1; create nonclustered index idx_data1 ON ##table1(data1); create nonclustered index idx_data2 ON ##table1(data2); create nonclustered index idx_data1 ON ##table2(data1); create nonclustered index idx_data2 ON ##table2(data2); --set statistics time on; select COUNT(*) from ##table1 option(recompile) select COUNT(*) from ##table2 option(recompile) --drop table ##table1; --drop table ##table2;
Please note that the code doesn't generate the plan possibly because of difference in data ditribution (# of data page is significantly different, don't know why).
and see what the number of reads are. My first thought is that SQL reads from the hard drive in extents (8 pages of 8k each page). So, with the smaller data types, you are able to store more data per page. Therefore, the number of physical reads would be less. As a record of int, int, int uses, for simplistic sake, uses 4+4+4=12 bytes, with a page holding roughly 8192/12 = 682 records per page. Now the second table uses int, smallint, it or 4+2+4=10 bytes, or 8192/10 = 819 records per page. So for each physical read at the data level, an extent (assuming 100% fill) would contain 5456 records versus 6552 records. Therefore, the use of the smallint would allow 1096 additional records to be read per physical read. Thus, explaining why you get better performance…the second query has less physical reads. This should be shown in the message window from the set statistics io on command.