September 25, 2019 at 8:01 am
Hi all,
it's the first time that I am observing the behavior that not all CPU cores are used to compress a table from HEAP to columnstore index. Instead of using all 16 cores, it fluctuates at 4-5 cores. The machine can do sustained I/O at 900MB/s but activity montitor shows only 70-80 MB/s.
The table I am currently compressing is a fact table with about 240 GByte in size, containing only of integer (4bytes) columns. A row has 104 Bytes (26 columns). The table has 2 billion records.
It's the largest table I am dealing with and I am just curious if you know situations where CREATE INDEX doesn't work in a full parallel fashion.
Thanks,
Sven
September 25, 2019 at 1:34 pm
Do you have a limit on the number of processors in use on the server? You really should, but if it's set to 0, then SQL Server is making the determination that it doesn't need them all. More likely though, it's the MaxDop limit.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 25, 2019 at 1:46 pm
another possible reason:
"The Database Engine can also reduce the degree of parallelism if the leading key column of a non-partitioned index has a limited number of distinct values or the frequency of each distinct value varies significantly."
Link:
September 25, 2019 at 5:21 pm
I am not limiting the number of threads. But I have a primary key on the fact table which is of course varies in each row :-). I have no partitions in place.
I will specify MaxDop for the index creation to the number of cores present in the machine and test it again.
Many thanks for the quick input :-).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply