6

I'm building a Columnstore index right now. The output from sp_whoisactive:

enter image description here

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.

3

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) :

enter image description here

Read up this excellent series : Stairway To Columnstore Indexes - By Hugo Kornelis

3

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?

  • Hm I don't believe CS index build plans contain hash operations. Maybe there's an exchange in there. If you're right then SLEEP_TASK would be meaningless. – usr Jun 17 '15 at 19:17
  • The above is Paul White's definition of the SLEEP_TASK wait type. – stacylaray Jun 17 '15 at 19:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.