I'm building a Columnstore index right now. The output from sp_whoisactive:
This is a dev machine. Nothing else is running. The server has 8GB of memory available to it. The CPU is nearly idle. The build seems to be in the global dictionary sampling phase because sqlservr.exe is doing random disk IO and writing almost nothing.
Does this SLEEP_TASK indicate that I can take action to make the index build go faster? Why is the build "sleeping" sometimes?
This is SQL Server 2014 SP1.
SLEEP_TASK Idle Generic Sleep. SLEEP_TASK is a general wait, used for all sorts of things where a more specific wait type doesn't exist, but it does crop up with Hashing operations (join/aggregate) that spill to tempdb, and occasionally at an exchange (Parallelism operator) where a consumer is waiting on something that isn't a normal CXPACKET or EXECSYNC wait. I would check for Hash spills first SLEEP_TASK Wait Type in SQL Server - What does it indicate?
To help the global dictionary be a better reflection of the data in the entire table, the column store index build process has been changed in SQL Server 2014.
It now first kicks off on a single thread that reads a sampled selection of data pages from the entire table in order to form a global dictionary for each column; after that the second phase starts to use all available threads to build the actual columnstore index.
The number of rows that SQL Server 2014 will sample when building the global dictionary depends on the total number of rows in the table (“cardinality”).
Since parallelism operator is involved, that where the SLEEP_TASK wait type is introduced.
You should ignore that wait_type (paul randal's script ignores it) until you have addressed all the other important wait types.
Below is excerpt from the research paper - (warning : pdf) :
Read up this excellent series : Stairway To Columnstore Indexes - By Hugo Kornelis
