Assume that there is a table with clustered index
create table [a_table] ([key] binary(900) unique clustered);
and some data
insert into [a_table] ([key])
select top (1000000) row_number() over (order by @@spid)
from sys.all_columns a cross join sys.all_columns b;
By inspecting storage statistics of this table
select st.index_level, page_count = sum(st.page_count)
from sys.dm_db_index_physical_stats(
db_id(), object_id('a_table'), NULL, NULL, 'DETAILED') st
group by rollup (st.index_level)
order by grouping_id(st.index_level), st.index_level desc;
one can see
index_level page_count
----------- ----------
8 1
7 7
6 30
5 121
4 487
3 1952
2 7812
1 31249
0 125000
NULL 166659
that table takes 166659 pages in total.
However table scan
set nocount on;
set statistics io, time on;
declare @cnt int;
select @cnt = count(1) from [a_table];
set statistics io, time off;
produces
Table 'a_table'. Scan count 5, logical reads 484367, ...
CPU time = 1757 ms, elapsed time = 460 ms.
almost three times higher number of logical reads in comparison to space taken by table. When I examined query plan, I noticed that SqlServer used parallel index scan. And this is where first part of the question arises.
How parallel index scan is performed that makes SqlServer to do so much logical reads?
Specifying option (maxdop 1) to suppress parallelism
set nocount on;
set statistics io, time on;
declare @cnt2 int;
select @cnt2 = count(1) from [a_table] option (maxdop 1);
set statistics io, time off;
resulted to
Table 'a_table'. Scan count 1, logical reads 156257, ...
CPU time = 363 ms, elapsed time = 367 ms.
Comparing statistics for parallel and non-parallel index scan in this case leads to a conclusion that sometimes it is better to avoid parallel index scan. And this is where the second part of the question arises.
When should I worry about parallel index scan? When should it be avoided/suppressed? What are the best practices?
The above results are obtained on
Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)
