STATISTICS IO for parallel index scan

7 votes

1 answer

537 views

sql-server sql-server-2014 database-internals parallelism scan

                          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)

                        

Asked by i-one (2374 rep)

Oct 27, 2016, 07:39 AM
Last activity: Apr 30, 2019, 06:48 PM

STATISTICS IO for parallel index scan

Related Questions